Hi How to Write Macro Using …
com.sun.star.sheet.ConditionEntry Type
Why dont use the search:
search for conditionalformat
https://ask.libreoffice.org/t/refresh-in-conditional-formatting-via-macro-in-python-calc/77578
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
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 …
'======================================================================
'======================================================================
'======================================================================
Please help Me …
I’m not sure but I think the conditional formatting has macro API only for Condition or ColorScale.
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.
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
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
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