Python Pandas to_excel() Method

Theto_excel() method in Pandas is used to write DataFrame or Series objects to an Excel file, enabling easy data exporting of Pandas objects into Excel files. Whether you're working with.xls,.xlsx, or.xlsm formats, this method offers comprehensive customization options, including sheet naming, handling missing values, freezing panes, and more.

This method also allows you to write data into multiple sheets with the help of theExcelWriter object. Each sheet can have a unique name, and you can add data to different sheets within the same file. In this tutorial we will learn about the syntax, parameters, and practical use cases of theto_excel() method for efficient Excel file manipulation.

Syntax

Following is the syntax of the Python Pandas to_excel() method −

DataFrame.to_excel(excel_writer, *, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, inf_rep='inf', freeze_panes=None, storage_options=None, engine_kwargs=None)

When using theto_excel() method on a Series object, you should call it asSeries.to_excel().

Parameters

The Python Pandas to_excel() method accepts the below parameters −

• excel_writer: Specifies the output file path or anExcelWriter object.

• sheet_name: Name of the sheet where data will be written (default is 'Sheet1').

• na_rep: String representation for missing values (default is '').

• float_format: String format for floating-point numbers.

• columns: Specific columns to write from the DataFrame.

• header: Writes column names in the output excel file.

• index: Determines whether to include the DataFrame index as a column. IfTrue, the index is written. IfFalse, it is not included.

• index_label: Column labels for the index. If set toNone then the index names are used.

• startrow and startcol: Specify the starting cell for data placement.

• engine: Defines the engine to write the Excel file (e.g., openpyxl, xlsxwriter).

• merge_cells: Writes MultiIndex and hierarchical rows as merged cells.

• freeze_panes: Freezes rows/columns to keep them visible while scrolling.

• storage_options: Additional storage connection options, e.g., for cloud storage.

• engine_kwargs: Specifies arbitrary keyword arguments passed to excel engine.

Return Value

The Pandasto_excel() method returnsNone, instead, it saves the data of Pandas DataFrame or Series into the specified excel file.

Example : Exporting DataFrame to Excel

Here is a basic example that demonstrates writing the contents of Pandas DataFrame to an Excel file using the PandasDataFrame.to_excel() method.

import pandas as pd# Create sample datadata = {"Name": ["Kiran", "Charan", "Riya"],"Age": [25, 30, 35],}# Create a DataFramedf = pd.DataFrame(data)# Save the data to an Excel filedf.to_excel("data_excel_file.xlsx")print("Pandas DataFrame is saved to an Excel file successfully...")

Following is an output of the above code −

Pandas DataFrame is saved to an Excel file successfully...

Example: Writing DataFrame to an Excel File with a Custom Sheet Name

This example demonstrates writing data to an Excel sheet with a custom sheet name using thesheet_name parameter of theto_excel() method. We will then read the saved excel data back to the Pandas DataFrame using theread_excel() method.

import pandas as pd# Create sample datadata = {'name': ['Ravi', 'Priya', 'Kiran'], 'salary': [50000, 60000, 65000]}# Create a DataFramedf = pd.DataFrame(data)# Save the data to an Excel filedf.to_excel('custom_sheet_name.xlsx', sheet_name='Employees', index=False)# Read the saved Excel data back to DataFrame result = pd.read_excel('custom_sheet_name.xlsx', sheet_name='Employees')print('Resultant DataFrame from the Excel file:')print(result)

When we run the above program, it produces following result −

Resultant DataFrame from the Excel file:

Example: Limiting Columns to Write in an Excel File

In this example, we write only specific columns of a DataFrame to an Excel file using thecolumns parameter of theto_excel() method.

import pandas as pd# Create sample datadata = {'name': ['Ravi', 'Priya', 'Kiran'], 'salary': [50000, 60000, 65000],'age': [20, 26, 19]}# Create a DataFramedf = pd.DataFrame(data)# Save specific columns to an Excel filedf.to_excel('selected_columns.xlsx', columns=['name', 'age'], index=False)# Read the saved Excel data back to DataFrame result = pd.read_excel('selected_columns.xlsx')print('Resultant DataFrame the Excel file:')print(result)

The output of the above code as follows −

Resultant DataFrame from the Excel file:

Example: Handling Missing Values in Excel Files

This example demonstrates writing DataFrame to an Excel file with missing values. Here we will replace the missing values with a custom string ("--") using thena_rep parameter of theDataFrame.to_excel() method.

import pandas as pd# Create a DataFramedf = pd.DataFrame({'name': ['Ravi', None, 'Kiran'], 'salary': [50000, 60000, None]})# Write the DataFrame to excel filedf.to_excel('missing_values.xlsx', na_rep='--', index=False)# Read the saved Excel data back to DataFrame result = pd.read_excel('missing_values.xlsx')print('Resultant DataFrame from the Excel file:')print(result)

While executing the above code we obtain the following output −

Resultant DataFrame from the Excel file:

Example: Freezing Panes in Excel

The following example demonstrates how to use theto_excel() method for writing an Excel file with frozen panes by settingfreeze_panes=(1,1) parameter. Here the 1st row and 1st column will be frozen.

import pandas as pdimport numpy as np# Create a DataFrame with 10 rows and 10 columns of random integersdf = pd.DataFrame(np.random.randint(1, 100, size=(10, 10)),                   columns=[f'Column_{i+1}' for i in range(10)])df.to_excel('fozen_panes.xlsx',freeze_panes=(1,1), sheet_name='Sheet1', index=False)    print("Pandas DataFrame is saved to an Excel file with Frozen rows and columns...")

Following is an output of the above code −

Pandas DataFrame is saved to an Excel file with frozen rows and columns...

In the following image you can observe the output excel file with frozen rows and columns.

Example: Writing Multiple Sheets with Pandas

This example demonstrates writing pandas DataFrame to multiple sheets of an Excel file.

import pandas as pd# Create a sample Excel file with multiple sheetsdata1 = {"Name": ["Kiran", "Priya"], "Age": [25, 30]}data2 = {"Department": ["HR", "Finance"], "Location": ["New Delhi", "Hyderabad"]}with pd.ExcelWriter("multi_sheet_example.xlsx") as writer:    pd.DataFrame(data1).to_excel(writer, sheet_name="Sheet1", index=False)    pd.DataFrame(data2).to_excel(writer, sheet_name="Sheet2", index=False)print("DataFrames are successfully saved to multiple sheets of a same Excel file.")

While executing the above code we obtain the following output −

DataFrames are successfully saved to multiple sheets of a same Excel file.