Using the openpyxl module to write to a spreadsheet creates a damaged spreadsheet, how to fix it with a zipfile module?

Question

I have a program that writes to a spreadsheet using openpyxl . After the program runs, the cells fill up as expected, but the table becomes damaged. Excel corrects the spreadsheet and I can view it again.

 import openpyxl from openpyxl import load_workbook amounts, row = [1, 2, 3, 4, 5], 2 book = load_workbook("output.xlsx") sheet = book.active for i, value in enumerate(amounts): sheet.cell(column=i+1, row=row, value=value) print ("Sheet updating complete.") book.save("output.xlsx") 

I tried using Microsoft's Open XML SDK Performance Tool to compare good and bad files with each other and noticed that styles.xml missing. I am trying to copy this using the following source code that I got from another question, but this does not solve the problem for me.

 import zipfile with zipfile.ZipFile('outputcopy.xlsx', 'r') as zgood: styles_xml = zgood.read('xl/styles.xml') with zipfile.ZipFile('output.xlsx', 'a') as zbad: zbad.writestr('xl/styles.xml', styles_xml) 

I can confirm from the Excel repair log that the problem is with xl/styles.xml . I need to copy this xml file from a good copy to a bad copy.

How can I copy the xl/styles.xml so that the program can work without compromising output.xlsx ?

I tried to fix this problem. In case styles.xml cannot be copied from another Excel file; I opened styles.xml from output.xlsx to book.save("output.xlsx") . After saving, I get styles.xml before the save statement and write it. Unfortunately, this has not changed anything, and I am still getting a damaged Excel file. With this attempt, my test code looks like this:

 import openpyxl import zipfile from openpyxl import load_workbook amounts, indexValue, row = [1, 2, 3, 4, 5], 0, 2 book = load_workbook("output.xlsx") sheet = book.active for i, value in enumerate(amounts): sheet.cell(column=i+1, row=row, value=value) print ("Sheet updating complete.") with zipfile.ZipFile('output.xlsx', 'r') as zgood: styles_xml = zgood.read('xl/styles.xml') book.save("output.xlsx") with zipfile.ZipFile('output.xlsx', 'a') as zbad: zbad.writestr('xl/styles.xml', styles_xml) 

I tried to save as a completely new Excel file, but still have the same problem. I tried using a zip file to open with output.xlsx and write to the newly saved file, but still no result.

 import openpyxl import zipfile from openpyxl import load_workbook amounts, indexValue, row, cell = [1, 2, 3, 4, 5], 0, 2, "A2" book = load_workbook("output.xlsx") sheet = book.active while indexValue != 5: sheet[cell] = amounts[indexValue] indexValue += 1 cell = chr(ord(cell[0]) + 1) + str(cell[1]) print ("Sheet updating complete.") book.save("test.xlsx") with zipfile.ZipFile('output.xlsx', 'r') as zgood: styles_xml = zgood.read('xl/styles.xml') with zipfile.ZipFile('test.xlsx', 'a') as zbad: zbad.writestr('xl/styles.xml', styles_xml) 

Although I have already fixed this problem, it is worth noting that this problem only occurs when the book is downloaded. I created another spreadsheet program that creates a workbook, rather than loading it. As a result, the table does not save the damaged ones.

Answer 1

After confirming that the problem was with styles.xml , I determined that the problem was most likely related to the formatting style of the recorded cells. Using styles from the openpyxl module, I fixed the problem.

In this case, I declare the fontStyle variable and set all the style parameters:

 fontStyle = Font(name="Calibri", size=12, color=colors.BLACK) 

When writing amounts to each cell, I also set the style of these cells using fontStyle :

 sheet[cell].font = fontStyle 

The completed code is as follows:

 import openpyxl from openpyxl import load_workbook from openpyxl.styles import colors from openpyxl.styles import Font, Color fontStyle = Font(name="Calibri", size=12, color=colors.BLACK) amounts, indexValue, cell = [1, 2, 3, 4, 5], 0, "A2" book = load_workbook("output.xlsx") sheet = book.active while indexValue != 5: sheet[cell] = amounts[indexValue] sheet[cell].font = fontStyle indexValue += 1 cell = chr(ord(cell[0]) + 1) + str(cell[1]) print ("Sheet updating complete.") book.save("output.xlsx") 

I believe this worked because the recording method has no default style settings. This explains why styles.xml missing when using the Open XML SDK Performance Tool. After checking this Excel file again after the correction, I can confirm that styles.xml no longer disappears.

When saving, the file is no longer damaged and can be opened normally again. In addition, I can now run this script to write to the Excel file again, without having to open and close it.

Note that I also changed my loop from the original loop - as part of one of my attempts to fix the problem. This did not affect the final result - the whole thing is to stylize the written cells.

This does not exactly answer the question about solving the problem using the zipfile , but it solves the problem.