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:
- openpyxl: It is good for reading and writing Excel files.
- 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.