Create DataBar conditional formatting in Calc by macro

English
, ,
#1

Need help creating a DATABAR conditional format for a cell using macro. Needed to set up the color and range of values

#2

There are pitfalls - see this topic.

#3

Well, I can copy the format too…

#4
#5

Too general question. And I’ve never used the com.sun.star.sheet.ConditionEntry service.

#6

So what have you tried? Or are you actually asking for someone to write the macro?

#7

I’ve tried com.sun.star.beans.PropertyValue. No, suggest a solution

#8

or give an example …

#9

Is it wright that I can’t safely create (or modify an existing) DataBar’s format in Basic, but only using Python UNO scripts, which can access TableConditionalEntry objects?

#10

As far as I remember, the problem is in the initial creation of the conditional formatting rule of the desired type by the macro. If there is at least one cell with the desired type of conditional formatting, then we can use the macro to copy and change the conditional formatting rule. This does not depend on the programming language.
Here is another example of a macro for conditional formatting “color scale”.

#11

Yes, I can copy DataBar format, but can’t edit. It’s enough to change min and max values.

#12

I warned you about the pitfalls. :slight_smile:
Try it on the attached example (TestDataBar macro).

  1. Conditional formatting is copied from cell A1 of Sheet2 to cell B1 of Sheet1.
  2. In the conditional formatting rule on Sheet1, the range is changed to A1:A5, then Min = 10, Max = 50 is assigned.
Option Explicit

' Setting conditional formatting (DataBar).
Sub TestDataBar()
  Dim oDoc as Object, oSheet as Object, oRange as Object, oCF as Object, oCE as Object, oRanges as Object, oDisp as Object
  Dim adr1, adr2 
  Dim args(0) as new com.sun.star.beans.PropertyValue 
  
  oDoc = ThisComponent
  oSheet=ThisComponent.Sheets(0)
  oRange = oSheet.getCellRangeByName("A1:A5")
  adr1 = oSheet.getCellByPosition(1, 0).getCellAddress()
  
  ' Copy cell with conditional formatting from Sheet2.A1 To Sheet1,B1 (B1 - any cell)
  oSheet.copyRange adr1, oDoc.Sheets(1).getCellRangeByName("A1").getRangeAddress() 
  
  ' Change range for ConditionalFormat.
  For Each oCF In oSheet.ConditionalFormats.ConditionalFormats
    oRanges=oCF.Range
    adr2=oRanges(0).RangeAddress
    If adr2.StartRow=adr1.Row And adr2.StartColumn=adr1.Column Then  ' change Ranges
      oRanges.addRangeAddress oRange.RangeAddress, False
      oRanges.removeRangeAddress adr2
      oCF.Range=oRanges
      oCE = oCF.getByIndex(0)              ' Conditional Entry     
      oCE.DatabarEntries(0).Formula = 10   ' Min
      oCE.DatabarEntries(1).Formula = 50   ' Max             
      Exit For
    End If 
  Next oCF
  
  ' Special paste (format only) into range. Without this conditional formatting is not displayed on the screen.
  With oDoc.CurrentController
    oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
    .Select oRange
    oDisp.executeDispatch(.Frame, ".uno:Copy", "", 0, Array())
    args(0).Name="Flags" : args(0).Value="T"
    oDisp.executeDispatch(.Frame, ".uno:InsertContents", "", 0, args)
  End With  
End Sub

DataBar_Example.ods (10.6 KB)

#13

Here is a sample file with embeded macro. The macro will list the CF properties of the sheet, and it will modify some properties.

CFmacro.ods (16.0 KB)

1 Like
#14

There is nothing to do on a river with pitfalls without a pilot. And if there are two of them, then we will definitely sail through… Thanks to the specifics of my sheets, I completed the task just by copying cell’s format, thanks to these two guys I can add a few lines of code and make it universal. Thanks a lot!

#15

I suggest you to use one of the excellent Object Inspection Tools (XrayTool or MRI) to list the existing properties, methods asnd others of the programming objects.
I could never have made my example file without the XrayTool.

1 Like

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