Pandas DataFrame to_excel() method exports the DataFrame to the Excel file. To write to multiple sheets, create an ExcelWriter object with a target filename and specify the sheet you must write.

The to_excel() method provides two engines:

1. openpyxl: It is good for reading and writing Excel files.
2. xlsxwriter: It provides advanced formatting to create excellent Excel files.

Syntax

df.to_excel(excel_writer, sheet_name='Sheet1', \*\*kwargs)

Parameters

Parameters	Type	Description
excel_writer	str or ExcelWriter object	It is a file path or existing ExcelWriter
sheet_name	str, default ‘Sheet1’	It is the name of the sheet that will contain DataFrame.
columns	sequence or list of str, optional	They are columns to write.
index	bool, default True	It will write row names (index)
index_label	str or sequence, optional	Column label for index column(s) if desired. If not specified, and ‘header’ and ‘index’ are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.

Basic Export with Index

By default, when you use the to_excel() method, it will export it to an Excel file with an index.

import pandas as pd

data = {
    'name': ['Krunal', 'Ankit', 'Dhaval'],
    'score': [85, 90, 78],
    'subject': ['Maths', 'Science', 'History'],
    'class': [10, 11, 10]
}
df = pd.DataFrame(data)

# Export with index (default)
df.to_excel("output_with_index.xlsx")

Output

You can check out the output_with_index.xlsx file and see its content. The Excel file will include a first column representing the DataFrame’s index.

Basic Export without Index

To export DataFrame to Excel without an index, pass the “index=False” argument.

import pandas as pd

data = {
    'name': ['Krunal', 'Ankit', 'Dhaval'],
    'score': [85, 90, 78],
    'subject': ['Maths', 'Science', 'History'],
    'class': [10, 11, 10]
}
df = pd.DataFrame(data)

# Export without index
df.to_excel("output_without_index.xlsx", index=False)

Output

You can see from the above image that the output Excel file does not contain any indices.

Writing multiple sheets

We can write multiple Frames with multiple sheets using Pandas.ExcelWriter() method. Let’s write an example in which we will create three DataFrames and save those DataFrames in a multiplesheet.xlsx file with three different sheets.

import pandas as pd
import numpy as np
import xlsxwriter

# Creating dataset using dictionary
data_set = {
    'Name': ['Rohit', 'Arun', 'Sohit', 'Arun', 'Shubh'],
    'Roll no': ['01', '02', '03', '04', np.nan],
    'maths': ['93', '63', np.nan, '94', '83'],
    'science': ['88', np.nan, '66', '94', np.nan],
    'english': ['93', '74', '84', '92', '87']}

data_set2 = {
    'Name': ['Ankit', 'Krunal', 'Rushabh', 'Dhaval', 'Nehal'],
    'Roll no': ['01', '02', '03', '04', np.nan],
    'maths': ['93', '63', np.nan, '94', '83'],
    'science': ['88', np.nan, '66', '94', np.nan],
    'english': ['93', '74', '84', '92', '87']}

data_set3 = {
    'Name': ['Millie', 'Jane', 'Michael', 'Bobby', 'Brown'],
    'Roll no': ['01', '02', '03', '04', np.nan],
    'maths': ['93', '63', np.nan, '94', '83'],
    'science': ['88', np.nan, '66', '94', np.nan],
    'english': ['93', '74', '84', '92', '87']}

# Converting into dataframe
df = pd.DataFrame(data_set)
df2 = pd.DataFrame(data_set2)
df3 = pd.DataFrame(data_set3)

with pd.ExcelWriter('multiplesheet.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet')
    df2.to_excel(writer, sheet_name='Sheet2')
    df3.to_excel(writer, sheet_name='Sheet3')

Output

Appending to existing Excel file

To append data to an Excel file, you must load the existing data into a DataFrame, append the new data, and then write the combined data back to the Excel file; however, if you want to write data to a new sheet in the existing Excel file, pandas.ExcelWriter() with openpyxl can be used.

with pd.ExcelWriter('multiplesheet.xlsx', mode='a') as writer:
     df.to_excel(writer, sheet_name='Sheet3')

Skipping Index

To skip Index from writing, use index=False param. It is set to True by default, meaning write a numerical Index to an Excel sheet.

df.to_excel("multiplesheet.xlsx", index = False)

That’s it.