How to Write Macro Using "com.sun.star.sheet.ConditionEntry Type" Constant

[sv.thiyagarajan]

Hi How to Write Macro Using …
com.sun.star.sheet.ConditionEntry Type

[karolus]

Why dont use the search:
search for conditionalformat

https://ask.libreoffice.org/t/formatoperator-begins-with-how-to-create-conditional-formatting-in-python/77761

https://ask.libreoffice.org/t/refresh-in-conditional-formatting-via-macro-in-python-calc/77578

[KamilLanda]

It is only the set of constants, so you can use it really easily

Sub small 'JOKE
	msgbox com.sun.star.sheet.ConditionEntryType.COLORSCALE & " = 1"
End Sub

I searched the SDK documentation for the string ConditionEntryType and the method creatEntry uses these constants LibreOffice: XConditionalFormat Interface Reference

[sv.thiyagarajan]

How to Write Macro For " Conditional Formatting " related to " Data Bar " , " Icon set ",
" Date " in LibreOffice Calc?
The Api and Sample Macro Codes , did not get from Internet …

1. Data Bar

'======================================================================

2. Icon Set

'======================================================================

3. Date

'======================================================================

Please help Me …

[KamilLanda]

I’m not sure but I think the conditional formatting has macro API only for Condition or ColorScale.

[sokol92]

Method createEntry “creates a new conditional format entry and insert its at the position”.
Parameter type specifying the type of the new entry: CONDITION (0), COLORSCALE (1), DATABAR (2), ICONSET (3), DATE (4).
The following services should be used: IconSet, ColorScale, ConditionFormatEntry, DataBar, DateCondition.

Update. Sorry - I forgot about the bug tdf#119590. So for formatting types other than CONDITION, we’ll have to look for workarounds for now. An example is in this message.

[sv.thiyagarajan]

Hi sokul92,
How To write BASIC Macro in LibreOffice Calc, For the Following Example… and How to get the following Result… Using Api
createEntry and IconSet …

Conditional Format - Icon Set

Conditional Format - Settings

Please Help

[KamilLanda]

It is possible to “bypass” the bug via copy the Conditional formatting from some Template to Current ODS, and then set values to cells.
Template with Conditional formatting template.ods (9.5 kB)

And macro, change the constant sUrlTemp

Sub copyConditionalFormat
	dim oDoc as object, oSheet as object, oRange as object, oRanges as object, oCF as object, oDocTemp as object, oCellTemp as object, data(), i&, _
	prop(1) as new com.sun.star.beans.PropertyValue
		prop(0).Name="Hidden" : prop(0).Value=true
		prop(1).Name="AsTemplate" : prop(1).Value=true

	const sUrlTemp="d:/template.ods" 'URL to template with conditional format
	
	rem Copy conditional formatting from template
	oDocTemp=StarDesktop.loadComponentFromURL(ConvertToUrl(sUrlTemp), "_blank", 0, prop)
	oCellTemp=oDocTemp.Sheets(0).getCellByPosition(0, 0) 'cell A1 in template
	oDocTemp.CurrentController.Select(oCellTemp)
	data=oDocTemp.CurrentController.getTransferable() 'copy A1
	oDocTemp.close(true)
	
	rem Paste to current Sheet -> but it pasted to single cells, no to the whole of range
	oDoc=StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, array() )
	oSheet=oDoc.Sheets(0)
	oRange=oSheet.getCellRangeByName("B2:B9") 'your range for Conditional Formatting
	oDoc.CurrentController.Select(oRange)
	oDoc.CurrentController.insertTransferable(data) 'paste Conditional Formatting before adding the values
	oSheet.ConditionalFormats.ConditionalFormats
	
	rem Extend 1st conditional format to the whole of range
	oCF=oSheet.ConditionalFormats.ConditionalFormats(0)
	oRanges=oCF.Range
	oRanges.addRangeAddress(oRange.RangeAddress, false)
	oCF.Range=oRanges
	
	rem remove singles from Conditional formats
	for i=2 to ubound(oSheet.ConditionalFormats.ConditionalFormats)+1
		oSheet.ConditionalFormats.removeByID(i)
	next i

	rem set values to cells
	oRange=oDoc.Sheets(0).getCellRangeByName("A2:B9")
	oRange.setDataArray( array( array("Hungry", 37), array("Greedy", 84), array("Merciful", 55), array("Friendly", 47), array("Gentle", 77), array("Dejected", 91), _
		array("Happy", 65), array("Annoyed", 81) ) ) 'put values to cells
End Sub

[KamilLanda]

The improvement. I set the conditional formatting to all Column A in template. The Copy/Paste now works with range and not Paste as single cells, so there isn’t the removing of single conditions from cells.
template2.ods (8.5 kB)

Sub copyConditionalFormat2
	dim oDoc as object, oSheet as object, oRange as object, data(), prop(1) as new com.sun.star.beans.PropertyValue
		prop(0).Name="Hidden" : prop(0).Value=true
		prop(1).Name="AsTemplate" : prop(1).Value=true

	const sRange="B2:B9" 'range for Conditional Formatting (only 1 column!)
	const sUrlTemp="d:/template2.ods" 'URL to template with conditional format
	
	rem Copy range with conditional formatting from TEMPLATE
	oDoc=StarDesktop.loadComponentFromURL(ConvertToUrl(sUrlTemp), "_blank", 0, prop)
	oSheet=oDoc.Sheets(0)
	oRange=oSheet.getCellRangeByName(sRange) 'select range
	oRange=oSheet.getCellRangeByPosition(0, oRange.RangeAddress.StartRow, 0, oRange.RangeAddress.EndRow) 'range in column A
	with oDoc.CurrentController 'copy range with Conditional Format
		.Select(oRange)
		data=.getTransferable()
	end with
	oDoc.close(true)
	
	rem Paste to CURRENT SHEET
	oDoc=StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, array() ) 'current ODS
	oSheet=oDoc.Sheets(0)
	oRange=oSheet.getCellRangeByName(sRange) 'range for Conditional Formatting
	with oDoc.CurrentController
		.Select(oRange)
		.insertTransferable(data) 'paste range with Conditional Formatting before adding the values
	end with

	rem set values to cells
	oRange=oDoc.Sheets(0).getCellRangeByName("A2:B9")
	oRange.setDataArray( array( array("Hungry", 37), array("Greedy", 84), array("Merciful", 55), array("Friendly", 47), array("Gentle", 77), array("Dejected", 91), _
		array("Happy", 65), array("Annoyed", 81) ) ) 'put values to cells
End Sub