Bug in conditional formats using LXW_CONDITIONAL_TYPE_TEXT with criteria LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING

[martinzeh]

I generated Excel file with libxlsxwriter and the conditonal format in sheet1.xml look as follows:

    <conditionalFormatting sqref="H2:AI500">
        <cfRule type="containsText" dxfId="3" priority="2" operator="containsText" text="u0">
            <formula>NOT(ISERROR(SEARCH("u0",AI2)))</formula>
        </cfRule>
    </conditionalFormatting>

Excel opens the file without errors, but the conditional format won't be applied if i write 'u0' into a cell. It seems to me, the problem is the cell 'AI2' in the formula. It should be 'H2'

To fix this, the function worksheet_conditional_format_range has to be changed. I think the cond_format->first_cell ist wrong in this case

    /* Store the first cell string for text and date rules. */
    lxw_rowcol_to_cell(cond_format->first_cell, first_row, last_col);
<<<<<<<
    /* Store the first cell string for text and date rules. */
    lxw_rowcol_to_cell(cond_format->first_cell, first_row, first_col);

[jmcnamara]

Could you add a small complete, compilable, example that demonstrates the issue.

Note, match strings usually have to be double quoted in Excel. However, we won't know if that is the issue or if it is something else without a program to demonstrate the issue.

[martinzeh]

Here is an example code. The created excel file has a conditional format but it does not apply to the cell "u0". If you change worksheet.c as I mentioned the format works.

#include "xlsxwriter.h"

int main(int argc, char *argv[])
{
    lxw_workbook  *workbook  = workbook_new("conditional_format_wrong.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    /* Write some sample data. */
    worksheet_write_string(worksheet, 4, 4, "u0", NULL);

    /* Add a format with red text. */
    lxw_format *custom_format = workbook_add_format(workbook);
    format_set_bg_color(custom_format, LXW_COLOR_RED);

    /* Create a conditional format object. A static object would also work. */
    lxw_conditional_format conditional_format;
    memset(&conditional_format, 0, sizeof(conditional_format));

    /* Set the format type: a cell conditional: */
    conditional_format.type     = LXW_CONDITIONAL_TYPE_TEXT;

    /* Set the criteria to use: */
    conditional_format.criteria = LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING;

    /* Set the value to which the criteria will be applied: */
    conditional_format.value_string = "u0";

    /* Set the format to use if the criteria/value applies: */
    conditional_format.format   = custom_format;

    /* Now apply the format to data range. */
    worksheet_conditional_format_range(
            worksheet
        ,   0
        ,   0
        ,   10
        ,   10
        ,   & conditional_format
        );

    /* Free the object and close the file. */
    return workbook_close(workbook);
}

[jmcnamara]

Thanks for the sample code.

That is a bug and it is in the area where you reported it. I've pushed a fix to main.

Regards.