Pandas corrupting file when writing data from xlsx to xlsm

Question

I have two excel files, the input file in xlsx and the output file in xlsm. With the below code i am creating a list of unique values of names. In the loop trying to filter the name with each unique value and trying to write the data xlsm format with separate excel tab for each name.

import pandas as pd
names = []
read_file = pd.read_excel('input_file.xlsx')
read_file['DATE'] = read_file['DATE'].dt.strftime('%d/%m/%y')
names = read_file['Name'].unique().astype(str) # gets unique values and stores in str

for n in names:
    data = read_file[(read_file['Name'] == n)]
    with pd.ExcelWriter('output_file.xlsm',engine='openpyxl',mode='a',if_sheet_exists='replace') as writer:
        data.to_excel(writer,sheet_name = n,index=False, startrow=0, header=True)

I am not getting any error while running this code, but the xlsm file/output_file is getting corrupted when trying to open this file.

When I used the same code for xlsx format, this code is working fine.

Any suggestion on where i am going wrong?

I need to use the xlsm file format as in the later part of my code i am using macro to do more operations — Madhav Rao, Aug 13 '23 at 15:45
https://stackoverflow.com/questions/28169731/write-pandas-dataframe-to-xlsm-file-excel-with-macros-enabled — Jason Baker, Aug 13 '23 at 17:36

score 0 · Answer 1 · answered Aug 13 '23 at 17:39

0

When writing an .xlsm file with pd.ExcelWriter on engine="openpyxl", the "keep_vba" parameter must be specified regardless of whether VBA code exists in output file. Otherwise, the output seems corrupt but is actually just .xlsx format. Renaming it to .xlsx allows opening normally in Excel.

for n in names:
    data = read_file[(read_file['Name'] == n)]
    with pd.ExcelWriter('output_file.xlsm',
                        engine="openpyxl", mode="a", engine_kwargs={"keep_vba": True},
                        if_sheet_exists='replace') as writer:
        data.to_excel(writer,sheet_name = n,index=False, startrow=0, header=True)

answered Aug 13 '23 at 17:39

taller_ExcelHome

2,232
1
2
12

I tried with the above code and when trying to open the xlsm file error message "Excel cannot open output_file.xlsm because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the file format of the file". – Madhav Rao Aug 14 '23 at 04:08
I have tested the code w/o any issure. My env is `>>> pd.__version__ '1.5.1' >>> xlsxwriter.__version__ '3.1.2'` – taller_ExcelHome Aug 14 '23 at 04:14
Sorry! I it is working for me as well. Thank you for the help – Madhav Rao Aug 14 '23 at 04:52

Pandas corrupting file when writing data from xlsx to xlsm

1 Answers1