[Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

Hello everyone!

I have a problem that I can't solve.
I have a cell formatted with a style. I don't have a problem for reading it. (myCell.cellStyle).
I have another cell, this time with conditional formatting. Its style is therefore variable, depending on a condition (no matter which one).

I would simply like, from the API, to know what is the ACTIVE style of my cell.

Although I have no problem using Xray, I haven't found it yet.
An attached file will help you to do tests.

Thank you all!
:D
Attachments
testColorsConditinalFormat.ods
(14.28 KiB) Downloaded 92 times
Last edited by Sébastien C on Sun Jul 03, 2022 2:01 pm, edited 1 time in total.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by JeJe »

MRI gives "Default" whichever. Perhaps the only way is indirectly by testing if the condition is set.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

MRI, Xray or even my little macro (showColorsC3) say “default”...
In my case (a bit too long to tell here) I want to give to the user the possibility to put as many conditions as he wants. So that also means as many styles. If we know the name, deducing the characteristics of a style (for example the font or background colors), is obviously not a problem:

Code: Select all

thisComponent.StyleFamilies("CellStyles").getByName("nameOfStyle")
But if I have to list my user's conditions and then test them one by one, when the spreadsheet has already done this job for the display, that seems extremely laborious to me.
I am really very surprised by this impossibility of querying just to know the active style.

But thank you very much for looking into my problem!
:)
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by JeJe »

Its fairly simple for your example. It would just need turning into a generic function or sub that loops through multiple entries for any settings.

Code: Select all

Sub showColorsC3
 Dim myCell As Object
sheet = thisComponent.sheets.getByIndex(0)
 myCell = sheet.getCellRangeByName("$C$3")
with mycell.conditionalformat.getbyindex(0)
sts=split( .formula1,"=")
if sheet.getCellRangeByName(trim(sts(0))).value  = clng(sts(1)) then cellstyle =.stylename else cellstyle =mycell.cellstyle
end with
msgbox cellstyle
End Sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

Thank you very much again Jeje for this attempt, the syntax of which I appreciate, by the way.

But, as I probably did not emphasize enough in my two previous posts, I am not in control of the conditional formatting expressions that my users will use.

In what you propose, we have an equality from which we extract the two parts, left and right, next to the equal sign. But it can also very well be, for example, a number range. The smallest formula can be incredibly complex, remembering again that there can be SEVERAL conditions... And, in any case, only lead to ONE style: the one really applied to the cell.

The API of our beloved office suite is full of redundancies. To name just one, it is trivial to extract thisComponent.sheets.getByIndex(0).Name from thisComponent.sheets.getByIndex(0).AbsoluteName. But let’s say it’s practical. Here it is the opposite: for a job that can be extremely complex, however executed by the spreadsheet, the API does not seem to give access to the result: in this case, the formatting.

I am trying to find a workaround; but I found it a good thing to expose this problem in this place filled with people infinitely more competent than the amateur that I remain.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by MrProgrammer »

Sébastien C wrote: Fri Jul 01, 2022 1:27 pm I am not in control of the conditional formatting expressions that my users will use.
It seems to me that we have XY Problem. You're creating something and have encountered a difficulty trying to complete a certain step of the implementation. But we don't know yet what the goal is. Extend your conditional formatting was created 6 years ago but perhaps it doesn't address this situation.

Taking a larger view, the goal seems to involve extending the capabilities of conditional formatting to include more conditions. The standard OpenOffice Calc conditional formatting dialog provides two options, Cell Value Is and Formula Is. Anything which can be accomplished with Cell Value Is can also be accomplished using Formula Is. Thus Cell Value Is is not really needed. I never use Cell Value Is and always use Formula Is. For example, instead of Cell value is greater than 0, I use Formula is «RelativeReferenceToCurrentCell» > 0 . Understanding relative references is fundamental to using Calc. Until people understand that concept they will struggle with many tasks in Calc in addition to conditional formatting.

The supplied formula in the conditional formatting dialog might be quite complex. This might make it hard for your project to evaluate. But the supplied formula could instead be placed in a cell and replaced in the dialog with the cell address. This is close to what you have. Any formula used in the dialog is evaluated to determine if the result is true or false. If true, the conditional formatting is applied. If not, the next condition is checked. Your attachment shows that the conditional formatting formula is F3=1. However, when the formula is either True or False, that is equivalent to simply F3.

If your project wants to provide for more than three conditionals, implementation of that part is easy if:
• it only allows the Formula Is method, and
• it only allows the formula to be a cell reference.

As discussed above, neither of these is a limitation of what can be accomplished with conditional formatting. Now it is easy to determine the applicable style. Your project looks at the conditions, tests the relevant cell, and selects the first style where the cell is nonzero. Once the relevant style has been determined, the cell's appearance is known. All the complexity of evaluating a formula or of determining whether a cell value meets a condition is now performed by Calc when it evaluates the formula in the cell.

