[GH-ISSUE #520] `lxw_table_column.format` is ignored for data written by the caller #518

New issue

Open

opened 2026-10-02 23:11:47 -06:00 by gitea-mirror · 1 comment

gitea-mirror commented

2026-10-02 23:11:47 -06:00

Owner

Originally created by @billdenney on GitHub (Jul 28, 2026).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/520

Originally assigned to: @jmcnamara on GitHub.

lxw_table_column.format is documented in worksheet.h as:

/** Set the format for the data rows in the column.  */
lxw_format *format;

but it is only applied to cells libxlsxwriter writes itself. Data written by
the caller before worksheet_add_table() keeps no format, silently.

column->format is read in exactly two places:

_write_column_function() (worksheet.c:1830) — the total-row cell
_write_column_formula() (worksheet.c:1874) — a calculated column

_write_table_column_data() never applies it to the rest of the range, so the
usual way of building a table — write the data, then mark it as a table —
loses the format.

Reproducer

#include "xlsxwriter.h"

int main(void) {
    lxw_workbook  *wb = workbook_new("table_format.xlsx");
    lxw_worksheet *ws = workbook_add_worksheet(wb, NULL);
    lxw_format    *money = workbook_add_format(wb);
    format_set_num_format(money, "$#,##0.00");

    worksheet_write_string(ws, 0, 0, "item",  NULL);
    worksheet_write_string(ws, 0, 1, "price", NULL);
    worksheet_write_string(ws, 0, 2, "calc",  NULL);
    worksheet_write_string(ws, 1, 0, "a", NULL);
    worksheet_write_number(ws, 1, 1, 1.5, NULL);
    worksheet_write_string(ws, 2, 0, "b", NULL);
    worksheet_write_number(ws, 2, 1, 2.5, NULL);

    lxw_table_column col_item  = {.header = "item"};
    lxw_table_column col_price = {.header = "price", .format = money};
    lxw_table_column col_calc  = {.header = "calc",
                                  .formula = "=[@price]*2",
                                  .format = money};
    lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL};

    lxw_table_options opts = {.columns = cols};
    worksheet_add_table(ws, 0, 0, 2, 2, &opts);

    return workbook_close(wb);
}

xl/worksheets/sheet1.xml:

