By BP_

2013-11-26 14:05:22 8 Comments

I use pandas to write to excel file in the following fashion:

import pandas

writer = pandas.ExcelWriter('Masterfile.xlsx') 

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

Masterfile.xlsx already consists of number of different tabs. However, it does not yet contain "Main".

Pandas correctly writes to "Main" sheet, unfortunately it also deletes all other tabs.


@MaxU 2017-12-10 15:21:16

Here is a helper function:

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
    Append a DataFrame [df] to existing Excel file [filename]
    into [sheet_name] Sheet.
    If [filename] doesn't exist, then this function will create it.

      filename : File path or existing ExcelWriter
                 (Example: '/path/to/file.xlsx')
      df : dataframe to save to workbook
      sheet_name : Name of sheet which will contain DataFrame.
                   (default: 'Sheet1')
      startrow : upper left cell row to dump data frame.
                 Per default (startrow=None) calculate the last row
                 in the existing DF and write to the next row...
      truncate_sheet : truncate (remove and recreate) [sheet_name]
                       before writing DataFrame to Excel file
      to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
                        [can be dictionary]

    Returns: None
    from openpyxl import load_workbook

    # ignore [engine] parameter if it was passed
    if 'engine' in to_excel_kwargs:

    writer = pd.ExcelWriter(filename, engine='openpyxl')

    # Python 2.x: define [FileNotFoundError] exception if it doesn't exist 
    except NameError:
        FileNotFoundError = IOError

        # try to open an existing workbook = load_workbook(filename)

        # get the last row in the existing Excel sheet
        # if it was not specified explicitly
        if startrow is None and sheet_name in
            startrow =[sheet_name].max_row

        # truncate sheet
        if truncate_sheet and sheet_name in
            # index of [sheet_name] sheet
            idx =
            # remove [sheet_name]
            # create an empty sheet [sheet_name] using old index
  , idx)

        # copy existing sheets
        writer.sheets = {ws.title:ws for ws in}
    except FileNotFoundError:
        # file does not exist yet, we will create it

    if startrow is None:
        startrow = 0

    # write out the new sheet
    df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)

    # save the workbook

NOTE: for Pandas < 0.21.0, replace sheet_name with sheetname!

Usage examples:

append_df_to_excel('d:/temp/test.xlsx', df)

append_df_to_excel('d:/temp/test.xlsx', df, header=None, index=False)

append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2', index=False)

append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2', index=False, startrow=25)

@cholo14 2018-09-25 09:04:22

This solution worked perfect for me, the other ones posted here do not work. Thanks a lot! Just one comment: when the file does not exist, I get an error "NameError: global name 'FileNotFoundError' is not defined"

@MaxU 2018-09-25 09:12:16

@cholo14, thank you for pointing this out! I've tested it on Python 3.x, so i missed that bug. I've fixed it in the answer...

@2one 2019-09-11 14:47:59

This worked for me but is there a way to maintain the xlsx formatting (from the original xlsx file)?

@MaxU 2019-09-11 15:05:45

@2one, I don't know exactly - give it a try or ask a new SO question

@doomdaam 2019-11-28 09:08:48

is there a way to write to columns instead of only rows? Like I wanna update a sheet automatically, but not appending new rows, but columns thanks!

@Ski 2013-11-26 15:45:59

Pandas docs says it uses openpyxl for xlsx files. Quick look through the code in ExcelWriter gives a clue that something like this might work out:

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

@BP_ 2013-11-26 16:04:38

Can you please explain what is writer.sheets for?

@Ski 2013-11-26 16:40:30

ExcelWriter for some reason uses this variable to access the sheet. If you leave it empty it will not know that sheet Main is already there and will create a new sheet.

@BP_ 2013-11-27 15:18:51

This solution works fine. It has one drawback though. It breaks formulas and connections within the spreadsheet. Any ideas how to change this behaviour?

@Ski 2013-11-27 17:22:52

What exactly do you get broken..? You might ask this as a separate question and tag it with openpyxl and provide enough details: what kind of formulas do you have, how data is updated, how does it brake the formulas. Now I just can't help, too many things I don't know.

@BP_ 2013-11-28 09:39:15

@gaurav gurnani 2015-04-01 10:48:48

Hi, this doesn't work for xls files. do you know how to do the same with xls files?

@Chris 2016-11-21 12:38:03

