How to use the ConditionFormatOperator CONTAINS? [SOVLED]

English
#1

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

#6

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

#7

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.

#8

Thank you @jimk

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

#2

Conditional formatting is described at Conditional Formats - Apache OpenOffice Wiki 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
#3

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

#4

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.

#5

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

#9

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 existing range you'll have to use pierre's absolute name range thing
		i = sheet.ConditionalFormats.Length-1
		cfs = sheet.ConditionalFormats.ConditionalFormats(i)
		
		'get the condition entry you just made and load your stuff
		cfe = cfs.getByIndex(cfs.Count-1)
		cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
		cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
		cfe.setPropertyValue("StyleName", "Positive")
		
		'create another entry, no need to re-get range since you haven't changed it
		cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
		cfe = cfs.getByIndex(cfs.Count-1)
		cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
		cfe.setPropertyValue("Formula1", """Tied""")
		cfe.setPropertyValue("StyleName", "Tied")

		'again only different
		cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
		cfe = cfs.getByIndex(cfs.Count-1)
		cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.NOT_CONTAINS)
		cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
		cfe.setPropertyValue("StyleName", "Negative")
	next
end sub

I found methods createByRange and createEntry in the ConditionalFormats, but was unable to made them work, so I went with the old addNew method to create my formats, perhaps someone will come by and show us how it’s supposed to be done. Anyway, I’m marking this question SOLVED, thanks again you all.

#10

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

#11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.