Refresh in conditional formatting via macro in python ? calc

mrkalvin · May 15, 2022, 6:11pm

I have a python macro that generates a number of random lines, and a formatting style is applied to these lines: ‘my_style’.

Parallel to this I have a conditional formatting that changes the cell style when the value is greater than 5.

I use clearContents to clear the ‘my_style’ style from the range. I generate new content and insert it back into the sheet. But in addition to deleting the ‘my_style’ style, the conditional formatting style is also deleted. I need to close and reopen the file for the conditional formatting to be reapplied to the range.

Would it be possible to refresh the conditional formatting via macro in python, without having to close the file?

import uno
import random

def test(*args):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    my_sheet = sheets['mySheet1']

    tag_clear = f'''A1:B100'''
    all_rng = my_sheet[tag_clear]

    #http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html#STYLES
    all_rng.clearContents(1+2+4+8+16+32+64+128+256+512)
    #all_rng.clearContents(1+2+4+8+16+64+128+256+512) #without HARDATTR = 32 : Keep the conditional formatting, but keep the style 'my_style'

    lst_itens = []
    count = random.randint(5,20)
    for i in range(count):
        item = random.randint(0,9)
        tp_item = (item, )
        lst_itens.append(tp_item)

    tag_insert = f'''A1:A{count}'''
    range_insert = my_sheet[tag_insert]
    range_insert.DataArray = tuple(lst_itens)
    range_insert.CellStyle = 'my_style'

example file:
test.ods (11,3,KB)

elmau · May 16, 2022, 1:16am

all_rng.clearContents(1+2+4)

mrkalvin · May 16, 2022, 2:52pm

So it doesn’t clear the formatting style that was created here:

range_insert.CellStyle = 'my_style'

I need to remove this formatting style from the cells: ‘my_style’
And at the same time, I need to keep the conditional formatting. Style: bad, when greater than 5

Because the number of lines varies each time the code is executed.