Sheet '(sheet_name)' already exists and if_sheet_exists is set to 'error'.

Package:

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#L430

Ways to fix

-2

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

anonim 13.0k

Add a possible fix

Please authorize to post fix