Guide To OpenPyXL: A Python Module For Excel

OpenPyXL is a Python module for interacting with Excel(.xlxs) files. But can’t you do that with Pandas? Yes, you can, but Pandas does support charts, formulae or images. OpenPyXL enables data scientists and data analysts to perform all kinds of operations on Excel workbooks:

• Read and write cells.
• Create and rename sheets.
• Draw charts.
• Add formulae.
• Create pivot tables.
• Insert images, and even do formatting and styling.

 Another notable thing about OpenPyXL is that it doesn’t require an install of Microsoft Excel. 

Working with Excel Files in Python 

Excel files that we will work with are called workbooks; these contain a minimum of one sheet and a maximum of tens of sheets. Each of the sheets consists of rows starting from 1 and columns starting from A. The intersections of these rows and columns form cells that can store data of any type. 

Install OpenPyXL form PyPI:

pip install openpyxl

Workbook & Worksheet

You can either read an existing .xlxs file using the load_workbook() method or create a new Workbook object. Let’s start by creating a new workbook: 

 from openpyxl import Workbook, load_workbook
 wb = Workbook() 

New Workbooks are always created with at least one sheet; you can access the current sheet using the active property:

 initial_sheet = wb.active
 print(initial_sheet) 

<Worksheet "Sheet">

You can also access the worksheets using the dictionary index, workbook[“sheet_name”], format. The name of a sheet can be changed using the title property:

initial_sheet.title = "Initial Sheet"

You can create additional sheets using OpenPyXL’s create_sheet() method which takes two optional arguments: the name of the sheet and its index:

 sheet_4 = wb.create_sheet("Sheet 4") # created as the last sheet
 sheet_1 = wb.create_sheet("Sheet 1", 0) # created at first position
 sheet_3 = wb.create_sheet("Sheet 3", -1) # inserted at the second last position 

All the sheets in the Workbook can be listed using the sheetnames property: 

print(wb.sheetnames)

['Sheet 1', 'Intial Sheet', 'Sheet 3', 'Sheet 4']

Writing to and reading from cells

When a new worksheet is created in OpenPyXL, it has no cells; the cells are created when they are accessed. You can access the individual cells of a sheet using the keys of the worksheet or use the row and column notation using cell() method: 

 initial_sheet['B3'] = 4
 initial_sheet.cell(row=4, column=2, value=10)
 # OR
 # initial_sheet.cell(row=4, column=2).value = 10   

Another way to add write data is to write rows using the append() method: 

 sheet_1.title = "Spells"
 sheet_1.append(["Wingardium", "Leviosa"])
 sheet_1.append(["Expecto", "Patronum"])
 sheet_1.append(["Alarte", "Ascendare"])
 sheet_1.append(["Sectumsempra"])
 sheet_1.append(["Avada", "Kedavra"]) 

There are three ways to read from multiple cells in OpenPyXL. The first method is the range operator. Let’s try reading each cell of the ‘Spells’ sheet using the range operator:

 cells = sheet_1["A1" : "B4"]
 for c1,c2 in cells:  
     print(f"{c1.value} {c2.value}")   

 Wingardium Leviosa
 Expecto Patronum
 Alarte Ascendare
 Sectumsempra None 

The second way of reading multiple cells is by iterating over the rows using iter_row() function; which reads data one row at a time:

 for row in sheet_1.iter_rows(min_row=1, min_col=1, max_row=5, max_col=2):  
     for cell in row:  
         print(cell.value, end=" ")  
     print()   

 Wingardium Leviosa
 Expecto Patronum
 Alarte Ascendare
 Sectumsempra None 

Similarly you can also read the data as columns using the  iter_col() method:

 for row in sheet_1.iter_cols(min_row=1, min_col=1, max_row=5, max_col=2):  
     for cell in row:  
         print(cell.value, end=" ")  
     print()   

 Wingardium Expecto Alarte Sectumsempra Avada 
 Leviosa Patronum Ascendare None Kedavra 

Saving Workbooks

Whatever you have done so far has been saved in a Workbook object in memory, but no .xlxs file has been created. To access the workbook outside OpenPyXL, you need to save it:

wb.save('example.xlsx')

Drawing charts

Excel uses charts to visualize data. To create a chart in our OpenPyXl worksheets, we first need to define the chart type such BarChart, LineChart, etc. We also need to define the data to be used for the chart using a Reference object. Let’s writing some student performance data and plot a bar chart:

 from openpyxl.chart import BarChart, Reference  
 sheet = wb['Sheet 3']
 sheet.title = "Performance"
 data = [  
     ["Roll no", "Spell Making", " Astronomy and Herbology"],  
     ["1", 86, 75],  
     ["2", 42, 60],  
     ["3", 66, 43],  
     ["4", 82, 97],  
     ["5", 84, 63],  
     ["6", 73, 54],  
     ["7", 92, 86],  
 ]  
 for row in data:  
     sheet.append(row)  
 chart = BarChart()  
 values = Reference(worksheet=sheet,  
                  min_row=1,  
                  max_row=8,  
                  min_col=2,  
                  max_col=3)  
 chart.add_data(values, titles_from_data=True)  
 sheet.add_chart(chart, "E2")  
 wb.save("example_chart.xlsx")  

You can learn more about the charts available in OpenPyXL here.

Using Formulae

To add a formula to a cell in OpenPyXL, you simply need to define the formula as a string in the required cell. Let’s try calculating the average marks for the two subjects:

 sheet = wb['Sheet 4']
 sheet.title = "Formula"
 data = [  
     ["Roll no", "Spell Making", " Astronomy and Herbology"],  
     ["1", 86, 75],  
     ["2", 42, 60],  
     ["3", 66, 43],  
     ["4", 82, 97],  
     ["5", 84, 63],  
     ["6", 73, 54],  
     ["7", 92, 86], 
     ["Average"]
 ] 
 for row in data:  
     sheet.append(row)  
 cell = sheet.cell(row=9, column=2)  
 cell.value = "=AVERAGE(B1:B8)"   
 cell = sheet.cell(row=9, column=3)  
 cell.value = "=AVERAGE(C1:C8)"  
 wb.save('formula.xlsx')   

Adding Images

Although images are not as common as charts or formulae, sometimes they can be used to enrich worksheets and make them more visually appealing. To work with images in OpenPyXL, you’ll need to install the Pillow module.  OpenPyXL provides the add_image() method for adding images to a specified cell:

 from openpyxl import load_workbook  
 from openpyxl.drawing.image import Image  
 initial_sheet = wb['Intial Sheet']
 logo = Image("AIM-Logo.jpg")  
 logo.height = 250
 logo.width = 250  
 initial_sheet.add_image(logo, "E2")  
 wb.save("logo.xlsx")   

The code for the above implementation can be found in a Colab notebook here.

To learn more about OpenPyXL, refer to the official documentation.