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:
|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.|
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:
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:
- How to Convert Pandas DataFrame to Excel file
- Plot data from Excel Sheet using Python
- Print Data Using PySpark – A Complete Guide
Thank you for taking your time out! Hope you learned something new!! 😄