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'])

writer.save()

Masterfile.xlsx already consists of number of different tabs.

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

10 comments

@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)

before:

enter image description here

after:

enter image description here

so upgrade your pandas now:

pip install --upgrade pandas

@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: 
     data_filtered.to_excel(writer) 

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

overwrites for me.

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

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

@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.

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

Here is a helper function:

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

    Parameters:
      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

    import pandas as pd

    # ignore [engine] parameter if it was passed
    if 'engine' in to_excel_kwargs:
        to_excel_kwargs.pop('engine')

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

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


    try:
        # try to open an existing workbook
        writer.book = 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 writer.book.sheetnames:
            startrow = writer.book[sheet_name].max_row

        # truncate sheet
        if truncate_sheet and sheet_name in writer.book.sheetnames:
            # index of [sheet_name] sheet
            idx = writer.book.sheetnames.index(sheet_name)
            # remove [sheet_name]
            writer.book.remove(writer.book.worksheets[idx])
            # create an empty sheet [sheet_name] using old index
            writer.book.create_sheet(sheet_name, idx)

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

    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
    writer.save()

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)

@Z_D 2018-04-10 20:59:27

Very helpful - appreciate it

@MaxU 2018-04-10 21:06:32

@Z_D, glad it helps :-)

@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...

@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')

xw.apps[0].quit()

@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? docs.xlwings.org/en/stable/api.html

@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,
                        sheetname=None)

# 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,
                    engine='xlsxwriter',
                    datetime_format='yyyy-mm-dd',
                    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',
                        sheetname=None)

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? github.com/pandas-dev/pandas/pull/21251

@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:
    writer.book = 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.

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

def append_sheet_to_master(self, master_file_path, current_file_path, sheet_name):
    try:
        master_book = load_workbook(master_file_path)
        master_writer = pandas.ExcelWriter(master_file_path, engine='openpyxl')
        master_writer.book = 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],
                                                               header=None,
                                                               index_col=None)
        current_frames.to_excel(master_writer, sheet_name, index=None, header=False)

        master_writer.save()
    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.

@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') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

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

writer.save()

@BP_ 2013-11-26 16:03:05

Yes, it does. Cheers mate!

@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?

Related Questions

Sponsored Content

26 Answered Questions

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

  • 2010-07-08 19:31:22
  • duhhunjonn
  • 3046134 View
  • 3339 Score
  • 26 Answer
  • Tags:   python directory

42 Answered Questions

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

17 Answered Questions

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

34 Answered Questions

[SOLVED] How to read a file line-by-line into a list?

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 980940 View
  • 1696 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

13 Answered Questions

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

  • 2012-09-06 22:21:21
  • user1530318
  • 1172491 View
  • 875 Score
  • 13 Answer
  • Tags:   python json

22 Answered Questions

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

26 Answered Questions

[SOLVED] How to print without newline or space?

  • 2009-01-29 20:58:25
  • Andrea Ambu
  • 1629227 View
  • 1576 Score
  • 26 Answer
  • Tags:   python newline

13 Answered Questions

[SOLVED] "Large data" work flows using pandas

6 Answered Questions

[SOLVED] Pandas writing dataframe to CSV file

Sponsored Content