python - 扩展 xlsxwriter.Worksheet() 类

Question

我目前覆盖了名为 rpt.Workbook 的 xlsxwriter.Workbook。想向 xlsxwriter.Worksheet() 添加方法，但由于 xlsxwriter.Workbook() 导入工作表，不确定如何在不发生重大抽搐的情况下完成此操作。目前，我必须将工作表对象作为参数传递。

似乎我需要为 xlsxwriter.Workbook() 编写覆盖方法以指向 xlsxwriter.Worksheet() 的自定义类，但不知道如何做。

这是当前正在使用的覆盖 rpt.Workbook():

####################################################################
class Workbook(xlsxwriter.Workbook):
####################################################################
    """\nSpreadsheet class provides methods to build a spreadsheet.
    """

    ####################################################################
    def __init__(self,filename=None, options={}):
    ####################################################################
        try:
           filename = rpt.fname(filename)
        except FileNotFoundError as err:
            log.error(err)
        return False
        log.info("Initializing excel file " + filename)
        super().__init__(filename,options)
    ####################################################################
    def add_tab(self,name=None,data=None,header=None,
            srow=0,scol=0,autocol=True):
    ####################################################################
        """\nProvides method to add_worksheet and add_table in 1 call.

             Required Attribute args:
             name = TAB name
             header = list of header names
             data = list of lists for spreadsheet contents

             Optional Attribute args:
                 srow = starting row for table, default 0
                 scol = starting col for table, default 0
                 autocol = True/False, auto set the column sizes

             add_tab also adds the worksheet.header attribute to
             allow the set_col_by_name function to work
        """

        if not data:
            log.warning("data=[][] required")
            return None
        if not header:
            log.warning("header=[] required")
            return False
        columns = []
        for field in header:
            columns.append({ 'header' : field })
        worksheet = self.add_worksheet(name)
        worksheet.header = header
        tableinfo= {
            'data' : data,
            'columns' : columns
            }
        lastcol = scol + (len(header) - 1)
        lastrow = srow + (len(data) + 1)
        worksheet.add_table(srow,scol,lastrow,lastcol,tableinfo)
       #if autocol:
           #self.auto_set_columns(worksheet=worksheet,data=data,scol=scol)
       worksheet.freeze_panes(0,1)
       return worksheet
   ####################################################################
   def auto_set_columns(self,worksheet=None,data=None,header=None,scol=0):
   ####################################################################
        """\nDetermines the max length of each column and then set
              that column width.

             Required Attribute args:
                 worksheet = worksheet object
                 data = list of lists data
             Optional Attribute args:
                 scol = Column start
                 header = row of headers for data list of lists.
                          If header not specified, worksheet
                          must have been created with self.add_tab()
        """
        if not header and worksheet.header:
            header = worksheet.header

        ## table = [] list of lists, combine header and data
        table = []
        table.append(header)        for row in data:
            table.append(row)
        ziptable = list(zip (*table))
        colsizes = []
        for idx,val in enumerate(table[0]):
            size = max(len(s) for s in ziptable[idx])
            colnum = idx + scol
            log.debug("Setting column => {} col size => {} =>  {}".format(colnum,val,size))
            worksheet.set_column(colnum,colnum,size)

我想向 xlsxwriter.Worksheet() 添加一个名为 auto_set_columns() 的方法。目前，我必须将工作表对象 (worksheet=worksheet) 作为对象传递才能使其正常工作。我想改用 worksheet.auto_set_columns() 。目前auto_set_columns()是rpt.Workbook的一个方法。

希望 auto_set_columns() 成为 xlsxwriter.Worksheet 的扩展方法。

脚本端使用目前看起来像这样并且有效:

excelfile = nashomes + '/nas_homes.xlsx'
spreadsheet = rpt.Workbook(excelfile)
worksheet = spreadsheet.add_tab(name='Nas Homes',data=hrpt.data,header=hrpt.header)
spreadsheet.auto_set_columns(worksheet=worksheet,data=hrpt.data,scol=0)

我想要的，注意最后一行的变化:

excelfile = nashomes + '/nas_homes.xlsx'
spreadsheet = rpt.Workbook(excelfile)
worksheet = spreadsheet.add_tab(name='Nas Homes',data=hrpt.data,header=hrpt.header)
worksheet.auto_set_columns(data=hrpt.data,scol=0)

此处所需的目标是工作表对象(即 xlsxwriter.Worksheet() )可以具有扩展的“auto_set_columns”方法。但是，由于工作表对象是从 xlsxwriter.Workbook() 类中的 add_worksheet() 方法创建的，因此我无法弄清楚如何在没有主要覆盖方法的情况下将 xlsxwriter.Worksheet() 扩展到 xlsxwriter.Workbook() 。当 Workbook.add_worksheet() 创建 Worksheet 对象时，如何让 xlsxwriter.Workbook() 引用我的扩展 xlsxwriter.Worksheet()？

Answer 1

猴子在从 add_tab 返回之前修补工作表怎么样？

首先在任何类定义之外创建独立函数:

import types

def auto_set_columns(self,data=None,header=None,scol=0):
    if not header and self.header:
        header = self.header

        ## table = [] list of lists, combine header and data
        table = []
        table.append(header)        
        for row in data:
            table.append(row)
        ziptable = list(zip (*table))
        colsizes = []
        for idx,val in enumerate(table[0]):
            size = max(len(s) for s in ziptable[idx])
            colnum = idx + scol
            print "Setting column => {} col size => {} =>  {}".format(colnum,val,size)
            self.set_column(colnum,colnum,size) 

然后在您的 Worksheet.add_tab 函数中，在返回之前修补该方法:

    ....
    worksheet.freeze_panes(0,1)
    worksheet.auto_set_columns = types.MethodType( auto_set_columns, worksheet )
    return worksheet

现在你应该可以运行了:

worksheet = spreadsheet.add_tab(name='Nas Homes',data=hrpt.data,header=hrpt.header)
worksheet.auto_set_columns(data=hrpt.data,scol=0)