根据pandas数据框修改现有excel工作簿的多个工作表 [英] Modifying an existing excel workbook's multiple worksheets based on pandas dataframe

问题描述

我目前有一个excel文件，对于最小的可行示例，例如3张纸.我想根据来自2个熊猫数据框(每个工作表1个数据框)的新值来更改这些表中的2个.

I currently have an excel file with, for minimally viable example, say 3 sheets. I want to change 2 of those sheets to be based on new values coming from 2 pandas dataframes (1 dataframe for each sheet).

这是我目前拥有的代码:

This is the code I currently have:

from openpyxl.writer.excel import ExcelWriter
from openpyxl import load_workbook

path = r"Libraries\Documents\Current_Standings.xlsx"
book = load_workbook('Current_Standings.xlsx')
writer = pd.ExcelWriter(path, 'Current_Standings.xlsx', 
engine='openpyxl')
writer.book = writer
Blank_Propensity_Scores.to_excel(writer, sheet_name = 
'Blank_Propensity.xlsx')
Leads_by_Rep.to_excel(writer,sheet_name = 'Leads_by_Rep.xlsx')
writer.save()

运行此命令时，我收到以下错误消息，不知道为什么，因为我查看的每个堆栈溢出答案中只有1个用于openpyxl:

when I run this I get the following error message, not sure why, because every stack overflow answer I have looked at has only 1 item for openpyxl:

TypeError: __new__() got multiple values for argument 'engine'

我还尝试摆脱了engine ='openpyxl'参数，但是当我这样做时，却收到了以下错误消息:

I also tried playing around with getting rid of the engine='openpyxl' argument but when I do that I get the following error message instead:

ValueError: No Excel writer 'Current_Standings.xlsx'

推荐答案

如果在Python命令行上执行命令'help(pd.ExcelWriter)'，则会在第一行看到参数:

If you execute on your Python command line the command 'help(pd.ExcelWriter)' you will see the parameters on the first lines:

class ExcelWriter(builtins.object)
 |  Class for writing DataFrame objects into excel sheets, default is to use
 |  xlwt for xls, openpyxl for xlsx.  See DataFrame.to_excel for typical usage.
 |
 |  Parameters
 |  ----------
 |  path : string
 |      Path to xls or xlsx file.
 |  engine : string (optional)
 |      Engine to use for writing. If None, defaults to
 |      ``io.excel.<extension>.writer``.  NOTE: can only be passed as a keyword
 |      argument.
 |  date_format : string, default None
 |      Format string for dates written into Excel files (e.g. 'YYYY-MM-DD')
 |  datetime_format : string, default None
 |      Format string for datetime objects written into Excel files
 |      (e.g. 'YYYY-MM-DD HH:MM:SS')
 |

换句话说，顺序中的第二个参数是引擎.因此，如果您放置没有爆炸的String，则它被视为引擎(尽管在帮助中有关于仅将此参数作为关键字传递的说明，这似乎是行为).如果再次输入engine ='openpyxl'，那么您将两次定义参数'engine'.

In other words, the second parameter in order is the engine. So if you put a String without any detonation, it is considered as the engine (despite the note on the help about passing this parameter only as keyword, seems that this is the behaviour). If you enter again engine='openpyxl', then you are defining the parameter 'engine' twice.

这是错误原因

TypeError: __new__() got multiple values for argument 'engine'

总而言之，您应该仅使用两个参数来调用ExcelWriter.第一个是Excel文件的路径(我猜是变量"path")和引擎的路径.

In summary, you should call ExcelWriter only with two parameters. The first one is the path of your Excel file (variable 'path', I guess) and the engine.

这篇关于根据pandas数据框修改现有excel工作簿的多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！