handsontable / handsontable/hyperformula

[Bug]: Wrong table after recalculation when referencing a non-existent sheet

Open
#1,615 1 comment 0 reactions 0 assignees View on GitHub

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"]
  ]
}

]
`

hyperformula_sheets_bug.zip

Video or screenshots
Image
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.