Conditional Formatting within a Calc Macro

Question

asked 2012-10-25 09:20:28 +0200

Gaffer
96 ● 1 ● 4 ● 12

This is a short BASIC Calc macro intended to build a simple spreadsheet with multiple "conditional formatting" cells. It runs cleanly but does not create the conditional formats. Clearly I am misunderstanding something important. A pointer would be appreciated.

Sub Main
    oDoc = ThisComponent
    oDoc.sheets(0).getCellByPosition(0, 0).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 0).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 0).ConditionalFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "A1=5" ), _
        MakePropertyValue( "StyleName", "Heading" ) ) 
    oDoc.sheets(0).getCellByPosition(0, 1).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 1).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 1).ConditionalFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ), _
        MakePropertyValue( "Formula1", "A2=5" ), _
        MakePropertyValue( "StyleName", "Result" ) ) 
End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function

Accepted Answer

answered 2012-10-25 10:28:42 +0200

JohnSUN

2338 ● 2 ● 23 ● 36
http://wmstrong.ru/

updated 2012-10-25 10:30:17 +0200

Try to save the object ConditionalFormat for this cell in a separate variable. Change its content using Clear and AddNew (or getByIndex and setters). And give back to the property ConditionalFormat for this cell with setPropertyValue("ConditionalFormat",...)

...
    oldCondFormat = oDoc.sheets(0).ConditionalFormat
    oldCondFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "A1=5" ), _
        MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, 0).getCellAddress() ), _
        MakePropertyValue( "StyleName", "Heading" ))) 
    oDoc.sheets(0).getCellByPosition(1, 0).setPropertyValue("ConditionalFormat", oldCondFormat)
...

Excellent. Thankyou.
Gaffer ( 2012-10-25 11:34:11 +0200 )edit
This is a great answer but i don;t understand why in the first line of your example code you chose oDoc.sheets(0).ConditionalFormat, which looks to me like a conditional format associated with the whole sheet rather than something like oDoc.sheets(0).getCellByPosition(1,0).ConditionalFormat ?
Gaffer ( 2012-10-25 15:04:43 +0200 )edit
Oh, sorry! It's just an unfortunate typo. Of course, you are absolutely right: for the conditional format the entire sheet should be used for reading and appropriation of this sheet, and for individual cells - their own conditional format. Their structure and methods are the same at now...
JohnSUN ( 2012-10-25 15:17:51 +0200 )edit

Conditional Formatting within a Calc Macro

1 Answer

Comments

Resources

Donate

Question tools

Stats

Related questions