What is the bean's properties(Name , Value) of conditional formatting in the program of the macro?

English
, , , , , ,
#1

Hello , Everyone.

Using LibreOffice 5 or 6 Macros (Basic), if the DATE data in the cell range (A3: A200) is in the past 7 days,
I want to be red the background color of these cells.

But, I can not find any missing beans properties (= name and value).

The cell range (A3: A200) attributes are “serial-based date” and the format are “yy/mm/dd”.

LibreOffice Version No is 6.2.3.2 or 5.0.5.2
The style “BackgroundColorR” is pre-defined.

The current code is as follows

Sub SetRedStyle()  
Dim oRange as object  
Dim oConFormat  
Dim oCondition(2) As New com.sun.star.beans.PropertyValue  
oRange = ThisComponent.Sheets(1).getCellRangeByName("A3:A200")  
oConFormat = oRange.ConditionalFormat  
oCondition(0).Name = "SourcePosition"  
oCondition(0).Value = oRange.getCellByposition(0,0).getCellAddress()  
oCondition(1).Name = "Operator"  
oCondition(1).Value = com.sun.star.sheet.DateType.LAST7DAYS  
oCondition(2).Name = "StyleName"  
oCondition(2).Value = "BackgroundColorR"  
oConFormat.addNew(oCondition())  
oRange.ConditionalFormat = oConFormat  
End Sub

[Status]

1.All input cells turn red.

2.When I check the conditional format created by a macro,
they are made like “Cell value is”,“Greater than the next value” and “next value = 0”

3.When I set this conditional formatting manually,
they are created correctly. When I check them,they are made conditional format like
“Date is”,“The values of cells are within the last 7 days”

link text
I ask the same question in Japanese,above link.
I corrected the link address to the question. 2019/06/09 16:05 JST

I received the first answer from Mr.himajin 100000 on the Japanese site.
And the number of links in the source text with the description is huge and I can not understand the whole story or details.

Please follow the URLs in order, just in case.

it would be not implemented in LibreOffice side, at least he answered that you can’t solve that problem in this way, it would be a LibreOffice bug.

In the first place, this structure does not hold information about dates.
https://opengrok.libreoffice.org/xref/core/sc/inc/fmtuno.hxx?r=09c657eb#49
This structure is probably “ScCondFormatEntryItem”.

Please check the link at the end of the answer.

There are two parts that I felt strange looking at LibreOffice Module sc (master): conditio.hxx Source File
from the link at the end.

1.line239
It is part of “enum class Type”. “Date” can be linked somewhere, but when it is clicked it becomes 404 Not Found.

2.line283
There are descriptions of the switch statement from line 265 to line 288, but the word following Type :: in the case statement and the word following “<<” in the stream statement on the next line have the same five other words 283 Only the line is different. The case statement is “Date” but the stream statement is “Data”. Is this probably causing a bug due to a typo?

That’s all, thanks.

#2

Edited code for readability

#3

Hello, SM_Riga

As I was the first to post to an overseas site (Ask-LIBO), I was unfamiliar.
Thank you in the future…

#4

The link above should point to your question. It instead links to the Japanese questions list only.

#5

Sorry, the link to my question is below.

link text

However, it is written in Japanese… .

#6

Thanks. However, I’m not able to understand Japanese, and my daughter-in-law who is Japanese is not much interested in LibO, and surely not at all in programming.
On the other hand there was an answer in the Japanese branch and a discussion abouit it seemingly. If there was specifically interesting information, it would be great to get informed by you about the fundamentals.

#7

I played with your code, completed it, and made it run.
See this attachment.

(It seems to be impossible to know enough about the uno-API. I only can play it like a violine after the first lesson. The LibreOffice: com Module Reference can sometimes help a bit.)

===Edit1 2019-06-08 13:35 UTC===
If you try with prerelease versions of 6.3 you may experience errors I reported about here: tdf#125800. There seems to be a bug tdf#12588 Sorry for the error! Couldn’t retrieve yet the correct bug.)

===Edit2 2019-06-09 10:45 UTC===
The related API bugs are older and have more ways to show than I thought when I created the above linked report. I may not find the time, however, to create a more detailed bug report concerning these issues.
Simply use simple means with CF, in specific if you insist on creating/editing it by “macro”. The ordinary 'Cell value is' and the 'Formula is' modes seem to be accessible via the API the way I demonstrated. The 'between', the 'Date is' and the more complicated modes are unclear and superfluous anyway. The 'AllCells' modes I personall never used and never will use.

#8

Thank you for the answer
to make the cells red if the date is within the last 7 days. I confirmed it.

However, I want to process categories with “date is” instead of “formula is”.
If possible, I would like you to respond in such a way.

.

#9

I would suggest you do it the way I did: Inspect an example made via the dialog and recreate its analogy by beans-PropertyValues.
The API seems to be broken concerning rhis variant of CF already in older versions.
The 'Date is' variant of CF is a useless MS-style hokum anyway as I see it. 'Formula is' can do it perfectly. If you actually want to count the present day as on in “the last 7 days” which I wouldn’t, you can set the Formula1 value in my example to (A3<=TODAY())*(A3>TODAY()-7). Done!
Sorry. I have to limit the amount of time I spend on such a highly specialized topic concerning features I never would use myself.

#10

Thanks a lot.
As described in the third item of “Status” in my own question script,
the “Date is” works properly in the manual, but not in the macro.
bye.

#11

“…the “Date is” works properly in the manual, but not in the macro.” I knowed. Did you read all my comments and the above linked bug report?

#12

the bug reference seems broken…

#13

Thank you for posting many times.

The bug report link seems to be broken,
The background is red and “You must enter valid bug number!” is displayed only. I could not refer to it.

The reason why I am concerned with “DATE is” instead of “FORMULA is” is as follows.

“CF” is an abbreviation of “conditional format”.

The table to which I apply “CF” sets 4 conditions in 3 cell range.
And I sort by “date” after adding, correcting, deleting rows in that table,
the “CF” is automatically configured by LIBO and is too complex to be reconfigured manually.

So I thought about initializing “CF” with a macro.

For that purpose, we are planning to create a “user function” dedicated to “CF”.

example)
=SetRange(“A3:B200”,“D3:K200”,“P3:P200”)
=SetConditionalFormat(“Formula”,"$A3<=$A$2",“RED”)
=SetConditionalFormat(“Date”,"$A3<=LAST7DAYS",“BLUE”)

So I need a logical background of “Date is” as part of its configuration.

Thanks. That’s all.

#14

@mikekaganski (and everybody): Sorry. No clue how the link-error could happen. Didn’t find yet the correct bug.
@sunshellsaloha: What you tell about your intentions concerning CF doesn’t convince me. I would suggest you use as few modes as possible, and in specific only those you completely understand. Regarding CF LibO did a lot, much of it trying to be compatible with MS Excel seemingly. On that way it may be unavoidable to accept a lot of hokum. … Using the new CF features to the full extent would definitely break the compatibility with AOO… I personally only use the modes Value is and Formula is, and you can easily translate any Value is CF to Formula is.
Dates in spreadsheets are numbers. CF should be independent of applied sorts anyway, as long as it doesn’t contain Formulae willingly breaking this.

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