This allows you to add content to an existing spreadsheet, but unfortunately this approach strips the spreadsheet of any graphs, or any other content that openpyxl can't natively understand. I've not found a way round this, except adding data to a separate Excel file which the graphs then reference.

@Lumos 2017-10-19 04:03:30

what does dict((ws.title, ws) for ws in book.worksheets) mean?

@dapaz 2017-11-15 16:16:07

can it be used with .xlsm files instead?

@blacksheep 2019-03-22 06:05:34

There is a better solution in pandas 0.24:

with pd.ExcelWriter(path, mode='a') as writer:
    s.to_excel(writer, sheet_name='another sheet', index=False)


enter image description here


enter image description here

so upgrade your pandas now:

pip install --upgrade pandas

@T.C. Proctor 2019-04-25 18:39:35

This is a duplicate of this earlier answer

@metinsenturk 2020-02-12 15:15:58

Just a heads-up for the future, this does not work with the XslxWriter option.

@Björn B 2020-04-01 11:17:48

it does also by default not work with engine=openpyxl as it will just add a new worksheet called the only worksheet1

@Will Ayd 2019-01-14 20:42:08

Starting in pandas 0.24 you can simplify this with the mode keyword argument of ExcelWriter:

import pandas as pd

with pd.ExcelWriter('the_file.xlsx', engine='openpyxl', mode='a') as writer: 

@keramat 2019-01-16 15:42:08

overwrites for me.

@T.C. Proctor 2019-04-25 18:36:49

@keramat I think there might be confusion on this question between two different goals. This allows you to add additional sheets to an existing workbook. It is not intended to append additional data to an existing sheet.

@Pedro Machado 2018-11-08 00:26:13

book = load_workbook(xlsFilename)
writer = pd.ExcelWriter(self.xlsFilename) = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name=sheetName, index=False)

@Roy Scheffers 2018-11-08 00:46:56

While this might answer the authors' question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer.

@Edward 2017-11-14 19:46:14

writer = pd.ExcelWriter('prueba1.xlsx'engine='openpyxl',keep_date_col=True)

The "keep_date_col" hope help you

@flyingmeatball 2017-10-24 16:40:06

I know this is an older thread, but this is the first item you find when searching, and the above solutions don't work if you need to retain charts in a workbook that you already have created. In that case, xlwings is a better option - it allows you to write to the excel book and keeps the charts/chart data.

simple example:

import xlwings as xw
import pandas as pd

#create DF
months = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
value1 = [x * 5+5 for x in range(len(months))]
df = pd.DataFrame(value1, index = months, columns = ['value1'])
df['value2'] = df['value1']+5
df['value3'] = df['value2']+5

#load workbook that has a chart in it
wb = xw.Book('C:\\data\\bookwithChart.xlsx')

ws = wb.sheets['chartData']

ws.range('A1').options(index=False).value = df

wb = xw.Book('C:\\data\\bookwithChart_updated.xlsx')


@Tinkinc 2018-03-26 19:34:12

Is there a way to create file if it doesnt exist first?

@flyingmeatball 2018-03-27 13:30:15

Yes, did you explore the docs?

@Tinkinc 2018-03-27 13:44:22

wb = xw.Book(filename) on their website says it creates a book. but it doesnt

@flyingmeatball 2018-03-27 13:55:13

wb = xw.Book() creates a new empty book, when you pass it a path you are trying to load an existing book.

@Tinkinc 2018-03-28 19:47:17

