How to extend the Worksheet class-CodePudding

Hi would like to extend the Worksheet-class in openpyxl. However since this a second level container I am not sure how. Usually in openpyxl you instantiate with wb = Workbook() and then get an Excel-Worksheet with ws = wb.active or wb.create_sheet(). I am a bit confused here how to proceed. Of course you can do:

from openpyxl.worksheet.worksheet import Worksheet
class SheetExtended(Worksheet):
    def adjust_column_width(self):       
        for col in self.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))

            adjusted_width = (max_length   2) * 1.2
            self.column_dimensions[column].width = adjusted_width

However I do I then incorperate that with the Workbook-class?

CodePudding user response：

Here's how you can go about "monkey patching" Worksheet, so what you're doing works:

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet


def adjust_column_width(self):
    for col in self.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))

        adjusted_width = (max_length   2) * 1.2
        self.column_dimensions[column].width = adjusted_width


# monkey patching the new method onto the existing class
Worksheet.adjust_column_width = adjust_column_width


# an example of using it
wb = load_workbook('wb.xlsx')
wb.active.adjust_column_width()
wb.save('wb.xlsx')

If you would have needed a class method, you could have done this:

from types import MethodType


def some_class_method(cls):
    print(cls.__name__)


Worksheet.some_class_method = MethodType(some_class_method, Worksheet)

In general there's better ways to go about solving a problem than monkey patching classes though. Monkey patching adds behaviour that users of the class won't expect, especially since it still 'is' the original class, with no knowledge of its new functionality in the existing class.

The operation you're asking about could just as easily be a function that takes a Worksheet as an argument and modifies it, which would be a better solution. So the real question here is: why do you think modifying the class (or subclassing as you suggested) is the better solution? What is the use case?