How to Write Excel Files (.xlsx and .xls) Using Pandas?


Write Excel with Python’s Pandas library. You can write any data (lists, strings, numbers, etc.) to Excel by first converting it into a Pandas DataFrame and then writing the DataFrame to an Excel file. This allows you to write any data into an Excel file.

In this short blog post, we’ll show you how to write Excel files using the Pandas library. Specifically, we’ll be looking at how to write an Excel file in two different formats: .xlsx and .xls. We’ll also go over some of the benefits of using Pandas to write your Excel files.

You can use the pandas to_excel() function to write a DataFrame to an excel sheet with the extension .xlsx. You can also write multiple sheets by using an ExcelWriter object with a target file name, and sheet name to write to.

Install all Libraries to write an Excel file

pip install pandas pip install numpy pip install openpyxl

The pandas to_excel() function use openpyxl library, and zip() function from numpy library.

Writing Excel Files Using Pandas

The first thing we need to do is import the Pandas library. We can do this by using the following code:

import pandas as pd
Code language: JavaScript (javascript)

Once we have imported the library, we can then use it to write our Excel files. Let’s take a look at how to do this for both .xlsx and .xls files.

import pandas as pd import numpy as np # Create multiple lists technologies = ['Dart','Angular','React','Python', 'PHP'] fee = [30000, 25000, 16000, 19000, 27000] duration = ['40 Days','25 Days','55 Days', np.nan, '30 Days'] discount = [300, 1100, 800, 600, 700] columns=['Courses','Fee','Duration','Discount'] # Create DataFrame from multiple lists mydf = pd.DataFrame(list(zip(technologies,fee,duration,discount)), columns=columns) print(mydf) # Outputs # Courses Fee Duration Discount #0 Dart 30000 40 Days 300 #1 Angular 25000 25 Days 1100 #2 React 16000 55 Days 800 #3 Python 19000 NaN 600 #4 PHP 27000 30 Days 700 # Write DataFrame to Excel file mydf.to_excel('mycourses.xlsx')
Code language: Python (python)

Content sheet1 of Excel file: mycourses.xlsx

Write DataFrame to Excel file

Another example write DataFrame to an Excel file (create a new or overwrite save)

import pandas as pd print(pd.__version__) # 1.5.0 mydf = pd.DataFrame([[11, 21, 31], [12, 22, 32], [31, 32, 33]], index=['one', 'two', 'three'], columns=['a', 'b', 'c']) print(mydf) #output # a b c # one 11 21 31 # two 12 22 32 # three 31 32 33 #If the path is given as the first argument to the method, the file will be made if it doesn't already exist and overwritten if it does. # sheet name Specify the name of the sheet as an argument. If not included, the name will be Sheet1. mydf.to_excel('users/data/myexcel.xlsx', sheet_name='new_sheet_name') # Output in new_sheet_name of excel file # a b c #one 11 21 31 #two 12 22 32 #three 31 32 33
Code language: Python (python)

If you do not need to write ( row name), (column name) using index=False, header=False

mydf.to_excel('myexcel1.xlsx', index=False, header=False) # Output in sheet1 of excel file #11 21 31 #12 22 32 #31 32 33
Code language: Python (python)

Write to Multiple Sheets

You are able to write or export several Pandas DataFrames to distinct sheets by using the ExcelWriter class. To begin, you will need to initiate the creation of an object for ExcelWriter.

In the following example, the data from the mydf1 object is written to a sheet titled Technologies, while the data from the mydf2 object is written to a sheet titled Schedule.

# Write to Multiple Sheets with pd.ExcelWriter('mycourses.xlsx') as writer: mydf1.to_excel(writer, sheet_name='Technologies') mydf2.to_excel(writer, sheet_name='Schedule')
Code language: PHP (php)

Python write to existing excel file (Pandas append to excel)

ExcelWriter can be used to append a DataFrame to an Excel file. Use mode param with the value ‘a’ to append (mode=’a’). The code below opens an existing file and adds the data from the DataFrame to the specified sheet.

# Append DataFrame to existing excel file with pd.ExcelWriter('mycourses.xlsx', mode='a') as writer: mydf.to_excel(writer, sheet_name='Technologies')
Code language: Python (python)

If you give a name for a sheet that already exists, a new sheet is made by adding a number that starts with 1 to the end of the name. Do not write over already-written sheets.

So there you have it—a quick rundown of how you can use Pandas to write your data into an Excel file in both the newer .XLSX format and the older .XLS format. Writing data into these formats using Pandas is quick and easy, which is why it’s such a popular choice among Python programmers who need to work with data from multiple sources. Thanks for reading!

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts