0

For the moment, I write my dataframe to an xlsx file that exists and everything works fine. Nevertheless, I'd like to write in a xlsm file because it's more convenient (after I can open my xlsx file and run macro).

How could I do that ?

Here is my code to write into a xlsx file.


def append_df_to_excel(filename, df, sheet_name, startrow=None,
                       truncate_sheet=False, 
                       **to_excel_kwargs):
   
    from openpyxl import load_workbook

  

    

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

   


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

When I simply do

def append_df_to_excel("name.xlsx", df)

it works, but when I change and do

def append_df_to_excel("name.xlsx", df)

it doesn't work.

Vishal Singh
  • 6,014
  • 2
  • 17
  • 33
  • It was xlsm in the last. I mistakenly changed it to xlsx in the edits but it is xlsm only. My apologies. – Vishesh Mangla Jul 16 '20 at 14:55
  • I don't think pandas support xlsm since it uses xlrd to read excel. This may help https://stackoverflow.com/questions/55109911/why-is-python-xlrd-errors-when-opening-a-xlsm-instead-of-xls. – Vishesh Mangla Jul 16 '20 at 14:57

1 Answers1

0

You can specify a python write mode in the pandas to_excel function. For append it is 'a'.So in your case :

with pd.ExcelWriter('output.xlsx',mode='a') as writer:  
    df.to_excel(writer, sheet_name='Sheet_name_3')
Mathieu P.
  • 344
  • 1
  • 10