FormatOperator: BEGINS_WITH , How to create conditional formatting in python?

mrkalvin · May 19, 2022, 10:49pm

https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1ConditionFormatOperator.html
To create conditional formatting through python macro, I need to use operators:
BEGINS_WITH
CONTAINS

I tried to reproduce the idea of this post but it didn’t work

import uno
from com.sun.star.beans import PropertyValue
#from com.sun.star.sheet.ConditionOperator import EQUAL
#from com.sun.star.sheet.ConditionOperator import GREATER

# https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1ConditionFormatOperator.html
from com.sun.star.sheet.ConditionFormatOperator import BEGINS_WITH
from com.sun.star.sheet.ConditionFormatOperator import CONTAINS

def main(*args):
    doc = XSCRIPTCONTEXT.getDocument()
    rango = doc.Sheets[0]['A1:A10']

    cf = rango.ConditionalFormat
    cf.clear()
    args = dict(
        Operator = BEGINS_WITH,
        Formula1 = '"(00)"',
        StyleName = 'Good'
    )
    pv = [PropertyValue(Name=n, Value=v) for n, v in args.items()]
    cf.addNew(pv)
    rango.ConditionalFormat = cf

    args = dict(
        Operator = CONTAINS,
        Formula1 = '"Unidade"',
        StyleName = 'Bad',
    )
    pv = [PropertyValue(Name=n, Value=v) for n, v in args.items()]
    cf.addNew(pv)
    rango.ConditionalFormat = cf
    return

How do I do what I need?

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/ConditionOperator.html

Complement

In this image I created the conditional formatting manually. This is how I would like the python macro to return.

This is the result of the code above, Begins_with and Contains is not applied. Instead select the first option in the list: ‘is equal to’ and the value is blank:

file test:
test1.ods (11,9,KB)

joshua4 · May 19, 2022, 11:43pm

Can you clarify what does not work?

Do you want to add the conditional format to a range, or do you want to add the conditional format to the Sheet level ConditionalFormats specifying the range?

For the latter, perhaps use com.sun.star.sheet.XConditionalFormats to create the format.

Sheet.ConditionalFormats.createByRange(...)

Then the range shows up under

Sheet.ConditionalFormats.ConditionalFormats(n).Range

See LibreOffice: XConditionalFormats Interface Reference

Also, FWIW, if I manually enter the (00) for a begins-with formatting I end up with just (0). Might there be a need for protecting the 00?

mrkalvin · May 20, 2022, 12:32am

@joshua4
See complement, I need to add conditional formatting on a range of cells but using ConditionFormatOperator instead of ConditionOperator

joshua4 · May 20, 2022, 1:02am

I see you have included the double quotes in the Python, Formula1 ='"(00)"'.

com.sun.star.sheet.ConditionFormatOperator.BEGINS_WITH is just a long value 20. CONTAINS is 22. Both the ConditionOperator enums and the ConditionFormatOperator enums are just longs. Have you tried just using the constant? There shouldn’t be any difference where they come from if they are just longs.

mrkalvin · May 20, 2022, 1:12am

I made the suggested change but it still doesn’t work.

Villeroy · May 20, 2022, 2:00am

All these operators are obsolete. You can replace any of them with a simple formula expression such as LEFT(A1;6)="(00)" or LEFT(TRIM(A1);6)="(00)"

In which module do you find operator BEGINS_WITH? (edit: the answer is c.s.s.sheet.ConditionFormatOperator)

joshua4 · May 20, 2022, 1:55pm

Sounds great to use a formula. But I wonder about the operators being obsolete. They still show up in the regular drop list for creating conditional formats in 7.3.3. Are you sure you’re not thinking of the ConditionOperator vs ConditionOperator2 from OO to LO?

Villeroy · May 20, 2022, 2:11pm

These operators are for the users who don’t understand formulas with functions and relative/absolute references. However, they can not apply conditional formatting anyway. For regular users, these operators are obsolete and not flexible enough.

joshua4 · May 20, 2022, 2:31pm

Or for users familiar with regular expressions, REGEX(). But I do not see what is meant by

I have no issues using Cell → begins with → “(00)” for conditional formatting in 7.3.3.

mrkalvin · May 20, 2022, 3:15pm

@Villeroy
If I use the formula below and add conditional formatting manually, it works:

IF(LEFT(TRIM(A1);6)="(00)";1;0)

But if I try to insert this formatting via python macro it doesn’t work, what am I doing wrong?

import uno
from com.sun.star.beans import PropertyValue
#from com.sun.star.sheet.ConditionOperator import EQUAL
#from com.sun.star.sheet.ConditionOperator import GREATER

# https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1ConditionFormatOperator.html
from com.sun.star.sheet.ConditionFormatOperator import BEGINS_WITH
from com.sun.star.sheet.ConditionFormatOperator import CONTAINS
from com.sun.star.sheet.ConditionOperator import FORMULA

def main(*args):
    doc = XSCRIPTCONTEXT.getDocument()
    rango = doc.Sheets[0]['A1:A10']

    #rango.clearContents(1+2+4+8+16+32+64+128+256+512)
    cf = rango.ConditionalFormat
    cf.clear()

    args = dict(
        Operator = FORMULA,
        Formula1 = 'IF(LEFT(TRIM(A1);6)="(00)";1;0)',
        StyleName = 'Error'
    )
    
    pv = [PropertyValue(Name=n, Value=v) for n, v in args.items()]
    cf.addNew(pv)
    rango.ConditionalFormat = cf
    return

example:
test02.ods (13,1,KB)

Villeroy · May 20, 2022, 4:04pm

I don’t know and I don’t care. In general I stopped writing other people’s macros some weeks ago.
Stop this macro madness. Use the program with templates and styles.
This is some working Python code I wrote many years ago: Apache OpenOffice Community Forum - [Calc] More than 3 conditional formats - (View topic)

Villeroy · May 20, 2022, 4:31pm

This works for the current cell only as in LEFT(A1;4)="(00)". It won’t work for any other cell, e.g. for the entire row (which is a very common question here). And you can not add any function calls as in LEFT(TRIM(A1);4)="(00)". When finding out the right non-trivial conditions, you have to debug your conditions in cell formulas anyway.

Villeroy · May 20, 2022, 4:34pm

Could it be that you ignore the anchor point for the relative addressing? I mean property SourcePosition.

mrkalvin · May 20, 2022, 4:59pm

OK thanks for your help I did what I need.

the formula was correct, but I made a typing error in the word Formula1

"Forumula1"

joshua4 · May 20, 2022, 5:24pm

@Villeroy, I see. Yes, in fact, before even mentioning REGEX() I did exactly that…I tested a REGEX() in a cell then copied it to the conditional format formula. I agree, there is no reasonable workflow for BEGINS_WITH other than the simplest of cases, and I see your point that they are so simple they are bound to break with changing needs.