使用Pandas.ExcelWriter的writer.sheets方法时触发KeyError求助

安全阿岳

2026-5-14

Fixing KeyError: 'Sheet1' in Pandas 0.23.0 + XlsxWriter

Hey there! Let's break down why you're hitting this KeyError and how to fix it right away.

The Root Cause

In Pandas 0.23.0 (the version you're using), when you first initialize pd.ExcelWriter, the writer.sheets dictionary is empty—it doesn't automatically create a default "Sheet1" for you. You're trying to access writer.sheets['Sheet1'] before any sheet has been added or written to the writer, which is why the error pops up.

Solution 1: Write Your DataFrame First (Most Common Use Case)

If you have a DataFrame to output to "Sheet1", write it to the writer first. This will automatically create the sheet and populate writer.sheets:

import pandas as pd

# Replace this with your actual DataFrame
df = pd.DataFrame({'Column1': [1, 2, 3], 'Column2': ['a', 'b', 'c']})

writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
# Write the DataFrame to Sheet1 first
df.to_excel(writer, sheet_name='Sheet1', index=False)  # Set index=True if you need row indexes

# Now you can safely access the worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Add any extra formatting here if needed (example below)
worksheet.set_column('A:B', 18)  # Widen columns for readability

# Don't forget to save the file!
writer.save()

Solution 2: Create an Empty Sheet Manually

If you need an empty "Sheet1" (no DataFrame to write initially), create it via the workbook and manually register it in writer.sheets:

writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
workbook = writer.book

# Create an empty Sheet1 using the workbook object
worksheet = workbook.add_worksheet('Sheet1')
# Register the sheet with the writer's sheets dictionary
writer.sheets['Sheet1'] = worksheet

# Now you can work with the empty worksheet (example below)
worksheet.write('A1', 'This is an empty sheet!')

writer.save()