CODEWITHSUNDEEP
pythonpandasopenpyxl
Peter
Peter

Reputation: 1128

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

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.

  1. Read an existing sheet using get_sheet_by_name (This errors: "NotImplementedError: use 'iter_rows()' instead".)

  2. 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
    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 obvious? I'm still in pandas 7.2

Upvotes: 5

Views: 8689

Answers (1)

Y__
Y__

Reputation: 1777

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

Upvotes: 3

Related Questions