How can I scan conditional formatting in a macro?

PJTraill · October 30, 2023, 2:41pm

Having read Conditional Formats - Apache OpenOffice Wiki , I tried to write a macro to scan all conditional format entries in my sheet, but I could not find any. What am I missing? (The silly thing is that I had a macro somewhere that successfully did this, but I can no longer find it.)

This is the essence of my code:

sub CF_Problem_demo
	dim	summary		as string	: summary		= ""
	dim	sheet		as object	: sheet			= ThisComponent.CurrentController.ActiveSheet	
	dim	formats		as object	: formats		= sheet.Spreadsheet.ConditionalFormats.ConditionalFormats	' ¿The implementation data?
	
	dim	format		as object	: for each format in formats
		dim	entries	as object	: entries		= format.Range.ConditionalFormat
		If entries.Count <> 0 Then
			summary	= summary + ", " + z.Range.AbsoluteName + " has " + entries.Count + " entries"
			dim	entry as object : for each entry in entries
				summary = summary + ": " + entry.StyleName		' Just showing we can access this
			next
		EndIf
	next
	if summary = "" then
		summary	= ", no conditional format entries found"
	endif
	summary	= "Sheet '" + sheet.LinkDisplayName + "' has " + (UBound (formats) - LBound (formats) + 1) + " formats" + summary
	MsgBox (summary,, "Conditional formatting")
End Sub

If I run this macro in a sheet with some manually added conditional formatting, which I can see with Format | Conditional | Manage, it reports that it has found nothing.

Edit: Conditional formatting was added manually.

Edit: Improved my demo, previous was:

sub CF_Problem_demo
	dim	z_S			as string	: z_S			= ""
	dim	sheet		as object	: sheet			= ThisComponent.CurrentController.ActiveSheet	
	dim	formats		as object	: formats		= sheet.Spreadsheet.ConditionalFormats				' ¿The implementation data?
	dim	z as object : for each z in formats.ConditionalFormats
		dim	y as object : for each y in z.Range.ConditionalFormat
			if y.Count <> 0 Then
				z_S	= z_S + " " + z.Range.AbsoluteName + " has " + y.Count + " conditional format entries"
			EndIf
		next
	next
	if z_S = "" then
		z_S	= "No conditional format entries found"
	endif
	z_S	= "In sheet '" + sheet.Name + "': " + z_s
	MsgBox (z_S,, "Conditional formatting")
End Sub

Zizi64 · October 30, 2023, 3:55pm

Here is my modified code embedded in th sample file:

getCF.ods (10.7 KB)

PJTraill · October 30, 2023, 4:57pm

Thanks, that helps — I am working my way through it.

karolus · October 30, 2023, 6:22pm

Transscription from @Zizi64 (getCF.ods) to python in interactive mode:

PJTraill · October 30, 2023, 7:59pm

The mistake was looking for the condition entries in the object representing the formatted range, at formatted-range.Range.ConditionalFormat. I do not know what that does, but it always seems to be empty, and each formatted range itself behaves as a collection of condition entries, as shown by Zizi64’s example.

This means one should write:

	dim	sheet	as object	: sheet		= ThisComponent.CurrentController.ActiveSheet.Spreadsheet
	dim	CF		as object	: CF		= sheet.ConditionalFormats	' ¿General information?
	dim	formats	as object	: formats	= CF.ConditionalFormats		' Collection of formatted ranges

	dim format	as object	: for each format	in formats			' For each conditionally formatted range
		'  etc.
		dim rule	as object	: for each rule in format				' For each formatting rule
			'  etc.
		next
	next

rather than:

	dim	format	as object	: for each format in formats
		dim	entry as object : for each entry in format.Range.ConditionalFormat
			'  this [entry] is the wrong sort of thing,
			' and so far I have always found this collection empty
		next
	next

Here is a function to display some conditional formatting information (which interests me), at least for colour scales (1) and conditional styles (0); it may be useful as a starting point for code scanning conditional formatting. (But see karolus’s code if you want Python):

