0

I'm trying to add my DataFrame data to an existing Excel worksheet and instead, it's completely overwriting it and deleting all of my existing worksheets. Here's the warning message I get in my console:

UserWarning: File contains an invalid specification for Closed POA&M Items. This will be removed
  warn(msg)

UserWarning: File contains an invalid specification for Open POA&M Items. This will be removed
  warn(msg)

I'm just trying to add to my "Open POA&M Items" sheet using the following code:

curr_poam = "Reports/September.xlsx"
curr_closed_sheet = "Open POA&M Items"

def main():
    writeToExcel(closed_items, curr_poam, curr_closed_sheet)

def writeToExcel(dataframe, path_to_current_poam, sheet_name):
    with pd.ExcelWriter(path_to_current_poam, engine="openpyxl", mode='a', if_sheet_exists='overlay') as writer:
        dataframe.to_excel(writer, sheet_name=sheet_name,  startrow=5, index=False, header=4)

Here's what my Open worksheet looks like, which has all the headers and columns formatted

enter image description here

Is it possible to start writing to this worksheet from row 6, adding my DataFrame to it? Based off the Pandas docs I thought I could just use mode='a' and if_sheet_exists='overlay' to append to an existing worksheet, but instead it's just deleting all worksheets and creating a new one with just my DataFrame in it.

Solutions attempted:

  • So far tried the two solutions here Pandas mode='a', if_sheet_exists='overlay' not working

  • Updating Pandas and Openpyxl didn't fix this problem for my use-case

  • Neither did adding a start row to this line of code dataframe.to_excel(writer, sheet_name=sheet_name, index=False, startrow=writer.sheets[sheet_name].max_row, header=None)

However, this did generate two new errors:

KeyError: 'Open POA&M Items'

IndexError: At least one sheet must be visible
Mitchell Privett
  • 77
  • 1
  • 7
  • Does this answer your question? [Pandas mode='a', if\_sheet\_exists='overlay' not working](https://stackoverflow.com/questions/71483123/pandas-mode-a-if-sheet-exists-overlay-not-working) – Andreas Jan 11 '23 at 23:49
  • @Andreas I tried two of the solutions: First I tried upgrading my Pandas and Openpyxl versions and that didn't solve it. Then I tried the top solution and added the start row to my code like-so `startrow=writer.sheets[sheet_name].max_row` and it's giving me a couple of errors from that line `KeyError: 'Open POA&M Items'` and the error `IndexError: At least one sheet must be visible` It's just not picking up the worksheets for some reason, I tried both of them and neither are working – Mitchell Privett Jan 12 '23 at 03:54
  • ok thanks for the update, are both sheets visible? did you right click the sheetname and copy it? e.g. there might be leading of following whitespaces, or special characters which look like whitespaces. The only other solution I know works but is more complicated is to use the openxlsx library directly. With that you have full controll over the workbook and worksheets but you have to script alot more. – Andreas Jan 12 '23 at 09:13
  • @Andreas Both sheets are visible and I tried copying and pasting the sheet name + it typically shows a different name in my IDE (with random characters added) and that didn't work either, which usually does with .xlsx files. Even if I save my .xlsm file as .xlsx it still just won't work, but when I try using a file that was originally created as .xlsx it works fine. These .xlsm files just seem to be buggy to work with, does openpyxl work with .xlsm files? – Mitchell Privett Jan 12 '23 at 18:08
  • you have to look into the documentation but I think so – Andreas Jan 12 '23 at 19:02
  • @MitchellPrivett, your code should not overwrite the existing worksheets. I can't reproduce your issue with `pandas 1.5.2` and `openpyxl 3.0.10`. And side note, you need to set `header` to `False`. – Timeless Jan 13 '23 at 09:20

0 Answers0