[Question] Conditional Formatting

[arov74arm]

@aral-matrix
Is there any possibility to specify Conditional Formatting on cell?
If no, then is it possible to retrieve Conditional Formatting from existing cell and apply it to another cell?

[aral-matrix]

Short answer: not yet.

If you can provide me with a simplest form of a (single spreadsheet) XLSX document that has conditional formatting, I can have a look at it - I always work better with examples :)

The standard seems to be clear:
https://c-rex.net/samples/ooxml/e1/Part4/OOXML_P4_DOCX_conditionalFormattin_topic_ID0EN4R4.html
https://c-rex.net/samples/ooxml/e1/Part4/OOXML_P4_DOCX_cfRule_topic_ID0EFKO4.html

It's non-trivial, but I could probably make a conditional formatting object that allows you to set the properties yourself from code - it would then be your responsibility to set them properly.

Something like

XLConditionalFormatting.setSqref( const string &cellRangeReference )
XLConditionalFormatting.cfRule().set[Property](...)

Unfortunately, the cfRule tag has a couple of different child elements - for starters, the only one I would support would be setting a string for the <formula> child element, until another use case arises or I get bored ;)

[arov74arm]

Thanks a lot for quick response.
Please find a simple spreadsheet attached. The A3 cell has a simple conditional formatting.
Of course I can set text and fill colors manually, performing calculations and applying corresponding conditions, but it would be nice to have XLConditionalFormatting object (like you said) with necessary properties and apply it to all rows I'll add into the spreadsheet.
Template.xlsx

[aral-matrix]

Thank you - I will have a look in the coming days. My apologies but this might take a bit as I am having a bit of trouble focusing today, and I need to sort my duties upcoming in the new year.

[arov74arm]

NOP, please don't worry.
I'll wait for news from you.
Good luck and success builds :)

[aral-matrix]

Bastards... I just had a closer look and - sure enough - Microsoft is intentionally breaking the schema that they apply throughout every feature that OpenXLSX supports so far: No, multiple conditional formats are not arranged under one conditional formatting root node - instead, they create a separate "root" node per conditional formatting rule under the worksheet root node. This means a conditional formats class (like the XLCellFormats in XLStyles) can not be implemented in the same way :/ I'll have to keep access to the worksheet XML and access / create conditional format nodes from there.

[arov74arm]

Microsoft wouldn't be Microsoft if it didn't make our life difficult :)

[aral-matrix]

Started implementing in the development-aral node: 039b9be
So far the only thing that works is modifying the sqref - using your Template.xlsx like in the example below - after adding a second, deviating, conditional formatting rule for cell B10 (or C10). Example usage below. The important thing is - despite my beer-head (or thanks to?), I have the framework in place, and can hopefully add the cfRules properties without too much extra effort.

From what LibreOffice saving an XLSX tells me, the following is true:

• conditionalFormatting nodes are (as mentioned above) an "array" of nodes on the root level of the worksheet XML
• conditional formatting rules can be plenty, all stored as an "array" of nodes on the root level of their respective conditional formatting node

I put "array" in quotes, because the only way these can be grouped into an array is by XML element (tag) name, while on the same hierarchy level, elements with differing node names can be stored.
I would base my implementation on the following assumptions (until proven otherwise):

• conditionalFormatting nodes on worksheet XML level must be inserted in the root node per the sequence "sheetPr", "dimension", "sheetViews", "sheetFormatPr", "cols", "sheetData", "sheetProtection", "mergeCells", "conditionalFormatting", "printOptions", "pageMargins", "pageSetup", "headerFooter" - so following mergeCells and prior to printOptions - if this proves to be the wrong location (MS Office would eventually complain loading the file), it's an easy fix
• cfRule nodes are a sequence of child nodes inserted at the beginning of the children of any given conditionalFormatting node and their sequence does not matter (again, until proven otherwise)

The below code can be used - for now - to change the sqref for an existing conditional formatting rule. Feel free to have a look already - or wait til I have implemented the whole thing. :)

    XLDocument doc;

    doc.open("Template.xlsx");
    auto wks = doc.workbook().worksheet("Sheet1");
    XLConditionalFormats cfs = wks.conditionalFormats();
    std::cout << "wks conditionalFormats count is " << cfs.count() << std::endl;
    for( size_t idx = 0; idx < cfs.count(); ++idx ) {
       XLConditionalFormat cf = cfs[ idx ];
       std::cout << "conditionalFormats[ " << idx << " ].sqref() is " << cf.sqref() << std::endl;
       if( cf.sqref() == "A3" ) cf.setSqref( "A4" );
       else if( cf.sqref() == "A4" ) cf.setSqref( "A3" );
       if( cf.sqref() == "B10" ) cf.setSqref( "C10" );
       else if( cf.sqref() == "C10" ) cf.setSqref( "B10" );
       std::cout << "conditionalFormats[ " << idx << " ].sqref() is " << cf.sqref() << std::endl;
    }
    doc.save();

[arov74arm]

I'll wait till you will finish implementation as I have something else to complete.
I owe you a box of good Armenian beer :)

[aral-matrix]

Here my friend, this was a piece of work: df96485

:D But well - it's a lot of general functionality gained, so I hope it is time well invested. I might take you up on that beer offer if I ever get a chance g

PS: Demo10 gives you a usage example - as mentioned in README.md

[arov74arm]

My dear friend,

Tons of thanks for great work you did.
First of all about most important thing... :) beer offer still stands, you just need to plan to spend some days of your vacation in Armenia, so I'm inviting you to visit us :)

My use-case is quite simple as I already have one conditional formatting specified in my template spreadsheet and it is necessary only to apply it to all newly added rows:

OpenXLSX::XLConditionalFormat condFormat = workSheet.conditionalFormats()[0];
std::string sqref("A");
sqref += std::to_string(DATA_START_ROW) + ":A" + std::to_string(DATA_START_ROW + dataRowsCount - 1);
condFormat.setSqref(sqref);

It works fine at my side!

PS: Please add some comment in this thread when you will release your changes so I'll switch to new release library.

[aral-matrix]

I am happy you have what you need now (though in hindsight, considering you only needed to update the sqref, I went a little overboard with the complete implementation ;) but if I do something I want to do it right).

I might actually visit Armenia sometime soon - I visit Turkey a lot and wanted to see a bit of the east of the country - I wonder if they will still let me enter with an Armenian passport stamp :D

[arov74arm]

Hi my friend,

I'm sure somebody will fully use the CF functionality you added. Per me it very easy to understand and use OpenXLSX library at all, so clever developers should choose it :)

There are flights from Istanbul to Yerevan and that should not be an issue for you. Another question is do you need a VISA to enter to Armenia... I can check it for you if you will let me know your country.