Extending openpyxl workbook class

Question

I would like to extend the existing capabilities of the openpyxl workbook class with custom made methods. I understand that normally I'd simply do so by defining my own class based on the original class

class WorkbookExtended(openpyxl.Workbook):
    def added_method(self):
        print("Do some special stuff here")

So far so good but my problem now is that the way I'm getting my Excel workbooks is by calling load_workbook(file) which is not part of the workbook class but returns a fully instanciated Workbook object. The Workbook class on the other hand, does not seem to offer a way to instanciate a new object based on an existing one. So the question is: how do I extend a class that

is the result of another function and
does not offer a way to init or copy itself based on an existing object

edit: More specifically I load an existing workbook and would like to add a method that searches for a given value within a given range and sheet. I want to be able to search for exact matches or for cell values that contain the search term. Now I can do all that using the existing functions in openpyxl but what I'd like to do is adding it to the Workbook class because that seems like the most natural place for such a function to be.

from openpyxl import Workbook, load_workbook

class WorkbookExtended(Workbook):
    def locate_value(self, value, range, sheet="active", strict=True):
        pass # perform the search and return a list of cells

wb = load_workbook("test.xlsx")
wbe = WorkbookExtended(wb) # does not work because Workbook class doesn't
                           # take a workbook object as __init__ parameter
result = wbe.locate_value("foo", "A2:B10") # result contains a list of cells 
                                           # that have "foo" as value

In spite of this more specific problem description, I'm still curious about the general approach to that problem. I.e. even if there is a workbook or worksheet function that does something like that, the original question remains.

***how do I extend a class***: Depends, if you want, to **add** or **overload** a existing, class method. — stovfl, Apr 22 '20 at 09:41
Well the goal is to have an existing class do more specialised stuff it wouldn't normally do without rebuilding the whole class from ground up. Overloading - as far as I understand it - takes an existing method and modifies it. If there's no _similar_ method available that lends itself to overloading, this doesn't look like a desirable way of handling things. — Midnight, Apr 22 '20 at 10:29
Consider this example: [Extending openpyxl class Worksheet](https://stackoverflow.com/a/58556221/7414759). My example uses `class Worksheet`, unfortunately can't remember if it would work with `load_workbook`, but it shows how to do without `inheritance`. — stovfl, Apr 22 '20 at 14:10

score 2 · Accepted Answer · answered Jun 09 '20 at 08:24

In similar situations I have used instance variables instead of extending the class. In your case create an instance variable for the workbook and reference that when you need to use functions from openpyxl, and reference your class instance when you need to use your custom functions.

from openpyxl import Workbook, load_workbook

class WorkbookExtended():
    def __init__(self):
        self.workbook = None

    def locate_value(self, value, range):
        ws = self.workbook.active
        cells_with_value = [cell.coordinate for row in ws[range] for cell in row if cell.value == value]
        return cells_with_value

wb = load_workbook("test.xlsx")
wbe = WorkbookExtended()
wbe.workbook = wb
wbe.locate_value("foo", "A2:B10")

# reference the instance variable for openpyxl functions where necessary
wbe.workbook.sheetnames

or if you are always going to use your class to load an existing workbook then you can put load_workbook inside the class init function.

class WorkbookExtended():
    def __init__(self, file_path):
        self.workbook = load_workbook(file_path)

    def locate_value(self, value, range):
        ws = self.workbook.active
        cells_with_value = [cell.coordinate for row in ws[range] for cell in row if cell.value == value]
        return cells_with_value

wbe = WorkbookExtended("test.xlsx")
wbe.locate_value("foo", "A2:B10")

I have by now moved on from the project and don't plan to go back unless the script, that initially sparked this question, requires me to do so for an update or fix. That being said this looks a very good and simple solution to the question I asked and I'll definitely try it if/when I have to do something similar with openpyxl. — Midnight, Jun 10 '20 at 08:34

Extending openpyxl workbook class

1 Answers1