Openpyxl in Python - A Brief Introduction

In this article, we’ll learn about openpyxl in Python. Excel spreadsheets are one of the many things that you would have to cope with at some stage in life. You may need to learn how to deal with spreadsheets, either because your manager prefers them or because marketers require them, and that is where learning openpyxl comes in helpful.

You may read the spreadsheet, cycle through each row, retrieve data from the database, and then write back to the spreadsheet using Openpyxl.

Understanding Some Basic Terminologies

Before we begin working with the module, let’s understand the basic definitions mentioned in the table below:

Term	Definition
Workbook	The main file we are working with/on.
Worksheet	Each separate sheet in the whole workbook.
Column	Vertical lines labeled as A, B, C, D, and so on.
Row	Horizontal lines labeled as 1,2,3,4,5, and so on.
Cell	A combination of a row and column labeled as A1, A2, A3, and so on.

Some basic terms and definitions

Introduction to Openpyxl Module

The Openpyxl module in Python is used to work with Excel files without requiring third-party Microsoft application software. We can manipulate Excel without having to launch the application by utilizing this module. It is used to execute excel activities such as reading data from an excel file or writing data to an excel file, drawing charts, accessing an excel sheet, renaming the sheet, modifying (adding and removing) the sheet, formatting, styling the sheet, and any other job. Openpyxl is a highly efficient tool for handling these jobs for you.

Data scientists frequently utilize Openpyxl to execute various tasks such as data copying, data mining, and data analysis.

Installation of Openpyxl in Python

To use Openpyxl, you must have Python 3.7 and openpyxl 2.6.2 installed on your machine. Let’s get started with openpyxl by installing it with the following command:

pip install openpyxl

Working with openpyxl in Python

Let’s get right into the working of the openpyxl library in Python. We’ll implement this library to ready through excel files. Without any further ado, let’s go ahead.

1. Creation and Assign Values

To begin, we may use the workbook function to create a new workbook. A workbook always includes at least one sheet.

from openpyxl import Workbook  
wb = Workbook()

We obtain the active sheet’s location and give any value to the active sheet’s cells.

sheet = wb.active 
sheet['A1'] = 67847  
sheet['A2'] = 'AskPython!'
sheet['A3'] = 8908.983

Finally, we can use the save() function to store all data to the ‘sample_file.xlsx’ file. The final excel file looks something like shown in the image below.

3. Append Data to File

The append() function in Openpyxl is used to add the group of data. Any sort of value can be appended. These values are added to the current working sheet at the bottom.

data =  ((7434, 675, 5433),  ("Python", 'Awesome!',452.8763) )
for i in data:  
    sheet.append(i)  
wb.save("sample_file.xlsx")

The modified excel sheet looks something like this:

Conclusion

You now understand how to use spreadsheets in Python! When it comes to Excel files, you can count on openpyxl to accomplish anything! Hope you enjoyed it! 😇

Liked the tutorial? In any case, I would recommend you to have a look at the tutorials mentioned below:

Thank you for taking your time out! Hope you learned something new!! 😄