online.git: engine/formula engine/include engine/sc

["Tomaž Vajngerl (via cogerrit)"]

engine/formula/inc/core_resource.hrc | 7
engine/formula/source/core/api/FormulaCompiler.cxx | 6
engine/include/formula/compiler.hxx | 3
engine/include/formula/errorcodes.hxx | 3
engine/include/formula/opcode.hxx | 2
engine/sc/inc/document.hxx | 3
engine/sc/inc/globstr.hrc | 1
engine/sc/qa/unit/ucalc_formula2.cxx | 300 +++++++++++++
engine/sc/source/core/data/documen4.cxx | 35 +
engine/sc/source/core/data/global.cxx | 6
engine/sc/source/filter/excel/xestream.cxx | 1
engine/sc/source/filter/excel/xltools.cxx | 2
engine/sc/source/filter/inc/xlconst.hxx | 1
engine/sc/source/ui/docshell/docfunc.cxx | 6
engine/sc/source/ui/inc/docfunc.hxx | 3
engine/sc/source/ui/inc/operation/EnterMatrixOperation.hxx | 3
engine/sc/source/ui/operation/EnterMatrixOperation.cxx | 10
engine/sc/source/ui/view/viewfunc.cxx | 7
18 files changed, 386 insertions(+), 13 deletions(-)

New commits:
commit a564bb54cb40af3ccf1c8e6c9ba4c56ae8b56da4
Author: Tomaž Vajngerl <tomaz.v...@collabora.co.uk>
AuthorDate: Tue Apr 21 10:31:57 2026 +0900
Commit: Miklos Vajna <vmi...@collabora.com>
CommitDate: Tue Apr 28 06:29:41 2026 +0000

sc: Add #SPILL! error if a Calc array formula spills (is blocked)