You have introduced a restriction: the users must put their conditions in cells. However, in exchange, this restriction allows you to provide them some new feature which they don't have now. If they don't like the restriction, they don't use your project. Even in the standard conditional formatting dialog, putting conditions in cells greatly simplifies determining why a troublesome conditional formatting dialog is misbehaving, thus it is a valuable technique to employ.

Although the final goal may be to allow for the same types of criteria that the standard conditional formatting dialog allows, introducing the restriction may allow you to present version 1 of the project. I think that will be better than nothing. Then if you later discover how to handle complex criteria you can create version 2 which removes the restriction.

 Edit: Your attachment suggests you're interested in the background color which is presented on the screen. To get that perhaps you could use method getBackground() from interface XAccessibleComponent. That doesn't tell you the active style, but maybe it is enough for your goal. I did not test that idea myself. 

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Lupp »

Taking the question literally, and not asking for the eventual goal:

If "the ACTIVE style" is meant to mean here the style being chosen for any specific cell due to its CondtionalFormat settings such a thing simply doesn't exist for cells outside the viewed area. The conditions aren't evaluated for such cells. They only get (re-) evaluated for cells coming into the view from outside (as I tested also sometimes if only near to it).

If you want to know the result(s) of the condition(s) used for CF, you need to calculate them in helper cells. This way they are marked "dirty" in the same way as an formula cells if predecessors changed or volatile dependencies exist, and the formulas get recalculated under the same regulations.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by JeJe »

The accessiblecontext doesn't appear to help you either, cell background is -1 in both conditions.

Code: Select all

mycell= thiscomponent.currentcontroller.componentwindow.windows(0).accessiblecontext.getaccessiblechild(4).accessiblecontext.getaccessiblechild(0).accessiblecontext.getAccessibleCellAt(2,2)
msgbox mycell.background

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Lupp »

The attached file is containg 3 sheets of which two bear a CF for all of their cells. The second variant includes a volatile function, and can therefore easily be forced to recalculate.
Play with these sheets to see the evidence for my claims. If you still aren't convinced then, I can supply a CF using an UDF and giving information about the number of cells for which the CF was recalculated when once triggered.
cfSophistical2.ods
(43.46 KiB) Downloaded 77 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

Dear All,

@Lupp
The germanity of your literalness is matched only by the effectiveness of your answer! VIE-LEN DANK! I couldn’t ask for more!!!
The workaround you propose is still very complicated; I prefer to slightly change my own concept to arrive at the finality that I proposed to myself. Maybe, we will talk about it again. But thank you again for your latest demonstration of the “why”.

@JeJe
Thank you for giving a concrete example to illustrate MrProgrammer’s Edit suggestion. I would not have known how to do it, and that, of course, opens doors to my ignorance.

[...] Returning to my lands by jumping the Rhine like a puss in boots, here I am having to row across the Atlantic (Christopher Columbus, it’s too modern for me) to, like a Viking this time, go back a little inland, in the Wisconsin, on plains discovered by my ancestors. [...]

@MrProgrammer
Thank you very much for your long answer. I hope you will understand that mine was not immediate, especially since I do not write (at all) in my native language.

For me, it is absolutely not a question of “to involve extending the capabilities of conditional formatting to include more conditions” but just to give my LibreOffice’s users (which therefore, in the latest versions does not need an extension to increase the number of conditional formatting conditions), the possibility of making fruits with what already exists.

Because it seems to me, however, that there is an API flaw here, I’m marking this thread not as solved but as an issue; you will modify my choice if you consider it unappropriate.

Indeed, if, in cell $A$1, you put the formula =1+1 (or even a slightly more complicated formula), the API offers us TWO possibilities:
  • either retrieve its formula:
    thisComponent.sheets.getByIndex(0).getCellRangeByName("$A$1").formula
  • either retrieve its value
    thisComponent.sheets.getByIndex(0).getCellRangeByName("$A$1").value
My question is therefore NOT an XY question: it is simply whether the programmer is given the same two types of possibilities in the admittedly more complicated case of conditional formats.

And Lupp is very clear on this: “Das ist NEIN” ► (jawoll!!!)...

With great kindness, you suggest that I redo everything that the spreadsheet has already done, as if, in the case described here for cell $A$1, I had to go through the formula again to arrive at the value, which, in the case of conditional formatting, can be compared to the active style, ultimately chosen by the spreadsheet.

In addition, but with as much kindness, you gave me a little lesson on the use of Booleans. But if I may allow myself, I believe that it is, in my regard, perfectly superfluous...

All this therefore pushes me to actually consider doing without styles as they are defined in Calc. Again, it’s quite complicated to explain (and especially very long) but I think I can get by anyway. If the file linked to this post inspires you, I can give you news when they emerge...