<row r="2" spans="1:3">
  <c r="A2" t="s"><v>3</v></c>
  <c r="B2"><v>1.5</v></c>
  <c r="C2" s="1"><f>[[#This Row],price]*2</f><v>0</v></c>
</row>

B2 carries no s attribute; C2 has s="1". Same lxw_format *, same
struct field, on adjacent columns of one table. The only difference is that
libxlsxwriter wrote C and the caller wrote B.

In Excel the calc column shows $3.00 and price shows 1.5.

Suggested resolutions

Either would resolve it; the first matches the documented contract:

Apply column->format across the column's data rows in
_write_table_column_data(), as _write_column_formula() already does for
the cells it writes.
If applying it to pre-existing cells is out of scope, narrow the doc comment
to say it applies only to columns libxlsxwriter writes (calculated columns
and the total row), so the limitation is discoverable.

Found while adding table support to writexl Version 1.2.4.

Originally created by @billdenney on GitHub (Jul 28, 2026). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/520 Originally assigned to: @jmcnamara on GitHub. `lxw_table_column.format` is documented in `worksheet.h` as: ```c /** Set the format for the data rows in the column. */ lxw_format *format; ``` but it is only applied to cells libxlsxwriter writes itself. Data written by the caller before `worksheet_add_table()` keeps no format, silently. `column->format` is read in exactly two places: * `_write_column_function()` (`worksheet.c:1830`) — the total-row cell * `_write_column_formula()` (`worksheet.c:1874`) — a calculated column `_write_table_column_data()` never applies it to the rest of the range, so the usual way of building a table — write the data, then mark it as a table — loses the format. ## Reproducer ```c #include "xlsxwriter.h" int main(void) { lxw_workbook *wb = workbook_new("table_format.xlsx"); lxw_worksheet *ws = workbook_add_worksheet(wb, NULL); lxw_format *money = workbook_add_format(wb); format_set_num_format(money, "$#,##0.00"); worksheet_write_string(ws, 0, 0, "item", NULL); worksheet_write_string(ws, 0, 1, "price", NULL); worksheet_write_string(ws, 0, 2, "calc", NULL); worksheet_write_string(ws, 1, 0, "a", NULL); worksheet_write_number(ws, 1, 1, 1.5, NULL); worksheet_write_string(ws, 2, 0, "b", NULL); worksheet_write_number(ws, 2, 1, 2.5, NULL); lxw_table_column col_item = {.header = "item"}; lxw_table_column col_price = {.header = "price", .format = money}; lxw_table_column col_calc = {.header = "calc", .formula = "=[@price]*2", .format = money}; lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL}; lxw_table_options opts = {.columns = cols}; worksheet_add_table(ws, 0, 0, 2, 2, &opts); return workbook_close(wb); } ``` `xl/worksheets/sheet1.xml`: ```xml <row r="2" spans="1:3"> <c r="A2" t="s"><v>3</v></c> <c r="B2"><v>1.5</v></c> <c r="C2" s="1"><f>[[#This Row],price]*2</f><v>0</v></c> </row> ``` `B2` carries no `s` attribute; `C2` has `s="1"`. Same `lxw_format *`, same struct field, on adjacent columns of one table. The only difference is that libxlsxwriter wrote `C` and the caller wrote `B`. In Excel the `calc` column shows `$3.00` and `price` shows `1.5`. ## Suggested resolutions Either would resolve it; the first matches the documented contract: 1. Apply `column->format` across the column's data rows in `_write_table_column_data()`, as `_write_column_formula()` already does for the cells it writes. 2. If applying it to pre-existing cells is out of scope, narrow the doc comment to say it applies only to columns libxlsxwriter writes (calculated columns and the total row), so the limitation is discoverable. Found while adding table support to writexl Version 1.2.4.

gitea-mirror added the

docs

label

2026-10-02 23:11:47 -06:00

gitea-mirror commented

2026-10-02 23:11:48 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 29, 2026):

The other versions of this library in Python/Perl/Rust handle this correctly because the allow the table data to be passed as one of the table parameters so that data is written by the internal table functions and the format is applied.

This is also why the table formatting works for table formulas (like in column "calc") in libxlsxwriter because the formula and the format are both known to the library at the time of writing.

In order to get this to work with libxlsxwriter you also need to apply the format when you are writing the data:

#include "xlsxwriter.h"

int main(void) {
    lxw_workbook  *wb = workbook_new("table_format.xlsx");
    lxw_worksheet *ws = workbook_add_worksheet(wb, NULL);
    lxw_format    *money = workbook_add_format(wb);
    format_set_num_format(money, "$#,##0.00");

    worksheet_write_string(ws, 0, 0, "item",  NULL);
    worksheet_write_string(ws, 0, 1, "price", NULL);
    worksheet_write_string(ws, 0, 2, "calc",  NULL);
    worksheet_write_string(ws, 1, 0, "a", NULL);
    worksheet_write_number(ws, 1, 1, 1.5, money); // Changed.
    worksheet_write_string(ws, 2, 0, "b", NULL);
    worksheet_write_number(ws, 2, 1, 2.5, money); // Changed.

    lxw_table_column col_item  = {.header = "item"};
    lxw_table_column col_price = {.header = "price", };
    lxw_table_column col_calc  = {.header = "calc",
                                  .formula = "=[@price]*2",
                                  .format = money};
    lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL};

    lxw_table_options opts = {.columns = cols};
    worksheet_add_table(ws, 0, 0, 2, 2, &opts);

    return workbook_close(wb);
}

In this case applying the format to column would probably be better:

#include "xlsxwriter.h"