If there is content in the way where an array formula would write
it's result, don't just overwrite the content, but produce a spill
error instead (#SPILL!) in the first cell of the array.

Change-Id: Ic2c31d638e2d4e51078a466e7f7b6f04ca6de417
Signed-off-by: Tomaž Vajngerl <tomaz.v...@collabora.co.uk>
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/1727
Tested-by: Jenkins CPCI <rel...@collaboraoffice.com>
Reviewed-by: Miklos Vajna <vmi...@collabora.com>

diff --git a/engine/formula/inc/core_resource.hrc b/engine/formula/inc/core_resource.hrc
index 25478cfd38b0..734791329720 100644
--- a/engine/formula/inc/core_resource.hrc
+++ b/engine/formula/inc/core_resource.hrc
@@ -479,6 +479,7 @@ const std::pair<const char *, int> RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF[] =
{ "#NAME?" , SC_OPCODE_ERROR_NAME },
{ "#NUM!" , SC_OPCODE_ERROR_NUM },
{ "#N/A" , SC_OPCODE_ERROR_NA },
+ { "#SPILL!" , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */
{ "COM.MICROSOFT.FILTERXML" , SC_OPCODE_FILTERXML },
{ "COM.MICROSOFT.WEBSERVICE" , SC_OPCODE_WEBSERVICE },
@@ -949,6 +950,7 @@ const std::pair<const char *, int> RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML[] =
{ "#NAME?" , SC_OPCODE_ERROR_NAME },
{ "#NUM!" , SC_OPCODE_ERROR_NUM },
{ "#N/A" , SC_OPCODE_ERROR_NA },
+ { "#SPILL!" , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */
{ "_xlfn.FILTERXML" , SC_OPCODE_FILTERXML },
{ "_xlfn.WEBSERVICE" , SC_OPCODE_WEBSERVICE },
@@ -1425,6 +1427,7 @@ const std::pair<const char *, int> RID_STRLIST_FUNCTION_NAMES_ENGLISH_PODF[] =
{ "#NAME?" , SC_OPCODE_ERROR_NAME },
{ "#NUM!" , SC_OPCODE_ERROR_NUM },
{ "#N/A" , SC_OPCODE_ERROR_NA },
+ { "#SPILL!" , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */
{ "FILTERXML" , SC_OPCODE_FILTERXML },
{ "WEBSERVICE" , SC_OPCODE_WEBSERVICE },
@@ -1898,6 +1901,7 @@ const std::pair<const char *, int> RID_STRLIST_FUNCTION_NAMES_ENGLISH_API[] =
{ "#NAME?" , SC_OPCODE_ERROR_NAME },
{ "#NUM!" , SC_OPCODE_ERROR_NUM },
{ "#N/A" , SC_OPCODE_ERROR_NA },
+ { "#SPILL!" , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */
{ "FILTERXML" , SC_OPCODE_FILTERXML },
{ "WEBSERVICE" , SC_OPCODE_WEBSERVICE },
@@ -2370,6 +2374,7 @@ const std::pair<const char *, int> RID_STRLIST_FUNCTION_NAMES_ENGLISH[] =
{ "#NAME?" , SC_OPCODE_ERROR_NAME },
{ "#NUM!" , SC_OPCODE_ERROR_NUM },
{ "#N/A" , SC_OPCODE_ERROR_NA },
+ { "#SPILL!" , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */
{ "FILTERXML" , SC_OPCODE_FILTERXML },
{ "WEBSERVICE" , SC_OPCODE_WEBSERVICE },
@@ -2836,6 +2841,8 @@ const std::pair<TranslateId, int> RID_STRLIST_FUNCTION_NAMES[] =
/* ERROR.TYPE( #N/A ) == 7 */
/* L10n: preserve the leading '#' hash character in translations. */
{ NC_("RID_STRLIST_FUNCTION_NAMES", "#N/A") , SC_OPCODE_ERROR_NA },
+ /* L10n: preserve the leading '#' hash character and trailing '!' in translations. */
+ { NC_("RID_STRLIST_FUNCTION_NAMES", "#SPILL!") , SC_OPCODE_ERROR_SPILL },
/* END defined ERROR.TYPE() values. */

{ NC_("RID_STRLIST_FUNCTION_NAMES", "FILTERXML") , SC_OPCODE_FILTERXML },
diff --git a/engine/formula/source/core/api/FormulaCompiler.cxx b/engine/formula/source/core/api/FormulaCompiler.cxx
index c9f8192d65d3..55071824b0e3 100644
--- a/engine/formula/source/core/api/FormulaCompiler.cxx
+++ b/engine/formula/source/core/api/FormulaCompiler.cxx
@@ -1427,6 +1427,9 @@ FormulaError FormulaCompiler::GetErrorConstant( const OUString& rName ) const
case ocErrNA:
nError = FormulaError::NotAvailable;
break;
+ case ocErrSpill:
+ nError = FormulaError::Spill;
+ break;
default:
; // nothing
}
@@ -1482,6 +1485,9 @@ void FormulaCompiler::AppendErrorConstant( OUStringBuffer& rBuffer, FormulaError
case FormulaError::NotAvailable:
eOp = ocErrNA;
break;
+ case FormulaError::Spill:
+ eOp = ocErrSpill;
+ break;
default:
{
// Per convention create detailed "#ERRxxx!" constants, always
diff --git a/engine/include/formula/compiler.hxx b/engine/include/formula/compiler.hxx
index d477c3c7e39f..f2385d51f7ef 100644
--- a/engine/include/formula/compiler.hxx
+++ b/engine/include/formula/compiler.hxx
@@ -73,7 +73,8 @@
#define SC_OPCODE_ERROR_NAME 45
#define SC_OPCODE_ERROR_NUM 46
#define SC_OPCODE_ERROR_NA 47
-#define SC_OPCODE_STOP_ERRORS 48
+#define SC_OPCODE_ERROR_SPILL 48
+#define SC_OPCODE_STOP_ERRORS 49

/*** Binary operators ***/
#define SC_OPCODE_START_BIN_OP 50
diff --git a/engine/include/formula/errorcodes.hxx b/engine/include/formula/errorcodes.hxx
index e2280ed12089..8d820fe1544b 100644
--- a/engine/include/formula/errorcodes.hxx
+++ b/engine/include/formula/errorcodes.hxx
@@ -87,6 +87,8 @@ enum class FormulaError : sal_uInt16
// Interpreter: signal result not available because updating links is not
// allowed (yet) and tell to try hybrid string as result.
LinkFormulaNeedingCheck = 540,
+// Array/matrix formula result can't spill because target cells are not empty.
+ Spill = 541,

// Interpreter: NA() not available condition, not a real error
NotAvailable = 0x7fff
@@ -176,6 +178,7 @@ inline bool isPublishedFormulaError( FormulaError nErr )

case FormulaError::MatrixSize:
case FormulaError::LinkFormulaNeedingCheck:
+ case FormulaError::Spill:
return true;

case FormulaError::NotAvailable:
diff --git a/engine/include/formula/opcode.hxx b/engine/include/formula/opcode.hxx
index 709db3cb3355..689e1214cd10 100644
--- a/engine/include/formula/opcode.hxx
+++ b/engine/include/formula/opcode.hxx
@@ -79,6 +79,7 @@ enum OpCode : sal_uInt16
ocErrName = SC_OPCODE_ERROR_NAME,
ocErrNum = SC_OPCODE_ERROR_NUM,
ocErrNA = SC_OPCODE_ERROR_NA,
+ ocErrSpill = SC_OPCODE_ERROR_SPILL,
// Binary operators
ocAdd = SC_OPCODE_ADD,
ocSub = SC_OPCODE_SUB,
@@ -593,6 +594,7 @@ inline std::string OpCodeEnumToString(OpCode eCode)
case ocErrName: return "ErrName";
case ocErrNum: return "ErrNum";
case ocErrNA: return "ErrNA";
+ case ocErrSpill: return "ErrSpill";
case ocAdd: return "Add";
case ocSub: return "Sub";
case ocMul: return "Mul";
diff --git a/engine/sc/inc/document.hxx b/engine/sc/inc/document.hxx
index 552c5e86bad8..3a574bd672ce 100644
--- a/engine/sc/inc/document.hxx
+++ b/engine/sc/inc/document.hxx
@@ -1275,7 +1275,8 @@ public:
const ScMarkData& rMark,
const OUString& rFormula,
const ScTokenArray* p = nullptr,
- const formula::FormulaGrammar::Grammar = formula::FormulaGrammar::GRAM_DEFAULT );
+ const formula::FormulaGrammar::Grammar = formula::FormulaGrammar::GRAM_DEFAULT,
+ bool bCheckForSpill = false);
SC_DLLPUBLIC void InsertTableOp(const ScTabOpParam& rParam, // multi-operation
SCCOL nCol1, SCROW nRow1,
SCCOL nCol2, SCROW nRow2, const ScMarkData& rMark);
diff --git a/engine/sc/inc/globstr.hrc b/engine/sc/inc/globstr.hrc
index 9303383ca4f2..44e106794e24 100644
--- a/engine/sc/inc/globstr.hrc
+++ b/engine/sc/inc/globstr.hrc
@@ -566,6 +566,7 @@
#define STR_QUERY_PIVOTTABLE_DELTAB NNC_("STR_QUERY_PIVOTTABLE_DELTAB", "The selected sheet contains source data of related pivot tables that will be lost.", "The %d selected sheets contain source data of related pivot tables that will be lost.")
#define STR_ERR_NAME_INVALID_CELL_REF NC_("STR_ERR_NAME_INVALID_CELL_REF", "Invalid name. Reference to a cell, or a range of cells not allowed.")
#define STR_ERR_LONG_LINK_FORMULA_NEEDING_CHECK NC_("STR_ERR_LONG_LINK_FORMULA_NEEDING_CHECK", "External content disabled.")
+#define STR_LONG_ERR_SPILL NC_("STR_LONG_ERR_SPILL", "Error: Array result could not expand because it would overwrite non-empty cells.")
#define STR_TEXTORIENTANGLE NC_("STR_TEXTORIENTANGLE", "Text orientation angle")
#define STR_SHRINKTOFITCELL_ON NC_("STR_SHRINKTOFITCELL_ON", "Shrink to fit cell: On")
#define STR_SHRINKTOFITCELL_OFF NC_("STR_SHRINKTOFITCELL_OFF", "Shrink to fit cell: Off")
diff --git a/engine/sc/qa/unit/ucalc_formula2.cxx b/engine/sc/qa/unit/ucalc_formula2.cxx
index 7981a72f9d2c..f0710a2819ff 100644
--- a/engine/sc/qa/unit/ucalc_formula2.cxx
+++ b/engine/sc/qa/unit/ucalc_formula2.cxx
@@ -11,6 +11,7 @@
#include <clipparam.hxx>
#include <scopetools.hxx>
#include <formulacell.hxx>
+#include <global.hxx>
#include <docfunc.hxx>
#include <tokenstringcontext.hxx>
#include <dbdata.hxx>
@@ -4757,6 +4758,305 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testVertQueryEmptyCell)
m_pDoc->DeleteTab(0);
}

+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorBasic)
+{
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // Put data in A2 that will block the spill range of a matrix formula.
+ m_pDoc->SetValue(ScAddress(0, 1, 0), 99.0);
+
+ // Insert a matrix formula in A1:A3 with spill check enabled.
+ // A2 contains data, so this should produce a spill error on the origin cell A1.
+ m_pDoc->InsertMatrixFormula(0, 0, 0, 2, aMark, u"=LEN(B1:B3)"_ustr, nullptr,
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // The origin cell should have the spill error.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have a spill error.", sal_Int32(FormulaError::Spill),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // Cell should display a spill error.
+ CPPUNIT_ASSERT_EQUAL(u"#SPILL!"_ustr, m_pDoc->GetString(ScAddress(0, 0, 0)));
+
+ // The master cell retains its intended dimensions (for future spill resolution).
+ SCCOL nCols = -1;
+ SCROW nRows = -1;
+ pFormulaCell->GetMatColsRows(nCols, nRows);
+ CPPUNIT_ASSERT_EQUAL(SCCOL(1), nCols);
+ CPPUNIT_ASSERT_EQUAL(SCROW(3), nRows);
+
+ // The blocking cell A2 should still contain its original value.
+ CPPUNIT_ASSERT_EQUAL(99.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
+
+ // A3 should remain empty
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(ScAddress(0, 2, 0)));
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorNoBlockingData)
+{
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // The target range A1:A3 is completely empty - no spill error should occur.
+ m_pDoc->InsertMatrixFormula(0, 0, 0, 2, aMark, u"=LEN(B1:B3)"_ustr, nullptr,
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // No error - range was empty, so the matrix expanded normally.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have no error.", sal_Int32(FormulaError::NONE),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // Should be a 1x3 matrix.
+ SCCOL nCols = -1;
+ SCROW nRows = -1;
+ pFormulaCell->GetMatColsRows(nCols, nRows);
+ CPPUNIT_ASSERT_EQUAL(SCCOL(1), nCols);
+ CPPUNIT_ASSERT_EQUAL(SCROW(3), nRows);
+
+ // A2 and A3 should be matrix reference cells.
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_FORMULA, m_pDoc->GetCellType(ScAddress(0, 1, 0)));
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_FORMULA, m_pDoc->GetCellType(ScAddress(0, 2, 0)));
+
+ const ScFormulaCell* pRef = m_pDoc->GetFormulaCell(ScAddress(0, 1, 0));
+ CPPUNIT_ASSERT_MESSAGE("A2 should contain a formula cell.", pRef != nullptr);
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A2 should be a matrix reference cell.", ScMatrixMode::Reference,
+ pRef->GetMatrixFlag());
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorMultiColumn)
+{
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // Place blocking data at B2 (inside a 2x2 matrix range A1:B2).
+ m_pDoc->SetString(ScAddress(1, 1, 0), u"blocker"_ustr);
+
+ // Insert a 2x2 matrix formula in A1:B2 with spill check.
+ m_pDoc->InsertMatrixFormula(0, 0, 1, 1, aMark, u"=LEN(C1:D2)"_ustr, nullptr,
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // Should be a spill error because B2 has data.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have a spill error.", sal_Int32(FormulaError::Spill),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // The blocking cell B2 should still contain its original string.
+ CPPUNIT_ASSERT_EQUAL(u"blocker"_ustr, m_pDoc->GetString(ScAddress(1, 1, 0)));
+
+ // A2, B1 should remain empty - no reference cells created.
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(ScAddress(0, 1, 0)));
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(ScAddress(1, 0, 0)));
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorDisabledByDefault)
+{
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // Place data in A2 that would block a spill.
+ m_pDoc->SetValue(ScAddress(0, 1, 0), 42.0);
+
+ // Insert a matrix formula without spill check.
+ // This should succeed and overwrite A2.
+ m_pDoc->InsertMatrixFormula(0, 0, 0, 2, aMark, u"=LEN(B1:B3)"_ustr);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // No spill error - overwrites existing cells.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have no error.", sal_Int32(FormulaError::NONE),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // Should be a 1x3 matrix.
+ SCCOL nCols = -1;
+ SCROW nRows = -1;
+ pFormulaCell->GetMatColsRows(nCols, nRows);
+ CPPUNIT_ASSERT_EQUAL(SCCOL(1), nCols);
+ CPPUNIT_ASSERT_EQUAL(SCROW(3), nRows);
+
+ // A2 should now be a matrix reference cell - old value was overwritten.
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_FORMULA, m_pDoc->GetCellType(ScAddress(0, 1, 0)));
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorSingleCell)
+{
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // A single-cell matrix formula should never produce a spill error,
+ // because there's nothing to spill into.
+ m_pDoc->SetValue(ScAddress(0, 1, 0), 99.0); // A2 has data, but range is only A1.
+ m_pDoc->InsertMatrixFormula(0, 0, 0, 0, aMark, u"=SUM(B1:B3)"_ustr, nullptr,
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // No spill error for a 1x1 matrix.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("Single-cell matrix should not produce a spill error.",
+ sal_Int32(FormulaError::NONE),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorAutoExpand)
+{
+ // Test that auto-expanding array formulas produce a spill error
+ // when the target range has non-empty cells.
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ // Source data in B1:B3.
+ m_pDoc->SetValue(ScAddress(1, 0, 0), 10.0);
+ m_pDoc->SetValue(ScAddress(1, 1, 0), 20.0);
+ m_pDoc->SetValue(ScAddress(1, 2, 0), 30.0);
+
+ // Place a blocker in A2
+ m_pDoc->SetString(ScAddress(0, 1, 0), u"blocker"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ // Enter a formula via ScDocFunc::EnterMatrix simulating the auto-expand path
+ ScRange aRange(0, 0, 0, 0, 2, 0); // A1:A3
+ ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
+ rDocFunc.EnterMatrix(aRange, &aMark, nullptr, u"=B1:B3"_ustr, true, false, OUString(),
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // Should be a spill error - blocker in A2.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have a spill error.", sal_Int32(FormulaError::Spill),
+ sal_Int32(pFormulaCell->GetErrCode()));
+ CPPUNIT_ASSERT_EQUAL(u"#SPILL!"_ustr, m_pDoc->GetString(ScAddress(0, 0, 0)));
+
+ // Blocker should still be there.
+ CPPUNIT_ASSERT_EQUAL(u"blocker"_ustr, m_pDoc->GetString(ScAddress(0, 1, 0)));
+
+ // A3 should remain empty.
+ CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(ScAddress(0, 2, 0)));
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorAutoExpandEmpty)
+{
+ // Test that auto-expanding array formulas succeed when the range is empty.
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ // Source data in B1:B3.
+ m_pDoc->SetValue(ScAddress(1, 0, 0), 10.0);
+ m_pDoc->SetValue(ScAddress(1, 1, 0), 20.0);
+ m_pDoc->SetValue(ScAddress(1, 2, 0), 30.0);
+
+ // A1:A3 is empty - auto-expand should work.
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ ScRange aRange(0, 0, 0, 0, 2, 0); // A1:A3
+ ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
+ rDocFunc.EnterMatrix(aRange, &aMark, nullptr, u"=B1:B3"_ustr, true, false, OUString(),
+ formula::FormulaGrammar::GRAM_DEFAULT, true);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // No spill error - range was empty.
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("A1 should have no error.", sal_Int32(FormulaError::NONE),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // The formula should have spilled into A2 and A3.
+ CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
+ CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
+ CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(ScAddress(0, 2, 0)));
+
+ // A2 should be a matrix reference cell.
+ const ScFormulaCell* pRef = m_pDoc->GetFormulaCell(ScAddress(0, 1, 0));
+ CPPUNIT_ASSERT_MESSAGE("A2 should be a formula cell.", pRef != nullptr);
+ CPPUNIT_ASSERT_EQUAL(ScMatrixMode::Reference, pRef->GetMatrixFlag());
+
+ m_pDoc->DeleteTab(0);
+}
+
+CPPUNIT_TEST_FIXTURE(TestFormula2, testSpillErrorOverwrites)
+{
+ // Test that no check for spill still overwrites
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
+
+ m_pDoc->InsertTab(0, u"Sheet1"_ustr);
+
+ m_pDoc->SetValue(ScAddress(1, 0, 0), 10.0);
+ m_pDoc->SetValue(ScAddress(1, 1, 0), 20.0);
+ m_pDoc->SetValue(ScAddress(1, 2, 0), 30.0);
+
+ // Place a blocker.
+ m_pDoc->SetString(ScAddress(0, 1, 0), u"blocker"_ustr);
+
+ ScMarkData aMark(m_pDoc->GetSheetLimits());
+ aMark.SelectOneTable(0);
+
+ ScRange aRange(0, 0, 0, 0, 2, 0); // A1:A3
+ ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
+ // bCheckForSpill=false
+ rDocFunc.EnterMatrix(aRange, &aMark, nullptr, u"=B1:B3"_ustr, true, false, OUString(),
+ formula::FormulaGrammar::GRAM_DEFAULT, false);
+
+ ScFormulaCell* pFormulaCell = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
+ CPPUNIT_ASSERT_MESSAGE("A1 should contain a formula cell.", pFormulaCell != nullptr);
+
+ // No spill error
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("CSE should not produce a spill error.",
+ sal_Int32(FormulaError::NONE),
+ sal_Int32(pFormulaCell->GetErrCode()));
+
+ // The blocker should have been overwritten.
+ CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
+ CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
+ CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(ScAddress(0, 2, 0)));
+
+ m_pDoc->DeleteTab(0);
+}
+
CPPUNIT_PLUGIN_IMPLEMENT();

