Humty Humty -4 years ago 248
Python Question

Print xlsx data using openpyxl in 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)


But it gives


if row < 1 or column < 1:
TypeError: unorderable types: tuple() < int()


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?

Edit 1

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

Answer Source

Read the OpenPyXL Documentation

Iteration over all worksheets in a workbook, for instance:

for sheet in wb.worksheets:
    print('sheet.title=%s' % sheet.title)  

Output:

sheet.title=Sheet  
sheet.title=Sheet1  
sheet.title=Sheet2  

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) )

Output:

<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  

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) )  

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 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))

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download