1

I have an .xlsx file that I want to append to. I am using:

excel_writer = pd.ExcelWriter('asdf.xlsx', engine='openpyxl', mode='a')
df.to_excel(excel_writer, sheet_name='dummy', index=False)
excel_writer.close()

which does successfully create a the sheet 'dummy', but deletes all other existing sheets in 'asdf.xlsx'. I am using append mode so I'm not too sure where else to check. Running Pandas version 0.20.1

I tried the code in the docs for 0.20.1 but it just overwrites everything as well.

If passing an existing ExcelWriter object, then the sheet will be added to the existing workbook. This can be used to save different DataFrames to one workbook:

writer = pd.ExcelWriter('output.xlsx')
df1.to_excel(writer,'Sheet1')
df2.to_excel(writer,'Sheet2')
writer.save()
reeeeeeeeeeee
  • 129
  • 2
  • 10

1 Answers1

1

The problem is, that you misunderstand the append functionality. It means, that you append to the current excelwriter, but you create a new empty excel file, if you set no mode. You have to set the mode of the ExcelWriter to a (append). This will append new sheets every time you run it:

import pandas as pd
from openpyxl import load_workbook

data = [
    {"a": 1, "b": 2}
]

df = pd.DataFrame(data)
df2 = pd.DataFrame(data)

book = load_workbook("output.xlsx")
writer = pd.ExcelWriter('output.xlsx', mode="a")

if "Sheet11" not in book.sheetnames:
    df.to_excel(writer, 'Sheet11')

if "Sheet21" not in book.sheetnames:
    df2.to_excel(writer, 'Sheet21')
    writer.save()

print(book.sheetnames)

EDIT:
Added load_workbook
A part of this answer is based on this answer

D-E-N
  • 1,242
  • 7
  • 14
  • 1
    so is there any way to populate the ExcelWriter with whatever data is already in the 'output' file you want? that way when you append, you truly are appending instead of overwriting? i think what you have above would not do that – reeeeeeeeeeee Oct 13 '20 at 17:46
  • 1
    This above lines will always append the data, if the sheet already exists, it will append with an other name. If you want to read data from an excel file you should have a look at `pd.read_excel` – D-E-N Oct 13 '20 at 17:55
  • 1
    so there's no clean way of tacking on sheet 'd' to an excel file made of sheets [a,b,c] without first ingesting the entire excel file into dataframes a,b,c and then writing a,b,c,d together effectively overwriting the whole file? – reeeeeeeeeeee Oct 14 '20 at 15:59
  • 2
    I edited my anser and added an additional link, this should do your stuff without loading all the data in dataframes. For more exmples follow the link – D-E-N Oct 14 '20 at 16:37