[GH-ISSUE #203] Problem with number formats #167

Closed
opened 2026-05-05 11:49:31 -06:00 by gitea-mirror · 4 comments
Owner

Originally created by @Alzathar on GitHub (Oct 29, 2018).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/203

Originally assigned to: @jmcnamara on GitHub.

libxlsxwriter: RELEASE_0.8.3
OS: Window 10 x64
IDE: MSVC15 x64


This problem is similar tothe issue #151 but with these number formats:

  • 0
  • 0.0
  • 0.00
  • 0.000

The problem is related to the functions lxw_hash_key_exists and lxw_insert_hash_element. The following snippet is the result of the generated numformat in the xl/styles.xml file.

    <numFmts count="3">
        <numFmt numFmtId="164" formatCode="0.000"/>
        <numFmt numFmtId="164" formatCode="0.0"/>
        <numFmt numFmtId="165" formatCode="0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
        <numFmt numFmtId="164" formatCode="0.000"/>
        <numFmt numFmtId="164" formatCode="0.0"/>
        <numFmt numFmtId="165" formatCode="0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
        <numFmt numFmtId="165" formatCode="0"/>
        <numFmt numFmtId="164" formatCode="0.000"/>
        <numFmt numFmtId="164" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
    </numFmts>

I would expect this:

    <numFmts count="4">
        <numFmt numFmtId="164" formatCode="0.000"/>
        <numFmt numFmtId="165" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0"/>
        <numFmt numFmtId="167" formatCode="0.00"/>
    </numFmts>

If I force the value of has_element to false in the code , I gave the good format in the XLSX. Of course, a new numFmt is generated everytime. The result is then this:

    <numFmts count="12">
        <numFmt numFmtId="164" formatCode="0.000"/>
        <numFmt numFmtId="165" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0"/>
        <numFmt numFmtId="167" formatCode="0.00"/>
        <numFmt numFmtId="168" formatCode="0.000"/>
        <numFmt numFmtId="169" formatCode="0.0"/>
        <numFmt numFmtId="170" formatCode="0"/>
        <numFmt numFmtId="171" formatCode="0.00"/>
        <numFmt numFmtId="172" formatCode="0"/>
        <numFmt numFmtId="173" formatCode="0.000"/>
        <numFmt numFmtId="174" formatCode="0.0"/>
        <numFmt numFmtId="175" formatCode="0.00"/>
    </numFmts>

You can reproduce this bug with the following code:
Note: The reported XML contents above do not come from the following code. However, you can see a similar error in the generated numFmts tag with the following code at the end of this issue.

  lxw_workbook  *workbook  = workbook_new("bug_report.xlsx");
  lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
  lxw_format *fmt, *fmt_alt, *fmt_t;

  double value = 123.456789;

  fmt = workbook_add_format(workbook);
  format_set_num_format(fmt,"0");
  format_set_font_size(fmt, 11.0);
  format_set_align(fmt, LXW_ALIGN_RIGHT);
  worksheet_write_number(worksheet, 0, 0, value, fmt);

  fmt_alt = workbook_add_format(workbook);
  format_set_num_format(fmt_alt, "0");
  format_set_font_size(fmt_alt, 11.0);
  format_set_align(fmt_alt, LXW_ALIGN_RIGHT);
  format_set_bg_color(fmt_alt, LXW_COLOR_SILVER);
  worksheet_write_number(worksheet, 1, 0, value, fmt_alt);

  fmt_t = workbook_add_format(workbook);
  format_set_num_format(fmt_t, "0");
  format_set_font_size(fmt_t, 11.0);
  format_set_align(fmt_t, LXW_ALIGN_RIGHT);
  format_set_top(fmt_t, LXW_BORDER_DOTTED);
  worksheet_write_number(worksheet, 2, 0, value, fmt_t);

  fmt = workbook_add_format(workbook);
  format_set_num_format(fmt,"0.0");
  format_set_font_size(fmt, 11.0);
  format_set_align(fmt, LXW_ALIGN_RIGHT);
  worksheet_write_number(worksheet, 0, 1, value, fmt);


  fmt_alt = workbook_add_format(workbook);
  format_set_num_format(fmt_alt, "0.0");
  format_set_font_size(fmt_alt, 11.0);
  format_set_align(fmt_alt, LXW_ALIGN_RIGHT);
  format_set_bg_color(fmt_alt, LXW_COLOR_SILVER);
  worksheet_write_number(worksheet, 1, 1, value, fmt_alt);

  fmt_t = workbook_add_format(workbook);
  format_set_num_format(fmt_t, "0.0");
  format_set_font_size(fmt_t, 11.0);
  format_set_align(fmt_t, LXW_ALIGN_RIGHT);
  format_set_top(fmt_t, LXW_BORDER_DOTTED);
  worksheet_write_number(worksheet, 2, 1, value, fmt_t);

  fmt = workbook_add_format(workbook);
  format_set_num_format(fmt,"0.00");
  format_set_font_size(fmt, 11.0);
  format_set_align(fmt, LXW_ALIGN_RIGHT);
  worksheet_write_number(worksheet, 0, 2, value, fmt);

  fmt_alt = workbook_add_format(workbook);
  format_set_num_format(fmt_alt, "0.00");
  format_set_font_size(fmt_alt, 11.0);
  format_set_align(fmt_alt, LXW_ALIGN_RIGHT);
  format_set_bg_color(fmt_alt, LXW_COLOR_SILVER);
  worksheet_write_number(worksheet, 1, 2, value, fmt_alt);

  fmt_t = workbook_add_format(workbook);
  format_set_num_format(fmt_t, "0.00");
  format_set_font_size(fmt_t, 11.0);
  format_set_align(fmt_t, LXW_ALIGN_RIGHT);
  format_set_top(fmt_t, LXW_BORDER_DOTTED);
  worksheet_write_number(worksheet, 2, 2, value, fmt_t);

  fmt = workbook_add_format(workbook);
  format_set_num_format(fmt,"0.000");
  format_set_font_size(fmt, 11.0);
  format_set_align(fmt, LXW_ALIGN_RIGHT);
  worksheet_write_number(worksheet, 0, 3, value, fmt);

  fmt_alt = workbook_add_format(workbook);
  format_set_num_format(fmt_alt, "0.000");
  format_set_font_size(fmt_alt, 11.0);
  format_set_align(fmt_alt, LXW_ALIGN_RIGHT);
  format_set_bg_color(fmt_alt, LXW_COLOR_SILVER);
  worksheet_write_number(worksheet, 1, 3, value, fmt_alt);

  fmt_t = workbook_add_format(workbook);
  format_set_num_format(fmt_t, "0.000");
  format_set_font_size(fmt_t, 11.0);
  format_set_align(fmt_t, LXW_ALIGN_RIGHT);
  format_set_top(fmt_t, LXW_BORDER_DOTTED);
  worksheet_write_number(worksheet, 2, 3, value, fmt_t);

  return workbook_close(workbook);

Generated numformat in the xl/styles.xml file from the code above.

    <numFmts count="3">
        <numFmt numFmtId="164" formatCode="0"/>
        <numFmt numFmtId="165" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
        <numFmt numFmtId="165" formatCode="0.000"/>
        <numFmt numFmtId="164" formatCode="0"/>
        <numFmt numFmtId="165" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
        <numFmt numFmtId="165" formatCode="0.000"/>
        <numFmt numFmtId="164" formatCode="0"/>
        <numFmt numFmtId="165" formatCode="0.0"/>
        <numFmt numFmtId="166" formatCode="0.00"/>
        <numFmt numFmtId="165" formatCode="0.000"/>
    </numFmts>
Originally created by @Alzathar on GitHub (Oct 29, 2018). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/203 Originally assigned to: @jmcnamara on GitHub. libxlsxwriter: RELEASE_0.8.3 OS: Window 10 x64 IDE: MSVC15 x64 ---- This problem is similar tothe issue #151 but with these number formats: - `0` - `0.0` - `0.00` - `0.000` The problem is related to the functions `lxw_hash_key_exists` and `lxw_insert_hash_element`. The following snippet is the result of the generated `numformat` in the `xl/styles.xml` file. ```xml <numFmts count="3"> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="164" formatCode="0.0"/> <numFmt numFmtId="165" formatCode="0"/> <numFmt numFmtId="166" formatCode="0.00"/> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="164" formatCode="0.0"/> <numFmt numFmtId="165" formatCode="0"/> <numFmt numFmtId="166" formatCode="0.00"/> <numFmt numFmtId="165" formatCode="0"/> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="164" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0.00"/> </numFmts> ``` I would expect this: ```xml <numFmts count="4"> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="165" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0"/> <numFmt numFmtId="167" formatCode="0.00"/> </numFmts> ``` If I force the value of `has_element` to false in the [code](https://github.com/jmcnamara/libxlsxwriter/blob/master/src/workbook.c#L465) , I gave the good format in the XLSX. Of course, a new numFmt is generated everytime. The result is then this: ``` <numFmts count="12"> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="165" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0"/> <numFmt numFmtId="167" formatCode="0.00"/> <numFmt numFmtId="168" formatCode="0.000"/> <numFmt numFmtId="169" formatCode="0.0"/> <numFmt numFmtId="170" formatCode="0"/> <numFmt numFmtId="171" formatCode="0.00"/> <numFmt numFmtId="172" formatCode="0"/> <numFmt numFmtId="173" formatCode="0.000"/> <numFmt numFmtId="174" formatCode="0.0"/> <numFmt numFmtId="175" formatCode="0.00"/> </numFmts> ``` ---- You can reproduce this bug with the following code: **Note**: The reported XML contents above do not come from the following code. However, you can see a similar error in the generated `numFmts` tag with the following code at the end of this issue. ```c lxw_workbook *workbook = workbook_new("bug_report.xlsx"); lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL); lxw_format *fmt, *fmt_alt, *fmt_t; double value = 123.456789; fmt = workbook_add_format(workbook); format_set_num_format(fmt,"0"); format_set_font_size(fmt, 11.0); format_set_align(fmt, LXW_ALIGN_RIGHT); worksheet_write_number(worksheet, 0, 0, value, fmt); fmt_alt = workbook_add_format(workbook); format_set_num_format(fmt_alt, "0"); format_set_font_size(fmt_alt, 11.0); format_set_align(fmt_alt, LXW_ALIGN_RIGHT); format_set_bg_color(fmt_alt, LXW_COLOR_SILVER); worksheet_write_number(worksheet, 1, 0, value, fmt_alt); fmt_t = workbook_add_format(workbook); format_set_num_format(fmt_t, "0"); format_set_font_size(fmt_t, 11.0); format_set_align(fmt_t, LXW_ALIGN_RIGHT); format_set_top(fmt_t, LXW_BORDER_DOTTED); worksheet_write_number(worksheet, 2, 0, value, fmt_t); fmt = workbook_add_format(workbook); format_set_num_format(fmt,"0.0"); format_set_font_size(fmt, 11.0); format_set_align(fmt, LXW_ALIGN_RIGHT); worksheet_write_number(worksheet, 0, 1, value, fmt); fmt_alt = workbook_add_format(workbook); format_set_num_format(fmt_alt, "0.0"); format_set_font_size(fmt_alt, 11.0); format_set_align(fmt_alt, LXW_ALIGN_RIGHT); format_set_bg_color(fmt_alt, LXW_COLOR_SILVER); worksheet_write_number(worksheet, 1, 1, value, fmt_alt); fmt_t = workbook_add_format(workbook); format_set_num_format(fmt_t, "0.0"); format_set_font_size(fmt_t, 11.0); format_set_align(fmt_t, LXW_ALIGN_RIGHT); format_set_top(fmt_t, LXW_BORDER_DOTTED); worksheet_write_number(worksheet, 2, 1, value, fmt_t); fmt = workbook_add_format(workbook); format_set_num_format(fmt,"0.00"); format_set_font_size(fmt, 11.0); format_set_align(fmt, LXW_ALIGN_RIGHT); worksheet_write_number(worksheet, 0, 2, value, fmt); fmt_alt = workbook_add_format(workbook); format_set_num_format(fmt_alt, "0.00"); format_set_font_size(fmt_alt, 11.0); format_set_align(fmt_alt, LXW_ALIGN_RIGHT); format_set_bg_color(fmt_alt, LXW_COLOR_SILVER); worksheet_write_number(worksheet, 1, 2, value, fmt_alt); fmt_t = workbook_add_format(workbook); format_set_num_format(fmt_t, "0.00"); format_set_font_size(fmt_t, 11.0); format_set_align(fmt_t, LXW_ALIGN_RIGHT); format_set_top(fmt_t, LXW_BORDER_DOTTED); worksheet_write_number(worksheet, 2, 2, value, fmt_t); fmt = workbook_add_format(workbook); format_set_num_format(fmt,"0.000"); format_set_font_size(fmt, 11.0); format_set_align(fmt, LXW_ALIGN_RIGHT); worksheet_write_number(worksheet, 0, 3, value, fmt); fmt_alt = workbook_add_format(workbook); format_set_num_format(fmt_alt, "0.000"); format_set_font_size(fmt_alt, 11.0); format_set_align(fmt_alt, LXW_ALIGN_RIGHT); format_set_bg_color(fmt_alt, LXW_COLOR_SILVER); worksheet_write_number(worksheet, 1, 3, value, fmt_alt); fmt_t = workbook_add_format(workbook); format_set_num_format(fmt_t, "0.000"); format_set_font_size(fmt_t, 11.0); format_set_align(fmt_t, LXW_ALIGN_RIGHT); format_set_top(fmt_t, LXW_BORDER_DOTTED); worksheet_write_number(worksheet, 2, 3, value, fmt_t); return workbook_close(workbook); ``` Generated `numformat` in the `xl/styles.xml` file from the code above. ```xml <numFmts count="3"> <numFmt numFmtId="164" formatCode="0"/> <numFmt numFmtId="165" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0.00"/> <numFmt numFmtId="165" formatCode="0.000"/> <numFmt numFmtId="164" formatCode="0"/> <numFmt numFmtId="165" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0.00"/> <numFmt numFmtId="165" formatCode="0.000"/> <numFmt numFmtId="164" formatCode="0"/> <numFmt numFmtId="165" formatCode="0.0"/> <numFmt numFmtId="166" formatCode="0.00"/> <numFmt numFmtId="165" formatCode="0.000"/> </numFmts> ```
gitea-mirror 2026-05-05 11:49:31 -06:00
  • closed this issue
  • added the
    bug
    label
Author
Owner

@jmcnamara commented on GitHub (Oct 29, 2018):

Thanks. That looks like a bug. I'll look into it.

Mainly for my reference, here is a simplified version of your code that reproduces it:

#include "xlsxwriter.h"

int main() {

    lxw_workbook  *workbook  = workbook_new("bug_report.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
    lxw_format    *format;

    double value = 123.456;

    format = workbook_add_format(workbook);
    format_set_num_format(format, "0.0");

    worksheet_write_number(worksheet, 0, 0, value, format);


    format = workbook_add_format(workbook);
    format_set_num_format(format, "0.000");

    worksheet_write_number(worksheet, 0, 1, value, format);


    format = workbook_add_format(workbook);
    format_set_num_format(format, "0.0000");

    worksheet_write_number(worksheet, 0, 2, value, format);


    format = workbook_add_format(workbook);
    format_set_num_format(format, "0.00000");

    worksheet_write_number(worksheet, 0, 3, value, format);


    return workbook_close(workbook);
}

Output:

  <numFmts count="3">
    <numFmt numFmtId="164" formatCode="0.0"/>
    <numFmt numFmtId="164" formatCode="0.000"/>
    <numFmt numFmtId="165" formatCode="0.0000"/>
    <numFmt numFmtId="166" formatCode="0.00000"/>
  </numFmts>
<!-- gh-comment-id:434075152 --> @jmcnamara commented on GitHub (Oct 29, 2018): Thanks. That looks like a bug. I'll look into it. Mainly for my reference, here is a simplified version of your code that reproduces it: ```C #include "xlsxwriter.h" int main() { lxw_workbook *workbook = workbook_new("bug_report.xlsx"); lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL); lxw_format *format; double value = 123.456; format = workbook_add_format(workbook); format_set_num_format(format, "0.0"); worksheet_write_number(worksheet, 0, 0, value, format); format = workbook_add_format(workbook); format_set_num_format(format, "0.000"); worksheet_write_number(worksheet, 0, 1, value, format); format = workbook_add_format(workbook); format_set_num_format(format, "0.0000"); worksheet_write_number(worksheet, 0, 2, value, format); format = workbook_add_format(workbook); format_set_num_format(format, "0.00000"); worksheet_write_number(worksheet, 0, 3, value, format); return workbook_close(workbook); } ``` Output: ```xml <numFmts count="3"> <numFmt numFmtId="164" formatCode="0.0"/> <numFmt numFmtId="164" formatCode="0.000"/> <numFmt numFmtId="165" formatCode="0.0000"/> <numFmt numFmtId="166" formatCode="0.00000"/> </numFmts> ```
Author
Owner

@jmcnamara commented on GitHub (Oct 30, 2018):

I've pushed a preliminary fix for this issue to the gh203 branch: https://github.com/jmcnamara/libxlsxwriter/tree/gh203

<!-- gh-comment-id:434508402 --> @jmcnamara commented on GitHub (Oct 30, 2018): I've pushed a preliminary fix for this issue to the gh203 branch: https://github.com/jmcnamara/libxlsxwriter/tree/gh203
Author
Owner

@jmcnamara commented on GitHub (Nov 10, 2018):

Fixed in version 0.8.4.

Thanks for the report.

<!-- gh-comment-id:437597214 --> @jmcnamara commented on GitHub (Nov 10, 2018): Fixed in version 0.8.4. Thanks for the report.
Author
Owner

@Alzathar commented on GitHub (Nov 21, 2018):

I did not have the chance to say "Thank you" for this fix!

<!-- gh-comment-id:440511125 --> @Alzathar commented on GitHub (Nov 21, 2018): I did not have the chance to say "Thank you" for this fix!
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/libxlsxwriter#167
No description provided.