Python Write Excel File with Multiple Sheets Example

Hi Dev,

In this example, I will show you python write excel file with multiple sheets. step by step explain how to create multiple excel sheets using python. It's a simple example of how to create multiple excel files in python. you will learn python create excel file with multiple sheets. you will do the following things for python create xlsx file with multiple sheets.

There are several modules and ways to create excel file with multiple sheets in python. i will give you simple three examples using pandas, openpyxl and xlsxwriter to generate excel file with multiple sheets. so let's see examples one by one.

In this example, we will create excel file with SheetOne and SheetTwo multiple sheet. so let's see the below examples.

You can use these examples with python3 (Python 3) version.

Example 1: using pandas

If you haven't install pandas in your system then you can install using the below command:

pip install pandas

main.py

import pandas as pd
    
# Create a Pandas Excel writer using XlsxWriter
writer = pd.ExcelWriter('demo.xlsx', engine='xlsxwriter')
    
# Create SheetOne with Data
df = pd.DataFrame({'ID': [1, 2, 3],
                   'Name': ["Hardik Savani", "Vimal Kashiyani", "Harshad Pathak"],
                   'Email': ["hardik@gmail.com", "vimal@gmail.com", "harshad@gmail.com"]})
   
df.to_excel(writer, sheet_name='SheetOne', index=False)
   
# Create SheetTwo with Data
df = pd.DataFrame({'ID': [1, 2, 3],
                   'Name': ["Hardik", "Vimal", "Harshad"]})
   
df.to_excel(writer, sheet_name='SheetTwo', index=False)
    
# Save Data to File
writer.save()

Output:

Now, It will generated demo.xlsx file in your root path with the below content.

demo.xlsx

Example 2: using openpyxl

If you haven't install openpyxl in your system then you can install using the below command:

pip install openpyxl

main.py

import openpyxl
   
# Define variable to load the dataframe
wb = openpyxl.Workbook()
    
# Create SheetOne with Data
sheetOne = wb.create_sheet("SheetOne")
   
data =[('ID', 'Name', 'Email'),
       (1, 'Hardik Savani', 'hardik@gmail.com'),
       (2, 'Vimal Kashiyani', 'vimal@gmail.com'),
       (3, 'Harshad Pathak', 'harshad@gmail.com')]
   
for item in data :
     sheetOne.append(item)
   
# Create SheetTwo with Data
sheetTwo = wb.create_sheet("SheetTwo")
   
data =[('ID', 'Name'),
       (1, 'Hardik'),
       (2, 'Vimal'),
       (3, 'Harshad')]
   
for item in data :
     sheetTwo.append(item)
    
# Remove default Sheet
wb.remove(wb['Sheet'])
    
# Iterate the loop to read the cell values
wb.save("demo.xlsx")

Output:

Now, It will generated demo.xlsx file in your root path with the below content.

demo.xlsx

Example 3: using xlsxwriter

If you haven't install xlsxwriter in your system then you can install using the below command:

pip install xlsxwriter

main.py

import xlsxwriter
  
# Cretae a xlsx file
xlsxFile = xlsxwriter.Workbook('demo.xlsx')
  
# Add new worksheet
sheetOne = xlsxFile.add_worksheet("SheetOne")
sheetTwo = xlsxFile.add_worksheet("SheetTwo")
  
# Create List for write data into xlsx file
data = [
    { "ID": 1, "Name": "Hardik Savani", "Email": "hardik@gmail.com"},
    { "ID": 2, "Name": "Vimal Kashiyani", "Email": "vimal@gmail.com"},
    { "ID": 3, "Name": "Harshad Pathak", "Email": "harshad@gmail.com"}
]
  
row = 1
column = 0
  
# Set Header for xlsx file(SheetONE)
sheetOne.write(0, 0, "ID")
sheetOne.write(0, 1, "Name")
sheetOne.write(0, 2, "Email")
  
# Set Header for xlsx file(SheetTwo)
sheetTwo.write(0, 0, "ID")
sheetTwo.write(0, 1, "Name")
sheetTwo.write(0, 2, "Email")
  
# write into the worksheet
for item in data :
   
    # write operation perform(SheetOne)
    sheetOne.write(row, 0, item["ID"])
    sheetOne.write(row, 1, item["Name"])
    sheetOne.write(row, 2, item["Email"])
  
    # write operation perform(SheetTwo)
    sheetTwo.write(row, 0, item["ID"])
    sheetTwo.write(row, 1, item["Name"])
    sheetTwo.write(row, 2, item["Email"])
   
    # incrementing the value of row by one
    row += 1
  
# Close the Excel file
xlsxFile.close()

Output:

Now, It will generated demo.xlsx file in your root path with the below content.

demo.xlsx

I hope it can help you...