2

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
  • 52,167
  • 11
  • 76
  • 124
psy.lue
  • 25
  • 6

2 Answers2

0

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')
David Zemens
  • 52,167
  • 11
  • 76
  • 124
  • 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
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()
MattR
  • 4,508
  • 9
  • 37
  • 62
  • 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