https://bugs.documentfoundation.org/show_bug.cgi?id=173198
Bug ID: 173198
Summary: XLSX import drops bSubTotal flag for formula-grouped
SUBTOTAL cells, so live AutoFilter changes don't
update them (ODS unaffected)
Product: LibreOffice
Version: 26.2.5.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
When an XLSX file is opened where a column contains one SUBTOTAL() formula per
row (Excel-style per-cell <f>, not a shared formula group), and a live
AutoFilter hide/show change is made to a driving column, only the first cell of
each contiguous run of identically-shaped SUBTOTAL formulas updates. Every
subsequent cell in that run stays stale until a hard recalculation
(Ctrl+Shift+F9 / calculateAll). The same workbook re-saved as ODS and reopened
does not have this problem — every cell updates live.
Root cause (verified against source, not just symptom):
sc/source/filter/oox/formulabuffer.cxx, applyCellFormulas() (lines 336-358):
when an imported cell's formula matches the previous row's cached token array
at an adjacent row, the importer folds it into a formula group via
ScFormulaCell::CreateCellGroup() instead of constructing a fresh formula cell.
ScFormulaCell::CreateCellGroup (formulacell.cxx:4464-4482) sets mpTopCell,
mbInvariant, mnLength, mpCode — but never mbSubTotal, which the group struct's
constructor initialises to false (formulacell.cxx:552). The only writes to
mbSubTotal anywhere in the tree are inside ScFormulaCellGroup::compileCode
(:579, :584), which this import path never calls.
The group-taking ScFormulaCell constructor then does
bSubTotal(xGroup->mbSubTotal) (:799) — so it inherits false — and as a result
skips rDocument.AddSubTotalCell(this) (:821-822). The cell is therefore never
registered in ScDocument::maSubTotalCells, and never receives the
ScHiddenRowsChanged notification that a live AutoFilter change broadcasts (this
notification mechanism was added by bug 93171 / commits 970b431f, 47cbf098,
c8433218 — the oox import path was apparently never updated for it).
The ODF/native import path performs the same adjacent-cell grouping but copies
the flag explicitly, in ScFormulaCell::CompileTokenArray
(formulacell.cxx:1364-1379):
bSubTotal = pPreviousCell->bSubTotal;
...
if (bSubTotal) rDocument.AddSubTotalCell(this);
That pair of lines is the entire difference between the working ODS behaviour
and the broken XLSX behaviour.
Real Excel-authored <f t="shared"> output goes through a different,
token-array-based constructor (formulabuffer.cxx:243-249) that does compute
bSubTotal correctly — so genuine Excel-saved files are largely immune. The bug
surfaces specifically for XLSX writers (e.g. Python's xlsxwriter, and
presumably others) that emit one non-shared <f> per cell rather than a
shared-formula group; those walk directly into the broken adjacent-grouping
branch.
Steps to Reproduce:
Programmatically build an XLSX with a helper column of SUBTOTAL(103, A2)-style
formulas, one per data row, referencing an AutoFilter-enabled range (use a
writer that emits per-cell <f>, not shared formulas — e.g. xlsxwriter).
Open the file in Calc, apply an AutoFilter to hide one of the rows (not the
first row covered by the formula column).
Observe: the SUBTOTAL cell for the first row of the formula run updates
immediately; SUBTOTAL cells for subsequent rows in the same contiguous run do
not, until a hard recalculation is forced.
Re-save the same file as ODS, reopen, repeat step 2: all cells update
immediately, no staleness.
Prediction test performed (confirms mechanism): Three fresh copies of the same
document, each hiding a different single host row:
hide row 0 (top of formula group): before=[1,1,1] after=[0,1,1] -- correct,
updates
hide row 1 (2nd in group): before=[1,1,1] after=[1,1,1] -- stale
hide row 2 (3rd in group): before=[1,1,1] after=[1,1,1] -- stale
This matches the mechanism exactly: only the group's top cell (constructed
before grouping occurs) is ever registered in maSubTotalCells.
This also plausibly explains the "patchy" update fingerprint reported in bug
135125 comment 0 ("C3 and C11 updated, while C5, C7, C9 didn't") — though that
bug is ODS/native and a different formula (LEN(), no SUBTOTAL), so it is not
the same bug and should not be treated as a duplicate.
Suggested fix:
Minimal, covers every present and future call site — seed the flag when the
group is created:
// formulacell.cxx, ScFormulaCell::CreateCellGroup
mxGroup->mbSubTotal = bSubTotal;
or, narrowly scoped to the oox import call site:
// formulabuffer.cxx:351
xGroup->mbSubTotal = rPrev.IsSubTotal();
Prior-art search performed (this appears unreported):
Full-text Bugzilla search for maSubTotalCells, AddSubTotalCell,
SetSubTotalCellsDirty, ScHiddenRowsChanged: zero hits, and no commit touches
those symbols in the oox import path.
Skim of all 68 bugs with "subtotal" in the summary (14 currently open: 55870,
61634, 88793, 88794, 109330, 118678, 140322, 163617, 163619, 166315, 170784,
171180, 172370, 173140) — none describe this symptom. Closest by keyword are
#171180 (Data>Subtotals outline row hidden by AutoFilter — a different feature,
the row-grouping "Subtotals" tool, not the SUBTOTAL() function/live-update
mechanism) and #173140 (#REF!/Err:509 corruption on xlsx save→reload — a
save-path corruption bug, unrelated to live filter-driven update).
Checked known related-but-distinct bugs: 93171 (RESOLVED FIXED — the fix that
created the ScHiddenRowsChanged mechanism this bug shows the oox path never
adopted), 135125 (NEW, different entry point — ODS/native, non-SUBTOTAL, does
not survive save/reload — not a duplicate), 99913/126505/115022 (VERIFIED FIXED
in 7.2 — a disjoint xlsx row-attribute desync, not formula-cell construction).
See Also: 93171, 135125 (related mechanism, not a duplicate)
--
You are receiving this mail because:
You are the assignee for the bug.
