DEV Community

ADEMOLA OGUNMOKUN
ADEMOLA OGUNMOKUN

Posted on

Basics of openpyxl : A Python module for Excel files

This is an introduction to openpyxl, a Python module built for reading and writing Excel document files.
Most often, we would like to automate many of our repetitive tasks on Excel using a programming language.

Python's openpyxl module makes it very easy for us to access an excel file.
Note that Openpyxl is not a built-in module, therefore we need to install it first.

Installing Openpyxl

Run the following code in your Python terminal to install Openpyxl.

Pip install openpyxl 
Enter fullscreen mode Exit fullscreen mode

The above code installs the openpyxl module after a few minutes.
To use the openpyxl on our in python, we need to import it.

import openpyxl 
Enter fullscreen mode Exit fullscreen mode

Loading our excel file can be done by:

from openpyxl import load_workbook
survey = load_workbook("ourworkbook.xlsx") 
Enter fullscreen mode Exit fullscreen mode

The code above uses the imported load_workbook method to read the excel file of interest and stores it in variable "survey"
Note that the file we are accessing must be in the same folder we are working from.

Excel document can contain many sheets, to print all the active sheets in the excel file .

 print(survey.sheetnames)
Enter fullscreen mode Exit fullscreen mode

The above code will display the name of the active sheets in the excel files.
To access the first sheet in the document:

 first = survey.active
Enter fullscreen mode Exit fullscreen mode

We stored the first sheet in the excel document as variable "first"

Rows and columns in excel file

Accessing column A:

col_A = first["A"]
col_A
Enter fullscreen mode Exit fullscreen mode

To access range of columns:

range_A_to_C = first["A:C"]
print(range_A_to_C)
Enter fullscreen mode Exit fullscreen mode

The range of columns from A to C is stored in the variable "range_A_to_C" as it is shown in the above code, we can thus print it out.

There are quite few nice things we can do with the Openpyxl module in Python, and interested person should check Openpyxl documentation for more information.

Openpyxl has been around for quite some time now and has its own usefulness. However, better tools (eg. pandas) has been developed in Python for better handling of Excel file.

Top comments (0)