/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/engine/sc/source/core/data/documen4.cxx b/engine/sc/source/core/data/documen4.cxx
index 83f8684ba2ad..b7958b9e98c6 100644
--- a/engine/sc/source/core/data/documen4.cxx
+++ b/engine/sc/source/core/data/documen4.cxx
@@ -256,7 +256,8 @@ void ScDocument::InsertMatrixFormula(SCCOL nCol1, SCROW nRow1,
const ScMarkData& rMark,
const OUString& rFormula,
const ScTokenArray* pArr,
- const formula::FormulaGrammar::Grammar eGram )
+ const formula::FormulaGrammar::Grammar eGram,
+ bool bCheckForSpill)
{
PutInOrder(nCol1, nCol2);
PutInOrder(nRow1, nRow2);
@@ -308,6 +309,38 @@ void ScDocument::InsertMatrixFormula(SCCOL nCol1, SCROW nRow1,
*pCell, *this, ScAddress(nCol1, nRow1, rTab), ScCloneFlags::StartListening));
}

+ // Check for spill: if any non-origin cell in the target range is non-empty,
+ // set the spill error on the master cell and don't create reference cells.
+ if (bCheckForSpill && (nCol2 > nCol1 || nRow2 > nRow1))
+ {
+ bool bSpillBlocked = false;
+ for (const SCTAB& nTab : rMark)
+ {
+ if (nTab >= nMax)
+ break;
+ if (!FetchTable(nTab))
+ continue;
+ for (SCCOL nCol = nCol1; nCol <= nCol2 && !bSpillBlocked; ++nCol)
+ {
+ for (SCROW nRow = nRow1; nRow <= nRow2 && !bSpillBlocked; ++nRow)
+ {
+ if (nCol == nCol1 && nRow == nRow1)
+ continue; // skip the origin cell
+ if (HasData(nCol, nRow, nTab))
+ bSpillBlocked = true;
+ }
+ }
+ }
+ if (bSpillBlocked)
+ {
+ // Set spill error on the master cell; don't create reference cells.
+ // The master cell keeps its intended dimensions so the spill range
+ // can be resolved if the blocking cells are later cleared.
+ pCell->SetErrCode(FormulaError::Spill);
+ return;
+ }
+ }
+
ScSingleRefData aRefData;
aRefData.InitFlags();
aRefData.SetRelCol(0);
diff --git a/engine/sc/source/core/data/global.cxx b/engine/sc/source/core/data/global.cxx
index 804cab3a80b6..54d4d46ed582 100644
--- a/engine/sc/source/core/data/global.cxx
+++ b/engine/sc/source/core/data/global.cxx
@@ -344,6 +344,8 @@ OUString ScGlobal::GetErrorString(FormulaError nErr)
return ScCompiler::GetNativeSymbol(ocErrDivZero);
case FormulaError::IllegalFPOperation:
return ScCompiler::GetNativeSymbol(ocErrNum);
+ case FormulaError::Spill:
+ return ScCompiler::GetNativeSymbol(ocErrSpill);
default:
return ScResId(STR_ERROR_STR) + OUString::number( static_cast<int>(nErr) );
}
@@ -432,6 +434,9 @@ OUString ScGlobal::GetLongErrorString(FormulaError nErr)
case FormulaError::LinkFormulaNeedingCheck:
pErrNumber = STR_ERR_LONG_LINK_FORMULA_NEEDING_CHECK;
break;
+ case FormulaError::Spill:
+ pErrNumber = STR_LONG_ERR_SPILL;
+ break;
case FormulaError::NoValue:
pErrNumber = STR_LONG_ERR_NO_VALUE;
break;
@@ -455,6 +460,7 @@ bool ScGlobal::IsValidOOXMLError(FormulaError nErrNumber)
case FormulaError::IllegalFPOperation:
case FormulaError::NoRef:
case FormulaError::NoValue:
+ case FormulaError::Spill:
return true;
default:
return false;
diff --git a/engine/sc/source/filter/excel/xestream.cxx b/engine/sc/source/filter/excel/xestream.cxx
index 20ac79853649..c562cc824bc1 100644
--- a/engine/sc/source/filter/excel/xestream.cxx
+++ b/engine/sc/source/filter/excel/xestream.cxx
@@ -662,6 +662,7 @@ static const char* lcl_GetErrorString( FormulaError nScErrCode )
case EXC_ERR_REF: return "#REF!";
case EXC_ERR_NAME: return "#NAME?";
case EXC_ERR_NUM: return "#NUM!";
+ case EXC_ERR_SPILL: return "#SPILL!";
case EXC_ERR_NA:
default: return "#N/A";
}
diff --git a/engine/sc/source/filter/excel/xltools.cxx b/engine/sc/source/filter/excel/xltools.cxx
index 1174c71351ba..d778e579c4f1 100644
--- a/engine/sc/source/filter/excel/xltools.cxx
+++ b/engine/sc/source/filter/excel/xltools.cxx
@@ -213,6 +213,7 @@ sal_uInt8 XclTools::GetXclErrorCode( FormulaError nScError )
case FormulaError::NoAddin: return EXC_ERR_NAME;
case FormulaError::NoMacro: return EXC_ERR_NAME;
case FormulaError::NotAvailable: return EXC_ERR_NA;
+ case FormulaError::Spill: return EXC_ERR_SPILL;
default: break;
}
return EXC_ERR_NA;
@@ -229,6 +230,7 @@ FormulaError XclTools::GetScErrorCode( sal_uInt8 nXclError )
case EXC_ERR_NAME: return FormulaError::NoName;
case EXC_ERR_NUM: return FormulaError::IllegalFPOperation;
case EXC_ERR_NA: return FormulaError::NotAvailable;
+ case EXC_ERR_SPILL: return FormulaError::Spill;
default: OSL_FAIL( "XclTools::GetScErrorCode - unknown error code" );
}
return FormulaError::NotAvailable;
diff --git a/engine/sc/source/filter/inc/xlconst.hxx b/engine/sc/source/filter/inc/xlconst.hxx
index 9402a5c973bd..2a6ad9a0c56d 100644
--- a/engine/sc/source/filter/inc/xlconst.hxx
+++ b/engine/sc/source/filter/inc/xlconst.hxx
@@ -109,6 +109,7 @@ const sal_uInt8 EXC_ERR_REF = 0x17;
const sal_uInt8 EXC_ERR_NAME = 0x1D;
const sal_uInt8 EXC_ERR_NUM = 0x24;
const sal_uInt8 EXC_ERR_NA = 0x2A;
+const sal_uInt8 EXC_ERR_SPILL = 0x2B;

