Python 迭代工作表、行、列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42974450/
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
Iterate over Worksheets, Rows, Columns
提问by Humty
I want to print all data (all rows) of a specific column in python using openpyxlI am working in this way;
我想使用openpyxl这种方式在 python 中打印特定列的所有数据（所有行）；
from openpyxl import load_workbook
workbook = load_workbook('----------/dataset.xlsx')
sheet = workbook.active
for i in sheet:
print(sheet.cell(row=i, column=2).value)
But it gives
但它给
if row < 1 or column < 1: TypeError: unorderable types: tuple() < int()
如果行 < 1 或列 < 1: TypeError: unorderable types: tuple() < int()
Because i am iterating in row=i. If I use sheet.cell(row=4, column=2).valueit print the value of cell. But how can I iterate over all document?
因为我在row=i. 如果我使用sheet.cell(row=4, column=2).value它打印单元格的值。但是如何遍历所有文档？
Edit 1
编辑 1
On some research, it is found that data can be get using Sheet Name. The Sheet 1exists in the .xlsxfile but its data is not printing. Any problem in this code?
在一些研究中，发现可以使用 Sheet Name 获取数据。该Sheet 1中存在的.xlsx文件，但它的数据不打印。这段代码有什么问题吗？
workbook = load_workbook('---------------/dataset.xlsx')
print(workbook.get_sheet_names())
worksheet =workbook.get_sheet_by_name('Sheet1')
c=2
for i in worksheet:
d = worksheet.cell(row=c, column=2)
if(d.value is None):
return
else:
print(d.value)
c=c+1
回答by stovfl
Read the OpenPyXL Documentation
Iteration over all worksheetsin a workbook, for instance:
迭代worksheetsa 中的所有内容workbook，例如：
for n, sheet in enumerate(wb.worksheets):
print('Sheet Index:[{}], Title:{}'.format(n, sheet.title))
Output:
Sheet Index:[0], Title: Sheet Sheet Index:[1], Title: Sheet1 Sheet Index:[2], Title: Sheet2
输出：
Sheet Index:[0], Title: Sheet Sheet Index:[1], Title: Sheet1 Sheet Index:[2], Title: Sheet2
Iteration over all rowsand columnsin oneWorksheet:
迭代所有rows，并columns在一个工作表：
worksheet = workbook.get_sheet_by_name('Sheet')
for row_cells in worksheet.iter_rows():
for cell in row_cells:
print('%s: cell.value=%s' % (cell, cell.value) )
Output:
输出：
<Cell Sheet.A1>: cell.value=2234
<Cell Sheet.B1>: cell.value=12.5
<Cell Sheet.C1>: cell.value=C1
<Cell Sheet.D1>: cell.value=D1
<Cell Sheet.A2>: cell.value=1234
<Cell Sheet.B2>: cell.value=8.2
<Cell Sheet.C2>: cell.value=C2
<Cell Sheet.D2>: cell.value=D2
Iteration over all columnsof onerow, for instance row==2:
迭代所有columns的一个row，例如row==2：
for row_cells in worksheet.iter_rows(min_row=2, max_row=2):
for cell in row_cells:
print('%s: cell.value=%s' % (cell, cell.value) )
Output:
输出：
<Cell Sheet.A2>: cell.value=1234
<Cell Sheet.B2>: cell.value=8.2
<Cell Sheet.C2>: cell.value=C2
<Cell Sheet.D2>: cell.value=D2
Iteration over allrows, only column2:
全部迭代rows，只有column2 次：
for col_cells in worksheet.iter_cols(min_col=2, max_col=2):
for cell in col_cells:
print('%s: cell.value=%s' % (cell, cell.value))
Output:
输出：
<Cell Sheet.B1>: cell.value=12.5
<Cell Sheet.B2>: cell.value=8.2
<Cell Sheet.B3>: cell.value=9.8
<Cell Sheet.B4>: cell.value=10.1
<Cell Sheet.B5>: cell.value=7.7
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2
用 Python 测试：3.4.2 - openpyxl:2.4.1 - LibreOffice：4.3.3.2
回答by Chanda Korat
Try this,
尝试这个，
from openpyxl import load_workbook
workbook = load_workbook('----------/dataset.xlsx')
sheet = workbook.active
row_count = sheet.max_row
for i in range(row_count):
print(sheet.cell(row=i, column=2).value)
回答by Martlark
This code will read a sheet as if it was a csv and populatte a list of dictionaries in resultusing the first row as the column titles.
此代码将读取工作表，就好像它是一个 csv 并result使用第一行作为列标题填充字典列表。
from openpyxl import load_workbook
result = []
wb = load_workbook(filename=file_name)
sheet = wb.active
col_count = sheet.max_column
column_names = {}
for c in range(1, col_count):
heading = sheet.cell(row=1, column=c).value
if not heading:
col_count = c
break
column_names[c] = heading
for r, row_cells in enumerate(sheet.iter_rows(2), 2):
row = {}
for c in range(1, col_count):
value = sheet.cell(row=r, column=c).value
if type(value) == datetime:
value = value.strftime('%Y-%m-%d')
row[column_names[c]] = value
result.append(row)
