Exporting a Pandas DataFrame to an Excel file

Sometimes we need an Excel file for reporting, so as a coder we will see how to export Pandas DataFrame to an Excel file. The to_excel() function in the Pandas library is utilized to export a DataFrame to an Excel sheet with the .xlsx extension. By default, it saves a single DataFrame to an Excel file. However, the capability to write multiple sheets can be achieved by employing an ExcelWriter object. This object requires a target file name and a sheet name for writing to the specified Excel file.

Pandas DataFrame to an Excel file

Below are different examples by which we can export our Pandas DataFrame to an Excel File.

Pandas DataFrame to Excel Using to_excel() Function

In this example, a Pandas DataFrame named marks_data is created to store information about students’ IDs, names, marks, and grades. The data is then saved to an Excel file named ‘MarksData.xlsx’ using the to_excel() function.

Python3

# importing the module
import pandas as pd

# creating the DataFrame
marks_data = pd.DataFrame({'ID': {0: 23, 1: 43, 2: 12,
                                  3: 13, 4: 67, 5: 89,
                                  6: 90, 7: 56, 8: 34},
                           'Name': {0: 'Ram', 1: 'Deep',
                                    2: 'Yash', 3: 'Aman',
                                    4: 'Arjun', 5: 'Aditya',
                                    6: 'Divya', 7: 'Chalsea',
                                    8: 'Akash'},
                           'Marks': {0: 89, 1: 97, 2: 45, 3: 78,
                                     4: 56, 5: 76, 6: 100, 7: 87,
                                     8: 81},
                           'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C',
                                     4: 'E', 5: 'C', 6: 'A', 7: 'B',
                                     8: 'B'}})

# determining the name of the file
file_name = 'MarksData.xlsx'

# saving the excel
marks_data.to_excel(file_name)
print('DataFrame is written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

The Excel file is:

Exporting a Pandas DataFrame to an Excel file Using ExcelWriter() Method

In this example, a Pandas DataFrame named cars_data is created to store information about different car models, their maximum speeds, and colors. The data is then written to an Excel file named ‘CarsData1.xlsx’ using the to_excel() function along with an ExcelWriter object, and the file is saved successfully.

Python3

# importing the module
import pandas as pd

# creating the DataFrame
cars_data = pd.DataFrame({'Cars': ['BMW', 'Audi', 'Bugatti',
                                   'Porsche', 'Volkswagen'],
                          'MaxSpeed': [220, 230, 240, 210, 190],
                          'Color': ['Black', 'Red', 'Blue',
                                    'Violet', 'White']})

# writing to Excel
datatoexcel = pd.ExcelWriter('CarsData1.xlsx')

# write DataFrame to excel
cars_data.to_excel(datatoexcel)

# save the excel
datatoexcel.close()
print('DataFrame is written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

Excel File

Example 3: Write to Multiple Sheet

In this example, a Pandas DataFrame named marks_data is created to represent student information. The data is then written to an Excel file named ‘MarksData.xlsx’ with a sheet named ‘Employee’ using an ExcelWriter object, and a success message is printed.

Python3

# importing the module
import pandas as pd

# creating the DataFrame
marks_data = pd.DataFrame({'ID': {0: 23, 1: 43, 2: 12,
                                  3: 13, 4: 67, 5: 89,
                                  6: 90, 7: 56, 8: 34},
                           'Name': {0: 'Ram', 1: 'Deep',
                                    2: 'Yash', 3: 'Aman',
                                    4: 'Arjun', 5: 'Aditya',
                                    6: 'Divya', 7: 'Chalsea',
                                    8: 'Akash'},
                           'Marks': {0: 89, 1: 97, 2: 45, 3: 78,
                                     4: 56, 5: 76, 6: 100, 7: 87,
                                     8: 81},
                           'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C',
                                     4: 'E', 5: 'C', 6: 'A', 7: 'B',
                                     8: 'B'}})

# determining the name of the file
file_name = 'MarksData.xlsx'

# creating an ExcelWriter object
with pd.ExcelWriter(file_name) as writer:
    # writing to the 'Employee' sheet
    marks_data.to_excel(writer, sheet_name='Employee', index=False)
print('DataFrames are written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

Employee Excel Sheet Output