// Cached values list (EXTERNNAME, ptgArray, ...) -----------------------------

diff --git a/engine/sc/source/ui/docshell/docfunc.cxx b/engine/sc/source/ui/docshell/docfunc.cxx
index 0dadabbcabe0..8d25c320a379 100644
--- a/engine/sc/source/ui/docshell/docfunc.cxx
+++ b/engine/sc/source/ui/docshell/docfunc.cxx
@@ -2064,10 +2064,12 @@ bool ScDocFunc::AutoFormat( const ScRange& rRange, const ScMarkData* pTabMark,

bool ScDocFunc::EnterMatrix( const ScRange& rRange, const ScMarkData* pTabMark,
const ScTokenArray* pTokenArray, const OUString& rString, bool bApi, bool bEnglish,
- const OUString& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar )
+ const OUString& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar,
+ bool bCheckForSpill)
{
sc::EnterMatrixOperation aOperation(rDocShell, rRange, pTabMark, pTokenArray,
- rString, bApi, bEnglish, rFormulaNmsp, eGrammar);
+ rString, bApi, bEnglish, rFormulaNmsp, eGrammar,
+ bCheckForSpill);
return aOperation.run();
}

diff --git a/engine/sc/source/ui/inc/docfunc.hxx b/engine/sc/source/ui/inc/docfunc.hxx
index a503e1343242..1d64c26fd920 100644
--- a/engine/sc/source/ui/inc/docfunc.hxx
+++ b/engine/sc/source/ui/inc/docfunc.hxx
@@ -188,7 +188,8 @@ public:
const ScTokenArray* pTokenArray,
const OUString& rString, bool bApi, bool bEnglish,
const OUString& rFormulaNmsp,
- const formula::FormulaGrammar::Grammar );
+ const formula::FormulaGrammar::Grammar,
+ bool bCheckForSpill = false);

