Read and write excel file in python using openpyxl.

 Spreadsheets in Excel are something you will have to deal with at some point. You will be expected to learn how to work with spreadsheets, either because your manager likes them or because marketing needs them, and this is where learning openpyxl comes in handy.

In most of the automation we need reading and writing of a file, generally many times we use excel to store the data. Consider task  you want to insert data into web page and do some operations on the web page one by one from Excel. We need a file reader library which can read the file line by line. Once reader is setup we can use any looping technique to traverse through all the data in the file. Here we are using openpyxl library. 

Read write excel using Python

Install the Library: Use the below code to install the library in command prompt

pip install openpyxl

Steps:

Steps to read a File

1. Get the workbook in python by providing file path.

2. Specify the Sheet Name which you want read from workbook.

3. Access the cell using column and row indexes.

Code:

#import the library
import openpyxl

Provide the file path with excel name. Use raw text indicator "r" in front of file path.

#Provide the file path of work book which want to read
my_wb = openpyxl.Workbook(r"filepath\Book1.xlsx")

Provide the sheet name as shown below

#Provide the sheet name which is in the work book for reading

my_sheet = my_wb["SheetName"]

To read the cell we are proving row and column values. If you want to read "C2" cell from excel provide the row=2 and column=3. Store the read value in variable.

var1=my_sheet.cell(row = 2, column = 3).value

Print the value which we read from cell "C2"

print(var1)

You can write the value to any cell in excel using below code. For example I want to write "Automation" in cell "D5" . We need to provide the row=5 and column=4

my_sheet.cell(row = 5, column = 4).value="Automation"

After writing data into sheet we need to save the workbook otherwise it will be lost.

my_wb.save(r"filepath\Book1.xlsx")

Here we are just printing the value for understanding how it works. In real time we pass this data to some other tool or web page as input.

Below is full code.

#import the library
import openpyxl

#Provide the file path of work book which want to read
my_wb = openpyxl.Workbook("File path")
#Provide the sheet name which is in the work book for reading
my_sheet = my_wb["SheetName"]
#Reading data from cell B3
var1=my_sheet.cell(row = 2, column = 3).value
print(var1)
#write data into cell D5
my_sheet.cell(row = 5, column = 4).value="Automation"
#Save the workbook
my_wb.save(r"filepath\Book1.xlsx")

In upcoming article we will explain how to traverse through all the rows.