# How to use the ConditionFormatOperator CONTAINS? [SOVLED]

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 close merge delete

Sort by » oldest newest most voted

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

more

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.

( 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

( 2017-10-08 06:48:28 +0200 )edit

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"
table.setPropertyValue("ConditionalFormat", cf)
end sub

more

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

( 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.

( 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.

( 2017-10-07 00:08:03 +0200 )edit

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.

sub createTable
sheet = thiscomponent.sheets(0)

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
if sheet.NamedRanges.hasByName("TeamTable") then sheet.NamedRanges.removeByName("TeamTable")
if sheet.NamedRanges.hasByName("Teams") then sheet.NamedRanges.removeByName("Teams")

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
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