BUG: "with pd.ExcelWriter" produces a corrupt Excel file in case of .xlsm extension

[maximrobi]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the master branch of pandas.

Reproducible Example

import pandas as pd

excel_path = r"D:\my_test.xlsm"
with pd.ExcelWriter(excel_path, mode='a') as writer:
    # df.to_excel(writer, sheet_name='Sheet1')
    pass

df_excel = pd.read_excel(excel_path, 'Sheet1')

Issue Description

The specified Excel file gets corrupted, despite the append mode. Changing its extension to .xlsx can retrieve the raw data from it, but the macro is forever gone.

In my example I used with and had only a pass statement inside it, yet the file became useless.

Excel_macro_corruption.zip

Q1: Can we know for sure that the default engine is used for .xlsm files?
Q2: Should I contact OpenPyxl instead of you with this issue?

Expected Behavior

As I've read since my first encounter of this bug, ExcelWriter doesn't support macros, but a warning of some kind would be nice before wiping out a carefully written VBA code from the Excel file.

Installed Versions

INSTALLED VERSIONS

commit : 945c9ed
python : 3.7.4.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19041
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 9, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None
pandas : 1.3.4
numpy : 1.17.3
pytz : 2021.3
dateutil : 2.8.2
pip : 19.3.1
setuptools : 40.8.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[twoertwein]

Do you have the same issue when using openpyxl directly (using it to read the file and then write it again)?

I assume you still lose your macro with the non-empty with-block?

If I understand the pandas code: pandas will use the engine (openpyxl in this case) to read the excel workbook, a user can append data to it (you choose not to, that should be okay - hopefully), and when pandas closes the file it asks the engine to write the potentially changed workbook back to the file.

If the engine does not support some excel features, these features will probably get lost.

[twoertwein]

It would be good to emphasize that features not supported by the underlying engine (which I think is always openpyxl for mode="a") are lost in the mode section of ExcelWriter:

[maximrobi]

Do you have the same issue when using openpyxl directly (using it to read the file and then write it again)?

I assume you still lose your macro with the non-empty with-block?

If I understand the pandas code: pandas will use the engine (openpyxl in this case) to read the excel workbook, a user can append data to it (you choose not to, that should be okay - hopefully), and when pandas closes the file it asks the engine to write the potentially changed workbook back to the file.

If the engine does not support some excel features, these features will probably get lost.

My main problem is, that after executing the code (which should do nothing..) my file looks intact with .xlsm extension, but I can't open it since it got corrupted somehow. I need to manually change the extension to .xlsx to be able to at least read the info in the cells - but this way the macro is well and truly lost. I will try to replicate the same behavior with openpyxl tomorrow and get back to you.

[maximrobi]

Does it really choose a default engine based on the mode? I read that it's based on the filetype/extension:

Based on this, my .xlsm shouldn't even work - since it's not supported by any of the engines. I have no idea what happens when I execute the code. Can I debug somehow to see which engine is getting selected by default?

[twoertwein]

Yes, you are right, it doesn't pick based on the mode (somehow I thought the only engine that supported it was openpyxl).

The engine is inferred by extension and based on the content. You could add a breakpoint in your empty with block and check writer.engine

[rhshadrach]

Or just print:

with pd.ExcelWriter('test.xlsx', mode='a', engine='openpyxl') as writer:
    print(writer.engine)

[rhshadrach]

@maximrobi - can you post a file example before the corruption occurs?

[maximrobi]

@maximrobi - can you post a file example before the corruption occurs?

@rhshadrach You have it in the zip file under Issue Description. The my_test - backup.xlsm should work 100%, just change the name to what the script is expecting to find.

[maximrobi]

Yes, you are right, it doesn't pick based on the mode (somehow I thought the only engine that supported it was openpyxl).

The engine is inferred by extension and based on the content. You could add a breakpoint in your empty with block and check writer.engine

@twoertwein Thanks for the info! I managed to debug the with statement and I can confirm that it is using openpyxl for my .xlsm file.

[rhshadrach]

I can't duplicate on Linux. I wonder if the code in OpenpyxlWriter.save is causing the issue. Upon the context manager exiting, we run

        self.book.save(self.handles.handle)
        if "r+" in self.mode and not isinstance(self.handles.handle, mmap.mmap):
            # truncate file to the written content
            self.handles.handle.truncate()

I haven't been able to discern what this truncate function is doing.