pandas：使用 to_excel 写入现有的 excel 文件 (xlsx)

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

提问by Peter

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.

我有一个df.to_excel()我正在努力解决的简单用例。我想写入现有 XLSX 工作簿的特定工作表选项卡（我们称之为“数据”），该选项卡可以由其他选项卡上的公式和枢轴引用。

I've tried to modify ExcelWriter in two ways but both produce errors from openpyxl.

我尝试以两种方式修改 ExcelWriter，但都从 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')
   

1. 使用 get_sheet_by_name 读取现有工作表（此错误：“NotImplementedError: use 'iter_rows()' instead”。）

2. 使用 create_sheet 创建一个新工作表。（此错误：“ReadOnlyWorkbookException：无法在只读工作簿中创建新工作表”）

   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

有什么想法吗？我错过了一些明显的东西吗？我还在熊猫 7.2

采纳答案by Y__

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

当您使用 加载工作簿时use_iterators=True，它将加载_set_optimized_read()到Workbook对象上，这会导致它以只读方式加载。

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.xlsxand the following output :

它创建一个名为的文件my_excel_file.xlsx和以下输出：

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

Hope it helps

希望能帮助到你