Python Data Persistence – Read Data from Worksheet

by

Python Data Persistence – Read Data from Worksheet

To read data from an existing Excel document, we need to load it with the load_workbook ( ) function.

>>> from openpyxl import load_workbook 
>>> wb=load_workbook (filename= ' test. xlsx ' )

Set the desired worksheet as active and retrieve the value of any cell.

Example

>>> sheet1.cell (row=1, column=1).value
'Hello World'
>>> #or
>>> sheetl['A1'] . value
'Hello World'

Following script writes data in a list object, each item being a tuple comprising of ProductID, name, and price.

Example

#saveworkbook.py 
from openpyxl import Workbook 
wb = Workbook( ) 
sheet1 = wb.active 
sheet1.title='PriceList' 
sheet1.cell(column=l, row=l, value='Pricelist') pricelist=[('ProductID', 'Name', 'Price'), 
                          (1,'Laptop',25000),(2, 'TV',40000), 
                          (3, 'Router' ,2000) , (4, 'Scanner',5000) , 
                          (5, 'Printer 1,9000) , (6, 'Mobile',15000)] 
        for col in range(1,4): 
                  for row in range(1,7): 
                     sheet1.cell(column=col, row=1+row, 
value=pricelist[row-1] [col-1]) 
wb. save (filename = "test.xlsx")

The Excel document in the current directory looks like this: (figure 10.3)

Python Data Presistence - Read Data from Worksheet chapter 10 img 1

Let us find out how to perform certain formatting actions on worksheet data.