Conditional formatting - how does it work?

[jimmer74]

Hi,

firstly let me say I love this project. Absolutely fantastic.

I am having a problem implementing conditional formatting though. I can find no examples of how this is done in the documentation and am flailing around blindly. The closest I have got to something that compiles is:

let out_sheet = out_book.new_sheet("listDataTable").unwrap();

let mut new_cond = ConditionalFormatting::default();
let mut ran = Range::default();
ran.set_range("B2:B4");

let mut add = Address::default();
add.set_range(ran);
add.set_sheet_name("listDataTable");

let mut form = Formula::default();
form.set_address(add);
form.set_string_value(">0");

let mut cond = ConditionalFormattingRule::default();
cond.set_formula(form);

println!("cond coll: {:?}", out_sheet.get_conditional_formatting_collection());


new_cond.set_conditional_collection(vec![cond]);

out_sheet.set_conditional_formatting_collection(vec![new_cond]);

however this produces a mangled sheet that excel has to run a repair on.

I'm thrashing about and have tried various combos of code, but it's not googleable as not that many are doing talking/about it.

A simple/solid example of how to conditionally colour a range of cells if their value is over a certain threshold would be appreciated!

Thanks in advance!

[MathNya]

@jimmer74 Thank you for contacting us.

ConditionalFormatting has not been a feature that has been talked about much since it was implemented. Therefore, I believe it still has many glitches.

We will proceed to investigate the defects first. I will respond as soon as the investigation is complete.

[jimmer74]

@MathNya thanks for the fast response.

Just looking for a simple example of the feature really. Just something so I get started (and test it and see how reliable it is for my use case).

TIA, Jim

[MathNya]

@jimmer74 An example implementation is shown below.

    let mut out_book = new_file();
    let out_sheet = out_book.new_sheet("listDataTable").unwrap();

    let mut color = Color::default();
    color.set_argb("FF0000");  // Background color when conditions are met
    let mut pattern_fill = PatternFill::default();
    pattern_fill.set_background_color(color);
    let mut fill = Fill::default();
    fill.set_pattern_fill(pattern_fill);
    let mut style = Style::default();
    style.set_fill(fill);

    let mut form = Formula::default();
    form.set_string_value("20"); // threshold

    let mut cond = ConditionalFormattingRule::default();
    cond.set_type(ConditionalFormatValues::CellIs)
        .set_operator(ConditionalFormattingOperatorValues::GreaterThan)  // Judgment condition with threshold value
        .set_priority(1)
        .set_style(style)
        .set_formula(form);

    let mut seq = SequenceOfReferences::default();
    seq.set_sqref("B2:B4");  // scope

    let mut new_cond = ConditionalFormatting::default();
    new_cond.set_sequence_of_references(seq);

    new_cond.set_conditional_collection(vec![cond]);
    out_sheet.set_conditional_formatting_collection(vec![new_cond]);

    let path = std::path::Path::new("./tests/result_files/issue_218.xlsx");
    let _ = writer::xlsx::write(&out_book, path);