时间戳

首页 > 解决方案 > Openpyxl 在保存时会损坏 xlsx。即使没有进行任何更改

python-3.x - Openpyxl 在保存时会损坏 xlsx。即使没有进行任何更改

问题描述

TL;博士;

我无法将数据添加到我们的一个团队正在使用的 Excel 文件中。excel 文件相当大（+3MB），有几张纸，包含公式和图表，还有图像。

谢天谢地，我需要输入数据的工作表没有，但是，我发现当我尝试保存工作簿时，最终出现以下错误：

Traceback (most recent call last):
  File "test.py", line 5, in <module>
    wb.save("new.xlsx")
  File "C:\Python3\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Python3\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Python3\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Python3\lib\site-packages\openpyxl\writer\excel.py", line 78, in write_data
    self._write_charts()
  File "C:\Python3\lib\site-packages\openpyxl\writer\excel.py", line 124, in _write_charts
    self._archive.writestr(chart.path[1:], tostring(chart._write()))
  File "C:\Python3\lib\site-packages\openpyxl\chart\_chart.py", line 134, in _write
    return cs.to_tree()
  File "C:\Python3\lib\site-packages\openpyxl\chart\chartspace.py", line 193, in to_tree
    tree = super(ChartSpace, self).to_tree()
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 154, in to_tree
    node = obj.to_tree(child_tag)
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 154, in to_tree
    node = obj.to_tree(child_tag)
  File "C:\Python3\lib\site-packages\openpyxl\chart\plotarea.py", line 135, in to_tree
    return super(PlotArea, self).to_tree(tagname)
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 146, in to_tree
    for node in nodes:
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\sequence.py", line 105, in to_tree
    el = v.to_tree(namespace=namespace)
  File "C:\Python3\lib\site-packages\openpyxl\chart\_chart.py", line 107, in to_tree
    return super(ChartBase, self).to_tree(tagname, idx)
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 146, in to_tree
    for node in nodes:
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\sequence.py", line 39, in to_tree
    el = v.to_tree(tagname, idx)
  File "C:\Python3\lib\site-packages\openpyxl\chart\series.py", line 170, in to_tree
    return super(Series, self).to_tree(tagname)
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 154, in to_tree
    node = obj.to_tree(child_tag)
  File "C:\Python3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 154, in to_tree
    node = obj.to_tree(child_tag)
AttributeError: 'str' object has no attribute 'to_tree'

这是我用来执行“另存为”过程的代码，所以不要介意添加数据等等，保存操作会破坏文件：

from openpyxl import load_workbook

wb=load_workbook("Production Monitoring Script.xlsx")
ws=wb['Prod Perf Script Data']
wb.save("new.xlsx")

我尝试了使用 Powershell 的替代解决方案，它奏效了。

$xl=New-Object -ComObject Excel.Application
$wb=$xl.WorkBooks.Open('<path here>\Production Monitoring Script.xlsx')
$ws=$wb.WorkSheets.item(1)
$xl.Visible=$true

$ws.Cells.Item(7, 618)=50

$wb.SaveAs('<path here>\New.xlsx')
$xl.Quit()

它能够在该单元格中保存值“50”。

标签： python-3.xexcelopenpyxlxlsx

解决方案

正如在原始帖子的评论中所讨论的：openpyxl 不支持某些图形和其他项目，即使它们位于未由您的代码修改的工作表中。这不是一个完整的解决方法，但仅在不受支持的对象位于其他工作表中时才有效。

我制作了一个带有两个工作表“TWC”和“UV240 结果”的示例 .xlsx 工作簿。此代码假定标题以“结果”结尾的任何工作表都包含不受支持的图像，并创建两个临时文件 - imageoutput 包含不受支持的图像，而 outputtemp 包含可以被 openpyxl 修改而不会损坏的工作表。然后最后将它们缝合在一起。

它可能是部分效率低下；请编辑或评论改进！

import os
import shutil
import win32com.client

from openpyxl import load_workbook

name = 'spreadsheet.xlsx'
outputfile = 'output.xlsx'
outputtemp = 'outputtemp.xlsx'

shutil.copyfile(name, 'output.xlsx')
wb = load_workbook('output.xlsx')
ws = wb['TWC']

# TWC doesn't have images. Anything ending with 'Results' has unsupported images etc

# Create new file with only openpyxl-unsupported worksheets
imageworksheets = [ws if ws.title.endswith('Results') else '' for ws in wb.worksheets]
if [ws for ws in wb if ws.title != 'TWC']:
    imageoutput = 'output2.xlsx'
    imagefilewritten = False
    while not imagefilewritten:
        try:
            shutil.copy(name, imageoutput)
        except PermissionError as error:
            # Catch an exception here - I usually have a GUI function
            pass
        else:
            imagefilewritten = True

    excel = win32com.client.Dispatch('Excel.Application')
    excel.Visible = False
    imagewb = excel.Workbooks.Open(os.path.join(os.getcwd(), imageoutput))
    excel.DisplayAlerts = False

    for i, ws in enumerate(imageworksheets[::-1]): # Go backwards to avoid reindexing
        if not ws:
            wsindex = len(imageworksheets) - i
            imagewb.Worksheets(wsindex).Delete()

    imagefileupdated = False
    while not imagefileupdated:
        try:
            imagewb.Save()
            imagewb.Close(SaveChanges = True)
            print('Temp image workbook saved.')
        except PermissionError as error:
            # Catch exception
            pass
        else:
            imagefileupdated = True

# Remove the unsupported worksheets in openpyxl
for ws in wb.worksheets:
    if ws in imageworksheets:
        wb.remove(ws)
wb.save(outputtemp)
print('Temp output workbook saved.')

''' Do your desired openpyxl manipulations on the remaining supported worksheet '''

# Merge the outputtemp and imageoutput into outputfile
wb1 = excel.Workbooks.Open(os.path.join(os.getcwd(), outputtemp))
wb2 = excel.Workbooks.Open(os.path.join(os.getcwd(), imageoutput))

for ws in wb1.Sheets:
    ws.Copy(wb2.Sheets(1))

wb2.SaveAs(os.path.join(os.getcwd(), outputfile))
wb1.Close(SaveChanges = True)
wb2.Close(SaveChanges = True)
print(f'Output workbook saved as {outputfile}.')

excel.Visible = True
excel.DisplayAlerts = True

推荐阅读