The speed of filling the background of cells through the fill field

[Alexey Elfimov]

I use openpyxl in my project, in one place I need to change the background of the cells. for this i use the function:

def fill_row(sheet: Worksheet, roew: int, col_maxv int): 

    for col in range(1, col_max + 1): 

        sheet.cell(row=row, column=col).fill = /

            Pattern fill(fgColor='000000', 

                         patternType='solid')

In practice, it takes the program up to 10 seconds to paint 10-30 thousand cells. It seems to me that this is unreasonably long. Tell me, my code is not optimal or is there a problem in the library?

[Charlie Clark]

Your code is not optimal: you are creating new instances of a fill object for every cell and object creation is expensive in Python. If you create the instances outside of the loop you should see a significant improvement. Things get even faster with named styles.

You should also use the openpyxl API wherever possible, especially when it comes to row and column counters.

fill = Pattern fill(fgColor="000000", patternType="solid")

for row in ws.iter_rows():
    for cell in row:
        cell.fill = fill

Let us now your results.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Alexey Elfimov]

Rewrote the function as you suggest.  I tried to create a PatternFill object outside the function, it didn't help much

This implementation of the function works approximately the same. Attaching profiler data

четверг, 22 июля 2021 г. в 12:39:09 UTC+3, charli...@clark-consulting.eu:

[Charlie Clark]

On 22 Jul 2021, at 14:35, Alexey Elfimov wrote:

> [image: 20210722_153347.jpg]
>
> Rewrote the function as you suggest. I tried to create a PatternFill
>
> object outside the function, it didn't help much
>
> This implementation of the function works approximately the same.

Then try using a named style as I suggested.