从 pandas xlsxwriter打开Excel(XLSX)文件时出错 [英] Error opening Excel (XLSX) files from pandas xlsxwriter

查看：132
本文介绍了从 pandas xlsxwriter打开Excel(XLSX)文件时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在MS Excel中打开XLSX文件时，出现错误对话框:我们在filename.xlsx中发现了某些内容的问题……"单击是"尝试恢复会产生以下XML错误消息:

Upon opening an XLSX file in MS Excel, an error dialog is presented: "We found a problem with some content in filename.xlsx ..." Clicking "Yes" to attempt recovery yields the following XML error message: 

 <?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引擎生成的.我的python代码正在读取一个CSV文件作为熊猫数据框，处理一些数据，使用text_wrap格式化输出，将文本与单元格的左侧和顶部对齐，设置特定于列的宽度，并为某些列设置日期格式.代码摘录如下.

The Excel file was produced by pandas with the xlsxwriter engine. My python code is reading a CSV file as a pandas dataframe, manipulating some of the data, formatting the output with text_wrap, aligning text to the left and top of the cell, setting column-specific widths, and for some columns setting date formats. Excerpts of the code appear below. 

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文件进行比较，可以发现以下差异:

Digging into the Excel file XML and doing a diff of the original and repaired Sheet1.xml files identifies the following differences:

问题:

-是否有人对问题的原因和可能的解决方案有深入的了解?
-对于给定的输入CSV文件，始终会发生此问题，但并非所有CSV文件都会发生此问题.
-是否可以通过excel_version = 2010设置来欺骗pandas/xlsxwriter?
-这样是否可以解决打开问题的错误?
-其他建议?
-预先感谢您的任何想法，问题或建议.

Questions:

- Does anyone have insights into the cause of the issue and a potential resolution?
- This issue happens consistently with a given input CSV file but does not happen with all CSV files.
- Is there any way to trick pandas/xlsxwriter into using the excel_version = 2010 setting?
- Would this likely resolve the error on opening issue?
- Other suggestions?
- Thank you in advance for any thoughts, questions, or suggestions.


-Office 365 MSO(16.0.11929.20436)32位的Microsoft Excel
-Python 3.7.2(tags/v3.7.2:9a3ffc0492，2018年12月23日，22:20:52)[MSC v.1916 32位(Intel)]
-熊猫v.1.0.3
-XlsxWriter 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"'值.

- Microsoft Excel for Office 365 MSO (16.0.11929.20436) 32-bit
- 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
- My python script reports xlsxwriter worksheet.excel_version = 2007.
- The XlsxWriter code (https://github.com/jmcnamara/XlsxWriter/blob/master/xlsxwriter/worksheet.py) includes a statement to include the 'x14ac:dyDescent="0.25"' value if worksheet.excel_value = 2010.


原始和修复的XML文件(部分地，不包括单元数据)包括在下面.


The original and repaired XML files (in part, excluding cell data) are included below. 

<?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>

额外:

为进行比较，下面给出了来自可比较文件的Sheet1.xml，该文件在MS Excel中打开时没有错误.它缺少'x14ac:dyDescent ="0.25"'值.

Extra:

For comparison, the Sheet1.xml from a comparable file that does not give an error upon opening in MS Excel is given below. It lacks the 'x14ac:dyDescent="0.25"' value. 

<?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>

推荐答案

根据XlsxWriter的作者@jmcnamara的建议，该问题被确定为意外的公式.通过使用 strings_to_formulas 选项关闭字符串到公式的默认转换，此问题已解决.

As suggested by @jmcnamara, the author of XlsxWriter, the issue was identified to be an unintended formula. By turning off the default conversion of strings to formulas with the strings_to_formulas option, the issue was resolved. 

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

这篇关于从 pandas xlsxwriter打开Excel(XLSX)文件时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Python最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