i am not getting referenced tab to update(i.e. trunk and load data. any ideas?

@virtualxtc 2018-06-14 07:37:32

Note: xlwings interacts with a running instance of Excel and therefore does not run on Linux.

@b2002 2017-04-22 21:51:29

Old question, but I am guessing some people still search for this - so...

I find this method nice because all worksheets are loaded into a dictionary of sheet name and dataframe pairs, created by pandas with the sheetname=None option. It is simple to add, delete or modify worksheets between reading the spreadsheet into the dict format and writing it back from the dict. For me the xlsxwriter works better than openpyxl for this particular task in terms of speed and format.

Note: future versions of pandas (0.21.0+) will change the "sheetname" parameter to "sheet_name".

# read a single or multi-sheet excel file
# (returns dict of sheetname(s), dataframe(s))
ws_dict = pd.read_excel(excel_file_path,

# all worksheets are accessible as dataframes.

# easy to change a worksheet as a dataframe:
mod_df = ws_dict['existing_worksheet']

# do work on mod_df...then reassign
ws_dict['existing_worksheet'] = mod_df

# add a dataframe to the workbook as a new worksheet with
# ws name, df as dict key, value:
ws_dict['new_worksheet'] = some_other_dataframe

# when done, write dictionary back to excel...
# xlsxwriter honors datetime and date formats
# (only included as example)...
with pd.ExcelWriter(excel_file_path,
                    date_format='yyyy-mm-dd') as writer:

    for ws_name, df_sheet in ws_dict.items():
        df_sheet.to_excel(writer, sheet_name=ws_name)

For the example in the 2013 question:

ws_dict = pd.read_excel('Masterfile.xlsx',

ws_dict['Main'] = data_filtered[['Diff1', 'Diff2']]

with pd.ExcelWriter('Masterfile.xlsx',
                    engine='xlsxwriter') as writer:

    for ws_name, df_sheet in ws_dict.items():
        df_sheet.to_excel(writer, sheet_name=ws_name)

@virtualxtc 2018-06-14 07:39:09

This sort of worked, however, my merged cells, cell colors, and cell widths were not preserved.

@b2002 2018-06-15 16:56:49

Yes, with this method that type of formatting will be lost because each worksheet is converted to a pandas dataframe (with none of that excel formatting), then converted from dataframes to worksheets withinin a new excel workbook (which has the same name as the original file). It appears that a new "append" method using openpyxl may be forthcoming which might preserve original file worksheet formatting?

@mvbentes 2017-03-06 23:46:37

With openpyxlversion 2.4.0 and pandasversion 0.19.2, the process @ski came up with gets a bit simpler:

import pandas
from openpyxl import load_workbook

with pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') as writer: = load_workbook('Masterfile.xlsx')
    data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])
#That's it!

@Qululu 2017-07-12 05:58:13

This doesn't work for me. If there is already a "Main" worksheet, it will create a new one called "Main1" with the new data only and leave the "Main" worksheet content unchanged.

@T.C. Proctor 2019-04-25 18:35:49

@Qululu I think there might be confusion on this question between two different goals. This allows you to add additional sheets to an existing workbook. It is not intended to append additional data to an existing sheet. If there is a sheet naming conflict, it renames the sheet. This is a feature, not a bug.

@Manish Mehra 2017-02-07 15:20:09

def append_sheet_to_master(self, master_file_path, current_file_path, sheet_name):
        master_book = load_workbook(master_file_path)
        master_writer = pandas.ExcelWriter(master_file_path, engine='openpyxl') = master_book
        master_writer.sheets = dict((ws.title, ws) for ws in master_book.worksheets)
        current_frames = pandas.ExcelFile(current_file_path).parse(pandas.ExcelFile(current_file_path).sheet_names[0],
        current_frames.to_excel(master_writer, sheet_name, index=None, header=False)
    except Exception as e:
        raise e

This works perfectly fine only thing is that formatting of the master file(file to which we add new sheet) is lost.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] How to iterate over rows in a DataFrame in Pandas?

23 Answered Questions

[SOLVED] Adding new column to existing DataFrame in Python pandas

43 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1095481 View
  • 1891 Score
  • 43 Answer
  • Tags:   c# .net excel file-io

39 Answered Questions

[SOLVED] How do I check whether a file exists without exceptions?

16 Answered Questions

[SOLVED] How do I copy a file in Python?

16 Answered Questions

[SOLVED] "Large data" work flows using pandas

21 Answered Questions

[SOLVED] How to print without newline or space?

  • 2009-01-29 20:58:25
  • Andrea Ambu
  • 1867924 View
  • 1872 Score
  • 21 Answer
  • Tags:   python newline

7 Answered Questions

[SOLVED] Writing a pandas DataFrame to CSV file

21 Answered Questions

[SOLVED] How do I list all files of a directory?

  • 2010-07-08 19:31:22
  • duhhunjonn
  • 4337788 View
  • 3473 Score
  • 21 Answer
  • Tags:   python directory

14 Answered Questions

[SOLVED] How do I write JSON data to a file?

  • 2012-09-06 22:21:21
  • user1530318
  • 1460670 View
  • 1122 Score
  • 14 Answer
  • Tags:   python json

Sponsored Content