Issue with Conditional Formatting Order in rust_xlsxwriter

Question

I have question about order of conditional format I'm trying to make a new excel sheet with 20 conditional format and they are overlapping in some cells but when i open the sheet the order is reversed i tried to reverse the range in my code for i in (0..19).rev() { but it did not work also i tried to use hard code not a loop and played with the order in the code and it always give me the same outcome am i doing some thing wrong?

    let color_array: [(u32, u32); 20] = [
        (0x0000ff, 0xffff00),
        (0x80ff80, 0x000000),
        (0xffff00, 0x3a00ff),
        (0x0000ff, 0xffffff),
        (0xff0000, 0xffffff),
        (0x80ffff, 0x000000),
        (0xff80ff, 0x000000),
        (0xffff80, 0x000000),
        (0x000000, 0xffffff),
        (0xc0c0c0, 0xffffff),
        (0x008000, 0xffffff),
        (0x0000a0, 0xffffff),
        (0x804000, 0xffffff),
        (0x800080, 0xffffff),
        (0xff8040, 0xffffff),
        (0x8080c0, 0xffffff),
        (0x808040, 0xffffff),
        (0x808080, 0xffffff),
        (0x408080, 0xffffff),
        (0x8080c0, 0xffffff),
    ];

//
......
//
    for i in 0..19 {
        let form = Format::new()
            .set_background_color(Color::RGB(color_array[i].0))
            .set_font_color(Color::RGB(color_array[i].1))
            .set_border_color(Color::RGB(BORDER_COLOR))
            .set_border_top(FormatBorder::Thin)
            .set_border_bottom(FormatBorder::Thin)
            .set_bold()
            .set_align(FormatAlign::Center);

        let rule = format!("$W4>={}", i + 1);
        let conditional = ConditionalFormatFormula::new()
            .set_rule(rule.as_str())
            .set_format(form);
        worksheet.add_conditional_format(3, i as u16, MAX_ROWS, 22, &conditional)?;
    }

*the complete program:

use rust_xlsxwriter::*;
fn main() -> Result<(), XlsxError> {
    const MAX_ROWS: u32 = 5000;
    //Colors
    let color_array: [(u32, u32); 20] = [
        (0x0000ff, 0xffff00),
        (0x80ff80, 0x000000),
        (0xffff00, 0x3a00ff),
        (0x0000ff, 0xffffff),
        (0xff0000, 0xffffff),
        (0x80ffff, 0x000000),
        (0xff80ff, 0x000000),
        (0xffff80, 0x000000),
        (0x000000, 0xffffff),
        (0xc0c0c0, 0xffffff),
        (0x008000, 0xffffff),
        (0x0000a0, 0xffffff),
        (0x804000, 0xffffff),
        (0x800080, 0xffffff),
        (0xff8040, 0xffffff),
        (0x8080c0, 0xffffff),
        (0x808040, 0xffffff),
        (0x808080, 0xffffff),
        (0x408080, 0xffffff),
        (0x8080c0, 0xffffff),
    ];

    const BORDER_COLOR: u32 = 0xc0c0c0;
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    //Set worksheet name
    worksheet.set_name("WBS")?;

    //Add heading data
    worksheet.write(2, 20, "WBS Code")?;
    worksheet.write(2, 21, "WBS Name")?;
    worksheet.write(2, 22, "WBS Level")?;

    //Add first level data
    worksheet.write(3, 20, "Project Code")?;
    worksheet.write(3, 21, "Project Name")?;
    worksheet.write(3, 22, "1")?;

    //first level format *Complete Later*
    let first_level_format = Format::new()
        .set_background_color(Color::RGB(color_array[0].0))
        .set_font_color(Color::RGB(color_array[0].1))
        .set_font_size(12)
        .set_border_color(Color::RGB(BORDER_COLOR))
        .set_border_top(FormatBorder::Thin)
        .set_border_bottom(FormatBorder::Thin)
        .set_bold();
    worksheet.set_range_format(3, 0, 3, 22, &first_level_format)?;

    //special first level format *Complete Later*
    let s_first_level_format = Format::new()
        .set_background_color(Color::RGB(color_array[0].0))
        .set_font_color(Color::RGB(color_array[0].1))
        .set_font_size(12)
        .set_border_color(Color::RGB(BORDER_COLOR))
        .set_border_top(FormatBorder::Thin)
        .set_border_left(FormatBorder::Thin)
        .set_bold();
    worksheet.set_cell_format(3, 0, &s_first_level_format)?;

    //Conditional format 1
    for i in 0..19 {
        // println!("{i}");
        let form = Format::new()
            .set_background_color(Color::RGB(color_array[i].0))
            .set_font_color(Color::RGB(color_array[i].1))
            .set_border_color(Color::RGB(BORDER_COLOR))
            .set_border_top(FormatBorder::Thin)
            .set_border_bottom(FormatBorder::Thin)
            .set_bold()
            .set_align(FormatAlign::Center);

        let rule = format!("$W4>={}", i + 1);
        let conditional = ConditionalFormatFormula::new()
            .set_rule(rule.as_str())
            .set_format(form);
        worksheet.add_conditional_format(3, i as u16, MAX_ROWS, 22, &conditional)?;
    }

    //set  columns width
    worksheet.set_column_range_width(0, 19, 0.5)?;
    worksheet.set_column_range_width(20, 22, 13)?;
    worksheet.set_column_width(21, 50)?;

    // Save the file to disk.
    workbook.save("test.xlsx")?;

    Ok(())
}

The wrong order The desired outcome

i tried to make an excel sheet with 20 conditional format with an order but i cant get this order

Answer 1

After digging into the problem i tried to see the root cause to see what factor affect this problem

i found it relate to the add_conditional_format() method and rules were being sorted into row/column order instead of insertion order only with nested ranges

I raised a GitHub issue that was fixed in version "0.79.1" The issue was present in version "0.79.0"

and here is the change log :

• Fixed issue where the precedence order of conditional formats wasn't being preserved and the rules were being sorted into row/column order instead of insertion order. This issue would only be visible with nested conditional formats and shouldn't affect most users.