Because if I saved myself from showing you the real situation of my code, it is because I think that it is infinitely more reasonable due to its complexity. Moreover, in addition to the fact that the two spreadsheets are in French, the same applies to the names of the variables. From Wisconsin where you are, this will certainly give you an idea of what we commonly practice from all non-English-speaking places on the planet (translation...), but I know that it is not necessarily a easy, as much from Germany or Hungary where we are read.

But it turns out that you and I have a taste for “proof of concept”. So, because you explicitly ask me what my goal is, I’m going to explain mine to you, giving you in this post the file in its state which will evolve quite considerably since it clearly turns out that using styles complicates too much programming.

You are probably familiar with the ImageMagick command line software. As often in this type of software, it has a power that must be tamed. I found it interesting to be able to list the files of a folder in a Calc sheet to, in the end, write (and possibly execute) a script acting on said files.

ImageMagick has, among other things, the ability to make annotations on graphic files. Calc, like any modern spreadsheet, gives the possibility of handling character strings with an infinite range of conditions. The use of Calc is therefore not, here, made as a simple (huge) dialog box, but with the idea that we can build annotations in a very complex way according to conditions and stored datas, them, on the right columns of my utility.

The aim is therefore to place specific annotations on a set of graphic files, giving the possibility to adjust individually:
  • The text of the annotation.
  • Its character color.
  • Its background color.
  • Its location on the image.
  • The font type.
  • The font size.
  • The transparency of the background of the annotation.
But, as you will have understood, this data can quite well come from formulas. For example, if the file name has a specific sequence of characters, be assigned a given style. Because as much for a reason of saving space, as, above all, to give an overview of the annotation I used the entry of the font and background colors in the cell itself. I couldn’t use the font, because ImageMagick’s font names are different between those used by the operating system and those named by ImageMagick. This is also the reason why it is necessary to cause the establishment of the list in the sheet named “Setup”.

You seem to be using a Mac and I, for my part, only tested under Linux (Mint) and M$-Win (10). Moreover, I don’t even know if my utility works under OpenOffice since I program, under Linux, only with LibreOffice (which means that for the treatment of temporal datas, there must be differences). So I can’t even really tell you that it might work for you. If, to do the tests, you feel the need to install ImageMagick, you must make its directory available in your system path. Similarly, you will benefit from installing the fonts “England Hand DB”, “FreeMono”, “Liberation Sans” and “Source Code Pro”, that you will easily find on the net.

This is for you today. I hope you will forgive the Frenchman that I am for having, in very bad English, evoked things which you may have nothing to do with... But you will have to agree that German literalness has the advantage of avoid suspecting XY problems and that the boredom of this length was only caused by you...

:D :super: :D
Attachments
magickAnnotVF.ods
(45.97 KiB) Downloaded 89 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by JeJe »

Have you considered a drawing textbox for your annotations?

(A form control label or textbox might be used if you didn't specify transparency.)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

Seems to me that would disallow the ability to play with spreadsheet cell formulas. Unlike LibreOffice, I’m not sure if it works on the OpenOffice you’re using because file dates (“Date du fichier”) are inserted expressly like spreadsheet-dates and, for time processing, there are API differences between the two versions of the office suite. Grab this screenshot:
Capture du 2022-07-03 15-40-44.jpg
Capture du 2022-07-03 15-40-44.jpg (114.83 KiB) Viewed 1676 times
The active cell is in $E$20. Its formula is
="Photo datée du " & TEXTE(C20 ; "jj/mm/aaaa") & "."
which means that it is an annotation built with a formula. It is very simple to set up in a spreadsheet cell, especially again, because it iss possible to use spreadsheet features as powerful as “Fill Cells - Down”.

Unless I don’t understand you, I don’t really see the advantage of using a textbox.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by JeJe »

Just making sure you knew about those features as it fitted your list of required display options. Your picture is a lot clearer than the file which lacked the image or the list of entries.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Issue][Calc] Read the ACTIVE style of a cell in conditional formatting.

Post by Sébastien C »

The princip :
  • Choose source folder (“Choisir le dossier source.”)
  • Choose target folder (“Choisir le dossier cible.”)
  • Loading the list from source folder (“Charger la liste du dossier source.”)
  • Execute the ImageMagick’s script on the lines checked by ☑ (“Script ImageMagick exécuté sur les lignes ☑.”)
For this last one, if you are a bit afraid (...) do not worry: you can edit the script without executing it. The program display a dialog box where it is asked: “Que voulez-vous faire ?” (“What do you want to do?”). The second choice/button “Éditer le script avec éditeur par défaut” is
Edit the script with the default editor” (also under M$-Win: notepad).

May be useful to you:
  • The french word “aperçu” is “preview”.
  • The french word “Voir” is “View”.
  • By clicking on the image of the Picto-control, you open the image (result image or simple preview) in your default editor. You can change this in the “Setup”'s sheet.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply