5
Sheet '(sheet_name)' already exists and if_sheet_exists is set to 'error'.
Package:
pandas
30911
pandas
30911
Exception Class:
ValueError
Raise code
elf.if_sheet_exists == "replace":
old_wks = self.sheets[sheet_name]
target_index = self.book.index(old_wks)
del self.book[sheet_name]
wks = self.book.create_sheet(sheet_name, target_index)
self.sheets[sheet_name] = wks
elif self.if_sheet_exists == "error":
raise ValueError(
f"Sheet '{sheet_name}' already exists and "
f"if_sheet_exists is set to 'error'."
)
else:
raise ValueError(
f"'{self.if_sheet_exists}' is not valid for if_sheet_exists. "
"Valid options are 'error', 'new' and 'replace'."
Links to the raise (1)
https://github.com/pandas-dev/pandas/blob/b3e335254f46a526ee3ce9bb757eac4011d9d1fe/pandas/io/excel/_openpyxl.py#L430Ways to fix
-1
Error code:
from openpyxl import Workbook
import pandas as pd
from pandas.io.formats.excel import ExcelCell
#Style of cells
sty_b1 = {"font": {"color": "00FF0000"}}
sty_a2 = {"font": {"color": "0000FF00"}}
#Creating cells
initial_cells = [
ExcelCell(col=1, row=0, val=42, style=sty_b1),
ExcelCell(col=0, row=1, val=99, style=sty_a2),
]
with pd.ExcelWriter('new.xlsx',mode ='r+') as writer:
writer.write_cells(initial_cells,sheet_name='H2') #<--- H2 which is exists already in excel file
Explanation:
write_cells function is used in ExcelWriter to write cells in an excel file. When the mode is on 'r+' which means reading and writing, placing, it will give an error. To avoid that error, we can use the if_sheet_exists argument.
if_sheet_exists parameter is to control the behaviour of append mode when writing to existing sheets. It can take only 3 string : "error","replace","new"
Fix code:
from openpyxl import Workbook
import pandas as pd
from pandas.io.formats.excel import ExcelCell
#Style of cells
sty_b1 = {"font": {"color": "00FF0000"}}
sty_a2 = {"font": {"color": "0000FF00"}}
#Creating cells
initial_cells = [
ExcelCell(col=1, row=0, val=42, style=sty_b1),
ExcelCell(col=0, row=1, val=99, style=sty_a2),
]
# if_sheet_exists takes 'error' as a defult
with pd.ExcelWriter('new.xlsx',mode ='r+',if_sheet_exists='replace') as writer:
writer.write_cells(initial_cells,sheet_name='H2')
Jul 09, 2021
