Openpyxl File Corruption/Pandas to Existing Excel Table

Question

Longtime lurker, first time poster. This forum is an amazing resource but I'm officially stumped.

I have a large and complex xlsm file that I need to manipulate using openpyxl but my final file ends up corrupted every time. Essentially, the program is a file splitter. I am making copies of a master xlsm file using shutil.copy, using openpyxl to modify the file to include only User A's data, saving, and repeating for User B, C, etc.

The master file is 11MB, houses data in multiple Excel tables (not just a range of cells, those two things are quite different), uses a data model, and has pivot tables that auto-update upon opening the file, charts, and slicers that control the charts.

Shutil.copy works fine and I can open the file successfully. I have isolated the issue to opening the copied file using openpyxl, saving, and reopening (disregarding any formatting I need to do). This is when I get the corruption error. I have been successful using a sample file that uses all of the same features on a much smaller scale (135KB) but my file always ends up corrupted when I use the big 11MB file.

Current code snippet:

import openpyxl

#Identify source file and destination
file = "C:\\blah blah\\sourcefile_usera.xlsm"

wb = openpyxl.load_workbook(file, read_only=False, keep_vba=True)

wb.save(file)
wb.close()

Excel error message: "We found a problem with some content in 'sourcefile_usera.xlsm'.  Do you want us to try to recover as much as we can?  If you trust the source of this workbook, click Yes. 

Clicking Yes causes Excel to get stuck in a loop of trying to open the file and I have to force-close Excel through my task manager to get it to stop.

I welcome suggestions for how to address what I think is a file size issue or if anyone can tell me how to successfully write a pandas dataframe back into an existing Excel table while protecting everything else in the file (multiple hidden tabs, pivot tables, charts, data model, etc.).

What I've tried:

• Of course pandas is far easier to use in removing data that isn't for User A. However, because the pivots and charts feed from the *tables *built in the file, I need to protect the integrity of those tables. I can't successfully take a pandas dataframe and re-paste it into the same Excel file within a table. Hence, openpyxl it is.
• Downgrading openpyxl to v3.0.1, 3.0.3, and 2.6.4 as suggested here: Openpyxl Workbook.save function creates a corrupt and un-openable Excel (.xlsx) file
• Changing the master source file to xlsx and saving my destination file as xlsx
• Exploring openpyxl optimised modes: https://openpyxl.readthedocs.io/en/stable/optimized.html but read_only is out because I need to overwrite the file and write_only doesn't seem to be the right answer because I need to protect the integrity of the original file since those tables feed everything else

Answer 1

It sounds like you have N sheets within a workbook. And they contain some fairly challenging formatting details.

Simplify your problem:

• Use your favorite tool, perhaps python or excel, to read the workbook and produce N worksheet.csv files, UTF-8 encoded.
• Read and process as many .CSVs as needed, writing results to .CSV or .XLSX.

Now you're in a better position to notice exactly where things fell apart. And you can conveniently truncate input files to identify the initial troublesome row, using a binary search approach.