Python

OpenPyXL: Create Multi-Sheets Excel Files With This Handy Python Library

This Python library helps you to export dataframes to different sheets within one Excel file

Ismael Araujo
4 min readJan 14, 2023

--

Photo by Rubaitul Azad on Unsplash

Even though Python is mighty, Excel will play a large chunk of work for most data analysts and scientists. Yes, Python can do most of what Excel can do, but most non-technical teams prefer to get their reports in Excel because it’s easier to interact and make changes when working in a multi-department company. However, sometimes Python and Excel files don’t talk to each other in the most effective ways.

Imagine the following scenario: you are working on a project with Python, and you need to export multiple related datasets. All the datasets need to be in the same Excel file but in different tabs; what would you do? I can say what I used to do. I would export each data into different CSV files, one for each dataframe. Then, I would open each file, copy the data, and paste it into an Excel file. I would repeat this process for each file. Finally, I would save the file, and the job would be done.

This doesn’t sound like the most practical way of doing things, and that’s because it’s not.

I have good news for you. There is a Python library that will solve this issue, and it’s called OpenPyXL. Not the best name, but it’s worth learning about it, and that’s what we will do now.

OpenPyXL

In short, OpenPyXL allows us to save multiple dataframes into different tabs in the same Excel file. It might not sound like a game-changer Python library at first, but you will love it when you get to a scenario where you need to use it. Thus, let’s learn how to use it.

Getting Started

Installing OpenPyXL is as easy as installing any other Python library. Just type !pip install openpyxl in your Jupyter notebook. Now, we can import Pandas and OpenPyXL to your notebook by typing the following line of code:

import pandas as pd
from openpyxl import Workbook

Now, let’s learn how to use it. I will use the following datasets: All Playstation 4 Games, Video Games Dataset, Video Games Sales Dataset

--

--