handsontable / handsontable/hyperformula
[Bug]: Wrong table after recalculation when referencing a non-existent sheet
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 2.8k
- Forks
- 171
- Avg merge
- 1d 22h
- Merged PRs (30d)
- 7
Description
Description
When a formula references a sheet that does not exist, after rebuildAndRecalculate() if you read from the table the dimensions and the positions of the data are wrong.
The error starts in version 3.1.1, version 3.1.0 seems to be fine
I am providing a repo ziped that shows the error but just in case you cannot open it here is the code:
Here is some code that you can run to reproduce this:
`import { HyperFormula, type RawCellContent } from "hyperformula";
import data from "./data.json";
const RESULT_CELL_WIDTH = 10;
const hf = HyperFormula.buildEmpty({
licenseKey: "gpl-v3",
functionArgSeparator: ';',
decimalSeparator: ",",
});
const sheetIds = new Map<string, number>();
type Result = { file_rows: number, file_cols: number, raw_rows: number, raw_cols: number, calc_rows: number, calc_cols: number}
const results = new Map<string, Result>();
function printResultCell(og: number, n: number): string{
let resultCell = ${og} -> ${n}
if(og === n) return resultCell;
const diff = n - og;
const signed = diff >= 0 ? +${diff} : ${diff};
const padded = signed.padStart(4);
return ${resultCell.padEnd(RESULT_CELL_WIDTH)} (${padded})
}
function runTest() {
// ADD THE SHEETS
for(const sheet of data) {
const sheetPath = sheet?.sheetPath ?? '';
const sheetData = sheet?.data ?? [];
const sheetName = hf.addSheet(sheetPath);
const sheetId = hf.getSheetId(sheetName)!;
sheetIds.set(sheetPath, sheetId);
hf.setSheetContent(sheetIds.get(sheetPath)!, sheetData);
const sheetValues = hf.getSheetValues(sheetIds.get(sheetPath)!);
const sheetRawValues = hf.getSheetSerialized(sheetIds.get(sheetPath)!);
results.set(`${sheetPath}_OG`, {
file_rows: sheetData.length,
file_cols: sheetData[0]?.length ?? 0,
raw_rows: sheetRawValues.length,
raw_cols: sheetRawValues[0]?.length ?? 0,
calc_rows: sheetValues.length,
calc_cols: sheetValues[0]?.length ?? 0
})
console.log("Added: ", sheetPath);
}
// RECALCULATE
hf.rebuildAndRecalculate();
// READ THE SHEETS
for(const sheet of data) {
const sheetPath = sheet?.sheetPath ?? '';
const sheetData = sheet?.data ?? [];
const sheetValues = hf.getSheetValues(sheetIds.get(sheetPath)!);
const sheetRawValues = hf.getSheetSerialized(sheetIds.get(sheetPath)!);
results.set(`${sheetPath}_NEW`, {
file_rows: sheetData.length,
file_cols: sheetData[0]?.length ?? 0,
raw_rows: sheetRawValues.length,
raw_cols: sheetRawValues[0]?.length ?? 0,
calc_rows: sheetValues.length,
calc_cols: sheetValues[0]?.length ?? 0
})
}
// PRINT THE RESULTS
const resultsTable = [];
for(const sheet of data) {
const sheetPath = sheet?.sheetPath ?? '';
const rog = results.get(`${sheetPath}_OG`) as Result;
const rnew = results.get(`${sheetPath}_NEW`) as Result;
resultsTable.push({
Sheet: sheetPath,
'File Rows': printResultCell(rog.file_rows, rnew.file_rows),
'Raw Rows': printResultCell(rog.raw_rows, rnew.raw_rows),
//'Calc Rows': printResultCell(rog.calc_rows, rnew.calc_rows),
'': ``,
'File Cols': printResultCell(rog.file_cols, rnew.file_cols),
'Raw Cols': printResultCell(rog.raw_cols, rnew.raw_cols),
//'Calc Cols': printResultCell(rog.calc_cols, rnew.calc_cols),
'Equal': (
rog.file_rows === rnew.file_rows &&
rog.raw_rows === rnew.raw_rows &&
rog.calc_rows === rnew.calc_rows &&
rog.file_cols === rnew.file_cols &&
rog.raw_cols === rnew.raw_cols &&
rog.calc_cols === rnew.calc_cols
)
})
}
console.table(resultsTable);
}
runTest();
`
And the data:
`[
{
"sheetPath": "P2212_DS1_WA1",
"data": [
["Requirements", null, null, null, "", "1524378"],
[
"Input that MUST be defined by the planner",
null,
null,
null,
"",
"1524379"
],
[
"Articles",
"=(P2212_DS1_PM!B94+P2212_DS1_PM!B95)*0,33+((P2212_DS1_PM!B94+P2212_DS1_PM!B95)*0,67)/3",
"something",
"from "Parameters", can be overwritten",
"from "Parameters", can be overwritten",
"1524380"
]
]
},
{
"sheetPath": "P2212_DS1_WA2",
"data": [
["Width", "=B77", "m", null, "", "1524801"],
["Length", "=B59", "m", null, "", "1524802"],
["Hosh (w/o roof)", "=B35", "m", null, "", "1524803"],
["Something", "=ROUND(B128/(10^6);2)", "M€", null, "", "1524804"],
["Time test (w/o WS)", "=B59*B77", "m²", null, "", "1524805"],
["Storage", "=B142/B137", "m² /Pallet", null, "", "1524806"]
]
}
]
`
Video or screenshots
Demo
The repo is above
HyperFormula version
3.1.1 (has the bug) and 3.1.0 (bug free)
Your framework
none
Your environment
It's just a script running on Bun
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the attached reproduction script and compare HyperFormula 3.1.0 with 3.1.1, focusing on rebuildAndRecalculate(), getSheetValues(), and getSheetSerialized(). Confirm that formulas referencing a missing sheet reproduce the dimension and position changes, then add a regression test showing that recalculation preserves the expected sheet dimensions and data positions.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- bun, typescript
- Domain
- data
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100