Pandas.ExcelWriter KeyError when using writer.sheets method

Question

Please help, I don't know why this error is happening. I have used this code previously with no issues. I hope it's not something stupid. Always appreciate the help.

Versions:

python 3.6

pd 0.23.0

xlsxwriter 1.0.4

writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Output:

Traceback (most recent call last):
File "/opt/eclipse/dropins/plugins/org.python.pydev.core_7.2.0.201903251948/pysrc/_pydevd_bundle/pydevd_exec2.py", line 3, in Exec
exec(exp, global_vars, local_vars)
File "<console>", line 1, in <module>
KeyError: 'Sheet1'

The first thing that comes to mind: Have you named your Sheet1 to something else? — prp
– prp, Commented Nov 19, 2019 at 15:22
It doesn't matter what the string is, you're trying to reference something that does not yet exist. — mauve
– mauve, Commented Nov 19, 2019 at 15:27

mauve · Accepted Answer · 2019-11-19 15:22:03Z

8

You didn't create a Sheet 1.

from here there's an example:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1') ***#this is where you create Sheet 1***

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1'] ***#here is where you select it***

answered Nov 19, 2019 at 15:22

mauve

2,7711 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TChi Over a year ago

Thank you @mauve ! You are correct I need to use call to_excel first.

Alireza815 · Accepted Answer · 2021-01-12 11:47:53Z

3

you have to add a sheet to your excel file before using it as follow:

    excel_writer = pandas.ExcelWriter('f2.xlsx',mode ='w',engine='xlsxwriter')
    workbook  = excel_writer.book
    excel_writer.sheets={'Sheet1':workbook.add_worksheet()}
    worksheet = excel_writer.sheets['Sheet1']

answered Jan 12, 2021 at 11:47

Alireza815

3594 silver badges7 bronze badges

Comments

virgilus · Accepted Answer · 2019-11-19 15:22:19Z

0

If you're using Pandas, you should use the .to_excel function. It'd be easier! :)

answered Nov 19, 2019 at 15:22

virgilus

1491 silver badge11 bronze badges

1 Comment

TChi Over a year ago

I agree, in this case I will be adding images and charts that to_excel does not support thanks

Collectives™ on Stack Overflow

Pandas.ExcelWriter KeyError when using writer.sheets method

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related