0

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()

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'

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'
bernando_vialli
  • 947
  • 4
  • 12
  • 27
  • @piRSquared Any help by you a python genius on stackoverflow would be appreciated:) – bernando_vialli Mar 22 '18 at 13:23
  • After reviewing my response I think the problem is another one so I'll delete it . The confussion is on the 'pd.' before the call to ExcelWriter. I thinked that you were using the ExcelWriter from pandas. I'm still thinking on a solution. – migjimen Mar 22 '18 at 19:01
  • @Parfait: I undeleted my response. One of the reasons to delete is because On my local test I get this error when writing the first worksheet: AttributeError: '_Openpyxl22Writer' object has no attribute 'create_sheet'. – migjimen Mar 22 '18 at 19:33
  • @Parfait: You're right. But in this concrete case the variable 'path' already has the name of the file. Thanks for your advice. – migjimen Mar 22 '18 at 20:22
  • I found a solution that worked in another blog post – bernando_vialli Mar 22 '18 at 21:01
  • You are correct. OP redundantly repeats file name. Good catch! We can advise him/her to simply remove second argument. – Parfait Mar 22 '18 at 21:27
  • @mkheifetz, you may want to check [this updated answer](https://stackoverflow.com/a/36450435/5741205) – MaxU - stand with Ukraine Mar 27 '18 at 22:20

1 Answers1

1

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')
 |

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.

This is the cause for error

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

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.

migjimen
  • 551
  • 1
  • 4
  • 6
  • thanks for your response. I am not sure I fully followed what you wrote. Can you write specifically what code you are saying I would need to change it to compared to what I have now? Thank you – bernando_vialli Mar 22 '18 at 13:09
  • I'm talking about this line: 'writer = pd.ExcelWriter(path, 'Current_Standings.xlsx', engine='openpyxl')'. I think it should should be replaced by this: 'writer = pd.ExcelWriter(path, engine='openpyxl')'. I can't see on your code but seems that you imported pandas as pd. – migjimen Mar 22 '18 at 14:00