How to add an empty Worksheet into an existing Workbook using Pandas ExcelWriter

Question

I am trying to add an empty excel sheet into an existing Excel File using python xlsxwriter.

Setting the formula up as follows works well.

workbook = xlsxwriter.Workbook(file_name)
worksheet_cover = workbook.add_worksheet("Cover")
Output4 = workbook
Output4.close()

But once I try to add further sheets with dataframes into the Excel it overwrites the previous excel:

with pd.ExcelWriter('Luther_April_Output4.xlsx') as writer:
    data_DifferingRates.to_excel(writer, sheet_name='Differing Rates')
    data_DifferingMonthorYear.to_excel(writer, sheet_name='Differing Month or Year')
    data_DoubleEntries.to_excel(writer, sheet_name='Double Entries')

How should I write the code, so that I can add empty sheets and existing data frames into an existing excel file.

Alternatively it would be helpful to answer how to switch engines, once I have produced the Excel file...

Thanks for any help!

David Zemens · Answer 1 · 2019-06-11T13:23:14.360

You could use pandas.ExcelWriter with optional mode='a' argument for appending to existing Excel workbook.

You can also append to an existing Excel file:

>>> with ExcelWriter('path_to_file.xlsx', mode='a') as writer:`
...     df.to_excel(writer, sheet_name='Sheet3')`

However unfortunately, this requires using a different engine, since as you observe the ExcelWriter does not support the optional mode='a' (append). If you try to pass this parameter to the constructor, it raises an error.

So you will need to use a different engine to do the append, like openpyxl. You'll need to ensure that the package is installed, otherwise you'll get a "Module Not Found" error. I have tested using openpyxl as the engine, and it is able to append new a worksheet to existing workbook:

with pd.ExcelWriter(engine='openpyxl', path='Luther_April_Output4.xlsx', mode='a') as writer:
    data_DifferingRates.to_excel(writer, sheet_name='Differing Rates')
    data_DifferingMonthorYear.to_excel(writer, sheet_name='Differing Month or Year')
    data_DoubleEntries.to_excel(writer, sheet_name='Double Entries')

Hi, Thanks for the advice. I had tried this trick perviously. Stupidly enough the engine "xlsxwriter" wont let me use this functionality 'mode="a"'. Do you have any idea on how to change that engine while writing excel half way into the code? I have used xlsxwriter so far since it has the most extensive literature and most commands such as also formatting cells etc... — psy.lue, Jun 11 '19 at 07:50
@phi.lue Unfortunately it seems the ExcelWriter does not support append mode :( but see revision to my answer for using the `openpyxl` engine instead. — David Zemens, Jun 11 '19 at 13:17

MattR · Accepted Answer · 2019-06-07T16:23:07.513

0

If you're not forced use xlsxwriter try using openpyxl. Simply pass 'openpyxl' as the Engine for the pandas built-in ExcelWriter class. I had asked a question a while back on why this works. It is helpful code. It works well with the syntax of pd.to_excel() and it won't overwrite your already existing sheets.

from openpyxl import load_workbook
import pandas as pd

book = load_workbook(file_name)
writer = pd.ExcelWriter(file_name, engine='openpyxl')
writer.book = book
data_DifferingRates.to_excel(writer, sheet_name='Differing Rates')
data_DifferingMonthorYear.to_excel(writer, sheet_name='Differing Month or Year')
data_DoubleEntries.to_excel(writer, sheet_name='Double Entries') 

writer.save()

edited Jun 07 '19 at 16:23

answered Jun 07 '19 at 16:15

MattR

4,887
9
40
67

Hi MattR Thanks a lot for your advice. Since I could see that openpyxl also allows me to change cell formats, ad comments and ad formulas into the excel file. that would be what I need??! But since I also want to have the engine read commands from "xlsx" as well as "xlsm" the code in this link seems to be best for me. https://stackoverflow.com/questions/45220247/pandas-excel-writer-using-openpyxl-with-existing-workbook – psy.lue Jun 11 '19 at 08:04
But could you please explain the code from line 4 onwards?! the "supported extensions" command seems to be right, but I don't get why and whether i need the rest as well... class _OpenpyxlWriter(ExcelWriter): engine = 'openpyxl' supported_extensions = ('.xlsx', '.xlsm') def __init__(self, path, engine=None, **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs) self.book = Workbook() – psy.lue Jun 11 '19 at 08:06
@phi.lue - if this answer or the other answer has solved *this* question you posted, please up vote them or mark as answered. As for the question you left in the comments, that is a completely different question and would require you to post another question :) – MattR Jun 11 '19 at 10:44
@phi.lue the other annswer (to which you're referring) is simply highlighting the *source code* for the `pandas.ExcelWriter` class, which is a wrapper for other i/o Excel libraries. You do not implement that code directly. – David Zemens Jun 11 '19 at 13:19

Adam Safier · Answer 3 · 2022-10-18T22:30:10.783

I think you need to write the data into a new file. This works for me:

#  Write multiple tabs (sheets) into to a new file
import pandas as pd
from openpyxl import load_workbook

Work_PATH = r'C:\PythonTest'+'\\'
ar_source =  Work_PATH + 'Test.xlsx'
Output_Wkbk = Work_PATH + 'New_Wkbk.xlsx'

# Need workbook from openpyxl load_workbook to enumerage tabs
#   is there another way with only xlsxwriter?
workbook = load_workbook(filename=ar_source)
# Set sheet names in workbook as a series.  
# You can also set the series manually tabs = ['sheet1', 'sheet2']
tabs = workbook.sheetnames  
print ('\nWorkbook sheets: ',tabs,'\n')

# Replace this function with functions for what you need to do
def default_col_width (df, sheetname, writer):
    # Note, this seems to use xlsxwriter as the default engine.
    for column in df:
        # map col width to col name. Ugh.
        column_width = max(df[column].astype(str).map(len).max(), len(column))
        # set special column widths 
        narrower_col = ['OS','URL'] #change to fit your workbook
        if column in narrower_col: column_width = 10
        if column_width >30: column_width = 30
        if column == 'IP Address': column_width = 15 #change for your workbook
        col_index = df.columns.get_loc(column)
        writer.sheets[sheetname].set_column(col_index,col_index,column_width)

    return  
    # Note nothing is returned.  Writer.sheets is global.

with pd.ExcelWriter(Output_Wkbk,engine='xlsxwriter') as writer:
    # Iterate throuth he series of sheetnames
    for tab in tabs:
        df1 = pd.read_excel(ar_source, tab).astype(str)
        # I need to trim my input
        df1.drop(list(df1)[23:],axis='columns', inplace=True, errors='ignore')

        try:
            # Set spreadsheet focus
            df1.to_excel(writer, sheet_name=tab, index = False, na_rep=' ')
            # Do something with the spreadsheet - Calling a function
            default_col_width(df1, tab, writer)  
        except:
            #  Function call failed so just copy tab with no changes
            df1.to_excel(writer, sheet_name=tab, index = False,na_rep=' ')

If I use the input file name as the output file name, it fails and erases the original. No need to save or close if you use With... it closes autmatically.

How to add an empty Worksheet into an existing Workbook using Pandas ExcelWriter

3 Answers3