Complex ConditionalFormatting ignored by calc?

asked 2012-10-25 16:11:29 +0200

Gaffer
96 ● 1 ● 4 ● 12

updated 2012-10-25 16:20:00 +0200

It seems that a macro that creates a simple ConditionalFormat with a formula that can be immediately calculated, such as "TRUE", works as expected. However, something more complex such as a formula containing "row()" fails. Oddly, when I open the ConditionalFormatting dialog, the formula is there and if I click OK, the formatting is applied(!)

The macro below creates a simple spreadsheet with 2 columns. The cells in Column A are individually conditionally formatted to "Heading" if the simple formula "TRUE" is true (which is always is...)

The cells in Column B are the problem. The ConditionalFormat is "mod(row(),2)" which is true only on odd rows, so every odd row should be in "Result" format. When the macro populates the cells, the conditional formatting instructions appear to be applied but the formatting is not updated. Look at conditional formatting in cell B1 and you can see the formula there. Press "OK" and the formatting is applied (the cell is formatted as "Result"). To see how it should look, copy B1 into the column below.

I have tried Recalculate (F9) but that doesn't help.

What am I doing wrong?

Sub Main
    oDoc = ThisComponent
For iRow = 0 To 20
    oDoc.sheets(0).getCellByPosition(0, iRow).setFormula("Simple")
    oldCondFormat = oDoc.sheets(0).getCellByPosition(0, iRow).ConditionalFormat

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

    oDoc.sheets(0).getCellByPosition(1, iRow).setFormula("Complex")
    oldCondFormat = oDoc.sheets(0).getCellByPosition(1, iRow).ConditionalFormat


    oldCondFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "mod(row(),2)"), _
        MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, iRow).getCellAddress() ), _
        MakePropertyValue( "StyleName", "Result" ) )
    oDoc.sheets(0).getCellByPosition(1, iRow).setPropertyValue("ConditionalFormat", oldCondFormat)
next iRow

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