[GH-ISSUE #395] Bug in conditional formats using LXW_CONDITIONAL_TYPE_TEXT with criteria LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING #313

New issue

Closed

opened 2026-05-05 12:08:31 -06:00 by gitea-mirror · 3 comments

gitea-mirror commented

2026-05-05 12:08:31 -06:00

Owner

Originally created by @martinzeh on GitHub (Feb 23, 2023).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/395

Originally assigned to: @jmcnamara on GitHub.

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);

Originally created by @martinzeh on GitHub (Feb 23, 2023). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/395 Originally assigned to: @jmcnamara on GitHub. I generated Excel file with libxlsxwriter and the conditonal format in sheet1.xml look as follows: ```XML <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 ```C /* 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); ```

gitea-mirror

2026-05-05 12:08:31 -06:00

closed this issue
added the
awaiting user feedback
label

gitea-mirror commented

2026-05-05 12:08:36 -06:00

Author

Owner

@jmcnamara commented on GitHub (Feb 23, 2023):

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.

@jmcnamara commented on GitHub (Feb 23, 2023): 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.

gitea-mirror commented

2026-05-05 12:08:37 -06:00

Author

Owner

@martinzeh commented on GitHub (Feb 23, 2023):

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);
}

@martinzeh commented on GitHub (Feb 23, 2023): 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. ```C #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); } ```

gitea-mirror commented

2026-05-05 12:08:39 -06:00

Author

Owner

@jmcnamara commented on GitHub (Feb 23, 2023):

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.

@jmcnamara commented on GitHub (Feb 23, 2023): 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.