SC_DLLPUBLIC bool TabOp( const ScRange& rRange, const ScMarkData* pTabMark,
const ScTabOpParam& rParam, bool bRecord, bool bApi );
diff --git a/engine/sc/source/ui/inc/operation/EnterMatrixOperation.hxx b/engine/sc/source/ui/inc/operation/EnterMatrixOperation.hxx
index e8366342ec26..e05101dc026c 100644
--- a/engine/sc/source/ui/inc/operation/EnterMatrixOperation.hxx
+++ b/engine/sc/source/ui/inc/operation/EnterMatrixOperation.hxx
@@ -31,6 +31,7 @@ private:
bool mbEnglish;
OUString maFormulaNmsp;
formula::FormulaGrammar::Grammar meGrammar;
+ bool mbCheckForSpill;

bool runImplementation() override;

@@ -38,7 +39,7 @@ public:
EnterMatrixOperation(ScDocShell& rDocShell, const ScRange& rRange, const ScMarkData* pTabMark,
const ScTokenArray* pTokenArray, const OUString& rString, bool bApi,
bool bEnglish, const OUString& rFormulaNmsp,
- formula::FormulaGrammar::Grammar eGrammar);
+ formula::FormulaGrammar::Grammar eGrammar, bool bCheckForSpill = false);
};
} // end sc namespace

