Array formulas in the #SPILL! state don't re-spill when space becomes available

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

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

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

評価

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

調査の方向性

Start with FormulaVertex.ts and Evaluator.recomputeFormulaVertexValue, especially how setNoSpace() and vertex.array.size.isRef affect a later space check. Reproduce the reported cases with structural changes, clearing a blocking cell, and dependency changes; inspect the paired tests for #1774 and add coverage for recovery without re-entering the formula. Done means a previously blocked array spills when a change makes room, while remaining #SPILL! when it still cannot fit.

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

説明

An array formula that returns #SPILL! keeps returning it after the cells around it change so that the result would fit. It only recovers when the formula is re-entered, updateConfig is called, or rebuildAndRecalculate() runs.

const hf = HyperFormula.buildFromArray([[null], ['=SEQUENCE(4)']], {licenseKey: 'gpl-v3', maxRows: 4})
// A2: #SPILL! (A2:A5 would pass maxRows)

hf.removeRows(0, [0, 1])
// the formula is now in A1, where A1:A4 fits, but A1 is still #SPILL!

hf.setCellContents({sheet: 0, col: 0, row: 0}, '=SEQUENCE(4)')
// A1:A4 = 1, 2, 3, 4

The same happens when the spill was blocked by a value and the blocking cell is cleared:

const hf = HyperFormula.buildFromArray([['=SEQUENCE(3)'], ['x']], {licenseKey: 'gpl-v3'})
// A1: #SPILL!

hf.setCellContents({sheet: 0, col: 0, row: 1}, null)
// A1 is still #SPILL!

It also stays #SPILL! after:

  • redo of the removeRows above
  • removeColumns (maxColumns: 4, =SEQUENCE(1,4) in B1, then remove column A)
  • moveRows of another row out from above the formula
  • a dependency change (=ARRAYFORMULA(SEQUENCE(4)*B2) in A2, then remove row 1 and change B1)

Undoing the write that blocked the spill does recover.

Cause: when isThereSpaceForArray returns false, setNoSpace() (FormulaVertex.ts) replaces the vertex's array with an ErroredArray of size ArraySize.error() (1×1, isRef), so the vertex no longer knows its real size. Evaluator.recomputeFormulaVertexValue then returns setNoSpace() whenever vertex.array.size.isRef is set, without calling isThereSpaceForArray, so the space check never runs again.

Expected: after a structural change, a cell write, or a dependency change, an array formula in the #SPILL! state should check for space again and spill if the result now fits. Excel Online does this in all three cases:

Setup Change Excel Online
=SEQUENCE(3) in A1048575 → #SPILL! (sheet edge) delete rows above spills 1, 2, 3
=SEQUENCE(1,3) in XFC1 → #SPILL! (sheet edge) delete column B moves to XFB1, spills 1, 2, 3 across XFB1:XFD1
=SEQUENCE(3) in D10, x in D11 → #SPILL! clear D11 spills 1, 2, 3 in D10:D12

Scope: the value-blocked case is pre-existing on develop. #1774 adds a second way into this state: a result that would extend past maxRows/maxColumns returns #SPILL!, so any array near the bottom or right edge of the sheet can get stuck. The paired tests for #1774 (hyperformula-tests#57) cover recovery only by editing the formula and by updateConfig. Their undo and dependency-change tests check that the error stays when the array still doesn't fit. None covers a change that makes room without re-entering the formula.

All cases reproduced on fix/HF-135 at ea7542586. Found by @marcin-kordas-hoc during the review of #1774.

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

環境構築

はじめの一歩

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

handsontable/hyperformula のほかの issue

handsontable/hyperformula の issue をすべて見る

似ている issue

TypeScript の issue をもっと見る

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

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