OpenPyXL Tutorial | Read and Write an Excel File

All About OpenPyXL

What is OpenPyXL

OpenPyXL is a python library created to read or write Excel files. This library supports Excel 2010 xlsx, xlsm, xltx, and xltm files.

How to Install

To install any library, we should always use pip, which is the case here. Open your shell/command prompt and simply execute the below command.

pip install openpyxl

Create a Simple WorkBook

If you are familiar with the concept of an Excel file you know that an Excel file is a workbook comprises of n number of sheets. Now, the good thing here is to create a workbook we don't have to make a file like shown below.

from openpyxl import Workbook
wb = Workbook()

Create a Sheet

Now, that we have seen how to create a workbook next step is to create sheets. Like I said before, we can create n number of sheets within a workbook. So, after you have a workbook object with you, the next step is shown below.

# this will create the sheet at the end as that is by default if we don't provide an index to it.

sh1 = wb.create_sheet("Sheet_1") 

sh2 = wb.create_sheet("Sheet_2", 0) # this will create the sheet at the first index

Now, you might be thinking that giving the index/position makes sense but why are we giving the sheet a name. What's the benefit there? The answer is simple, with a name it's easier to call out a sheet like shown below.

sh1 = wb["Sheet_1"]

Reading a Sheet

Now, to read a sheet, we first need to load the workboot in which we have our respective sheets. That's where load_workbook method comes into play. We can have multiple sheets in a workbook, but we will have one active sheet always. So, if have to read that sheet then we can get that sheet using workbook.active property.

import openpyxl as op

wb = op.load_workbook('put location of your file')

sh = wb.active

for row in sh.iter_rows():
    for cell in row:
        print(cell.value)

Writing to a Sheet

Similar to what we did to read a sheet, we have to do the same thing first and then we will write data to the sheet. 

Now, let's assume that we have the below excel data with us and what we want is to modify the age of Dulce Abril to 33. 

To do this, we first have to identify in which row and column or to be precise in which cell we have to change the data. As we can see in the above Excel file that this data is in Row 2 and Column 6 which is going to be our cell F2. 

Now that we have got the cell information let's take a look at the below code.

import openpyxl as op

wb = op.load_workbook('C:/Temp/test.xlsx') # opened our Excel file where we have to modification

sh = wb.active # got the sheet where we have to make changes

c = sh.cell(row=2, column=6) # got the cell

c.value = 33 # updated the value

wb.save('C:/Temp/updated_test.xlsx') # saved the workbook