diff --git a/engine/sc/source/ui/operation/EnterMatrixOperation.cxx b/engine/sc/source/ui/operation/EnterMatrixOperation.cxx
index 65e7e00de637..2e7ceaf07a9f 100644
--- a/engine/sc/source/ui/operation/EnterMatrixOperation.cxx
+++ b/engine/sc/source/ui/operation/EnterMatrixOperation.cxx
@@ -27,7 +27,8 @@ EnterMatrixOperation::EnterMatrixOperation(ScDocShell& rDocShell, const ScRange&
const ScMarkData* pTabMark,
const ScTokenArray* pTokenArray, const OUString& rString,
bool bApi, bool bEnglish, const OUString& rFormulaNmsp,
- formula::FormulaGrammar::Grammar eGrammar)
+ formula::FormulaGrammar::Grammar eGrammar,
+ bool bCheckForSpill)
: Operation(OperationType::EnterMatrix, true, bApi)
, mrDocShell(rDocShell)
, maRange(rRange)
@@ -37,6 +38,7 @@ EnterMatrixOperation::EnterMatrixOperation(ScDocShell& rDocShell, const ScRange&
, mbEnglish(bEnglish)
, maFormulaNmsp(rFormulaNmsp)
, meGrammar(eGrammar)
+ , mbCheckForSpill(bCheckForSpill)
{
}

@@ -92,7 +94,7 @@ bool EnterMatrixOperation::runImplementation()
if (mpTokenArray)
{
rDoc.InsertMatrixFormula(nStartCol, nStartRow, nEndCol, nEndRow, aMark, OUString(),
- mpTokenArray, meGrammar);
+ mpTokenArray, meGrammar, mbCheckForSpill);
}
else if (rDoc.IsImportingXML())
{
@@ -109,11 +111,11 @@ bool EnterMatrixOperation::runImplementation()
ScCompiler aComp(rDoc, aRange.aStart, meGrammar);
std::unique_ptr<ScTokenArray> pCode = aComp.CompileString(maString);
rDoc.InsertMatrixFormula(nStartCol, nStartRow, nEndCol, nEndRow, aMark, OUString(),
- pCode.get(), meGrammar);
+ pCode.get(), meGrammar, mbCheckForSpill);
}
else
rDoc.InsertMatrixFormula(nStartCol, nStartRow, nEndCol, nEndRow, aMark, maString,
- nullptr, meGrammar);
+ nullptr, meGrammar, mbCheckForSpill);

