openpyxl生成的Excel文件打开时需要修复 [英] Excel file generated by openpyxl need repaired when opening

问题描述

在 django 中使用 openpyxl=2.3.3 生成 excel 文件作为 HttpResponse 时遇到问题.

I have a problem when generate excel file as HttpResponse in django using openpyxl=2.3.3.

示例代码:

        # I just simply read a file and export
        wb = load_workbook('test.xlsx')  # WorkBook object.
        ws = wb.get_active_sheet()
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8')
        response['Content-Disposition'] = 'attachment; filename="test1.xlsx"'
        response.write(save_virtual_workbook(wb))

libreOffice可以正常打开excel文件.但是对于Microsoft excel，打开时会显示文件需要修复的警告.

The excel file can be opened normally by libreOffice. But for Microsoft excel, it shows warning that file need repaired when open.

我尝试通过 OpenXML SDK 打开损坏的文件，这是验证结果:

I tried open broken file by OpenXML SDK and this is the validation result:

然后我对比了原始文件和准备文件(左边是破文件):

Then I compared the original file and prepare files (broken file is on the left):

因此，spreadsheetml/2006/main 中似乎不存在 的结构.Microsoft Excel 引发验证警告，只需添加忽略"即可.还有不正确的 xfId:

So it seems the structure of doesn't exists in spreadsheetml/2006/main. Microsoft Excel raise validation warning and just add 'Ignore' to it. Also there are incorrect xfIds:

我目前正在使用 python 2.7 和 openpyxl 2.3.3 出于某种原因目前无法升级到 3.0.您能否建议一些解决方法或一些手动配置以避免来自 Excel 的警告.

I am currently using python 2.7 and openpyxl 2.3.3 for some reason cannot upgrade to 3.0 at the moment. Could you please suggest some work around or some manually config to avoid the warning from Excel.

推荐答案

我发现了这个问题:openpyxl读写后docProps/core.xml文件好像变了.

I found the problem: the file docProps/core.xml seems to be changed after read and write by openpyxl.

我通过添加预模块、解压缩和存储 docProps/core.xml 添加了一个变通方法.在导出前通过 openpyxl 处理后，我解压并用原始文件覆盖此文件.

I added a work around by adding pre-module, unzip and store docProps/core.xml. After process by openpyxl before export, I unzip and overwrite this file with the original one.

它对我有用，Excel 不再显示验证错误.

It works for me and Excel show no validation error anymore.

更新:core.xml 中不正确的部分:

Updated: incorrect part from core.xml:

<dct:created xmlns:dct="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="dcterms:W3CDTF">2016-06-30T14:08:36Z</dct:created>
<dct:modified xmlns:dct="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="dcterms:W3CDTF">2019-08-28T13:46:19Z</dct:modified>

它应该是dcterms:created"或dcterms:modified".

It should be 'dcterms:created' or 'dcterms:modified' instead.

然后我跟踪这个 '{http://purl.org/dc/terms/}created' 它真的返回 dct:created.所以可能不是 openpyxl 错误而是这个都柏林核心错误.

Then I track this '{http://purl.org/dc/terms/}created' it really return dct:created. So might not be openpyxl bug but this dublin core bug.

这篇关于openpyxl生成的Excel文件打开时需要修复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！