Function to highlight cells using xlwings is slow. How can I speed it up?

user_19479912 0
Viewed 60

I have a function within a script which highlights cells in excel books which contain a specified string.

The functions is:

def highlight_cell(workbooks, highlight_text):
with xw.App(visible=False) as app:
    for book in workbooks:
        book = app.books.open(book)
        for sheet in book.sheets:
            sheet = book.sheets(sheet.name)  
            for cell in sheet.used_range:
                if cell.value == highlight_text:
                    cell.color,  cell.api.Font.Bold  = (255,80, 80), True
        book.save()

The function is called a few times throughout the script.

highlight_cell([difference_table], "left_only")
highlight_cell([types_book], "Incorrect Data Type")
highlight_cell([nulls_book], "Non-Nullable - User must change")

The inputs are a list of workbooks, and a string which appears in the cells to highlight. The script creates said workbooks using pandas and openpyxl, having pulled the data from an originating excel book.

Creating half a dozen workbooks and populating them with the data I want takes all of 2 seconds in total. Highlighting the text with the above function takes about a minute extra. Is this slow time a result of how I structured my function, and can anyone suggest a way to speed it up?

xlwings excel python
1 Answers

The easiest way to highlight cells in Excel is conditional formatting. I'm convinced this can be triggered from outside, like using Python.

Good luck

user_42791550
Related