遍历工作表,行,列 [英] Iterate over Worksheets, Rows, Columns
问题描述
我想使用openpyxl
以这种方式打印python中特定列的所有数据(所有行);
I want to print all data (all rows) of a specific column in python using openpyxl
I am working in this way;
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)
但是它给出了
如果行< 1或<列1: TypeError:不可排序的类型:tuple()< int()
if row < 1 or column < 1: TypeError: unorderable types: tuple() < int()
因为我要在row=i
中进行迭代.如果我使用sheet.cell(row=4, column=2).value
,它将打印单元格的值.但是我该如何遍历所有文档?
Because i am iterating in row=i
. If I use sheet.cell(row=4, column=2).value
it print the value of cell. But how can I iterate over all document?
编辑1
在一些研究中,发现可以使用工作表名称获取数据. Sheet 1
存在于.xlsx
文件中,但其数据无法打印.这段代码有什么问题吗?
On some research, it is found that data can be get using Sheet Name. The Sheet 1
exists in the .xlsx
file but its data is not printing. Any problem in this code?
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
推荐答案
阅读 OpenPyXL文档
对workbook
中所有worksheets
的迭代,例如:
Iteration over all worksheets
in a workbook
, for instance:
for n, sheet in enumerate(wb.worksheets):
print('Sheet Index:[{}], Title:{}'.format(n, sheet.title))
输出:
Sheet Index:[0], Title: Sheet
Sheet Index:[1], Title: Sheet1
Sheet Index:[2], Title: Sheet2
在一个工作表中的所有rows
和columns
上进行迭代:
Iteration over all rows
and columns
in one Worksheet:
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) )
输出:
<Cell Sheet.A1>: cell.value=²234
<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
对一个 row
的所有columns
进行迭代,例如row==2
:
Iteration over all columns
of one row
, for instance 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) )
输出:
<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
全部 rows
上的迭代,仅column
2 :
Iteration over all rows
, only column
2:
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))
输出:
<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
使用Python:3.4.2测试-openpyxl:2.4.1-LibreOffice:4.3.3.2
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2
这篇关于遍历工作表,行,列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!