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

Question

I have a simple use case for `df.to_excel()` that I'm struggling with. I want to write to a specific worksheet tab (let's call it "Data") of an existing XLSX workbook, which could be referenced by formulas and pivots on other tabs. I've tried to modify ExcelWriter in two ways but both produce errors from openpyxl. - Read an existing sheet using get_sheet_by_name (This errors: "NotImplementedError: use 'iter_rows()' instead".) Create a new sheet using create_sheet. (This errors:"ReadOnlyWorkbookException: Cannot create new sheet in a read-only workbook") ``` 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 s…

Accepted Answer

When you load your workbook with `use_iterators=True`, it then `_set_optimized_read()` on the `Workbook` object, which cause it to be loaded read-only.

Thus, 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)
```

It create a file named `my_excel_file.xlsx` and the following output :

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

Hope it helps

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

Solution

Problem