Pandas: Writing to an existing excel file (xlsx) using to_excel

Question

I have a simple example of using df.to_excel() that I am df.to_excel() with. I want to write to a special tab (let it be called "Data") an existing XLSX book that can be referenced by formulas and reference points on other tabs.

I tried changing ExcelWriter in two ways, but both create errors from openpyxl.

• Read the existing sheet using get_sheet_by_name (These errors are: "NotImplementedError: use" iter_rows () "instead.)

• Create a new sheet using create_sheet. (These errors are: "ReadOnlyWorkbookException: Unable to create new sheet in read-only book")

   df=DataFrame() from openpyxl.reader.excel import load_workbook book = load_workbook('my_excel_file.xlsx', use_iterators=True) # Assume my_excel_file.xlsx contains a sheet called 'Data' class temp_excel_writer(ExcelWriter): # I need this to inherit the other methods of ExcelWriter in io/parsers.py def __init__(self, path, book): self.book=book test_sheet=self.book.create_sheet(title='Test') # This errors: ReadOnlyWorkbookException self.use_xlsx = True self.sheet_names=self.book.get_sheet_names() self.actual_sheets=self.book.worksheets self.sheets={} for i,j in enumerate(self.sheet_names): self.sheets[j] = (self.actual_sheets[i],1) self.cur_sheet = None self.path = save my_temp_writer=temp_excel_writer('my_excel_file.xlsx', book) df.to_excel(my_temp_writer, sheet_name='Data') 

Any thoughts? Am I missing something? I'm still in pandas 7.2

Answer 1

When you load your book with use_iterators=True , then _set_optimized_read() on the Workbook object, which makes it load read-only.

So with the following code:

 from openpyxl.reader.excel import load_workbook book = load_workbook('t.xlsx', use_iterators=False) # Assume t.xlsx contains ['Data', 'Feuil2', 'Feuil3'] print book.get_sheet_names() class temp_excel_writer(): def __init__(self, path, book): self.book=book test_sheet=self.book.create_sheet(title='Test') # No exception here now self.book.save(path) self.use_xlsx = True self.sheet_names=self.book.get_sheet_names() print self.sheet_names self.actual_sheets=self.book.worksheets self.sheets={} for i,j in enumerate(self.sheet_names): self.sheets[j] = (self.actual_sheets[i],1) self.cur_sheet = None self.path = path # I had to modify this line also my_temp_writer = temp_excel_writer('my_excel_file.xlsx', book) 

Creates a file called my_excel_file.xlsx and the following output:

  ['Data', 'Feuil2', 'Feuil3'] ['Data', 'Feuil2', 'Feuil3', 'Test'] 

Hope this helps