使用openpyxl模块写入电子表格会创建损坏的电子表格，如何使用zipfile模块进行修复? [英] Using openpyxl module to write to spreadsheet creates a damaged spreadsheet, how to fix with zipfile module?

问题描述

我有一个使用openpyxl写入电子表格的程序.程序执行后，单元格将按预期填充，但电子表格已损坏. Excel会修复电子表格，然后我可以再次查看它.

I have a program which writes to a spreadsheet using openpyxl. Upon execution of the program, the cells are filled as expected but the spreadsheet becomes damaged. Excel repairs the spreadsheet and I can then 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")

我尝试使用Microsoft的Open XML SDK生产率工具将一个好文件和一个坏文件相互进行比较，并发现styles.xml丢失了.我尝试使用从另一个问题中获得的以下源代码来复制此代码，但这并不能解决我的问题.

I have tried using the Open XML SDK Productivity Tool from Microsoft to compare a good and bad file with each other and noticed that styles.xml is missing. I try to copy this over using the following source code I have obtained from another question, but it does not solve the issue 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)

我可以从Excel生成的修复日志中确认问题出在xl/styles.xml.我需要将此xml文件从好的副本复制到坏的副本.

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

如何复制xl/styles.xml文件，以便程序可以运行而不会损坏output.xlsx?

How can I get the xl/styles.xml file copied so that the program can run without damaging output.xlsx?

我已再次尝试解决此问题.无法从其他Excel文件复制styles.xml的机会；我已经在book.save("output.xlsx")之前从output.xlsx打开styles.xml.保存后，我从save语句之前获取styles.xml并将其写入.不幸的是，这并没有改变任何东西，而且我仍然收到损坏的Excel文件.通过这种尝试，我的测试代码如下所示:

I have made another attempt to fix this issue. In the off chance that styles.xml cannot be copied from a different Excel file; I have opened styles.xml from output.xlsx prior to book.save("output.xlsx"). After saving, I then get the styles.xml from 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)

我尝试另存为全新的Excel文件，但仍然存在相同的问题.我尝试使用zip file从output.xlsx打开并写入新保存的文件，但仍然没有结果.

I have tried saving as a completely new Excel File, but still have the same issue. I tried using zip file to open from output.xlsx and writing 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 issue, it is worth noting that this problem only seems to occur when loading a workbook. I have created another program with spreadsheets that creates a workbook, rather than loading it. As a result of this, the spreadsheet does not saves damaged.

推荐答案

在确认问题与styles.xml有关之后，我确定问题最有可能与书写单元格的样式格式有关.通过使用openpyxl模块中的styles，我已解决了该问题.

After confirming that the issue was with styles.xml, I identified that the issue was most likely with the style formatting of the written cells. By using styles from the openpyxl module, I have fixed the issue.

我声明一个变量，在这种情况下为fontStyle，并设置所有样式设置:

I declare a variable, fontStyle in this case, and set all the style settings:

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

在每个单元格中写入amounts时，我还使用fontStyle设置这些单元格的样式:

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

sheet[cell].font = fontStyle

完成的代码如下:

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

我相信这是可行的，因为编写方法没有默认的样式设置.这可以解释为什么在使用Open XML SDK生产率工具时缺少styles.xml的原因.修复后再次检查此Excel文件时，我可以确认styles.xml不再丢失.

I believe this has worked because the writing method has no default style settings. This would explain why styles.xml was missing when using the Open XML SDK Productivity Tool. Upon checking this Excel file again after the fix, I can confirm that styles.xml is no longer missing.

文件保存后不再损坏，可以再次正常打开.另外，我现在可以执行此脚本以再次写入Excel文件，而无需打开和关闭来对其进行修复.

The file is no longer damaged upon being saved and can be opened normally again. Also, I am now able to execute this script to write to the Excel file again, without having to open and close to repair it.

请注意，我还从原始循环中更改了循环-这是我尝试解决此问题的尝试之一.这对最终结果没有影响-完全取决于所编写单元格的样式.

Note that I have also changed my loop from the original loop - as part of one of my attempts to fix the issue. This has not had an effect on the final outcome - it is all down to the styling of the cells written.

这不能完全解决使用zipfile专门解决问题的问题，但确实可以解决问题.

This doesn't exactly answer the question of solving the issue specifically with zipfile but it does solve the problem.

这篇关于使用openpyxl模块写入电子表格会创建损坏的电子表格，如何使用zipfile模块进行修复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！