logo标签列表

无法打开pandas xlsxwriter生成的Excel (XLSX)文件

python-3.xexcelpandasxlsxwriter
7
在MS Excel中打开XLSX文件时，会出现错误对话框："我们发现filename.xlsx中的一些内容存在问题..."。 点击"Yes"尝试恢复将导致出现以下XML错误消息：
 <?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
 <logFileName>error359720_09.xml</logFileName>
 <summary>Errors were detected in file 'C:\Users\username\Github\Project\Data\20200420b.xlsx'</summary>
-<removedRecords>
 <removedRecord>Removed Records: Formula from /xl/worksheets/sheet1.xml part</removedRecord>
 </removedRecords>
 </recoveryLog>

Excel文件是由使用xlsxwriter引擎的pandas生成的。我的Python代码将CSV文件作为pandas dataframe读取，对一些数据进行操作，使用text_wrap格式化输出，将文本对齐到单元格的左边和顶部，设置列特定的宽度，并对一些列设置日期格式。以下是代码的摘录。
import pandas as pd
.
# read CSV into pandas dataframe, etc.
.
xlsxfile = '20200420b.xlsx'
writer = pd.ExcelWriter(xlsxfile, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
.
# several worksheet.add_format() and worksheet.set_column() statements define the formats
.
writer.save()

挖掘Excel文件XML并对比原始和修复后的Sheet1.xml文件，可确定以下差异：
  • 修复后文件的worksheet标签中有额外的xmlns参数。
  • 修复后的文件在Sheet1.xml的sheetFormatPr标签和其他XML文件中包含'x14ac:dyDescent="0.25"'。
  • 原始文件中相邻列的若干等效但不同的列格式被合并成修复后文件中的范围。

    • 问题：

    - 有人了解此问题的原因和潜在解决方法吗？
    - 此问题在给定的输入CSV文件上始终发生，但并不会在所有CSV文件上发生。
    - 有没有办法欺骗 pandas/xlsxwriter 使用 excel_version = 2010 设置？
    - 这可能会解决打开错误的问题吗？
    - 其他建议？
    - 非常感谢您提供任何想法、问题或建议。

    详细信息：

    - Microsoft Excel for Office 365 MSO (16.0.11929.20436) 32 位
    - Python 3.7.2 (tags/v3.7.2:9a3ffc0492, Dec 23 2018, 22:20:52) [MSC v.1916 32 bit (Intel)]
    - pandas v. 1.0.3
    - XlsxWriter v. 1.2.8
    - 我的Python脚本报告xlsxwriter worksheet.excel_version = 2007。
    - XlsxWriter代码（https://github.com/jmcnamara/XlsxWriter/blob/master/xlsxwriter/worksheet.py）包括一个语句，如果worksheet.excel_value = 2010，则包含'x14ac:dyDescent="0.25"'值。


    以下是原始和修复后的XML文件（部分，不包括单元格数据）。

    原始：

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1:S746"/>
<sheetViews>
<sheetView tabSelected="1" zoomScale="80" zoomScaleNormal="80" workbookViewId="0"/>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
<col min="1" max="1" width="9.140625" style="1"/>
<col min="2" max="2" width="13.42578125" style="1" customWidth="1"/>
<col min="3" max="3" width="11.28515625" style="1" customWidth="1"/>
<col min="4" max="4" width="36.7109375" style="1" customWidth="1"/>
<col min="5" max="5" width="18.7109375" style="1" customWidth="1"/>
<col min="6" max="6" width="40.7109375" style="1" customWidth="1"/>
<col min="7" max="7" width="9.140625" style="1"/>
<col min="8" max="8" width="9.140625" style="1"/>
<col min="9" max="9" width="9.140625" style="1"/>
<col min="10" max="10" width="15.5703125" style="1" customWidth="1"/>
<col min="11" max="11" width="13.7109375" style="1" customWidth="1"/>
<col min="12" max="12" width="14.28515625" style="1" customWidth="1"/>
<col min="13" max="13" width="22.28515625" style="2" customWidth="1"/>
<col min="14" max="14" width="22.28515625" style="2" customWidth="1"/>
<col min="15" max="15" width="13" style="2" customWidth="1"/>
<col min="16" max="16" width="24.140625" style="2" customWidth="1"/>
<col min="17" max="17" width="23.5703125" style="2" customWidth="1"/>
<col min="18" max="18" width="13" style="2" customWidth="1"/>
<col min="19" max="19" width="9.140625" style="1"/>
</cols>
<sheetData>

    修复完成：

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
mc:Ignorable="x14ac xr xr2 xr3" 
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" 
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" 
xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" 
xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" 
xr:uid="{00000000-0001-0000-0000-000000000000}">
<dimension ref="A1:S746"/>
<sheetViews>
<sheetView tabSelected="1" zoomScale="80" zoomScaleNormal="80" workbookViewId="0"/>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
<cols>
<col min="1" max="1" width="9.140625" style="1"/>
<col min="2" max="2" width="13.42578125" style="1" customWidth="1"/>
<col min="3" max="3" width="11.28515625" style="1" customWidth="1"/>
<col min="4" max="4" width="36.7109375" style="1" customWidth="1"/>
<col min="5" max="5" width="18.7109375" style="1" customWidth="1"/>
<col min="6" max="6" width="40.7109375" style="1" customWidth="1"/>
<col min="7" max="9" width="9.140625" style="1"/>
<col min="10" max="10" width="15.5703125" style="1" customWidth="1"/>
<col min="11" max="11" width="13.7109375" style="1" customWidth="1"/>
<col min="12" max="12" width="14.28515625" style="1" customWidth="1"/>
<col min="13" max="14" width="22.28515625" style="2" customWidth="1"/>
<col min="15" max="15" width="13" style="2" customWidth="1"/>
<col min="16" max="16" width="24.140625" style="2" customWidth="1"/>
<col min="17" max="17" width="23.5703125" style="2" customWidth="1"/>
<col min="18" max="18" width="13" style="2" customWidth="1"/>
<col min="19" max="19" width="9.140625" style="1"/>
</cols>
<sheetData>

    额外信息：

    为了比较，下面提供一个与MS Excel打开时没有错误的相似文件的Sheet1.xml。它缺少“x14ac：dyDescent =”0.25“”值。

    <?xml version="1.0" encoding="UTF-8" standalone="true"?>
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dimension ref="A1:AI105"/>
<sheetViews>
<sheetView workbookViewId="0" zoomScaleNormal="80" zoomScale="80" tabSelected="1"/>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
<col customWidth="1" style="1" width="13.42578125" max="1" min="1"/>
<col customWidth="1" style="1" width="10.7109375" max="2" min="2"/>
<col customWidth="1" style="1" width="36.7109375" max="3" min="3"/>
<col customWidth="1" style="1" width="13.7109375" max="4" min="4"/>
<col customWidth="1" style="1" width="13.7109375" max="5" min="5"/>
<col customWidth="1" style="1" width="11.5703125" max="6" min="6"/>
<col customWidth="1" style="1" width="18.7109375" max="7" min="7"/>
<col customWidth="1" style="1" width="20.7109375" max="8" min="8"/>
<col customWidth="1" style="1" width="24.85546875" max="9" min="9"/>
<col customWidth="1" style="1" width="34.85546875" max="10" min="10"/>
<col customWidth="1" style="1" width="23.5703125" max="11" min="11"/>
<col customWidth="1" style="1" width="37.140625" max="12" min="12"/>
<col customWidth="1" style="1" width="44.42578125" max="13" min="13"/>
<col customWidth="1" style="1" width="16.140625" max="14" min="14"/>
<col customWidth="1" style="1" width="14.28515625" max="15" min="15"/>
<col customWidth="1" style="1" width="15.85546875" max="16" min="16"/>
<col customWidth="1" style="1" width="13.42578125" max="17" min="17"/>
<col customWidth="1" style="1" width="20.42578125" max="18" min="18"/>
<col customWidth="1" style="1" width="11.42578125" max="19" min="19"/>
<col style="1" width="9.140625" max="20" min="20"/>
<col customWidth="1" style="1" width="13.7109375" max="21" min="21"/>
<col customWidth="1" style="1" width="14.42578125" max="22" min="22"/>
<col customWidth="1" style="1" width="18.42578125" max="23" min="23"/>
<col customWidth="1" style="2" width="13" max="24" min="24"/>
<col customWidth="1" style="2" width="24.140625" max="25" min="25"/>
<col customWidth="1" style="2" width="22.28515625" max="26" min="26"/>
<col customWidth="1" style="2" width="24" max="27" min="27"/>
<col customWidth="1" style="2" width="23.5703125" max="28" min="28"/>
<col customWidth="1" style="1" width="18.7109375" max="29" min="29"/>
<col customWidth="1" style="1" width="18" max="30" min="30"/>
<col customWidth="1" style="1" width="19.140625" max="31" min="31"/>
<col customWidth="1" style="1" width="30.42578125" max="32" min="32"/>
<col customWidth="1" style="1" width="19.85546875" max="33" min="33"/>
<col customWidth="1" style="1" width="18.28515625" max="34" min="34"/>
<col customWidth="1" style="1" width="40.7109375" max="35" min="35"/>
</cols>
<sheetData>
    1
    好的分析。我认为问题出在文件中的一个意外公式上，因为Excel的removedRecord消息是关于公式的。可能是类似于'=something'这样的字符串被默认解释为公式。尝试关闭公式转换：pd.ExcelWriter('filename.xlsx', engine='xlsxwriter', options={'strings_to_formulas': False})。 - jmcnamara
    1
    顺便说一下，这个问题与“x14ac：dyDescent”无关。那是一个完全可选的参数，并不会像这样（或任何其他问题）引起问题。可能存在列范围重叠的问题，但从你的XML输出来看，似乎不是这个情况。 - jmcnamara
    感谢您的反馈和建议。将options={'strings_to_formulas':False}添加到语句中立即解决了问题，而不需要对代码进行其他更改。顺便说一句，worksheet.excel_version现在报告它是2010年（而不是2007年）。我的意图是拥有一个完全没有公式（有意或无意）的Excel文件。这是很好的信息。感谢您的及时解决。 - BalooRM
    2
    当我打开我的Excel文件时，遇到了相同的错误。我已经安装了Pandas，但没有安装xlsxwriter。在我的Conda环境中，我安装了xlsxwriter 1.3.7，然后错误消失了？脚本甚至可以正常运行，并且Excel文件可以在没有import xlsxwriter的情况下创建？但是，有一个警告：C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\xlsxwriter\workbook.py:329: UserWarning: Calling close() on already closed file. warn("Calling close() on already closed file.") - Pfalbaum
    2
    通过安装 xlsxwriter 解决了我的问题。同时还遇到了 Calling close() on an already closed file 的错误，通过从代码中删除 writer.save() 解决了该问题。 - chakalakka
    1个回答
    10

    根据XlsxWriter的作者@jmcnamara的建议，问题被识别为一个无意的公式。通过使用strings_to_formulas选项关闭字符串转换为公式的默认转换，问题得到了解决。

    pd.ExcelWriter('filename.xlsx', engine='xlsxwriter', options={'strings_to_formulas': False})

    注意（如果使用上述代码会出现未来警告）：
    FutureWarning：使用**kwargs已被弃用，请改用engine_kwargs。
    pd.ExcelWriter('filename.xlsx', engine='xlsxwriter', engine_kwargs={'options':{'strings_to_formulas': False}})

    感谢Suprateem Banerjee在这里识别出了此解决方案。

    嗨@BalooRM，我使用openpyxl引擎时遇到了相同的错误，并且strings_to_formulas解决方案对该引擎无效。您知道是否有任何方法可以通过使用openpyxl解决相同的错误吗？谢谢！ - Xavier Sun
    @XavierSun，我在使用openpyxl时没有遇到过这个问题。 - BalooRM
    1
    感谢@BalooRM。希望有人能提供解决方案。 - Xavier Sun
    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接