CODEWITHSUNDEEP
pythonclassopenpyxlextending-classes
Midnight
Midnight

Reputation: 423

Extending openpyxl workbook class

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

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.

Upvotes: 1

Views: 1111

Answers (1)

rosshug
rosshug

Reputation: 76

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

Upvotes: 2

Related Questions