Python Openpyxl Read multiple cells














































Python Openpyxl Read multiple cells



Python Openpyxl Read multiple cells

Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and modify Excel files.

For example, user might have to go through thousands of rows and pick out few handful information to make small changes based on some criteria. Using Openpyxl module, these tasks can be done very efficiently and easily.

We can read the values from the multiple cells. In the following example, we have marks.xlsx named excel file and we will read each cell of file using the range operator. Let's have a look at the following program:

Program:

import openpyxl

wb = openpyxl.load_workbook('marks.xlsx')  

sheet = wb.active 

cells = sheet['A1','B7']  

for i1,i2 in cells:  

    print("{0:8} {1:8}".format(i1.value,i2.value))  

 

Output:

Student_name        Marks

Tony Stark           47

Loki                 59

Oddin                73

Nick Fury            62

Samaul               75

Peter Parkar         80

Openpyxl Iterate by rows:

The openpyxl provides the iter_row() function, which is used to read data corresponding to rows. Consider the following example:

Program:

from openpyxl import Workbook  

  

wb = Workbook()  

sheet = wb.active  

  

rows = (  

    (90464844),  

    (81303216),  

    (239587,27),  

    (65128953),  

    (42814044),  

    (34517642)  

)  

  

for row in rows:  

    sheet.append(row)  

  

for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=4):  

    for cell in row:  

        print(cell.value, end=" ")  

    print()  

  

book.save('iter_rows.xlsx')  

Output:

90 46 48 44

81 30 32 16

23 95 87 27

65 12 89 53

42 81 40 44

34 51 76 42

Openpyxl Iterate by Column:

The openpyxl provides iter_col() method which return cells from the worksheet as columns. Consider the following example:

Program:

from openpyxl import Workbook  

  

book = Workbook()  

sheet = book.active  

  

rows = (  

     (90464844),  

     (81303216),  

     (23958727),  

     (65128953),  

     (42814044),  

     (34517642)  

)  

for row in rows:  

    sheet.append(row)  

  

for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):  

    for cell in row:  

        print(cell.value, end=" ")  

    print()  

  

book.save('iterbycols.xlsx')  

Output:

90 81 23 65 42 34

46 30 95 12 81 51

48 32 87 89 40 76

 


Comments