Ask Your Question
1

How to use the ConditionFormatOperator CONTAINS? [SOVLED]

asked 2017-10-05 00:43:28 +0200

southbayfan gravatar image

updated 2017-10-16 22:19:16 +0200

I'm trying to write a spreadsheet macro that conditionally formats rows of a range I've named. LibreOffice allows an operator of Contains to be used and I am trying to write a macro that automates this. The following code does NOT work. What am I missing?

sub formatTeamTable
breaks = ThisComponent.sheets.getByName("Tiebreakers")
table = breaks.getCellRangeByName("TeamTable")
tableColumns = table.Rows.Count-1
dim condition(2) as new com.sun.star.beans.PropertyValue
for r = 0 to tableColumns
    condition(0).Name = "Operator"
    condition(0).Value = com.sun.star.sheet.ConditionFormatOperator.CONTAINS
    condition(1).Name = "Formula1"
    condition(1).Value = """Tied"""
    condition(2).Name = "StyleName"
    condition(2).Value = "Tied"
    row = table.getCellRangeByPosition(0,r,tableColumns,r)
    cf = row.ConditionalFormat : cf.addNew(condition) : row.ConditionalFormat = cf
next

end sub

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2017-10-07 09:44:02 +0200

pierre-yves samyn gravatar image

Hi

Ok, you can not do that (expected: "com.sun.star.sheet.ConditionOperator") but it seems I found a workaround:

  • create the conditional formating with an accepted operator (e.g. EQUAL), then...
  • "go up" to the sheet level to browse the defined formats and...
  • modify the one for the range we are interested in.

In my example the TeamTable name refers to this cell range: $Tiebreakers.$A$1:$A$11 (adapt to your case). I do not have much time so this name written "hard". One could of course improve e.g. by retrieving these references from the name.

sub formatTeamTable2
    breaks = ThisComponent.sheets.getByName("Tiebreakers")
    table = breaks.getCellRangeByName("TeamTable")
    cf = table.getPropertyValue("ConditionalFormat")
    cf.Clear()
    dim condition(3) as new com.sun.star.beans.PropertyValue
    condition(0).Name = "Operator"
    condition(0).Value = com.sun.star.sheet.ConditionOperator.EQUAL
    condition(1).Name = "Formula1"
    condition(1).Value = """Tied"""
    condition(2).Name = "StyleName"
    condition(2).Value = "Tied"
    cf.addNew(condition)
    table.setPropertyValue("ConditionalFormat", cf)
    oCFS = breaks.ConditionalFormats()
    odummy = oCFS.getConditionalFormats()
    for i = 0 to oCFS.length -1
        oneCF = oDummy(i)
        if oneCF.range.absolutename = "$Tiebreakers.$A$1:$A$11" then
            oMyCF = oneCF.getByIndex(0) 'just created so only one condition'
            oMyCF.setPropertyValue("Operator",  com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
            exit for
        end if
    next i

end sub

Regards

edit flag offensive delete link more

Comments

Good job -- how did you figure this out? I tried XrayTool and searching for documentation but neither helped. Note: To get this to work, define the range as $A$1:$A$11 and put the "Tied" values in that column.

Jim K gravatar imageJim K ( 2017-10-07 17:33:44 +0200 )edit

Thank you @Jim K

how did you figure this out?

Some time ago, as an exercise for courses, I asked the trainees to write a "detective" function (list of formula, formats ...). I had already dug the API on these issues.

To get this to work, define the range as...

This was explained in my answer, was not it?

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-10-08 06:48:28 +0200 )edit
0

answered 2017-10-05 19:21:03 +0200

Jim K gravatar image

updated 2017-10-05 19:21:20 +0200

Conditional formatting is described at https://wiki.openoffice.org/wiki/Docu... The operator must be of type com.sun.star.sheet.ConditionOperator, not com.sun.star.sheet.ConditionFormatOperator like in your attempt.

Here is working code using EQUAL.

sub formatTeamTable
    breaks = ThisComponent.sheets.getByName("Tiebreakers")
    table = breaks.getCellRangeByName("TeamTable")
    cf = table.getPropertyValue("ConditionalFormat")
    cf.Clear()
    dim condition(3) as new com.sun.star.beans.PropertyValue
    condition(0).Name = "Operator"
    condition(0).Value = com.sun.star.sheet.ConditionOperator.EQUAL
    condition(1).Name = "Formula1"
    condition(1).Value = """Tied"""
    condition(2).Name = "StyleName"
    condition(2).Value = "Tied"
    cf.addNew(condition)
    table.setPropertyValue("ConditionalFormat", cf)
end sub
edit flag offensive delete link more

Comments

Thanks for your response. I know how to use EQUAL and the ConditionalOperators, I'm asking how to use the ConditionFormatOperators. Calc uses them.

