如何使用 Python 从 Excel 文件中读取一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36235559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Python to read one column from Excel file?
提问by Sirui Li
I want to read the data in one column in excel, here is my code:
我想在excel中读取一列中的数据，这是我的代码：
import xlrd
file_location = "location/file_name.xlsx"
workbook = xlrd.open_workbook(file_location)
sheet = workbook.sheet_by_name('sheet')
x = []
for cell in sheet.col[9]:
if isinstance(cell, float):
x.append(cell)
print(x)
It is wrong because there is no method in sheet called col[col.num], but I just want to extract the data from column 8 (column H), what can I do?
这是错误的，因为工作表中没有名为col[col.num]的方法，但我只想从第8列（H列）中提取数据，我该怎么办？
回答by Deusdeorum
If you're not locked with xlrd I would probably have used pandas instead which is pretty good when working with data from anywhere:
如果你没有被 xlrd 锁定，我可能会使用 Pandas，这在处理来自任何地方的数据时非常好：
import pandas as pd
df = pd.ExcelFile('location/test.xlsx').parse('Sheet1') #you could add index_col=0 if there's an index
x=[]
x.append(df['name_of_col'])
You could then just write the new extracted columns to a new excel file with pandas df.to_excel()
然后，您可以使用 Pandas 将新提取的列写入新的 excel 文件 df.to_excel()
回答by Roee Aharoni
You can get the values of the 8th column like this:
您可以像这样获取第 8 列的值：
for rownum in range(sheet.nrows):
x.append(sheet.cell(rownum, 7))
回答by John Y
By far the easiest way to get all the values in a column using xlrdis the col_values()worksheet method:
到目前为止，使用工作表方法获取列中所有值的最简单方法xlrd是col_values()：
x = []
for value in sheet.col_values(8):
if isinstance(value, float):
x.append(value)
(Note that if you want column H, you should use 7, because the indices start at 0.)
（请注意，如果您想要 H 列，则应使用 7，因为索引从 0 开始。）
Incidentally, you canuse col()to get the cell objectsin a column:
顺便说一句，您可以使用col()来获取列中的单元格对象：
x = []
for cell in sheet.col(8):
if isinstance(cell.value, float):
x.append(cell.value)
The best place to find this stuff is the official tutorial(which serves as a decent reference for xlrd, xlwt, and xlutils). You could of course also check out the documentationand the source code.
找到这个东西最好的地方是官方教程（其作为一个体面的基准xlrd，xlwt和xlutils）。您当然也可以查看文档和源代码。
回答by Irfanullah
I would recommend to do it as:
我建议这样做：
import openpyxl
fname = 'file.xlsx'
wb = openpyxl.load_workbook(fname)
sheet = wb.get_sheet_by_name('sheet-name')
for rowOfCellObjects in sheet['C5':'C7']:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate, cellObj.value)
Result:
C5 70.82
C6 84.82
C7 96.82
结果：
C5 70.82
C6 84.82
C7 96.82
Note: fnamerefers to excel file, get_sheet_by_name('sheet-name')refers to desired sheet and in sheet['C5':'C7']ranges are mentioned for columns.
注意：fname指的是 excel 文件，get_sheet_by_name('sheet-name')指的是所需的工作表，并且在sheet['C5':'C7']中提到了列的范围。
Check out the linkfor more detail. Code segment taken from here too.
查看链接了解更多详情。代码段也取自这里。
回答by Rahul
XLRD is good, but for this case you might find Pandas good because it has routines to select columns by using an operator '[ ]'
XLRD 很好，但对于这种情况，您可能会发现 Pandas 很好，因为它具有使用运算符“[]”选择列的例程
Complete Working code for your context would be
您的上下文的完整工作代码将是
import pandas as pd
file_location = "file_name.xlsx"
sheet = pd.read_excel(file_location)
print(x['column name of col 9'])
