[GH-ISSUE #522] `constant_memory` silently drops all but the first cell of a multi-cell array formula #521

New issue

Closed

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

gitea-mirror commented

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

Owner

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

With constant_memory enabled, a multi-cell array formula writes only its
top-left cell. The ref attribute still declares the full range, so the file
claims a result that most of the cells do not contain.
worksheet_write_array_formula() returns LXW_NO_ERROR either way.

Excel opens the file without complaint — it recalculates the array on load and
fills the missing cells — so the problem is invisible if Excel is the only
consumer. It shows up when something reads the stored cells instead of
recalculating them: readxl, pandas, and anything else parsing the sheet
directly see a one-cell result where a three-cell one was declared. A pipeline
that reads such a file and writes it back out, never passing through Excel,
loses the array's extent silently.

Cause

_store_array_formula() (worksheet.c:8163):

/* Pad out the rest of the area with formatted zeroes. */
if (!self->optimize) {
    for (tmp_row = first_row; tmp_row <= last_row; tmp_row++) {
        ...

The same guard also means a format passed for the range is applied only to
the top-left cell.

Reproducer

#include "xlsxwriter.h"

static void build(const char *name, uint8_t constant_memory) {
    lxw_workbook_options options = {.constant_memory = constant_memory};
    lxw_workbook  *wb = workbook_new_opt(name, &options);
    lxw_worksheet *ws = workbook_add_worksheet(wb, NULL);
    lxw_error err;

    /* strictly in row order, as constant_memory requires */
    worksheet_write_number(ws, 0, 0, 10, NULL);
    err = worksheet_write_array_formula(ws, 0, 2, 2, 2, "{=A1:A3*2}", NULL);
    printf("%-14s worksheet_write_array_formula() -> %s\n",
           name, lxw_strerror(err));

    worksheet_write_number(ws, 1, 0, 20, NULL);
    worksheet_write_number(ws, 2, 0, 30, NULL);
    workbook_close(wb);
}

int main(void) {
    build("normal.xlsx",   LXW_FALSE);
    build("constant.xlsx", LXW_TRUE);
    return 0;
}

normal.xlsx    worksheet_write_array_formula() -> No error.
constant.xlsx  worksheet_write_array_formula() -> No error.

normal.xlsx:

<row r="1" spans="1:3"><c r="A1"><v>10</v></c><c r="C1"><f t="array" ref="C1:C3">A1:A3*2</f><v>0</v></c></row>
<row r="2" spans="1:3"><c r="A2"><v>20</v></c><c r="C2"><v>0</v></c></row>
<row r="3" spans="1:3"><c r="A3"><v>30</v></c><c r="C3"><v>0</v></c></row>

constant.xlsx:

<row r="1"><c r="A1"><v>10</v></c><c r="C1"><f t="array" ref="C1:C3">A1:A3*2</f><v>0</v></c></row>
<row r="2"><c r="A2"><v>20</v></c></row>
<row r="3"><c r="A3"><v>30</v></c></row>

Why this may be awkward to fix, and a smaller ask

I appreciate the padding is not simply an oversight: in streaming mode the rows
below the current one have not been reached, so they cannot be written at the
time of the call without buffering or deferring.

If padding in optimize mode is out of scope, the smaller fix would be to stop
returning success. libxlsxwriter already has the precedent —
worksheet_add_table() refuses outright in this mode:

if (self->optimize) {
    LXW_WARN_FORMAT("worksheet_add_table(): "
                    "worksheet tables aren't supported in "
                    "'constant_memory' mode");
    return LXW_ERROR_FEATURE_NOT_SUPPORTED;
}

A multi-cell array formula (last_row > first_row || last_col > first_col)
degrading silently rather than warning is the part that made this hard to find.
Single-cell array formulas are unaffected and could keep working.

Found while adding array-formula support to writexl (R), which now turns
constant_memory off whenever a multi-cell array range is present. Version
1.2.4.

Originally created by @billdenney on GitHub (Jul 28, 2026). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/522 With `constant_memory` enabled, a multi-cell array formula writes only its top-left cell. The `ref` attribute still declares the full range, so the file claims a result that most of the cells do not contain. `worksheet_write_array_formula()` returns `LXW_NO_ERROR` either way. Excel opens the file without complaint — it recalculates the array on load and fills the missing cells — so the problem is invisible if Excel is the only consumer. It shows up when something reads the stored cells instead of recalculating them: readxl, pandas, and anything else parsing the sheet directly see a one-cell result where a three-cell one was declared. A pipeline that reads such a file and writes it back out, never passing through Excel, loses the array's extent silently. ## Cause `_store_array_formula()` (`worksheet.c:8163`): ```c /* Pad out the rest of the area with formatted zeroes. */ if (!self->optimize) { for (tmp_row = first_row; tmp_row <= last_row; tmp_row++) { ... ``` The same guard also means a `format` passed for the range is applied only to the top-left cell. ## Reproducer ```c #include "xlsxwriter.h" static void build(const char *name, uint8_t constant_memory) { lxw_workbook_options options = {.constant_memory = constant_memory}; lxw_workbook *wb = workbook_new_opt(name, &options); lxw_worksheet *ws = workbook_add_worksheet(wb, NULL); lxw_error err; /* strictly in row order, as constant_memory requires */ worksheet_write_number(ws, 0, 0, 10, NULL); err = worksheet_write_array_formula(ws, 0, 2, 2, 2, "{=A1:A3*2}", NULL); printf("%-14s worksheet_write_array_formula() -> %s\n", name, lxw_strerror(err)); worksheet_write_number(ws, 1, 0, 20, NULL); worksheet_write_number(ws, 2, 0, 30, NULL); workbook_close(wb); } int main(void) { build("normal.xlsx", LXW_FALSE); build("constant.xlsx", LXW_TRUE); return 0; } ``` ``` normal.xlsx worksheet_write_array_formula() -> No error. constant.xlsx worksheet_write_array_formula() -> No error. ``` `normal.xlsx`: ```xml <row r="1" spans="1:3"><c r="A1"><v>10</v></c><c r="C1"><f t="array" ref="C1:C3">A1:A3*2</f><v>0</v></c></row> <row r="2" spans="1:3"><c r="A2"><v>20</v></c><c r="C2"><v>0</v></c></row> <row r="3" spans="1:3"><c r="A3"><v>30</v></c><c r="C3"><v>0</v></c></row> ``` `constant.xlsx`: ```xml <row r="1"><c r="A1"><v>10</v></c><c r="C1"><f t="array" ref="C1:C3">A1:A3*2</f><v>0</v></c></row> <row r="2"><c r="A2"><v>20</v></c></row> <row r="3"><c r="A3"><v>30</v></c></row> ``` ## Why this may be awkward to fix, and a smaller ask I appreciate the padding is not simply an oversight: in streaming mode the rows below the current one have not been reached, so they cannot be written at the time of the call without buffering or deferring. If padding in optimize mode is out of scope, the smaller fix would be to stop returning success. libxlsxwriter already has the precedent — `worksheet_add_table()` refuses outright in this mode: ```c if (self->optimize) { LXW_WARN_FORMAT("worksheet_add_table(): " "worksheet tables aren't supported in " "'constant_memory' mode"); return LXW_ERROR_FEATURE_NOT_SUPPORTED; } ``` A multi-cell array formula (`last_row > first_row || last_col > first_col`) degrading silently rather than warning is the part that made this hard to find. Single-cell array formulas are unaffected and could keep working. Found while adding array-formula support to writexl (R), which now turns `constant_memory` off whenever a multi-cell array range is present. Version 1.2.4.

gitea-mirror closed this issue

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

gitea-mirror commented

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

Author

Owner

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

I think this one is just going to have to be a known (or unknown) limitation.

Again, the Rust version of the library handles this correctly in constant memory mode since it does a form of caching for array formula and table data that are outside the current row and then adds them as the row is being written. This is technically possible in the C version but fiddly and unlikely to be implemented.

I think your workaround of not using the constant_memory when multi-cell array range is present is probably best. In fact you should probably consider not using it at all since you are going to run into other potential issues like this.

Closing as won't fix.

@jmcnamara commented on GitHub (Jul 29, 2026): I think this one is just going to have to be a known (or unknown) limitation. Again, the Rust version of the library handles this correctly in constant memory mode since it does a form of caching for array formula and table data that are outside the current row and then adds them as the row is being written. This is technically possible in the C version but fiddly and unlikely to be implemented. I think your workaround of not using the `constant_memory` when multi-cell array range is present is probably best. In fact you should probably consider not using it at all since you are going to run into other potential issues like this. Closing as won't fix.