southbayfan gravatar imagesouthbayfan ( 2017-10-06 05:33:53 +0200 )edit

A search for "ConditionFormatOperators" reveals nothing about where these are used in the API, so it may not be possible. Yes, Calc uses them, but that does not mean the functionality is exposed through the API.

Jim K gravatar imageJim K ( 2017-10-06 19:13:37 +0200 )edit

Thanks again. I can see them used in the ConditionalFormat, but my knowledge of the API is meager.

southbayfan gravatar imagesouthbayfan ( 2017-10-07 00:08:03 +0200 )edit
0

answered 2017-10-16 22:16:45 +0200

southbayfan gravatar image

updated 2017-10-16 22:27:32 +0200

Come back after working on another project and interesting things have happened. First of all I want to thank everyone who participated in answering this question, especially pierre-yves samyn who's answer I went with. This was my first time posting a question to a forum, I'm normally able to find answers to whatever questions I have by googling, thanks again for your patience with me. What follows is just a bit of code to setup an example table, skip below it to find my take on pierre's answer.

load code into empty spreadsheet

sub createTable
    sheet = thiscomponent.sheets(0)

    'load some data
    data = array(array("Team","BOS","CHI","HOU","KC"),array("Boston","","CHI","HOU(1)","KC"),_
                 array("Chicago","CHI","","CHI","Tied"),array("Houston","HOU(1)","CHI","","Tied(1)"),_
                 array("Kansas City","KC","Tied","Tied(1)",""))
    sheet.getCellRangeByName("A1:E5").DataArray = data

    'make some ranges
    refPos = sheet.getCellRangeByName("A1").cellAddress
    addrs = sheet.getCellRangeByName("B2:E5").absoluteName
    if sheet.NamedRanges.hasByName("TeamTable") then sheet.NamedRanges.removeByName("TeamTable")
    sheet.NamedRanges.addNewByName("TeamTable", addrs, refPos, 0)
    addrs = sheet.getCellRangeByName("B1:E1").absoluteName
    if sheet.NamedRanges.hasByName("Teams") then sheet.NamedRanges.removeByName("Teams")
    sheet.NamedRanges.addNewByName("Teams", addrs, refPos, 0)

    'load some crosses
    border = createUnoStruct("com.sun.star.table.BorderLine")
    border.Color = RGB(0,0,128)     'blue
    border.OuterLineWidth = 26      '26=.75pt, 35=1.00pt
    table = sheet.getCellRangeByName("TeamTable")
    for pos = 0 to table.Rows.Count-1
        cell = table.getCellByPosition(pos, pos)
        cell.DiagonalTLBR = border : cell.DiagonalBLTR = border
    next

    'prettify stuff
    table.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("Teams").HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("A1:E1").CharWeight = com.sun.star.awt.FontWeight.BOLD

    'make some styles
    cellStyles = thiscomponent.StyleFamilies.getByName("CellStyles")
    styles = array(array("Positive", RGB(0,153,0)), array("Tied", RGB(128,0,128)),_
                   array("Negative", RGB(255,0,0)))
    for each style in styles
        if cellStyles.hasByName(style(0)) then cellStyles.removeByName(style(0))
        cs = thiscomponent.createInstance("com.sun.star.style.CellStyle")
        cellStyles.insertByName(style(0), cs) : cellStyle = cellStyles.getByName(style(0))
        cellStyle.ParentStyle = "Default" : cellStyle.CharColor = style(1)
    next
end sub

What follows is, largely, the code I implemented. This code creates mutliple conditions on a range.

sub formatTable
    'get stuff you just made
    sheet = ThisComponent.Sheets(0)
    table = sheet.getCellRangeByName("TeamTable") : teams = sheet.getCellRangeByName("Teams")
    tableColumns = table.Columns.Count-1    'columns rows doesn't matter, table is square

    dim empty() as new com.sun.star.beans.PropertyValue 'create an empty property
    for r = 0 to tableColumns
        row = table.getCellRangeByPosition(0,r,tableColumns,r)  'get row of table

        'creates a conditional range format and an empty entry format for the row range, 
        'clear is optional, it's just there so that this sub can run multiple times
        cf = row.ConditionalFormat : cf.clear : cf.addNew(empty) : row.ConditionalFormat = cf

        'get sheet conditional range format you just made, formats are added to a list, 
        'most recent format is at the bottom of the list. if you're adding a condition entry 
        'to an ...
(more)
edit flag offensive delete link more

Comments

On this site, do not add [SOLVED] to the title. Instead, mark an answer as correct by clicking ✔.

Jim K gravatar imageJim K ( 2017-10-17 17:28:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-05 00:43:28 +0200

Seen: 124 times

Last updated: Oct 16 '17