[Bug]: Array formula spill cells serialize as literal values, so rebuildAndRecalculate() produces #SPILL!

オープン 初心者向け
  • 何か問題がありますか？
#1,745 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
2/5
見積もり時間
1〜3時間
初心者へのやさしさ
84/100
issue の種類
バグ
明瞭さ
明確に書かれている
活発さ
静か
技術スタック
typescript
領域
backend

調査の方向性

src/Serialization.ts、特に getCellSerialized() と getCellFormula() から始め、次に issue にある自己完結型の Node 再現コードを実行します。アンカーではない配列スピルセルが計算済みの値なしでシリアライズされること、また getSheetSerialized() と rebuildAndRecalculate() が #SPILL! を生成せずに配列数式の結果を保持することを確認します。

索引モデルが issue の本文から書いたものです。

説明

Bug
Description

A cell holding an array formula such as ={1,2} calculates correctly and spills across A1 and B1. Calling rebuildAndRecalculate() turns it into #SPILL!.

The cause is in serialization, not in the rebuild itself.

rebuildAndRecalculate() does not rebuild from the stored formulas. It serializes every sheet, then builds a new engine from that serialized content:

// src/HyperFormula.ts — rebuildWithConfig()
const serializedSheets = this._serialization.withNewConfig(configNewLanguage, this._namedExpressions).getAllSheetsSerialized()

The serializer then writes out the computed spill value for B1 as if it were cell content:

// src/Serialization.ts:64
public getCellSerialized(address: SimpleCellAddress, targetAddress?: SimpleCellAddress): RawCellContent {
  return this.getCellFormula(address, targetAddress) ?? this.getRawValue(address)
}

getCellFormula() deliberately returns undefined for a cell that belongs to an array but is not that array's anchor cell (src/Serialization.ts:48-52). The ?? then falls through to getRawValue(), which returns the spilled value.

For B1 in the example above:

call result
isCellPartOfArray(B1) true
getCellFormula(B1) undefined
getCellSerialized(B1) 2

So the sheet serializes to [["={1,2}", 2]]. B1 now holds a hard-coded 2. The new engine builds from that, ={1,2} has no room to spill into B1, and A1 becomes #SPILL!.

The serialize-then-rebuild round trip is not idempotent for array formulas. The spilled value is baked in as real content, and then blocks its own formula.

Steps to reproduce
import { HyperFormula } from 'hyperformula';

const hf = HyperFormula.buildFromArray([['={1,2}', null]], { licenseKey: 'gpl-v3' });

console.log(hf.getSheetValues(0));      // [[1, 2]]                        correct
console.log(hf.getSheetSerialized(0));  // [["={1,2}", 2]]                 already wrong

hf.rebuildAndRecalculate();

console.log(hf.getSheetValues(0));      // [[#SPILL!, 2]]                  bug
Expected behavior

rebuildAndRecalculate() preserves the array formula result: [[1, 2]].

A cell that is part of an array but is not the array's anchor has no content of its own, so it should serialize to nothing.

Notes

This is not limited to rebuildAndRecalculate(). getSheetSerialized() returns [["={1,2}", 2]] before any rebuild happens. Any save-and-restore flow that round-trips through serialization corrupts array formulas the same way. rebuildAndRecalculate() is just the most visible trigger, because it performs that round trip internally.

Vertical spills fail identically. ={1;2} in A1 serializes as [["={1;2}"], [2]] and yields #SPILL! after a rebuild.

Ordinary formulas are unaffected. =A1+B1 survives a rebuild correctly, so the problem is specific to array formulas.

Reported downstream. This surfaced as handsontable/handsontable#11141. The Handsontable Formulas plugin also calls rebuildAndRecalculate() on its own, on the sheetAdded and sheetRemoved events, so Handsontable users can hit this without ever calling the API themselves — attaching a second grid to a shared engine breaks every array formula in the first grid.

Possibly related: #1287, which covers #SPILL! not clearing when the blocking area is freed. Same area, different defect.

Suggested fix

In getCellSerialized(), return undefined when the address belongs to an array vertex but is not that array's anchor cell, instead of falling through to getRawValue(). That makes the round trip idempotent for array formulas and fixes the save/restore case at the same time.

Demo

Reproduces in Node with no sandbox needed — the snippet under "Steps to reproduce" is complete and self-contained.

The original downstream report has a browser demo: https://stackblitz.com/edit/6sjfmr-eszi6u?file=index.js

HyperFormula version

Every version tested reproduces it: 2.4.0, 2.7.1, 3.0.0, 3.3.0, and 3.4.0 (latest).

Your framework

None — plain HyperFormula.

Your environment

Node.js 24 on macOS. Also reported in Chrome by the downstream reporter.

主要言語
TypeScript
スター
2.8k
フォーク
175
平均マージ
1日 20時間
マージ済み PR（30日）
17

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

handsontable/hyperformula のほかの issue

handsontable/hyperformula の issue をすべて見る

似ている issue

TypeScript の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。