Repository navigation
fix(xls): a formula kept as its saved value loses a text result (394 cells in one POI file) #802
Description
Summary
Some formulas can't be represented by the .xls reader: references to other workbooks, a reference to a missing name, What-If data tables. For these it keeps the saved result as a value and warns "Some formulas use …; their saved results were kept as values." That works for numbers, booleans and errors. A text result is lost: the cell comes out empty unless it has a cell format of its own.
Observed
Excel 365 shows the text in every case below; GridCraft (main fc45b7f, also with #464 merged) shows the cell empty.
| file | why it's kept as a value | text results shown empty | example |
|---|---|---|---|
POI 19599-1.xls |
links to another workbook | 394 of 394 | 'adjusted syteline'!F3 =VLOOKUP(B3,'C:\…\[0231324V1-1.xls]Sheet1'!$D$1:$I$65536,1,FALSE) → 023-1324-1 |
POI testArraysAndTables.xls |
What-If data tables | 33 of 43 (every text cell of the three data tables) | Sheet1!C27 {=TABLE(B26,)} → 103a |
POI 52575_main.xls |
link to another workbook | 1 of 1 | Tabelle1!A2 → POI rocks! |
POI 56737.xls |
reference to a missing name | 1 of 4 | Uses!D2 =Defines!NR_To_A1 → Test A1 |
Damodaran wacccalc.xls |
link to another workbook | 1 of 12 | 'Global industry averages'!A97 → Total Market |
How I counted: for each FORMULA record with a string result, I read the text from the STRING record that follows and compared it with what GridCraft shows (gridcraft-cli cat --csv). I did this for every .xls in Apache POI's test data and in Damodaran's spreadsheets (426 files; two that GridCraft can't open were skipped). The table lists every file where text was lost. Cells of array and shared formulas are fine because they are recalculated.
Root cause
formula_cell(crates/xlsx/src/xls/mod.rs:736) stores the cell with an empty value until the STRING record arrives.Cells::set(crates/model/src/store.rs:50) drops a cell that is blank (empty value, no formula, default style).- A formula that is attached gets its cell back in
attach. A formula kept as a value doesn't, so the STRING handler (mod.rs:600,cells.get_mut(pos)) finds no cell and drops the text. - A cell with a non-default XF isn't blank, which is why some text results in the same files survive.
Suggested fix
Have the STRING handler create the cell when it is missing: keep the cell's style with pending_string and set the text. Test with a BIFF8 sheet holding a FORMULA record whose formula is kept as a value (for example an external reference), with a string result and the default XF, followed by its STRING record.
Activity
XLS: a formula kept as its saved value keeps a text result (fixes sto…