pandas :使用to_excel写入现有的Excel文件(xlsx) [英] pandas: Writing to an existing excel file (xlsx) using to_excel

问题描述

我有一个正在使用的df.to_excel()的简单用例.我想写一个现有XLSX工作簿的特定工作表选项卡(我们称其为数据")，该公式可以由公式和其他选项卡上的枢轴引用.

我试图用两种方式修改ExcelWriter，但是两种方式都会因openpyxl产生错误.

1. 使用get_sheet_by_name读取现有工作表(此错误:"NotImplementedError:改为使用'iter_rows()'".)

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')
   

有什么想法吗?我缺少明显的东西吗?我仍然在熊猫7.2中

解决方案

使用use_iterators=True加载工作簿时，它随后在Workbook对象上_set_optimized_read()加载，从而使其以只读方式加载. /p>

因此，带有以下代码:

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)

它将创建一个名为my_excel_file.xlsx的文件，并显示以下输出:

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

希望有帮助

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

解决方案

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 :使用to_excel写入现有的Excel文件(xlsx)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！