int main(void) {
    lxw_workbook  *wb = workbook_new("table_format.xlsx");
    lxw_worksheet *ws = workbook_add_worksheet(wb, NULL);
    lxw_format    *money = workbook_add_format(wb);
    format_set_num_format(money, "$#,##0.00");

    worksheet_set_column(ws, 1, 1, LXW_DEF_COL_WIDTH, money);  // Changed.

    worksheet_write_string(ws, 0, 0, "item",  NULL);
    worksheet_write_string(ws, 0, 1, "price", NULL);
    worksheet_write_string(ws, 0, 2, "calc",  NULL);
    worksheet_write_string(ws, 1, 0, "a", NULL);
    worksheet_write_number(ws, 1, 1, 1.5, NULL);
    worksheet_write_string(ws, 2, 0, "b", NULL);
    worksheet_write_number(ws, 2, 1, 2.5, NULL);

    lxw_table_column col_item  = {.header = "item"};
    lxw_table_column col_price = {.header = "price", };
    lxw_table_column col_calc  = {.header = "calc",
                                  .formula = "=[@price]*2",
                                  .format = money};
    lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL};

    lxw_table_options opts = {.columns = cols};
    worksheet_add_table(ws, 0, 0, 2, 2, &opts);

    return workbook_close(wb);
}

Note, you still need to apply the column format via lxw_table_options for strict correctness with Excel.

This limitation should be documented. I will do that.

@jmcnamara commented on GitHub (Jul 29, 2026): The other versions of this library in Python/Perl/Rust handle this correctly because the allow the table data to be passed as one of the table parameters so that data is written by the internal table functions and the format is applied. This is also why the table formatting works for table formulas (like in column "calc") in `libxlsxwriter` because the formula and the format are both known to the library at the time of writing. In order to get this to work with `libxlsxwriter` you also need to apply the format when you are writing the data: ```C #include "xlsxwriter.h" int main(void) { lxw_workbook *wb = workbook_new("table_format.xlsx"); lxw_worksheet *ws = workbook_add_worksheet(wb, NULL); lxw_format *money = workbook_add_format(wb); format_set_num_format(money, "$#,##0.00"); worksheet_write_string(ws, 0, 0, "item", NULL); worksheet_write_string(ws, 0, 1, "price", NULL); worksheet_write_string(ws, 0, 2, "calc", NULL); worksheet_write_string(ws, 1, 0, "a", NULL); worksheet_write_number(ws, 1, 1, 1.5, money); // Changed. worksheet_write_string(ws, 2, 0, "b", NULL); worksheet_write_number(ws, 2, 1, 2.5, money); // Changed. lxw_table_column col_item = {.header = "item"}; lxw_table_column col_price = {.header = "price", }; lxw_table_column col_calc = {.header = "calc", .formula = "=[@price]*2", .format = money}; lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL}; lxw_table_options opts = {.columns = cols}; worksheet_add_table(ws, 0, 0, 2, 2, &opts); return workbook_close(wb); } ``` <img width="612" height="452" alt="Image" src="https://github.com/user-attachments/assets/afffa9cb-499f-4f5a-a8d8-e267b952e1ab" /> In this case applying the format to column would probably be better: ```C #include "xlsxwriter.h" int main(void) { lxw_workbook *wb = workbook_new("table_format.xlsx"); lxw_worksheet *ws = workbook_add_worksheet(wb, NULL); lxw_format *money = workbook_add_format(wb); format_set_num_format(money, "$#,##0.00"); worksheet_set_column(ws, 1, 1, LXW_DEF_COL_WIDTH, money); // Changed. worksheet_write_string(ws, 0, 0, "item", NULL); worksheet_write_string(ws, 0, 1, "price", NULL); worksheet_write_string(ws, 0, 2, "calc", NULL); worksheet_write_string(ws, 1, 0, "a", NULL); worksheet_write_number(ws, 1, 1, 1.5, NULL); worksheet_write_string(ws, 2, 0, "b", NULL); worksheet_write_number(ws, 2, 1, 2.5, NULL); lxw_table_column col_item = {.header = "item"}; lxw_table_column col_price = {.header = "price", }; lxw_table_column col_calc = {.header = "calc", .formula = "=[@price]*2", .format = money}; lxw_table_column *cols[] = {&col_item, &col_price, &col_calc, NULL}; lxw_table_options opts = {.columns = cols}; worksheet_add_table(ws, 0, 0, 2, 2, &opts); return workbook_close(wb); } ``` Note, you still need to apply the column format via `lxw_table_options` for strict correctness with Excel. This limitation should be documented. I will do that.

gitea-mirror referenced this issue

2026-10-02 23:12:47 -06:00

[PR #518] [CLOSED] Add workbook_rename_worksheet() and workbook_remove_worksheet() #525