将数据框写入现有Excel文件中的多个工作表中.打开Excel文件时获取“我们在X.xlsx中发现了一些内容的问题" [英] Writing dataframes to multiple sheets in existing Excel file. Get 'We Found Problem with some content in X.xlsx' when opening excel file

问题描述

我正在基于现有的excel文件创建一些df.然后，我将这些df中的每一个写入不同(现有excel)文件中的单独工作表中.脚本执行正常，但是当我打开excel文件时，将dfs写入其中，并收到以下错误消息:我们在'X.xlsx'中发现了某些内容问题...

I'm creating a few dfs based on existing excel files. I'm then writing each of those dfs to their own separate sheet in a different (existing excel) file. Script executes fine, but when I open the excel file the dfs were written to I get the following error msg: "We found a problem with some content in 'X.xlsx'...

我尝试过不使用openpyxl的原因，因为类似帖子中的几个答案表明您不必使用openpyxl；但是，熊猫文档指示如果写入.xlsx，则需要使用openpyxl.

I tried this not using openpyxl as several answers on similar posts indicated you didn't have to use openpyxl; however, pandas docs indicate you need to use openpyxl if writing to .xlsx.

import pandas as pd
from openpyxl import load_workbook

df_complete = pd.read_excel('completed_contracts_2019.xlsx', 
index_col=None)
df_wip_out = pd.read_excel('wip19.xlsx', index_col=None)
df_in = pd.read_excel('wip_18_to_19.xlsx', index_col=None)

with pd.ExcelWriter('Final_Template.xlsx', engine='openpyxl') as writer:
    writer.book = load_workbook('Final_Template.xlsx')
    df_complete.to_excel(writer, sheet_name='complete', index=False)
    df_wip_out.to_excel(writer, sheet_name='wipout', index=False)
    df_in.to_excel(writer, sheet_name='wipin', index=False)

我希望打开excel文件而不会出现错误.

I expect to open the excel file without getting the error.

推荐答案

openpyxl遇到相同的问题，但实际上可能不是openpyxl的问题.

Hit the same issue with openpyxl, but actually it might not be the problem of openpyxl.

根据我的经验，在弹出窗口中显示警报，我们发现某些内容存在问题……".只需检查一下错误是什么，最后发现，在Excel中，如果数据格式为常规"，则无法输入以"=="开头的字符串，否则Excel会将其视为语法错误.但是，如果数据格式为文本"，则不会有问题.

Based on my experience, after I got a pop window with "Alert We found a problem with some content in......". Just look into what was the error, and finally found that, in Excel if the data format is "General", you cannot input the string starts with "==", otherwise Excel will deem it as a syntax error. But if the data format is "Text", there will be no problem.

两种解决方法:1)如果您确定某些字符串以"=="开头，并且在您的情况下不是必需的，则可能必须更改第一个"="或在开头插入另一个字符.然后使用openpyxl将其写入xls文件.这就是我的申请.2)或者您可以使用openpyxl将单元格的数据格式(可能是"number_format")设置为文本".我没有成功尝试这种方法，因为在手动将单元格数据格式设置为文本"之后，使用openpyxl读取了其"number_format"，得到了值"@"(不是文本").但是，当我使用openpyxl将"number_format"设置为"@"，然后在其中写入"=="时，仍然遇到问题.

Two solution: 1) If you are sure some of the string starts with "==", and it is not that necessary in your case, you might have to change the first "=" or insert another character at the beginning. Then write it into xls file with openpyxl. This is what I applied. 2) Or you may use openpyxl set the data format of cell (might be "number_format") as "Text". I didn't successfully try this way, because after manually setting a cell data format as "Text", read its "number_format" with openpyxl, got the value of "@"(not "Text"). But when I set "number_format" to "@" with openpyxl, then wrote "==" into it, it still hit the issue.

除了"=="之外，不确定Excel是否还会将任何其他字符串视为语法错误.无论如何，如果由于"=="碰巧遇到问题，则可以尝试第一种解决方案.

Besides "==", not sure if any other string will also be deemed as a syntax error by Excel. Anyway, if you happen to hit the issue due to "==", you can try the first solution as a workaround.

这篇关于将数据框写入现有Excel文件中的多个工作表中.打开Excel文件时获取“我们在X.xlsx中发现了一些内容的问题"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！