sub CF_display_Summary
	dim	sheet		as object	: sheet			= ThisComponent.CurrentController.ActiveSheet	
	dim	ranges		as object	: ranges		= sheet.Spreadsheet.ConditionalFormats			' Information on ranges with conditional formatting
	dim	formats		as object	: formats		= ranges.ConditionalFormats						' The formatted ranges
	dim	summary		as string	: summary		= "Sheet '" + sheet.LinkDisplayName + "' has " + ranges.Length + " formatted ranges"
	dim	entries_N	as integer	: entries_N		= 0

	dim format	as object	: for each format	in formats
		summary	= summary + LF + format.Range.AbsoluteName + " has " + format.Count + " condition " + IIf (format.Count = 1, "entry", "entries")

		dim rule	as object	: for each rule in format
			entries_N	= entries_N + 1
			summary	= summary + LF + "  "	' + "Type " + rule.Type
			select case rule.Type
				case 0:
					summary = summary + "Condition: ( " + rule.Formula1 + " " + CF_Rule_Operator_S (rule.Operator) + " " + rule.Formula2 + " ) ⇒ " + rule.StyleName
				case 1:
					summary = summary + "Colour scale: ("
					dim mark as object : for each mark in rule.ColorScaleEntries
						summary = summary + "[ #" + Hex (mark.Color) + " " + mark.Formula + " " + mark.Type + "] , "
					next
					summary = left (summary, len (summary) - 3) + ")"
				case else
					summary	= summary + LF + "Unrecognised type " + rule.Type
			end select
		next
	next
	
	summary	= summary + LF + entries_N + " conditional format " + IIf (entries_N = 1, "entry", "entries") + " found"

	MsgBox (summary,, "Conditional formatting")
End Sub


Function LF : LF = Chr (13) : End Function


Function CF_Rule_Operator_S (y as Integer) as String
	dim	z	as	String
	Select Case y
		Case 0		:	z	= "="
		Case Else	:	z	= "¿Operator " + y + "?" 
	End Select
	CF_Rule_Operator_S	= z
End Function

which produces this message. (Note that many of the numbers shown need interpretation):

from this sheet:

karolus · October 30, 2023, 9:38pm

Update:

from com.sun.star.lang import IllegalArgumentException as illegal_Argument

doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
con_formats = sheet.ConditionalFormats.ConditionalFormats
for con_format in con_formats:
    print(f"{con_format.Range.AbsoluteName}")
    try: 
        
        for i, condition in enumerate(con_format,1):
            match(condition.Type):
                case 0:
                    print("  Classic_CF")
                    print(f"{'condition':>13}{i: 2}: {condition.Formula1=}\n"
                          f"{': ':>17}{condition.Formula2=}\n"
                          f"{': ':>17}{condition.StyleName=}\n")
                case 1:
                    print("  ColorScale")
                    for entry in condition.ColorScaleEntries:
                        print(f"\t{entry.Color=:06x}\n\t{entry.Formula=}")
                case 2:
                    print(f"  DataBar {condition.Color= :06X}")
                    for entry in condition.DataBarEntries:
                        print(f"\t{entry.Formula=}")
                case _:
                    print(" #Todo…")
                        
    except illegal_Argument:
        pass

and the Output for my example-sheet:

$Sheet1.$C$7:$C$11
  Classic_CF
    condition 1: condition.Formula1='C7=1'
               : condition.Formula2='0'
               : condition.StyleName='Gut'

  Classic_CF
    condition 2: condition.Formula1='C7=0'
               : condition.Formula2='0'
               : condition.StyleName='Schlecht'

$Sheet1.$F$15
  Classic_CF
    condition 1: condition.Formula1='1'
               : condition.Formula2='0'
               : condition.StyleName='Neutral'

$Sheet1.$A$9:$A$29
  ColorScale
	entry.Color=ff0000
	entry.Formula='0'
	entry.Color=ffff00
	entry.Formula='50'
	entry.Color=00a933
	entry.Formula='100'
$Sheet1.$I$7:$I$26
  DataBar condition.Color= 2A6099
	entry.Formula='0'
	entry.Formula='0'

Zizi64 · October 31, 2023, 6:51am

The usage of an Object Inspection Tool (like the XrayTool or the MRI) always can help you.

PJTraill · October 31, 2023, 10:45am

So far I have just used the plain vanilla basic IDE of LibreOffice, which is enough to see that that collection is empty — do these other tools tell me what it is meant to be? I thought this was more of a documentation issue.

But I might check out those tools anyway — thanks!

Steph1 · October 30, 2023, 3:17pm

I couldn’t find the attribute y.count, but there is a z.count. So I have slightly modified the macro:

sub CF_Problem_demo

	dim	z_S as string
	dim s as string
	dim	sheet as object
	dim	formats as object
	dim	z as object
	dim	y as object
	
	s = chr(10)
	z_S = ""
	sheet = ThisComponent.CurrentController.ActiveSheet	
	formats = sheet.Spreadsheet.ConditionalFormats
	for each z in formats.ConditionalFormats
	    z_S = z_S + s + z.Range.AbsoluteName + " has " + z.count + " conditional format entrie(s)"
	next
	
	if z_S = "" then
		z_S	= "No conditional format entries found"
	endif
	
	z_S	= "In sheet '" + sheet.Name + "':" + z_S
	MsgBox (z_S,, "Conditional formatting")

End Sub

PJTraill · October 30, 2023, 6:51pm

I had not found that because z.Range.ConditionalFormat was always empty and this Basic is sloppily typed.