if (mbRecord)
{
diff --git a/engine/sc/source/ui/view/viewfunc.cxx b/engine/sc/source/ui/view/viewfunc.cxx
index 61a1553b7495..88b39077c38c 100644
--- a/engine/sc/source/ui/view/viewfunc.cxx
+++ b/engine/sc/source/ui/view/viewfunc.cxx
@@ -1002,7 +1002,8 @@ void ScViewFunc::EnterMatrix( const OUString& rString, ::formula::FormulaGrammar
const SCCOL nCol = rData.GetCurX();
const SCROW nRow = rData.GetCurY();
const ScMarkData& rMark = rData.GetMarkData();
- if ( !rMark.IsMarked() && !rMark.IsMultiMarked() )
+ bool bAutoExpand = !rMark.IsMarked() && !rMark.IsMultiMarked();
+ if (bAutoExpand)
{
// nothing marked -> temporarily calculate block
// with size of result formula to get the size
@@ -1029,8 +1030,10 @@ void ScViewFunc::EnterMatrix( const OUString& rString, ::formula::FormulaGrammar
if (rData.GetSimpleArea(aRange) == SC_MARK_SIMPLE)
{
ScDocShell* pDocSh = rData.GetDocShell();
+ // Auto expanded formulas (no explicit range selection) use spill checking
+ // to avoid overwriting non-empty cells.
bool bSuccess = pDocSh->GetDocFunc().EnterMatrix(
- aRange, &rMark, nullptr, rString, false, false, OUString(), eGram );
+ aRange, &rMark, nullptr, rString, false, false, OUString(), eGram, bAutoExpand);
if (bSuccess)
pDocSh->UpdateOle(GetViewData());
else