1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
3 * This file is part of the LibreOffice project.
5 * This Source Code Form is subject to the terms of the Mozilla Public
6 * License, v. 2.0. If a copy of the MPL was not distributed with this
7 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
10 #include "helper/qahelper.hxx"
11 #include <clipparam.hxx>
12 #include <scopetools.hxx>
13 #include <formulacell.hxx>
14 #include <docfunc.hxx>
15 #include <tokenstringcontext.hxx>
17 #include <scmatrix.hxx>
18 #include <docoptio.hxx>
19 #include <externalrefmgr.hxx>
20 #include <undomanager.hxx>
21 #include <broadcast.hxx>
24 #include <svl/broadcast.hxx>
25 #include <sfx2/docfile.hxx>
33 using namespace formula
;
40 ScRange
getCachedRange(const ScExternalRefCache::TableTypeRef
& pCacheTab
)
45 pCacheTab
->getAllRows(aRows
);
47 for (const SCROW nRow
: aRows
)
50 pCacheTab
->getAllCols(nRow
, aCols
);
51 for (const SCCOL nCol
: aCols
)
55 aRange
.aStart
= ScAddress(nCol
, nRow
, 0);
56 aRange
.aEnd
= aRange
.aStart
;
61 if (nCol
< aRange
.aStart
.Col())
62 aRange
.aStart
.SetCol(nCol
);
63 else if (aRange
.aEnd
.Col() < nCol
)
64 aRange
.aEnd
.SetCol(nCol
);
66 if (nRow
< aRange
.aStart
.Row())
67 aRange
.aStart
.SetRow(nRow
);
68 else if (aRange
.aEnd
.Row() < nRow
)
69 aRange
.aEnd
.SetRow(nRow
);
86 const SCROW m_nTotalRows
;
87 const SCROW m_nStart1
;
89 const SCROW m_nStart2
;
93 ColumnTest(ScDocument
* pDoc
, SCROW nTotalRows
, SCROW nStart1
, SCROW nEnd1
, SCROW nStart2
,
96 , m_nTotalRows(nTotalRows
)
104 void operator()(SCCOL nColumn
, const OUString
& rFormula
,
105 std::function
<double(SCROW
)> const& lExpected
) const
107 ScDocument
aClipDoc(SCDOCMODE_CLIP
);
108 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
110 ScAddress
aPos(nColumn
, m_nStart1
, 0);
111 m_pDoc
->SetString(aPos
, rFormula
);
112 ASSERT_DOUBLES_EQUAL(lExpected(m_nStart1
), m_pDoc
->GetValue(aPos
));
114 // Copy formula cell to clipboard.
115 ScClipParam
aClipParam(ScRange(aPos
), false);
116 aMark
.SetMarkArea(ScRange(aPos
));
117 m_pDoc
->CopyToClip(aClipParam
, &aClipDoc
, &aMark
, false, false);
119 // Paste it to first range.
120 InsertDeleteFlags nFlags
= InsertDeleteFlags::CONTENTS
;
121 ScRange
aDestRange(nColumn
, m_nStart1
, 0, nColumn
, m_nEnd1
, 0);
122 aMark
.SetMarkArea(aDestRange
);
123 m_pDoc
->CopyFromClip(aDestRange
, aMark
, nFlags
, nullptr, &aClipDoc
);
125 // Paste it second range.
126 aDestRange
= ScRange(nColumn
, m_nStart2
, 0, nColumn
, m_nEnd2
, 0);
127 aMark
.SetMarkArea(aDestRange
);
128 m_pDoc
->CopyFromClip(aDestRange
, aMark
, nFlags
, nullptr, &aClipDoc
);
130 // Check the formula results for passed column.
131 for (SCROW i
= 0; i
< m_nTotalRows
; ++i
)
133 if (!((m_nStart1
<= i
&& i
<= m_nEnd1
) || (m_nStart2
<= i
&& i
<= m_nEnd2
)))
135 double fExpected
= lExpected(i
);
136 ASSERT_DOUBLES_EQUAL(fExpected
, m_pDoc
->GetValue(ScAddress(nColumn
, i
, 0)));
143 class TestFormula2
: public ScUcalcTestBase
146 template <size_t DataSize
, size_t FormulaSize
, int Type
>
147 void runTestMATCH(ScDocument
* pDoc
, const char* aData
[DataSize
],
148 const StrStrCheck aChecks
[FormulaSize
]);
149 template <size_t DataSize
, size_t FormulaSize
, int Type
>
150 void runTestHorizontalMATCH(ScDocument
* pDoc
, const char* aData
[DataSize
],
151 const StrStrCheck aChecks
[FormulaSize
]);
153 void testExtRefFuncT(ScDocument
* pDoc
, ScDocument
& rExtDoc
);
154 void testExtRefFuncOFFSET(ScDocument
* pDoc
, ScDocument
& rExtDoc
);
155 void testExtRefFuncVLOOKUP(ScDocument
* pDoc
, ScDocument
& rExtDoc
);
156 void testExtRefConcat(ScDocument
* pDoc
, ScDocument
& rExtDoc
);
159 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncIF
)
161 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
163 m_pDoc
->InsertTab(0, u
"Formula"_ustr
);
165 m_pDoc
->SetString(ScAddress(0, 0, 0), u
"=IF(B1=2;\"two\";\"not two\")"_ustr
);
166 CPPUNIT_ASSERT_EQUAL(u
"not two"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
167 m_pDoc
->SetValue(ScAddress(1, 0, 0), 2.0);
168 CPPUNIT_ASSERT_EQUAL(u
"two"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
169 m_pDoc
->SetValue(ScAddress(1, 0, 0), 3.0);
170 CPPUNIT_ASSERT_EQUAL(u
"not two"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
172 // Test nested IF in array/matrix if the nested IF condition is a scalar.
173 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
174 aMark
.SelectOneTable(0);
175 m_pDoc
->InsertMatrixFormula(0, 2, 1, 2, aMark
, u
"=IF({1;0};IF(1;23);42)"_ustr
);
176 // Results must be 23 and 42.
177 CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc
->GetValue(ScAddress(0, 2, 0)));
178 CPPUNIT_ASSERT_EQUAL(42.0, m_pDoc
->GetValue(ScAddress(1, 2, 0)));
180 // Test nested IF in array/matrix if nested IF conditions are range
181 // references, data in A5:C8, matrix formula in D4 so there is no
182 // implicit intersection between formula and ranges.
184 std::vector
<std::vector
<const char*>> aData
185 = { { "1", "1", "16" }, { "0", "1", "32" }, { "1", "0", "64" }, { "0", "0", "128" } };
186 ScAddress
aPos(0, 4, 0);
187 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
188 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
190 m_pDoc
->InsertMatrixFormula(3, 3, 3, 3, aMark
, u
"=SUM(IF(A5:A8;IF(B5:B8;C5:C8;0);0))"_ustr
);
191 // Result must be 16, only the first row matches all criteria.
192 CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc
->GetValue(ScAddress(3, 3, 0)));
195 // Test nested IF in array/matrix if the nested IF has no Else path.
196 m_pDoc
->InsertMatrixFormula(0, 10, 1, 10, aMark
, u
"=IF(IF({1;0};12);34;56)"_ustr
);
197 // Results must be 34 and 56.
198 CPPUNIT_ASSERT_EQUAL(34.0, m_pDoc
->GetValue(ScAddress(0, 10, 0)));
199 CPPUNIT_ASSERT_EQUAL(56.0, m_pDoc
->GetValue(ScAddress(1, 10, 0)));
201 m_pDoc
->DeleteTab(0);
204 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncCHOOSE
)
206 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
208 m_pDoc
->InsertTab(0, u
"Formula"_ustr
);
210 m_pDoc
->SetString(ScAddress(0, 0, 0), u
"=CHOOSE(B1;\"one\";\"two\";\"three\")"_ustr
);
211 FormulaError nError
= m_pDoc
->GetErrCode(ScAddress(0, 0, 0));
212 CPPUNIT_ASSERT_MESSAGE("Formula result should be an error since B1 is still empty.",
213 nError
!= FormulaError::NONE
);
214 m_pDoc
->SetValue(ScAddress(1, 0, 0), 1.0);
215 CPPUNIT_ASSERT_EQUAL(u
"one"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
216 m_pDoc
->SetValue(ScAddress(1, 0, 0), 2.0);
217 CPPUNIT_ASSERT_EQUAL(u
"two"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
218 m_pDoc
->SetValue(ScAddress(1, 0, 0), 3.0);
219 CPPUNIT_ASSERT_EQUAL(u
"three"_ustr
, m_pDoc
->GetString(ScAddress(0, 0, 0)));
220 m_pDoc
->SetValue(ScAddress(1, 0, 0), 4.0);
221 nError
= m_pDoc
->GetErrCode(ScAddress(0, 0, 0));
222 CPPUNIT_ASSERT_MESSAGE("Formula result should be an error due to out-of-bound input..",
223 nError
!= FormulaError::NONE
);
225 m_pDoc
->DeleteTab(0);
228 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncIFERROR
)
230 // IFERROR/IFNA (fdo#56124)
232 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
234 // Empty A1:A39 first.
235 clearRange(m_pDoc
, ScRange(0, 0, 0, 0, 40, 0));
237 // Raw data (rows 1 through 12)
238 const char* aData
[] = { "1", "e", "=SQRT(4)", "=SQRT(-2)", "=A4", "=1/0",
239 "=NA()", "bar", "4", "gee", "=1/0", "23" };
241 SCROW nRows
= SAL_N_ELEMENTS(aData
);
242 for (SCROW i
= 0; i
< nRows
; ++i
)
243 m_pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aData
[i
]));
245 printRange(m_pDoc
, ScRange(0, 0, 0, 0, nRows
- 1, 0), "data range for IFERROR/IFNA");
247 // formulas and results
250 const char* pFormula
;
253 { "=IFERROR(A1;9)", "1" },
254 { "=IFERROR(A2;9)", "e" },
255 { "=IFERROR(A3;9)", "2" },
256 { "=IFERROR(A4;-7)", "-7" },
257 { "=IFERROR(A5;-7)", "-7" },
258 { "=IFERROR(A6;-7)", "-7" },
259 { "=IFERROR(A7;-7)", "-7" },
260 { "=IFNA(A6;9)", "#DIV/0!" },
261 { "=IFNA(A7;-7)", "-7" },
262 { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
263 { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
264 { "{=IFERROR(3*A11:A12;1998)}[0]",
265 "1998" }, // um... this is not the correct way to insert a
266 { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place holder, see below
269 nRows
= SAL_N_ELEMENTS(aChecks
);
270 for (SCROW i
= 0; i
< nRows
- 2; ++i
)
273 m_pDoc
->SetString(0, nRow
, 0, OUString::createFromAscii(aChecks
[i
].pFormula
));
276 // Create a matrix range in last two rows of the range above, actual data
277 // of the placeholders.
278 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
279 aMark
.SelectOneTable(0);
280 m_pDoc
->InsertMatrixFormula(0, 20 + nRows
- 2, 0, 20 + nRows
- 1, aMark
,
281 u
"=IFERROR(3*A11:A12;1998)"_ustr
);
285 for (SCROW i
= 0; i
< nRows
; ++i
)
288 OUString aResult
= m_pDoc
->GetString(0, nRow
, 0);
289 CPPUNIT_ASSERT_EQUAL_MESSAGE(aChecks
[i
].pFormula
,
290 OUString::createFromAscii(aChecks
[i
].pResult
), aResult
);
293 const SCCOL nCols
= 3;
294 std::vector
<std::vector
<const char*>> aData2
295 = { { "1", "2", "3" }, { "4", "=1/0", "6" }, { "7", "8", "9" } };
296 const char* aCheck2
[][nCols
] = { { "1", "2", "3" }, { "4", "Error", "6" }, { "7", "8", "9" } };
299 ScAddress
aPos(2, 0, 0);
300 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData2
);
301 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
303 // Array formula in F4:H6
304 const SCROW nElems2
= SAL_N_ELEMENTS(aCheck2
);
305 const SCCOL nStartCol
= aPos
.Col() + nCols
;
306 const SCROW nStartRow
= aPos
.Row() + nElems2
;
307 m_pDoc
->InsertMatrixFormula(nStartCol
, nStartRow
, nStartCol
+ nCols
- 1,
308 nStartRow
+ nElems2
- 1, aMark
, u
"=IFERROR(C1:E3;\"Error\")"_ustr
);
312 for (SCCOL nCol
= nStartCol
; nCol
< nStartCol
+ nCols
; ++nCol
)
314 for (SCROW nRow
= nStartRow
; nRow
< nStartRow
+ nElems2
; ++nRow
)
316 OUString aResult
= m_pDoc
->GetString(nCol
, nRow
, 0);
317 CPPUNIT_ASSERT_EQUAL_MESSAGE(
318 "IFERROR array result",
319 OUString::createFromAscii(aCheck2
[nRow
- nStartRow
][nCol
- nStartCol
]), aResult
);
323 m_pDoc
->DeleteTab(0);
326 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncSHEET
)
328 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet",
329 m_pDoc
->InsertTab(SC_TAB_APPEND
, u
"test1"_ustr
));
331 m_pDoc
->SetString(0, 0, 0, u
"=SHEETS()"_ustr
);
332 m_pDoc
->CalcFormulaTree(false, false);
333 double original
= m_pDoc
->GetValue(0, 0, 0);
335 CPPUNIT_ASSERT_EQUAL_MESSAGE(
336 "result of SHEETS() should equal the number of sheets, but doesn't.",
337 static_cast<SCTAB
>(original
), m_pDoc
->GetTableCount());
339 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet",
340 m_pDoc
->InsertTab(SC_TAB_APPEND
, u
"test2"_ustr
));
342 double modified
= m_pDoc
->GetValue(0, 0, 0);
343 ASSERT_DOUBLES_EQUAL_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
344 1.0, modified
- original
);
346 SCTAB nTabCount
= m_pDoc
->GetTableCount();
347 m_pDoc
->DeleteTab(--nTabCount
);
349 modified
= m_pDoc
->GetValue(0, 0, 0);
350 ASSERT_DOUBLES_EQUAL_MESSAGE("result of SHEETS() did not get updated after sheet removal.", 0.0,
351 modified
- original
);
353 m_pDoc
->DeleteTab(--nTabCount
);
356 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncNOW
)
358 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
361 m_pDoc
->SetValue(0, 0, 0, val
);
362 m_pDoc
->SetString(0, 1, 0, u
"=IF(A1>0;NOW();0"_ustr
);
363 double now1
= m_pDoc
->GetValue(0, 1, 0);
364 CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1
> 0.0);
367 m_pDoc
->SetValue(0, 0, 0, val
);
368 m_pDoc
->CalcFormulaTree(false, false);
369 double zero
= m_pDoc
->GetValue(0, 1, 0);
370 ASSERT_DOUBLES_EQUAL_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", 0.0,
374 m_pDoc
->SetValue(0, 0, 0, val
);
375 m_pDoc
->CalcFormulaTree(false, false);
376 double now2
= m_pDoc
->GetValue(0, 1, 0);
377 CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2
- now1
) >= 0.0);
379 m_pDoc
->DeleteTab(0);
382 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncNUMBERVALUE
)
384 // NUMBERVALUE fdo#57180
386 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
388 // Empty A1:A39 first.
389 clearRange(m_pDoc
, ScRange(0, 0, 0, 0, 40, 0));
391 // Raw data (rows 1 through 6)
393 = { "1ag9a9b9", "1ag34 5g g6 78b9%%", "1 234d56E-2", "d4", "54.4", "1a2b3e1%" };
395 SCROW nRows
= SAL_N_ELEMENTS(aData
);
396 for (SCROW i
= 0; i
< nRows
; ++i
)
397 m_pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aData
[i
]));
399 printRange(m_pDoc
, ScRange(0, 0, 0, 0, nRows
- 1, 0), "data range for NUMBERVALUE");
401 // formulas and results
404 const char* pFormula
;
406 } aChecks
[] = { { "=NUMBERVALUE(A1;\"b\";\"ag\")", "199.9" },
407 { "=NUMBERVALUE(A2;\"b\";\"ag\")", "134.56789" },
408 { "=NUMBERVALUE(A2;\"b\";\"g\")", "#VALUE!" },
409 { "=NUMBERVALUE(A3;\"d\")", "12.3456" },
410 { "=NUMBERVALUE(A4;\"d\";\"foo\")", "0.4" },
411 { "=NUMBERVALUE(A4;)", "Err:502" },
412 { "=NUMBERVALUE(A5;)", "Err:502" },
413 { "=NUMBERVALUE(A6;\"b\";\"a\")", "1.23" } };
415 nRows
= SAL_N_ELEMENTS(aChecks
);
416 for (SCROW i
= 0; i
< nRows
; ++i
)
419 m_pDoc
->SetString(0, nRow
, 0, OUString::createFromAscii(aChecks
[i
].pFormula
));
423 for (SCROW i
= 0; i
< nRows
; ++i
)
426 OUString aResult
= m_pDoc
->GetString(0, nRow
, 0);
427 CPPUNIT_ASSERT_EQUAL_MESSAGE(aChecks
[i
].pFormula
,
428 OUString::createFromAscii(aChecks
[i
].pResult
), aResult
);
431 m_pDoc
->DeleteTab(0);
434 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncLEN
)
436 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
438 m_pDoc
->InsertTab(0, u
"Formula"_ustr
);
440 // Leave A1:A3 empty, and insert an array of LEN in B1:B3 that references
441 // these empty cells.
443 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
444 aMark
.SelectOneTable(0);
445 m_pDoc
->InsertMatrixFormula(1, 0, 1, 2, aMark
, u
"=LEN(A1:A3)"_ustr
);
447 ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(1, 0, 0));
449 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should be a matrix origin.", ScMatrixMode::Formula
,
450 pFC
->GetMatrixFlag());
452 // This should be a 1x3 matrix.
455 pFC
->GetMatColsRows(nCols
, nRows
);
456 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL
>(1), nCols
);
457 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW
>(3), nRows
);
459 // LEN value should be 0 for an empty cell.
460 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
461 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(ScAddress(1, 1, 0)));
462 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(ScAddress(1, 2, 0)));
464 m_pDoc
->DeleteTab(0);
467 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncLOOKUP
)
469 FormulaGrammarSwitch
aFGSwitch(m_pDoc
, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1
);
471 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
474 const char* aData
[][2] = {
475 { "=CONCATENATE(\"A\")", "1" },
476 { "=CONCATENATE(\"B\")", "2" },
477 { "=CONCATENATE(\"C\")", "3" },
478 { nullptr, nullptr } // terminator
481 // Insert raw data into A1:B3.
482 for (SCROW i
= 0; aData
[i
][0]; ++i
)
484 m_pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aData
[i
][0]));
485 m_pDoc
->SetString(1, i
, 0, OUString::createFromAscii(aData
[i
][1]));
488 const char* aData2
[][2] = {
489 { "A", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
490 { "B", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
491 { "C", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
492 { nullptr, nullptr } // terminator
495 // Insert check formulas into A5:B7.
496 for (SCROW i
= 0; aData2
[i
][0]; ++i
)
498 m_pDoc
->SetString(0, i
+ 4, 0, OUString::createFromAscii(aData2
[i
][0]));
499 m_pDoc
->SetString(1, i
+ 4, 0, OUString::createFromAscii(aData2
[i
][1]));
502 printRange(m_pDoc
, ScRange(0, 4, 0, 1, 6, 0), "Data range for LOOKUP.");
504 // Values for B5:B7 should be 1, 2, and 3.
505 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
506 static_cast<int>(m_pDoc
->GetErrCode(ScAddress(1, 4, 0))));
507 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
508 static_cast<int>(m_pDoc
->GetErrCode(ScAddress(1, 5, 0))));
509 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
510 static_cast<int>(m_pDoc
->GetErrCode(ScAddress(1, 6, 0))));
512 ASSERT_DOUBLES_EQUAL(1.0, m_pDoc
->GetValue(ScAddress(1, 4, 0)));
513 ASSERT_DOUBLES_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(1, 5, 0)));
514 ASSERT_DOUBLES_EQUAL(3.0, m_pDoc
->GetValue(ScAddress(1, 6, 0)));
516 m_pDoc
->DeleteTab(0);
519 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncLOOKUParrayWithError
)
521 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true);
522 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
524 std::vector
<std::vector
<const char*>> aData
= { { "x", "y", "z" }, { "a", "b", "c" } };
525 insertRangeData(m_pDoc
, ScAddress(2, 1, 0), aData
); // C2:E3
526 m_pDoc
->SetString(0, 0, 0, u
"=LOOKUP(2;1/(C2:E2<>\"\");C3:E3)"_ustr
); // A1
528 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for last column.", u
"c"_ustr
,
529 m_pDoc
->GetString(0, 0, 0));
530 m_pDoc
->SetString(4, 1, 0, u
""_ustr
); // E2
531 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for second last column.", u
"b"_ustr
,
532 m_pDoc
->GetString(0, 0, 0));
534 m_pDoc
->SetString(6, 1, 0, u
"one"_ustr
); // G2
535 m_pDoc
->SetString(6, 5, 0, u
"two"_ustr
); // G6
536 // Creates an interim array {1,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!}
537 m_pDoc
->SetString(7, 8, 0, u
"=LOOKUP(2;1/(NOT(ISBLANK(G2:G9)));G2:G9)"_ustr
); // H9
538 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for last row.", u
"two"_ustr
,
539 m_pDoc
->GetString(7, 8, 0));
541 // Lookup on empty range.
542 m_pDoc
->SetString(9, 8, 0, u
"=LOOKUP(2;1/(NOT(ISBLANK(I2:I9)));I2:I9)"_ustr
); // J9
543 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find no match.", u
"#N/A"_ustr
, m_pDoc
->GetString(9, 8, 0));
545 m_pDoc
->DeleteTab(0);
548 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf141146
)
550 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true);
551 m_pDoc
->InsertTab(0, u
"Test1"_ustr
);
552 m_pDoc
->InsertTab(1, u
"Test2"_ustr
);
554 std::vector
<std::vector
<const char*>> aData
555 = { { "k1", "value1" }, { "k2", "value2" }, { "k3", "value3" } };
557 insertRangeData(m_pDoc
, ScAddress(0, 1, 1), aData
); // A2:B4
558 m_pDoc
->SetString(4, 0, 1, u
"k2"_ustr
); // E1
560 m_pDoc
->SetString(4, 1, 1, u
"=LOOKUP(1;1/(A$2:A$4=E$1);1)"_ustr
);
561 m_pDoc
->SetString(4, 2, 1, u
"=LOOKUP(E1;A$2:A$4;B2:B4)"_ustr
);
562 m_pDoc
->SetString(4, 3, 1, u
"=LOOKUP(1;1/(A$2:A$4=E$1);B2:B4)"_ustr
);
564 // Without the fix in place, this test would have failed with
567 CPPUNIT_ASSERT_EQUAL(u
"#N/A"_ustr
, m_pDoc
->GetString(4, 1, 1));
568 CPPUNIT_ASSERT_EQUAL(u
"value2"_ustr
, m_pDoc
->GetString(4, 2, 1));
569 CPPUNIT_ASSERT_EQUAL(u
"value2"_ustr
, m_pDoc
->GetString(4, 3, 1));
571 m_pDoc
->DeleteTab(1);
572 m_pDoc
->DeleteTab(0);
575 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncVLOOKUP
)
579 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
582 clearRange(m_pDoc
, ScRange(0, 0, 0, 5, 39, 0));
585 const char* aData
[][2] = {
586 { "Key", "Val" }, { "10", "3" }, { "20", "4" }, { "30", "5" },
587 { "40", "6" }, { "50", "7" }, { "60", "8" }, { "70", "9" },
588 { "B", "10" }, { "B", "11" }, { "C", "12" }, { "D", "13" },
589 { "E", "14" }, { "F", "15" }, { nullptr, nullptr } // terminator
592 // Insert raw data into A1:B14.
593 for (SCROW i
= 0; aData
[i
][0]; ++i
)
595 m_pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aData
[i
][0]));
596 m_pDoc
->SetString(1, i
, 0, OUString::createFromAscii(aData
[i
][1]));
599 printRange(m_pDoc
, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
605 const char* pFormula
;
607 } aChecks
[] = { { "Lookup", "Formula", nullptr },
608 { "12", "=VLOOKUP(D2;A2:B14;2;1)", "3" },
609 { "29", "=VLOOKUP(D3;A2:B14;2;1)", "4" },
610 { "31", "=VLOOKUP(D4;A2:B14;2;1)", "5" },
611 { "45", "=VLOOKUP(D5;A2:B14;2;1)", "6" },
612 { "56", "=VLOOKUP(D6;A2:B14;2;1)", "7" },
613 { "65", "=VLOOKUP(D7;A2:B14;2;1)", "8" },
614 { "78", "=VLOOKUP(D8;A2:B14;2;1)", "9" },
615 { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", "#N/A" },
616 { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", "11" },
617 { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", "12" },
618 { "David", "=VLOOKUP(D12;A2:B14;2;1)", "13" },
619 { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", "14" },
620 { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", "15" },
621 { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", "15" },
622 { "100", "=VLOOKUP(D16;A2:B14;2;1)", "9" },
623 { "1000", "=VLOOKUP(D17;A2:B14;2;1)", "9" },
624 { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", "15" } };
626 // Insert formula data into D1:E18.
627 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
629 m_pDoc
->SetString(3, i
, 0, OUString::createFromAscii(aChecks
[i
].pLookup
));
630 m_pDoc
->SetString(4, i
, 0, OUString::createFromAscii(aChecks
[i
].pFormula
));
633 printRange(m_pDoc
, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
636 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
639 // Skip the header row.
642 OUString aRes
= m_pDoc
->GetString(4, i
, 0);
643 bool bGood
= aRes
.equalsAscii(aChecks
[i
].pRes
);
646 cerr
<< "row " << (i
+ 1) << ": lookup value='" << aChecks
[i
].pLookup
<< "' expected='"
647 << aChecks
[i
].pRes
<< "' actual='" << aRes
<< "'" << endl
;
648 CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
652 // Clear the sheet and start over.
653 clearSheet(m_pDoc
, 0);
655 // Lookup on sorted data interspersed with empty cells.
657 // A1:B8 is the search range.
658 m_pDoc
->SetValue(ScAddress(0, 2, 0), 1.0);
659 m_pDoc
->SetValue(ScAddress(0, 4, 0), 2.0);
660 m_pDoc
->SetValue(ScAddress(0, 7, 0), 4.0);
661 m_pDoc
->SetString(ScAddress(1, 2, 0), u
"One"_ustr
);
662 m_pDoc
->SetString(ScAddress(1, 4, 0), u
"Two"_ustr
);
663 m_pDoc
->SetString(ScAddress(1, 7, 0), u
"Four"_ustr
);
665 // D1:D5 contain match values.
666 m_pDoc
->SetValue(ScAddress(3, 0, 0), 1.0);
667 m_pDoc
->SetValue(ScAddress(3, 1, 0), 2.0);
668 m_pDoc
->SetValue(ScAddress(3, 2, 0), 3.0);
669 m_pDoc
->SetValue(ScAddress(3, 3, 0), 4.0);
670 m_pDoc
->SetValue(ScAddress(3, 4, 0), 5.0);
672 // E1:E5 contain formulas.
673 m_pDoc
->SetString(ScAddress(4, 0, 0), u
"=VLOOKUP(D1;$A$1:$B$8;2)"_ustr
);
674 m_pDoc
->SetString(ScAddress(4, 1, 0), u
"=VLOOKUP(D2;$A$1:$B$8;2)"_ustr
);
675 m_pDoc
->SetString(ScAddress(4, 2, 0), u
"=VLOOKUP(D3;$A$1:$B$8;2)"_ustr
);
676 m_pDoc
->SetString(ScAddress(4, 3, 0), u
"=VLOOKUP(D4;$A$1:$B$8;2)"_ustr
);
677 m_pDoc
->SetString(ScAddress(4, 4, 0), u
"=VLOOKUP(D5;$A$1:$B$8;2)"_ustr
);
680 // Check the formula results in E1:E5.
681 CPPUNIT_ASSERT_EQUAL(u
"One"_ustr
, m_pDoc
->GetString(ScAddress(4, 0, 0)));
682 CPPUNIT_ASSERT_EQUAL(u
"Two"_ustr
, m_pDoc
->GetString(ScAddress(4, 1, 0)));
683 CPPUNIT_ASSERT_EQUAL(u
"Two"_ustr
, m_pDoc
->GetString(ScAddress(4, 2, 0)));
684 CPPUNIT_ASSERT_EQUAL(u
"Four"_ustr
, m_pDoc
->GetString(ScAddress(4, 3, 0)));
685 CPPUNIT_ASSERT_EQUAL(u
"Four"_ustr
, m_pDoc
->GetString(ScAddress(4, 4, 0)));
688 clearSheet(m_pDoc
, 0);
690 // Set A,B,...,G to A1:A7.
691 m_pDoc
->SetString(ScAddress(0, 0, 0), u
"A"_ustr
);
692 m_pDoc
->SetString(ScAddress(0, 1, 0), u
"B"_ustr
);
693 m_pDoc
->SetString(ScAddress(0, 2, 0), u
"C"_ustr
);
694 m_pDoc
->SetString(ScAddress(0, 3, 0), u
"D"_ustr
);
695 m_pDoc
->SetString(ScAddress(0, 4, 0), u
"E"_ustr
);
696 m_pDoc
->SetString(ScAddress(0, 5, 0), u
"F"_ustr
);
697 m_pDoc
->SetString(ScAddress(0, 6, 0), u
"G"_ustr
);
699 // Set the formula in C1.
700 m_pDoc
->SetString(ScAddress(2, 0, 0), u
"=VLOOKUP(\"C\";A1:A16;1)"_ustr
);
701 CPPUNIT_ASSERT_EQUAL(u
"C"_ustr
, m_pDoc
->GetString(ScAddress(2, 0, 0)));
703 // A21:E24, test position dependent implicit intersection as argument to a
704 // scalar value parameter in a function that has a ReferenceOrForceArray
705 // type parameter somewhere else and formula is not in array mode,
706 // VLOOKUP(Value;ReferenceOrForceArray;...)
707 std::vector
<std::vector
<const char*>> aData2
708 = { { "1", "one", "3", "=VLOOKUP(C21:C24;A21:B24;2;0)", "three" },
709 { "2", "two", "1", "=VLOOKUP(C21:C24;A21:B24;2;0)", "one" },
710 { "3", "three", "4", "=VLOOKUP(C21:C24;A21:B24;2;0)", "four" },
711 { "4", "four", "2", "=VLOOKUP(C21:C24;A21:B24;2;0)", "two" } };
713 ScAddress
aPos2(0, 20, 0);
714 ScRange aRange2
= insertRangeData(m_pDoc
, aPos2
, aData2
);
715 CPPUNIT_ASSERT_EQUAL(aPos2
, aRange2
.aStart
);
717 aPos2
.SetCol(3); // column D formula results
718 for (size_t i
= 0; i
< aData2
.size(); ++i
)
720 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aData2
[i
][4]), m_pDoc
->GetString(aPos2
));
724 m_pDoc
->DeleteTab(0);
727 template <size_t DataSize
, size_t FormulaSize
, int Type
>
728 void TestFormula2::runTestMATCH(ScDocument
* pDoc
, const char* aData
[DataSize
],
729 const StrStrCheck aChecks
[FormulaSize
])
731 size_t nDataSize
= DataSize
;
732 for (size_t i
= 0; i
< nDataSize
; ++i
)
733 pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aData
[i
]));
735 for (size_t i
= 0; i
< FormulaSize
; ++i
)
737 pDoc
->SetString(1, i
, 0, OUString::createFromAscii(aChecks
[i
].pVal
));
739 OUString aFormula
= "=MATCH(B" + OUString::number(i
+ 1) + ";A1:A"
740 + OUString::number(nDataSize
) + ";" + OUString::number(Type
) + ")";
741 pDoc
->SetString(2, i
, 0, aFormula
);
745 printRange(pDoc
, ScRange(0, 0, 0, 2, FormulaSize
- 1, 0), "MATCH");
747 // verify the results.
748 for (size_t i
= 0; i
< FormulaSize
; ++i
)
750 OUString aStr
= pDoc
->GetString(2, i
, 0);
751 if (!aStr
.equalsAscii(aChecks
[i
].pRes
))
753 cerr
<< "row " << (i
+ 1) << ": expected='" << aChecks
[i
].pRes
<< "' actual='" << aStr
756 << aChecks
[i
].pVal
<< "'" << endl
;
757 CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
762 template <size_t DataSize
, size_t FormulaSize
, int Type
>
763 void TestFormula2::runTestHorizontalMATCH(ScDocument
* pDoc
, const char* aData
[DataSize
],
764 const StrStrCheck aChecks
[FormulaSize
])
766 size_t nDataSize
= DataSize
;
767 for (size_t i
= 0; i
< nDataSize
; ++i
)
768 pDoc
->SetString(i
, 0, 0, OUString::createFromAscii(aData
[i
]));
770 for (size_t i
= 0; i
< FormulaSize
; ++i
)
772 pDoc
->SetString(i
, 1, 0, OUString::createFromAscii(aChecks
[i
].pVal
));
774 // Assume we don't have more than 26 data columns...
775 OUString aFormula
= "=MATCH(" + OUStringChar(static_cast<sal_Unicode
>('A' + i
))
776 + "2;A1:" + OUStringChar(static_cast<sal_Unicode
>('A' + nDataSize
))
777 + "1;" + OUString::number(Type
) + ")";
778 pDoc
->SetString(i
, 2, 0, aFormula
);
782 printRange(pDoc
, ScRange(0, 0, 0, FormulaSize
- 1, 2, 0), "MATCH");
784 // verify the results.
785 for (size_t i
= 0; i
< FormulaSize
; ++i
)
787 OUString aStr
= pDoc
->GetString(i
, 2, 0);
788 if (!aStr
.equalsAscii(aChecks
[i
].pRes
))
790 cerr
<< "column " << char('A' + i
) << ": expected='" << aChecks
[i
].pRes
<< "' actual='"
794 << aChecks
[i
].pVal
<< "'" << endl
;
795 CPPUNIT_ASSERT_MESSAGE("Unexpected result for horizontal MATCH", false);
800 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncMATCH
)
802 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
804 clearRange(m_pDoc
, ScRange(0, 0, 0, 40, 40, 0));
806 // Ascending in-exact match
808 // data range (A1:A9)
809 const char* aData
[] = {
810 "1", "2", "3", "4", "5", "6", "7", "8", "9", "B", "B", "C",
814 static const StrStrCheck aChecks
[]
815 = { { "0.8", "#N/A" }, { "1.2", "1" }, { "2.3", "2" }, { "3.9", "3" },
816 { "4.1", "4" }, { "5.99", "5" }, { "6.1", "6" }, { "7.2", "7" },
817 { "8.569", "8" }, { "9.59", "9" }, { "10", "9" }, { "100", "9" },
818 { "Andy", "#N/A" }, { "Bruce", "11" }, { "Charlie", "12" } };
820 runTestMATCH
<SAL_N_ELEMENTS(aData
), SAL_N_ELEMENTS(aChecks
), 1>(m_pDoc
, aData
, aChecks
);
821 clearRange(m_pDoc
, ScRange(0, 0, 0, 4, 40, 0));
822 runTestHorizontalMATCH
<SAL_N_ELEMENTS(aData
), SAL_N_ELEMENTS(aChecks
), 1>(m_pDoc
, aData
,
824 clearRange(m_pDoc
, ScRange(0, 0, 0, 40, 4, 0));
828 // Descending in-exact match
830 // data range (A1:A9)
831 const char* aData
[] = { "D", "C", "B", "9", "8", "7", "6", "5", "4", "3", "2", "1" };
834 static const StrStrCheck aChecks
[]
835 = { { "10", "#N/A" }, { "8.9", "4" }, { "7.8", "5" }, { "6.7", "6" },
836 { "5.5", "7" }, { "4.6", "8" }, { "3.3", "9" }, { "2.2", "10" },
837 { "1.1", "11" }, { "0.8", "12" }, { "0", "12" }, { "-2", "12" },
838 { "Andy", "3" }, { "Bruce", "2" }, { "Charlie", "1" }, { "David", "#N/A" } };
840 runTestMATCH
<SAL_N_ELEMENTS(aData
), SAL_N_ELEMENTS(aChecks
), -1>(m_pDoc
, aData
, aChecks
);
841 clearRange(m_pDoc
, ScRange(0, 0, 0, 4, 40, 0));
842 runTestHorizontalMATCH
<SAL_N_ELEMENTS(aData
), SAL_N_ELEMENTS(aChecks
), -1>(m_pDoc
, aData
,
844 clearRange(m_pDoc
, ScRange(0, 0, 0, 40, 4, 0));
848 // search range contains leading and trailing empty cell ranges.
850 clearRange(m_pDoc
, ScRange(0, 0, 0, 2, 100, 0));
852 // A5:A8 contains sorted values.
853 m_pDoc
->SetValue(ScAddress(0, 4, 0), 1.0);
854 m_pDoc
->SetValue(ScAddress(0, 5, 0), 2.0);
855 m_pDoc
->SetValue(ScAddress(0, 6, 0), 3.0);
856 m_pDoc
->SetValue(ScAddress(0, 7, 0), 4.0);
858 // Find value 2 which is in A6.
859 m_pDoc
->SetString(ScAddress(1, 0, 0), u
"=MATCH(2;A1:A20)"_ustr
);
862 CPPUNIT_ASSERT_EQUAL(u
"6"_ustr
, m_pDoc
->GetString(ScAddress(1, 0, 0)));
866 // Test the ReferenceOrForceArray parameter.
868 clearRange(m_pDoc
, ScRange(0, 0, 0, 1, 7, 0));
870 // B1:B5 contain numeric values.
871 m_pDoc
->SetValue(ScAddress(1, 0, 0), 1.0);
872 m_pDoc
->SetValue(ScAddress(1, 1, 0), 2.0);
873 m_pDoc
->SetValue(ScAddress(1, 2, 0), 3.0);
874 m_pDoc
->SetValue(ScAddress(1, 3, 0), 4.0);
875 m_pDoc
->SetValue(ScAddress(1, 4, 0), 5.0);
877 // Find string value "33" in concatenated array, no implicit
878 // intersection is involved, array is forced.
879 m_pDoc
->SetString(ScAddress(0, 5, 0), u
"=MATCH(\"33\";B1:B5&B1:B5)"_ustr
);
881 CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc
->GetValue(ScAddress(0, 5, 0)));
884 m_pDoc
->DeleteTab(0);
887 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncCELL
)
889 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
891 clearRange(m_pDoc
, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
894 const char* pContent
= "Some random text";
895 m_pDoc
->SetString(2, 9, 0, OUString::createFromAscii(pContent
)); // Set this value to C10.
896 m_pDoc
->SetValue(2, 0, 0, 1.2); // Set numeric value to C1;
898 // We don't test: FILENAME, FORMAT, WIDTH, PROTECT, PREFIX
899 StrStrCheck aChecks
[]
900 = { { "=CELL(\"COL\";C10)", "3" }, { "=CELL(\"COL\";C5:C10)", "3" },
901 { "=CELL(\"ROW\";C10)", "10" }, { "=CELL(\"ROW\";C10:E10)", "10" },
902 { "=CELL(\"SHEET\";C10)", "1" }, { "=CELL(\"ADDRESS\";C10)", "$C$10" },
903 { "=CELL(\"CONTENTS\";C10)", pContent
}, { "=CELL(\"COLOR\";C10)", "0" },
904 { "=CELL(\"TYPE\";C9)", "b" }, { "=CELL(\"TYPE\";C10)", "l" },
905 { "=CELL(\"TYPE\";C1)", "v" }, { "=CELL(\"PARENTHESES\";C10)", "0" } };
907 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
908 m_pDoc
->SetString(0, i
, 0, OUString::createFromAscii(aChecks
[i
].pVal
));
911 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
913 OUString aVal
= m_pDoc
->GetString(0, i
, 0);
914 CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal
.equalsAscii(aChecks
[i
].pRes
));
918 m_pDoc
->DeleteTab(0);
921 /** See also test case document fdo#44456 sheet cpearson */
922 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncDATEDIF
)
924 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
926 std::vector
<std::vector
<const char*>> aData
= {
927 { "2007-01-01", "2007-01-10", "d", "9", "=DATEDIF(A1;B1;C1)" },
928 { "2007-01-01", "2007-01-31", "m", "0", "=DATEDIF(A2;B2;C2)" },
929 { "2007-01-01", "2007-02-01", "m", "1", "=DATEDIF(A3;B3;C3)" },
930 { "2007-01-01", "2007-02-28", "m", "1", "=DATEDIF(A4;B4;C4)" },
931 { "2007-01-01", "2007-12-31", "d", "364", "=DATEDIF(A5;B5;C5)" },
932 { "2007-01-01", "2007-01-31", "y", "0", "=DATEDIF(A6;B6;C6)" },
933 { "2007-01-01", "2008-07-01", "d", "547", "=DATEDIF(A7;B7;C7)" },
934 { "2007-01-01", "2008-07-01", "m", "18", "=DATEDIF(A8;B8;C8)" },
935 { "2007-01-01", "2008-07-01", "ym", "6", "=DATEDIF(A9;B9;C9)" },
936 { "2007-01-01", "2008-07-01", "yd", "182", "=DATEDIF(A10;B10;C10)" },
937 { "2008-01-01", "2009-07-01", "yd", "181", "=DATEDIF(A11;B11;C11)" },
938 { "2007-01-01", "2007-01-31", "md", "30", "=DATEDIF(A12;B12;C12)" },
939 { "2007-02-01", "2009-03-01", "md", "0", "=DATEDIF(A13;B13;C13)" },
940 { "2008-02-01", "2009-03-01", "md", "0", "=DATEDIF(A14;B14;C14)" },
941 { "2007-01-02", "2007-01-01", "md", "Err:502",
942 "=DATEDIF(A15;B15;C15)" } // fail date1 > date2
945 clearRange(m_pDoc
, ScRange(0, 0, 0, 4, aData
.size(), 0));
946 ScAddress
aPos(0, 0, 0);
947 ScRange aDataRange
= insertRangeData(m_pDoc
, aPos
, aData
);
948 CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos
,
953 for (size_t i
= 0; i
< aData
.size(); ++i
)
955 OUString aVal
= m_pDoc
->GetString(4, i
, 0);
956 //std::cout << "row "<< i << ": " << OUStringToOString( aVal, RTL_TEXTENCODING_UTF8).getStr() << ", expected " << aData[i][3] << std::endl;
957 CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal
.equalsAscii(aData
[i
][3]));
960 m_pDoc
->DeleteTab(0);
963 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncINDIRECT
)
965 OUString
aTabName(u
"foo"_ustr
);
966 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, aTabName
));
967 clearRange(m_pDoc
, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
969 bool bGood
= m_pDoc
->GetName(0, aTabName
);
970 CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood
);
972 OUString aTest
= u
"Test"_ustr
, aRefErr
= u
"#REF!"_ustr
;
973 m_pDoc
->SetString(0, 10, 0, aTest
);
974 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cell value.", aTest
, m_pDoc
->GetString(0, 10, 0));
976 OUString aPrefix
= u
"=INDIRECT(\""_ustr
;
978 OUString aFormula
= aPrefix
+ aTabName
+ ".A11\")"; // Calc A1
979 m_pDoc
->SetString(0, 0, 0, aFormula
);
980 aFormula
= aPrefix
+ aTabName
+ "!A11\")"; // Excel A1
981 m_pDoc
->SetString(0, 1, 0, aFormula
);
982 aFormula
= aPrefix
+ aTabName
+ "!R11C1\")"; // Excel R1C1
983 m_pDoc
->SetString(0, 2, 0, aFormula
);
984 aFormula
= aPrefix
+ aTabName
+ "!R11C1\";0)"; // Excel R1C1 (forced)
985 m_pDoc
->SetString(0, 3, 0, aFormula
);
989 // Default (for new documents) is to use current formula syntax
991 const OUString
* aChecks
[] = { &aTest
, &aRefErr
, &aRefErr
, &aTest
};
993 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
995 OUString aVal
= m_pDoc
->GetString(0, i
, 0);
996 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks
[i
], aVal
);
1000 ScCalcConfig aConfig
;
1001 aConfig
.SetStringRefSyntax(formula::FormulaGrammar::CONV_OOO
);
1002 m_pDoc
->SetCalcConfig(aConfig
);
1005 // Explicit Calc A1 syntax
1006 const OUString
* aChecks
[] = { &aTest
, &aRefErr
, &aRefErr
, &aTest
};
1008 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
1010 OUString aVal
= m_pDoc
->GetString(0, i
, 0);
1011 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks
[i
], aVal
);
1015 aConfig
.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_A1
);
1016 m_pDoc
->SetCalcConfig(aConfig
);
1020 const OUString
* aChecks
[] = { &aRefErr
, &aTest
, &aRefErr
, &aTest
};
1022 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
1024 OUString aVal
= m_pDoc
->GetString(0, i
, 0);
1025 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks
[i
], aVal
);
1029 aConfig
.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1
);
1030 m_pDoc
->SetCalcConfig(aConfig
);
1033 // Excel R1C1 syntax
1034 const OUString
* aChecks
[] = { &aRefErr
, &aRefErr
, &aTest
, &aTest
};
1036 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
1038 OUString aVal
= m_pDoc
->GetString(0, i
, 0);
1039 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks
[i
], aVal
);
1043 m_pDoc
->DeleteTab(0);
1046 // Test case for tdf#83365 - Access across spreadsheet returns Err:504
1048 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncINDIRECT2
)
1050 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
1051 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(1, u
"bar"_ustr
));
1052 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(2, u
"baz"_ustr
));
1054 m_pDoc
->SetValue(0, 0, 0, 10.0);
1055 m_pDoc
->SetValue(0, 1, 0, 10.0);
1056 m_pDoc
->SetValue(0, 2, 0, 10.0);
1058 // Fill range bar.$A1:bar.$A10 with 1s
1059 for (SCROW i
= 0; i
< 10; ++i
)
1060 m_pDoc
->SetValue(0, i
, 1, 1.0);
1062 // Test range triplet (absolute, relative, relative) : (absolute, relative, relative)
1063 m_pDoc
->SetString(0, 0, 2, u
"=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$1),1)"_ustr
);
1065 // Test range triplet (absolute, relative, relative) : (absolute, absolute, relative)
1066 m_pDoc
->SetString(0, 1, 2, u
"=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$2),1)"_ustr
);
1068 // Test range triplet (absolute, relative, relative) : (absolute, absolute, absolute)
1069 m_pDoc
->SetString(0, 2, 2, u
"=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$3),1)"_ustr
);
1071 // Test range triplet (absolute, absolute, relative) : (absolute, relative, relative)
1072 m_pDoc
->SetString(0, 3, 2, u
"=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$1),1)"_ustr
);
1074 // Test range triplet (absolute, absolute, relative) : (absolute, absolute, relative)
1075 m_pDoc
->SetString(0, 4, 2, u
"=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$2),1)"_ustr
);
1077 // Test range triplet (absolute, absolute, relative) : (absolute, absolute, relative)
1078 m_pDoc
->SetString(0, 5, 2, u
"=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$3),1)"_ustr
);
1080 // Test range triplet (absolute, absolute, absolute) : (absolute, relative, relative)
1081 m_pDoc
->SetString(0, 6, 2, u
"=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$1),1)"_ustr
);
1083 // Test range triplet (absolute, absolute, absolute) : (absolute, absolute, relative)
1084 m_pDoc
->SetString(0, 7, 2, u
"=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$2),1)"_ustr
);
1086 // Check indirect reference "bar.$A\"&foo.$A$1
1087 m_pDoc
->SetString(0, 8, 2, u
"=COUNTIF(bar.$A$1:INDIRECT(\"bar.$A\"&foo.$A$1),1)"_ustr
);
1089 // This case should return illegal argument error because
1090 // they reference 2 different absolute sheets
1091 // Test range triplet (absolute, absolute, absolute) : (absolute, absolute, absolute)
1092 m_pDoc
->SetString(0, 9, 2, u
"=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$3),1)"_ustr
);
1096 // Loop all formulas and check result = 10.0
1097 for (SCROW i
= 0; i
< 9; ++i
)
1098 CPPUNIT_ASSERT_MESSAGE(
1099 OString("Failed to INDIRECT reference formula value: " + OString::number(i
)).getStr(),
1100 m_pDoc
->GetValue(0, i
, 2) != 10.0);
1102 // Check formula cell error
1103 ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(0, 9, 2));
1104 CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC
);
1105 CPPUNIT_ASSERT_MESSAGE("This formula cell should be an error.",
1106 pFC
->GetErrCode() != FormulaError::NONE
);
1108 m_pDoc
->DeleteTab(2);
1109 m_pDoc
->DeleteTab(1);
1110 m_pDoc
->DeleteTab(0);
1113 // Test for tdf#107724 do not propagate an array context from MATCH to INDIRECT
1114 // as INDIRECT returns ParamClass::Reference
1115 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFunc_MATCH_INDIRECT
)
1117 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
1119 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1121 ScRangeName
* pGlobalNames
= m_pDoc
->GetRangeName();
1122 ScRangeData
* pRangeData
= new ScRangeData(*m_pDoc
, u
"RoleAssignment"_ustr
, u
"$D$4:$D$13"_ustr
);
1123 pGlobalNames
->insert(pRangeData
);
1125 // D6: data to match, in 3rd row of named range.
1126 m_pDoc
->SetString(3, 5, 0, u
"Test1"_ustr
);
1127 // F15: Formula generating indirect reference of corner addresses taking
1128 // row+offset and column from named range, which are not in array context
1129 // thus don't create arrays of offsets.
1130 m_pDoc
->SetString(5, 14, 0,
1131 u
"=MATCH(\"Test1\";INDIRECT(ADDRESS(ROW(RoleAssignment)+1;COLUMN("
1132 "RoleAssignment))&\":\"&ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;"
1133 "COLUMN(RoleAssignment)));0)"_ustr
);
1135 // Match in 2nd row of range offset by 1 expected.
1136 ASSERT_DOUBLES_EQUAL_MESSAGE("Failed to not propagate array context from MATCH to INDIRECT",
1137 2.0, m_pDoc
->GetValue(5, 14, 0));
1139 m_pDoc
->DeleteTab(0);
1142 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaDepTracking
)
1144 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
1146 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1148 const ScAddress
aA5(0, 4, 0);
1149 const ScAddress
aB2(1, 1, 0);
1150 const ScAddress
aB5(1, 4, 0);
1151 const ScAddress
aC5(2, 4, 0);
1152 const ScAddress
aD2(3, 1, 0);
1153 const ScAddress
aD5(3, 4, 0);
1154 const ScAddress
aD6(3, 5, 0);
1155 const ScAddress
aE2(4, 1, 0);
1156 const ScAddress
aE3(4, 2, 0);
1157 const ScAddress
aE6(4, 5, 0);
1159 // B2 listens on D2.
1160 m_pDoc
->SetString(aB2
, u
"=D2"_ustr
);
1161 double val
= m_pDoc
->GetValue(aB2
);
1162 ASSERT_DOUBLES_EQUAL_MESSAGE("Referencing an empty cell should yield zero.", 0.0, val
);
1165 // Check the internal broadcaster state.
1166 auto aState
= m_pDoc
->GetBroadcasterState();
1167 aState
.dump(std::cout
, m_pDoc
);
1168 CPPUNIT_ASSERT(aState
.hasFormulaCellListener(aD2
, aB2
));
1171 // Changing the value of D2 should trigger recalculation of B2.
1172 m_pDoc
->SetValue(aD2
, 1.1);
1173 val
= m_pDoc
->GetValue(aB2
);
1174 ASSERT_DOUBLES_EQUAL_MESSAGE("Failed to recalculate on value change.", 1.1, val
);
1177 m_pDoc
->SetValue(aD2
, 2.2);
1178 val
= m_pDoc
->GetValue(aB2
);
1179 ASSERT_DOUBLES_EQUAL_MESSAGE("Failed to recalculate on value change.", 2.2, val
);
1181 clearRange(m_pDoc
, ScRange(0, 0, 0, 10, 10, 0));
1184 // Make sure nobody is listening on anything.
1185 auto aState
= m_pDoc
->GetBroadcasterState();
1186 aState
.dump(std::cout
, m_pDoc
);
1187 CPPUNIT_ASSERT(aState
.aCellListenerStore
.empty());
1190 // Now, let's test the range dependency tracking.
1192 // B2 listens on D2:E6.
1193 m_pDoc
->SetString(aB2
, u
"=SUM(D2:E6)"_ustr
);
1194 val
= m_pDoc
->GetValue(aB2
);
1195 ASSERT_DOUBLES_EQUAL_MESSAGE("Summing an empty range should yield zero.", 0.0, val
);
1198 // Check the internal state to make sure it matches.
1199 auto aState
= m_pDoc
->GetBroadcasterState();
1200 aState
.dump(std::cout
, m_pDoc
);
1201 CPPUNIT_ASSERT(aState
.hasFormulaCellListener({ aD2
, aE6
}, aB2
));
1204 // Set value to E3. This should trigger recalc on B2.
1205 m_pDoc
->SetValue(aE3
, 2.4);
1206 val
= m_pDoc
->GetValue(aB2
);
1207 ASSERT_DOUBLES_EQUAL_MESSAGE("Failed to recalculate on single value change.", 2.4, val
);
1209 // Set value to D5 to trigger recalc again. Note that this causes an
1210 // addition of 1.2 + 2.4 which is subject to binary floating point
1211 // rounding error. We need to use approxEqual to assess its value.
1213 m_pDoc
->SetValue(aD5
, 1.2);
1214 val
= m_pDoc
->GetValue(aB2
);
1215 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1216 rtl::math::approxEqual(val
, 3.6));
1218 // Change the value of D2 (boundary case).
1219 m_pDoc
->SetValue(aD2
, 1.0);
1220 val
= m_pDoc
->GetValue(aB2
);
1221 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1222 rtl::math::approxEqual(val
, 4.6));
1224 // Change the value of E6 (another boundary case).
1225 m_pDoc
->SetValue(aE6
, 2.0);
1226 val
= m_pDoc
->GetValue(aB2
);
1227 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1228 rtl::math::approxEqual(val
, 6.6));
1230 // Change the value of D6 (another boundary case).
1231 m_pDoc
->SetValue(aD6
, 3.0);
1232 val
= m_pDoc
->GetValue(aB2
);
1233 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1234 rtl::math::approxEqual(val
, 9.6));
1236 // Change the value of E2 (another boundary case).
1237 m_pDoc
->SetValue(aE2
, 0.4);
1238 val
= m_pDoc
->GetValue(aB2
);
1239 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1240 rtl::math::approxEqual(val
, 10.0));
1242 // Change the existing non-empty value cell (E2).
1243 m_pDoc
->SetValue(aE2
, 2.4);
1244 val
= m_pDoc
->GetValue(aB2
);
1245 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.",
1246 rtl::math::approxEqual(val
, 12.0));
1248 clearRange(m_pDoc
, ScRange(0, 0, 0, 10, 10, 0));
1250 // Now, column-based dependency tracking. We now switch to the R1C1
1251 // syntax which is easier to use for repeated relative references.
1253 FormulaGrammarSwitch
aFGSwitch(m_pDoc
, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1
);
1256 for (SCROW nRow
= 1; nRow
<= 9; ++nRow
)
1258 // Static value in column 1.
1259 m_pDoc
->SetValue(0, nRow
, 0, ++val
);
1261 // Formula in column 2 that references cell to the left.
1262 m_pDoc
->SetString(1, nRow
, 0, u
"=RC[-1]"_ustr
);
1264 // Formula in column 3 that references cell to the left.
1265 m_pDoc
->SetString(2, nRow
, 0, u
"=RC[-1]*2"_ustr
);
1268 // Check formula values.
1270 for (SCROW nRow
= 1; nRow
<= 9; ++nRow
)
1273 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected formula value.", val
,
1274 m_pDoc
->GetValue(1, nRow
, 0));
1275 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected formula value.", val
* 2.0,
1276 m_pDoc
->GetValue(2, nRow
, 0));
1279 // Intentionally insert a formula in column 1. This will break column 1's
1280 // uniformity of consisting only of static value cells.
1281 m_pDoc
->SetString(aA5
, u
"=R2C3"_ustr
);
1282 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected formula value.", 2.0, m_pDoc
->GetValue(aA5
));
1283 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected formula value.", 2.0, m_pDoc
->GetValue(aB5
));
1284 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected formula value.", 4.0, m_pDoc
->GetValue(aC5
));
1286 m_pDoc
->DeleteTab(0);
1289 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaDepTracking2
)
1291 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"foo"_ustr
));
1293 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1296 m_pDoc
->SetValue(0, 0, 0, val
);
1298 m_pDoc
->SetValue(1, 0, 0, val
);
1300 m_pDoc
->SetValue(0, 1, 0, val
);
1301 m_pDoc
->SetString(2, 0, 0, u
"=A1/B1"_ustr
);
1302 m_pDoc
->SetString(1, 1, 0, u
"=B1*C1"_ustr
);
1304 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(1, 1, 0)); // B2 should equal 2.
1306 clearRange(m_pDoc
, ScRange(ScAddress(2, 0, 0))); // Delete C1.
1308 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(1, 1, 0)); // B2 should now equal 0.
1310 m_pDoc
->DeleteTab(0);
1313 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaDepTracking3
)
1315 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1317 m_pDoc
->InsertTab(0, u
"Formula"_ustr
);
1319 std::vector
<std::vector
<const char*>> aData
= {
1320 { "1", "2", "=SUM(A1:B1)", "=SUM(C1:C3)" },
1321 { "3", "4", "=SUM(A2:B2)", nullptr },
1322 { "5", "6", "=SUM(A3:B3)", nullptr },
1325 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
);
1327 // Check the initial formula results.
1328 CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc
->GetValue(ScAddress(2, 0, 0)));
1329 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc
->GetValue(ScAddress(2, 1, 0)));
1330 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc
->GetValue(ScAddress(2, 2, 0)));
1331 CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc
->GetValue(ScAddress(3, 0, 0)));
1333 // Change B3 and make sure the change gets propagated to D1.
1334 ScDocFunc
& rFunc
= m_xDocShell
->GetDocFunc();
1335 rFunc
.SetValueCell(ScAddress(1, 2, 0), 60.0, false);
1336 CPPUNIT_ASSERT_EQUAL(65.0, m_pDoc
->GetValue(ScAddress(2, 2, 0)));
1337 CPPUNIT_ASSERT_EQUAL(75.0, m_pDoc
->GetValue(ScAddress(3, 0, 0)));
1339 m_pDoc
->DeleteTab(0);
1342 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaDepTrackingDeleteRow
)
1344 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1346 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
1349 m_pDoc
->SetValue(ScAddress(0, 0, 0), 1.0);
1350 m_pDoc
->SetValue(ScAddress(0, 1, 0), 3.0);
1351 m_pDoc
->SetValue(ScAddress(0, 2, 0), 5.0);
1353 // SUM(A1:A3) in A5.
1354 m_pDoc
->SetString(ScAddress(0, 4, 0), u
"=SUM(A1:A3)"_ustr
);
1356 // A6 to reference A5.
1357 m_pDoc
->SetString(ScAddress(0, 5, 0), u
"=A5*10"_ustr
);
1358 const ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(0, 5, 0));
1359 CPPUNIT_ASSERT(pFC
);
1361 // A4 should have a broadcaster with A5 listening to it.
1362 SvtBroadcaster
* pBC
= m_pDoc
->GetBroadcaster(ScAddress(0, 4, 0));
1363 CPPUNIT_ASSERT(pBC
);
1364 SvtBroadcaster::ListenersType
* pListeners
= &pBC
->GetAllListeners();
1365 CPPUNIT_ASSERT_EQUAL_MESSAGE("A5 should have one listener.", size_t(1), pListeners
->size());
1366 const SvtListener
* pListener
= pListeners
->at(0);
1367 CPPUNIT_ASSERT_EQUAL_MESSAGE("A6 should be listening to A5.",
1368 static_cast<const ScFormulaCell
*>(pListener
), pFC
);
1370 // Check initial values.
1371 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc
->GetValue(ScAddress(0, 4, 0)));
1372 CPPUNIT_ASSERT_EQUAL(90.0, m_pDoc
->GetValue(ScAddress(0, 5, 0)));
1375 ScDocFunc
& rFunc
= m_xDocShell
->GetDocFunc();
1376 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
1377 aMark
.SelectOneTable(0);
1378 rFunc
.DeleteCells(ScRange(0, 1, 0, m_pDoc
->MaxCol(), 1, 0), &aMark
, DelCellCmd::CellsUp
, true);
1380 pBC
= m_pDoc
->GetBroadcaster(ScAddress(0, 3, 0));
1381 CPPUNIT_ASSERT_MESSAGE("Broadcaster at A5 should have shifted to A4.", pBC
);
1382 pListeners
= &pBC
->GetAllListeners();
1383 CPPUNIT_ASSERT_EQUAL_MESSAGE("A3 should have one listener.", size_t(1), pListeners
->size());
1384 pFC
= m_pDoc
->GetFormulaCell(ScAddress(0, 4, 0));
1385 CPPUNIT_ASSERT(pFC
);
1386 pListener
= pListeners
->at(0);
1387 CPPUNIT_ASSERT_EQUAL_MESSAGE("A5 should be listening to A4.",
1388 static_cast<const ScFormulaCell
*>(pListener
), pFC
);
1390 // Check values after row deletion.
1391 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc
->GetValue(ScAddress(0, 3, 0)));
1392 CPPUNIT_ASSERT_EQUAL(60.0, m_pDoc
->GetValue(ScAddress(0, 4, 0)));
1394 m_pDoc
->DeleteTab(0);
1397 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaDepTrackingDeleteCol
)
1399 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1401 m_pDoc
->InsertTab(0, u
"Formula"_ustr
);
1403 std::vector
<std::vector
<const char*>> aData
= {
1404 { "2", "=A1", "=B1" }, // not grouped
1405 { nullptr, nullptr, nullptr }, // empty row to separate the formula groups.
1406 { "3", "=A3", "=B3" }, // grouped
1407 { "4", "=A4", "=B4" }, // grouped
1410 ScAddress
aPos(0, 0, 0);
1411 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
1412 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
1414 // Check the initial values.
1415 for (SCCOL i
= 0; i
<= 2; ++i
)
1417 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(i
, 0, 0)));
1418 CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc
->GetValue(ScAddress(i
, 2, 0)));
1419 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc
->GetValue(ScAddress(i
, 3, 0)));
1422 // Make sure B3:B4 and C3:C4 are grouped.
1423 const ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(1, 2, 0));
1424 CPPUNIT_ASSERT(pFC
);
1425 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW
>(2), pFC
->GetSharedTopRow());
1426 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW
>(2), pFC
->GetSharedLength());
1428 pFC
= m_pDoc
->GetFormulaCell(ScAddress(2, 2, 0));
1429 CPPUNIT_ASSERT(pFC
);
1430 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW
>(2), pFC
->GetSharedTopRow());
1431 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW
>(2), pFC
->GetSharedLength());
1433 // Delete column A. A1, B1, A3:A4 and B3:B4 should all show #REF!.
1434 ScDocFunc
& rFunc
= m_xDocShell
->GetDocFunc();
1435 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
1436 aMark
.SelectOneTable(0);
1437 rFunc
.DeleteCells(ScRange(0, 0, 0, 0, m_pDoc
->MaxRow(), 0), &aMark
, DelCellCmd::CellsLeft
,
1441 // Expected output table content. 0 = empty cell
1442 std::vector
<std::vector
<const char*>> aOutputCheck
= {
1443 { "#REF!", "#REF!" },
1444 { nullptr, nullptr },
1445 { "#REF!", "#REF!" },
1446 { "#REF!", "#REF!" },
1449 ScRange
aCheckRange(0, 0, 0, 1, 3, 0);
1451 = checkOutput(m_pDoc
, aCheckRange
, aOutputCheck
, "Check after deleting column A");
1452 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess
);
1455 // Undo and check the result.
1456 SfxUndoManager
* pUndoMgr
= m_pDoc
->GetUndoManager();
1457 CPPUNIT_ASSERT(pUndoMgr
);
1461 // Expected output table content. 0 = empty cell
1462 std::vector
<std::vector
<const char*>> aOutputCheck
= {
1464 { nullptr, nullptr, nullptr },
1469 ScRange
aCheckRange(0, 0, 0, 2, 3, 0);
1470 bool bSuccess
= checkOutput(m_pDoc
, aCheckRange
, aOutputCheck
, "Check after undo");
1471 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess
);
1477 // Expected output table content. 0 = empty cell
1478 std::vector
<std::vector
<const char*>> aOutputCheck
= {
1479 { "#REF!", "#REF!" },
1480 { nullptr, nullptr },
1481 { "#REF!", "#REF!" },
1482 { "#REF!", "#REF!" },
1485 ScRange
aCheckRange(0, 0, 0, 1, 3, 0);
1486 bool bSuccess
= checkOutput(m_pDoc
, aCheckRange
, aOutputCheck
, "Check after redo");
1487 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess
);
1490 // Undo and change the values in column A.
1492 m_pDoc
->SetValue(ScAddress(0, 0, 0), 22.0);
1493 m_pDoc
->SetValue(ScAddress(0, 2, 0), 23.0);
1494 m_pDoc
->SetValue(ScAddress(0, 3, 0), 24.0);
1497 // Expected output table content. 0 = empty cell
1498 std::vector
<std::vector
<const char*>> aOutputCheck
= {
1499 { "22", "22", "22" },
1500 { nullptr, nullptr, nullptr },
1501 { "23", "23", "23" },
1502 { "24", "24", "24" },
1505 ScRange
aCheckRange(0, 0, 0, 2, 3, 0);
1506 bool bSuccess
= checkOutput(m_pDoc
, aCheckRange
, aOutputCheck
,
1507 "Check after undo & value change in column A");
1508 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess
);
1511 m_pDoc
->DeleteTab(0);
1514 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaMatrixResultUpdate
)
1516 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
1518 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calculation.
1520 // Set a numeric value to A1.
1521 m_pDoc
->SetValue(ScAddress(0, 0, 0), 11.0);
1523 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
1524 aMark
.SelectOneTable(0);
1525 m_pDoc
->InsertMatrixFormula(1, 0, 1, 0, aMark
, u
"=A1"_ustr
);
1526 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
1527 ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(1, 0, 0));
1528 CPPUNIT_ASSERT_MESSAGE("Failed to get formula cell.", pFC
);
1530 false); // Clear this flag to simulate displaying of formula cell value on screen.
1532 m_pDoc
->SetString(ScAddress(0, 0, 0), u
"ABC"_ustr
);
1533 CPPUNIT_ASSERT_EQUAL(u
"ABC"_ustr
, m_pDoc
->GetString(ScAddress(1, 0, 0)));
1534 pFC
->SetChanged(false);
1536 // Put a new value into A1. The formula should update.
1537 m_pDoc
->SetValue(ScAddress(0, 0, 0), 13.0);
1538 CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
1540 m_pDoc
->DeleteTab(0);
1543 CPPUNIT_TEST_FIXTURE(TestFormula2
, testExternalRef
)
1545 ScDocShellRef xExtDocSh
= new ScDocShell
;
1546 OUString
aExtDocName(u
"file:///extdata.fake"_ustr
);
1547 OUString
aExtSh1Name(u
"Data1"_ustr
);
1548 OUString
aExtSh2Name(u
"Data2"_ustr
);
1549 OUString
aExtSh3Name(u
"Data3"_ustr
);
1550 SfxMedium
* pMed
= new SfxMedium(aExtDocName
, StreamMode::STD_READWRITE
);
1551 xExtDocSh
->DoLoad(pMed
);
1552 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
1553 findLoadedDocShellByName(aExtDocName
) != nullptr);
1555 // Populate the external source document.
1556 ScDocument
& rExtDoc
= xExtDocSh
->GetDocument();
1557 rExtDoc
.InsertTab(0, aExtSh1Name
);
1558 rExtDoc
.InsertTab(1, aExtSh2Name
);
1559 rExtDoc
.InsertTab(2, aExtSh3Name
);
1561 static OUString
constexpr name(u
"Name"_ustr
);
1562 static OUString
constexpr value(u
"Value"_ustr
);
1565 rExtDoc
.SetString(0, 0, 0, name
);
1566 rExtDoc
.SetString(0, 1, 0, u
"Andy"_ustr
);
1567 rExtDoc
.SetString(0, 2, 0, u
"Bruce"_ustr
);
1568 rExtDoc
.SetString(0, 3, 0, u
"Charlie"_ustr
);
1569 rExtDoc
.SetString(0, 4, 0, u
"David"_ustr
);
1570 rExtDoc
.SetString(1, 0, 0, value
);
1572 rExtDoc
.SetValue(1, 1, 0, val
);
1574 rExtDoc
.SetValue(1, 2, 0, val
);
1576 rExtDoc
.SetValue(1, 3, 0, val
);
1578 rExtDoc
.SetValue(1, 4, 0, val
);
1580 // Sheet 2 remains empty.
1583 rExtDoc
.SetString(0, 0, 2, name
);
1584 rExtDoc
.SetString(0, 1, 2, u
"Edward"_ustr
);
1585 rExtDoc
.SetString(0, 2, 2, u
"Frank"_ustr
);
1586 rExtDoc
.SetString(0, 3, 2, u
"George"_ustr
);
1587 rExtDoc
.SetString(0, 4, 2, u
"Henry"_ustr
);
1588 rExtDoc
.SetString(1, 0, 2, value
);
1590 rExtDoc
.SetValue(1, 1, 2, val
);
1592 rExtDoc
.SetValue(1, 2, 2, val
);
1594 rExtDoc
.SetValue(1, 3, 2, val
);
1596 rExtDoc
.SetValue(1, 4, 2, val
);
1598 // Test external references on the main document while the external
1599 // document is still in memory.
1600 m_pDoc
->InsertTab(0, u
"Test Sheet"_ustr
);
1601 m_pDoc
->SetString(0, 0, 0, u
"='file:///extdata.fake'#Data1.A1"_ustr
);
1602 OUString test
= m_pDoc
->GetString(0, 0, 0);
1603 CPPUNIT_ASSERT_EQUAL_MESSAGE("Value is different from the original", name
, test
);
1605 // After the initial access to the external document, the external ref
1606 // manager should create sheet cache entries for *all* sheets from that
1607 // document. Note that the doc may have more than 3 sheets but ensure
1608 // that the first 3 are what we expect.
1609 ScExternalRefManager
* pRefMgr
= m_pDoc
->GetExternalRefManager();
1610 sal_uInt16 nFileId
= pRefMgr
->getExternalFileId(aExtDocName
);
1611 vector
<OUString
> aTabNames
;
1612 pRefMgr
->getAllCachedTableNames(nFileId
, aTabNames
);
1613 CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames
.size() >= 3);
1614 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected sheet name.", aTabNames
[0], aExtSh1Name
);
1615 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected sheet name.", aTabNames
[1], aExtSh2Name
);
1616 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected sheet name.", aTabNames
[2], aExtSh3Name
);
1618 m_pDoc
->SetString(1, 0, 0, u
"='file:///extdata.fake'#Data1.B1"_ustr
);
1619 test
= m_pDoc
->GetString(1, 0, 0);
1620 CPPUNIT_ASSERT_EQUAL_MESSAGE("Value is different from the original", value
, test
);
1622 m_pDoc
->SetString(0, 1, 0, u
"='file:///extdata.fake'#Data1.A2"_ustr
);
1623 m_pDoc
->SetString(0, 2, 0, u
"='file:///extdata.fake'#Data1.A3"_ustr
);
1624 m_pDoc
->SetString(0, 3, 0, u
"='file:///extdata.fake'#Data1.A4"_ustr
);
1625 m_pDoc
->SetString(0, 4, 0, u
"='file:///extdata.fake'#Data1.A5"_ustr
);
1626 m_pDoc
->SetString(0, 5, 0, u
"='file:///extdata.fake'#Data1.A6"_ustr
);
1629 // Referencing an empty cell should display '0'.
1630 const char* pChecks
[] = { "Andy", "Bruce", "Charlie", "David", "0" };
1631 for (size_t i
= 0; i
< SAL_N_ELEMENTS(pChecks
); ++i
)
1633 test
= m_pDoc
->GetString(0, static_cast<SCROW
>(i
+ 1), 0);
1634 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test
.equalsAscii(pChecks
[i
]));
1637 m_pDoc
->SetString(1, 1, 0, u
"='file:///extdata.fake'#Data1.B2"_ustr
);
1638 m_pDoc
->SetString(1, 2, 0, u
"='file:///extdata.fake'#Data1.B3"_ustr
);
1639 m_pDoc
->SetString(1, 3, 0, u
"='file:///extdata.fake'#Data1.B4"_ustr
);
1640 m_pDoc
->SetString(1, 4, 0, u
"='file:///extdata.fake'#Data1.B5"_ustr
);
1641 m_pDoc
->SetString(1, 5, 0, u
"='file:///extdata.fake'#Data1.B6"_ustr
);
1643 double pChecks
[] = { 10, 11, 12, 13, 0 };
1644 for (size_t i
= 0; i
< SAL_N_ELEMENTS(pChecks
); ++i
)
1646 val
= m_pDoc
->GetValue(1, static_cast<SCROW
>(i
+ 1), 0);
1647 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected cell value.", pChecks
[i
], val
);
1651 m_pDoc
->SetString(2, 0, 0, u
"='file:///extdata.fake'#Data3.A1"_ustr
);
1652 m_pDoc
->SetString(2, 1, 0, u
"='file:///extdata.fake'#Data3.A2"_ustr
);
1653 m_pDoc
->SetString(2, 2, 0, u
"='file:///extdata.fake'#Data3.A3"_ustr
);
1654 m_pDoc
->SetString(2, 3, 0, u
"='file:///extdata.fake'#Data3.A4"_ustr
);
1656 const char* pChecks
[] = { "Name", "Edward", "Frank", "George" };
1657 for (size_t i
= 0; i
< SAL_N_ELEMENTS(pChecks
); ++i
)
1659 test
= m_pDoc
->GetString(2, static_cast<SCROW
>(i
), 0);
1660 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test
.equalsAscii(pChecks
[i
]));
1664 m_pDoc
->SetString(3, 0, 0, u
"='file:///extdata.fake'#Data3.B1"_ustr
);
1665 m_pDoc
->SetString(3, 1, 0, u
"='file:///extdata.fake'#Data3.B2"_ustr
);
1666 m_pDoc
->SetString(3, 2, 0, u
"='file:///extdata.fake'#Data3.B3"_ustr
);
1667 m_pDoc
->SetString(3, 3, 0, u
"='file:///extdata.fake'#Data3.B4"_ustr
);
1669 const char* pChecks
[] = { "Value", "99", "98", "97" };
1670 for (size_t i
= 0; i
< SAL_N_ELEMENTS(pChecks
); ++i
)
1672 test
= m_pDoc
->GetString(3, static_cast<SCROW
>(i
), 0);
1673 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test
.equalsAscii(pChecks
[i
]));
1677 // At this point, all accessed cell data from the external document should
1678 // have been cached.
1679 ScExternalRefCache::TableTypeRef pCacheTab
1680 = pRefMgr
->getCacheTable(nFileId
, aExtSh1Name
, false);
1681 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab
);
1682 ScRange aCachedRange
= getCachedRange(pCacheTab
);
1683 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCCOL(0),
1684 aCachedRange
.aStart
.Col());
1685 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCCOL(1),
1686 aCachedRange
.aEnd
.Col());
1687 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCROW(0),
1688 aCachedRange
.aStart
.Row());
1689 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCROW(4),
1690 aCachedRange
.aEnd
.Row());
1692 // Sheet2 is not referenced at all; the cache table shouldn't even exist.
1693 pCacheTab
= pRefMgr
->getCacheTable(nFileId
, aExtSh2Name
, false);
1694 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 2 should *not* exist.", !pCacheTab
);
1696 // Sheet3's row 5 is not referenced; it should not be cached.
1697 pCacheTab
= pRefMgr
->getCacheTable(nFileId
, aExtSh3Name
, false);
1698 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab
);
1699 aCachedRange
= getCachedRange(pCacheTab
);
1700 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCCOL(0),
1701 aCachedRange
.aStart
.Col());
1702 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCCOL(1),
1703 aCachedRange
.aEnd
.Col());
1704 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCROW(0),
1705 aCachedRange
.aStart
.Row());
1706 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cached data range.", SCROW(3),
1707 aCachedRange
.aEnd
.Row());
1709 // Unload the external document shell.
1710 xExtDocSh
->DoClose();
1711 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1712 !findLoadedDocShellByName(aExtDocName
));
1714 m_pDoc
->DeleteTab(0);
1717 CPPUNIT_TEST_FIXTURE(TestFormula2
, testExternalRangeName
)
1719 ScDocShellRef xExtDocSh
= new ScDocShell
;
1720 static OUString
constexpr aExtDocName(u
"file:///extdata.fake"_ustr
);
1721 SfxMedium
* pMed
= new SfxMedium(aExtDocName
, StreamMode::STD_READWRITE
);
1722 xExtDocSh
->DoLoad(pMed
);
1723 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
1724 findLoadedDocShellByName(aExtDocName
) != nullptr);
1726 ScDocument
& rExtDoc
= xExtDocSh
->GetDocument();
1727 rExtDoc
.InsertTab(0, u
"Data1"_ustr
);
1728 rExtDoc
.SetValue(0, 0, 0, 123.456);
1730 ScRangeName
* pRangeName
= rExtDoc
.GetRangeName();
1731 ScRangeData
* pRangeData
= new ScRangeData(rExtDoc
, u
"ExternalName"_ustr
, u
"$Data1.$A$1"_ustr
);
1732 pRangeName
->insert(pRangeData
);
1734 m_pDoc
->InsertTab(0, u
"Test Sheet"_ustr
);
1735 m_pDoc
->SetString(0, 1, 0, u
"='file:///extdata.fake'#ExternalName"_ustr
);
1737 double nVal
= m_pDoc
->GetValue(0, 1, 0);
1738 ASSERT_DOUBLES_EQUAL(123.456, nVal
);
1740 xExtDocSh
->DoClose();
1741 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1742 !findLoadedDocShellByName(aExtDocName
));
1743 m_pDoc
->DeleteTab(0);
1746 void TestFormula2::testExtRefFuncT(ScDocument
* pDoc
, ScDocument
& rExtDoc
)
1748 clearRange(pDoc
, ScRange(0, 0, 0, 1, 9, 0));
1749 clearRange(&rExtDoc
, ScRange(0, 0, 0, 1, 9, 0));
1751 rExtDoc
.SetString(0, 0, 0, u
"'1.2"_ustr
);
1752 rExtDoc
.SetString(0, 1, 0, u
"Foo"_ustr
);
1753 rExtDoc
.SetValue(0, 2, 0, 12.3);
1754 pDoc
->SetString(0, 0, 0, u
"=T('file:///extdata.fake'#Data.A1)"_ustr
);
1755 pDoc
->SetString(0, 1, 0, u
"=T('file:///extdata.fake'#Data.A2)"_ustr
);
1756 pDoc
->SetString(0, 2, 0, u
"=T('file:///extdata.fake'#Data.A3)"_ustr
);
1759 OUString aRes
= pDoc
->GetString(0, 0, 0);
1760 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected result with T.", u
"1.2"_ustr
, aRes
);
1761 aRes
= pDoc
->GetString(0, 1, 0);
1762 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected result with T.", u
"Foo"_ustr
, aRes
);
1763 aRes
= pDoc
->GetString(0, 2, 0);
1764 CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes
.isEmpty());
1767 void TestFormula2::testExtRefFuncOFFSET(ScDocument
* pDoc
, ScDocument
& rExtDoc
)
1769 clearRange(pDoc
, ScRange(0, 0, 0, 1, 9, 0));
1770 clearRange(&rExtDoc
, ScRange(0, 0, 0, 1, 9, 0));
1772 sc::AutoCalcSwitch
aACSwitch(*pDoc
, true);
1774 // External document has sheet named 'Data', and the internal doc has sheet named 'Test'.
1775 rExtDoc
.SetValue(ScAddress(0, 1, 0), 1.2); // Set 1.2 to A2.
1776 pDoc
->SetString(ScAddress(0, 0, 0), u
"=OFFSET('file:///extdata.fake'#Data.$A$1;1;0;1;1)"_ustr
);
1777 CPPUNIT_ASSERT_EQUAL(1.2, pDoc
->GetValue(ScAddress(0, 0, 0)));
1780 void TestFormula2::testExtRefFuncVLOOKUP(ScDocument
* pDoc
, ScDocument
& rExtDoc
)
1782 clearRange(pDoc
, ScRange(0, 0, 0, 1, 9, 0));
1783 clearRange(&rExtDoc
, ScRange(0, 0, 0, 1, 9, 0));
1785 // Populate the external document.
1786 rExtDoc
.SetString(ScAddress(0, 0, 0), u
"A1"_ustr
);
1787 rExtDoc
.SetString(ScAddress(0, 1, 0), u
"A2"_ustr
);
1788 rExtDoc
.SetString(ScAddress(0, 2, 0), u
"A3"_ustr
);
1789 rExtDoc
.SetString(ScAddress(0, 3, 0), u
"A4"_ustr
);
1790 rExtDoc
.SetString(ScAddress(0, 4, 0), u
"A5"_ustr
);
1792 rExtDoc
.SetString(ScAddress(1, 0, 0), u
"B1"_ustr
);
1793 rExtDoc
.SetString(ScAddress(1, 1, 0), u
"B2"_ustr
);
1794 rExtDoc
.SetString(ScAddress(1, 2, 0), u
"B3"_ustr
);
1795 rExtDoc
.SetString(ScAddress(1, 3, 0), u
"B4"_ustr
);
1796 rExtDoc
.SetString(ScAddress(1, 4, 0), u
"B5"_ustr
);
1798 // Put formula in the source document.
1800 pDoc
->SetString(ScAddress(0, 0, 0), u
"A2"_ustr
);
1803 pDoc
->SetString(ScAddress(1, 0, 0), u
"=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;1)"_ustr
);
1804 CPPUNIT_ASSERT_EQUAL(u
"B2"_ustr
, pDoc
->GetString(ScAddress(1, 0, 0)));
1806 // Sort order FALSE. It should return the same result.
1807 pDoc
->SetString(ScAddress(1, 0, 0), u
"=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;0)"_ustr
);
1808 CPPUNIT_ASSERT_EQUAL(u
"B2"_ustr
, pDoc
->GetString(ScAddress(1, 0, 0)));
1811 void TestFormula2::testExtRefConcat(ScDocument
* pDoc
, ScDocument
& rExtDoc
)
1813 clearRange(pDoc
, ScRange(0, 0, 0, 1, 9, 0));
1814 clearRange(&rExtDoc
, ScRange(0, 0, 0, 1, 9, 0));
1816 sc::AutoCalcSwitch
aACSwitch(*pDoc
, true);
1818 // String and number
1819 rExtDoc
.SetString(ScAddress(0, 0, 0), u
"Answer: "_ustr
);
1820 rExtDoc
.SetValue(ScAddress(0, 1, 0), 42);
1822 // Concat operation should combine string and number converted to string
1823 pDoc
->SetString(ScAddress(0, 0, 0),
1824 u
"='file:///extdata.fake'#Data.A1 & 'file:///extdata.fake'#Data.A2"_ustr
);
1825 CPPUNIT_ASSERT_EQUAL(u
"Answer: 42"_ustr
, pDoc
->GetString(ScAddress(0, 0, 0)));
1828 CPPUNIT_TEST_FIXTURE(TestFormula2
, testExternalRefFunctions
)
1830 #ifndef DISABLE_NAN_TESTS
1831 ScDocShellRef xExtDocSh
= new ScDocShell
;
1832 OUString
aExtDocName(u
"file:///extdata.fake"_ustr
);
1833 SfxMedium
* pMed
= new SfxMedium(aExtDocName
, StreamMode::STD_READWRITE
);
1834 xExtDocSh
->DoLoad(pMed
);
1835 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
1836 findLoadedDocShellByName(aExtDocName
) != nullptr);
1838 ScExternalRefManager
* pRefMgr
= m_pDoc
->GetExternalRefManager();
1839 CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr
);
1840 sal_uInt16 nFileId
= pRefMgr
->getExternalFileId(aExtDocName
);
1841 const OUString
* pFileName
= pRefMgr
->getExternalFileName(nFileId
);
1842 CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.", pFileName
);
1843 CPPUNIT_ASSERT_EQUAL_MESSAGE("file name registration has somehow failed.", aExtDocName
,
1846 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
1848 // Populate the external source document.
1849 ScDocument
& rExtDoc
= xExtDocSh
->GetDocument();
1850 rExtDoc
.InsertTab(0, u
"Data"_ustr
);
1852 rExtDoc
.SetValue(0, 0, 0, val
);
1853 // leave cell B1 empty.
1855 rExtDoc
.SetValue(0, 1, 0, val
);
1856 rExtDoc
.SetValue(1, 1, 0, val
);
1858 rExtDoc
.SetValue(0, 2, 0, val
);
1859 rExtDoc
.SetValue(1, 2, 0, val
);
1861 rExtDoc
.SetValue(0, 3, 0, val
);
1862 rExtDoc
.SetValue(1, 3, 0, val
);
1864 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
1868 const char* pFormula
;
1871 { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
1872 { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
1873 { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
1874 { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
1875 { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
1876 { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 },
1877 // Should not crash, MUST be 0,m_pDoc->MaxRow() and/or 0,m_pDoc->MaxCol() range (here both)
1878 // to yield a result instead of 1x1 error matrix.
1879 { "=SUM('file:///extdata.fake'#Data.1:1048576)", 19 }
1882 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
1884 m_pDoc
->SetString(0, 0, 0, OUString::createFromAscii(aChecks
[i
].pFormula
));
1885 val
= m_pDoc
->GetValue(0, 0, 0);
1886 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("unexpected result involving external ranges.",
1887 aChecks
[i
].fResult
, val
, 1e-15);
1890 // A huge external range should not crash, the matrix generated from the
1891 // external range reference should be 1x1 and have one error value.
1892 // XXX NOTE: in case we supported sparse matrix that can hold this large
1893 // areas these tests may be adapted.
1894 m_pDoc
->SetString(0, 0, 0, u
"=SUM('file:///extdata.fake'#Data.B1:AMJ1048575)"_ustr
);
1895 ScFormulaCell
* pFC
= m_pDoc
->GetFormulaCell(ScAddress(0, 0, 0));
1896 FormulaError nErr
= pFC
->GetErrCode();
1897 CPPUNIT_ASSERT_EQUAL_MESSAGE(
1898 "huge external range reference expected to yield FormulaError::MatrixSize",
1899 int(FormulaError::MatrixSize
), static_cast<int>(nErr
));
1901 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
1902 aMark
.SelectOneTable(0);
1903 m_pDoc
->InsertMatrixFormula(0, 0, 0, 0, aMark
,
1904 u
"'file:///extdata.fake'#Data.B1:AMJ1048575"_ustr
);
1905 pFC
= m_pDoc
->GetFormulaCell(ScAddress(0, 0, 0));
1906 nErr
= pFC
->GetErrCode();
1907 CPPUNIT_ASSERT_EQUAL_MESSAGE(
1908 "huge external range reference expected to yield FormulaError::MatrixSize",
1909 int(FormulaError::MatrixSize
), static_cast<int>(nErr
));
1910 SCSIZE nMatCols
, nMatRows
;
1911 const ScMatrix
* pMat
= pFC
->GetMatrix();
1912 CPPUNIT_ASSERT_MESSAGE("matrix expected", pMat
!= nullptr);
1913 pMat
->GetDimensions(nMatCols
, nMatRows
);
1914 CPPUNIT_ASSERT_EQUAL_MESSAGE("1x1 matrix expected", SCSIZE(1), nMatCols
);
1915 CPPUNIT_ASSERT_EQUAL_MESSAGE("1x1 matrix expected", SCSIZE(1), nMatRows
);
1917 pRefMgr
->clearCache(nFileId
);
1918 testExtRefFuncT(m_pDoc
, rExtDoc
);
1919 testExtRefFuncOFFSET(m_pDoc
, rExtDoc
);
1920 testExtRefFuncVLOOKUP(m_pDoc
, rExtDoc
);
1921 testExtRefConcat(m_pDoc
, rExtDoc
);
1923 // Unload the external document shell.
1924 xExtDocSh
->DoClose();
1925 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1926 !findLoadedDocShellByName(aExtDocName
));
1928 m_pDoc
->DeleteTab(0);
1932 CPPUNIT_TEST_FIXTURE(TestFormula2
, testExternalRefUnresolved
)
1934 #if !defined(_WIN32) //FIXME
1935 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
1936 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
1938 // Test error propagation of unresolved (not existing document) external
1939 // references. Well, let's hope no build machine has such file with sheet...
1941 std::vector
<std::vector
<const char*>> aData
= {
1942 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1" },
1943 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1+23" },
1944 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1&\"W\"" },
1945 { "=ISREF('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1946 { "=ISERROR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1947 { "=ISERR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1948 { "=ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1949 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1950 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1951 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1+23)" },
1952 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1&\"W\")" },
1953 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1=0" },
1954 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1=\"\"" },
1955 { "=INDIRECT(\"'file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1\")" },
1956 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2" },
1957 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2+23" },
1958 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2&\"W\"" },
1959 { "=ISREF('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1960 { "=ISERROR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1961 { "=ISERR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1962 { "=ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1963 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1964 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1965 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2+23)" },
1966 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2&\"W\")" },
1967 // TODO: gives Err:504 FIXME { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2=0" },
1968 // TODO: gives Err:504 FIXME { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2=\"\"" },
1969 { "=INDIRECT(\"'file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2\")" },
1972 ScAddress
aPos(0, 0, 0);
1973 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
1974 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
1976 std::vector
<std::vector
<const char*>> aOutputCheck
= {
1977 { "#REF!" }, // plain single ref
1979 { "#REF!" }, // &"W"
1980 { "FALSE" }, // ISREF
1981 { "TRUE" }, // ISERROR
1982 { "TRUE" }, // ISERR
1983 { "FALSE" }, // ISBLANK
1984 { "FALSE" }, // ISNUMBER
1985 { "FALSE" }, // ISTEXT
1986 { "FALSE" }, // ISNUMBER
1987 { "FALSE" }, // ISTEXT
1990 { "#REF!" }, // INDIRECT
1991 { "#REF!" }, // A1:A2 range
1993 { "#REF!" }, // &"W"
1994 { "FALSE" }, // ISREF
1995 { "TRUE" }, // ISERROR
1996 { "TRUE" }, // ISERR
1997 { "FALSE" }, // ISBLANK
1998 { "FALSE" }, // ISNUMBER
1999 { "FALSE" }, // ISTEXT
2000 { "FALSE" }, // ISNUMBER
2001 { "FALSE" }, // ISTEXT
2002 // TODO: gives Err:504 FIXME { "#REF!" }, // =0
2003 // TODO: gives Err:504 FIXME { "#REF!" }, // =""
2004 { "#REF!" }, // INDIRECT
2008 = checkOutput(m_pDoc
, aRange
, aOutputCheck
, "Check unresolved external reference.");
2009 CPPUNIT_ASSERT_MESSAGE("Unresolved reference check failed", bSuccess
);
2011 m_pDoc
->DeleteTab(0);
2015 CPPUNIT_TEST_FIXTURE(TestFormula2
, testMatrixOp
)
2017 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
2019 for (SCROW nRow
= 0; nRow
< 4; ++nRow
)
2021 m_pDoc
->SetValue(0, nRow
, 0, nRow
);
2023 m_pDoc
->SetValue(1, 0, 0, 2.0);
2024 m_pDoc
->SetValue(3, 0, 0, 1.0);
2025 m_pDoc
->SetValue(3, 1, 0, 2.0);
2026 m_pDoc
->SetString(2, 0, 0, u
"=SUMPRODUCT((A1:A4)*B1+D1)"_ustr
);
2027 m_pDoc
->SetString(2, 1, 0, u
"=SUMPRODUCT((A1:A4)*B1-D2)"_ustr
);
2029 double nVal
= m_pDoc
->GetValue(2, 0, 0);
2030 CPPUNIT_ASSERT_EQUAL(16.0, nVal
);
2032 nVal
= m_pDoc
->GetValue(2, 1, 0);
2033 CPPUNIT_ASSERT_EQUAL(4.0, nVal
);
2035 m_pDoc
->SetString(4, 0, 0, u
"=SUMPRODUCT({1;2;4}+8)"_ustr
);
2036 m_pDoc
->SetString(4, 1, 0, u
"=SUMPRODUCT(8+{1;2;4})"_ustr
);
2037 m_pDoc
->SetString(4, 2, 0, u
"=SUMPRODUCT({1;2;4}-8)"_ustr
);
2038 m_pDoc
->SetString(4, 3, 0, u
"=SUMPRODUCT(8-{1;2;4})"_ustr
);
2039 m_pDoc
->SetString(4, 4, 0, u
"=SUMPRODUCT({1;2;4}+{8;16;32})"_ustr
);
2040 m_pDoc
->SetString(4, 5, 0, u
"=SUMPRODUCT({8;16;32}+{1;2;4})"_ustr
);
2041 m_pDoc
->SetString(4, 6, 0, u
"=SUMPRODUCT({1;2;4}-{8;16;32})"_ustr
);
2042 m_pDoc
->SetString(4, 7, 0, u
"=SUMPRODUCT({8;16;32}-{1;2;4})"_ustr
);
2043 double fResult
[8] = { 31.0, 31.0, -17.0, 17.0, 63.0, 63.0, -49.0, 49.0 };
2044 for (size_t i
= 0; i
< SAL_N_ELEMENTS(fResult
); ++i
)
2046 CPPUNIT_ASSERT_EQUAL(fResult
[i
], m_pDoc
->GetValue(4, i
, 0));
2049 m_pDoc
->DeleteTab(0);
2052 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncRangeOp
)
2054 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
2056 m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
);
2057 m_pDoc
->InsertTab(1, u
"Sheet2"_ustr
);
2058 m_pDoc
->InsertTab(2, u
"Sheet3"_ustr
);
2061 m_pDoc
->SetValue(1, 0, 0, 1.0);
2062 m_pDoc
->SetValue(1, 1, 0, 2.0);
2063 m_pDoc
->SetValue(1, 2, 0, 4.0);
2065 m_pDoc
->SetValue(1, 0, 1, 8.0);
2066 m_pDoc
->SetValue(1, 1, 1, 16.0);
2067 m_pDoc
->SetValue(1, 2, 1, 32.0);
2069 m_pDoc
->SetValue(1, 0, 2, 64.0);
2070 m_pDoc
->SetValue(1, 1, 2, 128.0);
2071 m_pDoc
->SetValue(1, 2, 2, 256.0);
2073 // Range operator should extend concatenated literal references during
2074 // parse time already, so with this we can test ScComplexRefData::Extend()
2076 // Current sheet is Sheet1, so B1:B2 implies relative Sheet1.B1:B2
2078 ScAddress
aPos(0, 0, 0);
2079 m_pDoc
->SetString(aPos
, u
"=SUM(B1:B2:B3)"_ustr
);
2080 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(B1:B3)"_ustr
,
2081 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2082 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc
->GetValue(aPos
));
2085 m_pDoc
->SetString(aPos
, u
"=SUM(B1:B3:B2)"_ustr
);
2086 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(B1:B3)"_ustr
,
2087 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2088 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc
->GetValue(aPos
));
2091 m_pDoc
->SetString(aPos
, u
"=SUM(B2:B3:B1)"_ustr
);
2092 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(B1:B3)"_ustr
,
2093 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2094 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc
->GetValue(aPos
));
2097 m_pDoc
->SetString(aPos
, u
"=SUM(Sheet2.B1:B2:B3)"_ustr
);
2098 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(Sheet2.B1:B3)"_ustr
,
2099 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2100 CPPUNIT_ASSERT_EQUAL(56.0, m_pDoc
->GetValue(aPos
));
2103 m_pDoc
->SetString(aPos
, u
"=SUM(B2:B2:Sheet1.B2)"_ustr
);
2104 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(Sheet1.B2:B2)"_ustr
,
2105 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2106 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(aPos
));
2109 m_pDoc
->SetString(aPos
, u
"=SUM(B2:B3:Sheet2.B1)"_ustr
);
2110 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(Sheet1.B1:Sheet2.B3)"_ustr
,
2111 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2112 CPPUNIT_ASSERT_EQUAL(63.0, m_pDoc
->GetValue(aPos
));
2115 m_pDoc
->SetString(aPos
, u
"=SUM(Sheet1.B1:Sheet2.B2:Sheet3.B3)"_ustr
);
2116 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(Sheet1.B1:Sheet3.B3)"_ustr
,
2117 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2118 CPPUNIT_ASSERT_EQUAL(511.0, m_pDoc
->GetValue(aPos
));
2120 // B1:Sheet2.B2 would be ambiguous, Sheet1.B1:Sheet2.B2 or Sheet2.B1:B2
2121 // The actual representation of the error case may change, so this test may
2122 // have to be adapted.
2124 m_pDoc
->SetString(aPos
, u
"=SUM(B1:Sheet2.B2:Sheet3.B3)"_ustr
);
2125 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(b1:sheet2.b2:Sheet3.B3)"_ustr
,
2126 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2127 CPPUNIT_ASSERT_EQUAL(u
"#NAME?"_ustr
, m_pDoc
->GetString(aPos
));
2130 m_pDoc
->SetString(aPos
, u
"=SUM(Sheet1.B1:Sheet3.B2:Sheet2.B3)"_ustr
);
2131 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(Sheet1.B1:Sheet3.B3)"_ustr
,
2132 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2133 CPPUNIT_ASSERT_EQUAL(511.0, m_pDoc
->GetValue(aPos
));
2136 m_pDoc
->SetString(aPos
, u
"=SUM(B$2:B$2:B2)"_ustr
);
2137 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", u
"=SUM(B$2:B2)"_ustr
,
2138 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
2139 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(aPos
));
2141 m_pDoc
->DeleteTab(2);
2142 m_pDoc
->DeleteTab(1);
2143 m_pDoc
->DeleteTab(0);
2146 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncFORMULA
)
2148 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
2150 m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
);
2153 std::vector
<std::vector
<const char*>> aData
= {
2154 { "=A1", "=FORMULA(B1)", "=FORMULA(B1:B3)" },
2155 { nullptr, "=FORMULA(B2)", "=FORMULA(B1:B3)" },
2156 { "=A3", "=FORMULA(B3)", "=FORMULA(B1:B3)" },
2159 ScAddress
aPos(1, 0, 0);
2160 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
2161 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
2163 // Checks of C1:D3, where Cy==Dy, and D4:D6
2164 const char* aChecks
[] = {
2169 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
2171 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks
[i
]), m_pDoc
->GetString(2, i
, 0));
2172 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks
[i
]), m_pDoc
->GetString(3, i
, 0));
2175 // Matrix in D4:D6, no intersection with B1:B3
2176 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
2177 aMark
.SelectOneTable(0);
2178 m_pDoc
->InsertMatrixFormula(3, 3, 3, 5, aMark
, u
"=FORMULA(B1:B3)"_ustr
);
2179 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
2181 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks
[i
]), m_pDoc
->GetString(3, i
+ 3, 0));
2184 m_pDoc
->DeleteTab(0);
2187 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncTableRef
)
2189 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
2191 m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
);
2192 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
2193 aMark
.SelectOneTable(0);
2194 ScDocFunc
& rDocFunc
= m_xDocShell
->GetDocFunc();
2197 ScDBCollection
* pDBs
= m_pDoc
->GetDBCollection();
2198 CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs
);
2200 // Insert "table" database range definition for A1:B4, with default
2201 // HasHeader=true and HasTotals=false.
2202 std::unique_ptr
<ScDBData
> pData(new ScDBData(u
"table"_ustr
, 0, 0, 0, 1, 3));
2203 bool bInserted
= pDBs
->getNamedDBs().insert(std::move(pData
));
2204 CPPUNIT_ASSERT_MESSAGE("Failed to insert \"table\" database range.", bInserted
);
2208 // Populate "table" database range with headers and data in A1:B4
2209 std::vector
<std::vector
<const char*>> aData
2210 = { { "Header1", "Header2" }, { "1", "2" }, { "4", "8" }, { "16", "32" } };
2211 ScAddress
aPos(0, 0, 0);
2212 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
2213 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
2216 // Named expressions that use Table structured references.
2217 /* TODO: should the item/header separator really be equal to the parameter
2218 * separator, thus be locale dependent and ';' semicolon here, or should it
2219 * be a fixed ',' comma instead? */
2225 pCounta
; // expected result when used in row 2 (first data row) as argument to COUNTA()
2227 pSum3
; // expected result when used in row 3 (second data row) as argument to SUM().
2229 pSum4
; // expected result when used in row 4 (third data row) as argument to SUM().
2231 pSumX
; // expected result when used in row 5 (non-intersecting) as argument to SUM().
2233 = { { "all", "table[[#All]]", "8", "63", "63", "63" },
2234 { "data_implicit", "table[]", "6", "63", "63", "63" },
2235 { "data", "table[[#Data]]", "6", "63", "63", "63" },
2236 { "headers", "table[[#Headers]]", "2", "0", "0", "0" },
2237 { "header1", "table[[Header1]]", "3", "21", "21", "21" },
2238 { "header2", "table[[Header2]]", "3", "42", "42", "42" },
2239 { "data_header1", "table[[#Data];[Header1]]", "3", "21", "21", "21" },
2240 { "data_header2", "table[[#Data];[Header2]]", "3", "42", "42", "42" },
2241 { "this_row", "table[[#This Row]]", "2", "12", "48", "#VALUE!" },
2242 { "this_row_header1", "table[[#This Row];[Header1]]", "1", "4", "16", "#VALUE!" },
2243 { "this_row_header2", "table[[#This Row];[Header2]]", "1", "8", "32", "#VALUE!" },
2244 { "this_row_range_header_1_to_2", "table[[#This Row];[Header1]:[Header2]]", "2", "12",
2245 "48", "#VALUE!" } };
2248 // Insert named expressions.
2249 ScRangeName
* pGlobalNames
= m_pDoc
->GetRangeName();
2250 CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames
);
2252 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2254 // Choose base position that does not intersect with the database
2255 // range definition to test later use of [#This Row] results in
2258 = new ScRangeData(*m_pDoc
, OUString::createFromAscii(aNames
[i
].pName
),
2259 OUString::createFromAscii(aNames
[i
].pExpr
), ScAddress(2, 4, 0),
2260 ScRangeData::Type::Name
, formula::FormulaGrammar::GRAM_NATIVE
);
2261 bool bInserted
= pGlobalNames
->insert(pName
);
2262 CPPUNIT_ASSERT_MESSAGE(OString(OString::Concat("Failed to insert named expression ")
2263 + aNames
[i
].pName
+ ".")
2269 // Use the named expressions in COUNTA() formulas, on row 2 that intersects.
2270 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2272 OUString
aFormula("=COUNTA(" + OUString::createFromAscii(aNames
[i
].pName
) + ")");
2273 ScAddress
aPos(3 + i
, 1, 0);
2274 m_pDoc
->SetString(aPos
, aFormula
);
2275 // For easier "debugability" have position and formula in assertion.
2276 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2277 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aNames
[i
].pCounta
)),
2278 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2281 // Use the named expressions in SUM() formulas, on row 3 that intersects.
2282 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2284 OUString
aFormula("=SUM(" + OUString::createFromAscii(aNames
[i
].pName
) + ")");
2285 ScAddress
aPos(3 + i
, 2, 0);
2286 m_pDoc
->SetString(aPos
, aFormula
);
2287 // For easier "debugability" have position and formula in assertion.
2288 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2289 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aNames
[i
].pSum3
)),
2290 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2293 // Use the named expressions in SUM() formulas, on row 4 that intersects.
2294 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2296 OUString
aFormula("=SUM(" + OUString::createFromAscii(aNames
[i
].pName
) + ")");
2297 ScAddress
aPos(3 + i
, 3, 0);
2298 m_pDoc
->SetString(aPos
, aFormula
);
2299 // For easier "debugability" have position and formula in assertion.
2300 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2301 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aNames
[i
].pSum4
)),
2302 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2305 // Use the named expressions in SUM() formulas, on row 5 that does not intersect.
2306 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2308 OUString
aFormula("=SUM(" + OUString::createFromAscii(aNames
[i
].pName
) + ")");
2309 ScAddress
aPos(3 + i
, 4, 0);
2310 m_pDoc
->SetString(aPos
, aFormula
);
2311 // For easier "debugability" have position and formula in assertion.
2312 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2313 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aNames
[i
].pSumX
)),
2314 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2317 // Insert a column at column B to extend database range from column A,B to
2318 // A,B,C. Use ScDocFunc so RefreshDirtyTableColumnNames() is called.
2319 rDocFunc
.InsertCells(ScRange(1, 0, 0, 1, m_pDoc
->MaxRow(), 0), &aMark
, INS_INSCOLS_BEFORE
,
2322 // Re-verify the named expression in SUM() formula, on row 4 that
2323 // intersects, now starting at column E, still works.
2325 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aNames
); ++i
)
2327 OUString
aFormula("=SUM(" + OUString::createFromAscii(aNames
[i
].pName
) + ")");
2328 ScAddress
aPos(4 + i
, 3, 0);
2329 // For easier "debugability" have position and formula in assertion.
2330 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2331 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aNames
[i
].pSum4
)),
2332 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2335 const char* pColumn2Formula
= "=SUM(table[[#Data];[Column2]])";
2337 // Populate "table" database range with empty header and data in newly
2338 // inserted column, B1:B4 plus a table formula in B6. The empty header
2339 // should result in the internal table column name "Column2" that is
2340 // used in the formula.
2341 std::vector
<std::vector
<const char*>> aData
2342 = { { "" }, { "64" }, { "128" }, { "256" }, { "" }, { pColumn2Formula
} };
2343 ScAddress
aPos(1, 0, 0);
2344 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
2345 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
2348 // Verify the formula result in B6 (64+128+256=448).
2350 OUString
aFormula(OUString::createFromAscii(pColumn2Formula
));
2351 ScAddress
aPos(1, 5, 0);
2352 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2353 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ "448"),
2354 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2357 // Set header in column B. Use ScDocFunc to have table column names refreshed.
2358 rDocFunc
.SetStringCell(ScAddress(1, 0, 0), u
"NewHeader"_ustr
, true);
2359 // Verify that formula adapted using the updated table column names.
2360 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", u
"=SUM(table[[#Data];[NewHeader]])"_ustr
,
2361 m_pDoc
->GetFormula(1, 5, 0));
2363 // Set header in column A to identical string. Internal table column name
2364 // for B should get a "2" appended.
2365 rDocFunc
.SetStringCell(ScAddress(0, 0, 0), u
"NewHeader"_ustr
, true);
2366 // Verify that formula adapted using the updated table column names.
2367 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", u
"=SUM(table[[#Data];[NewHeader2]])"_ustr
,
2368 m_pDoc
->GetFormula(1, 5, 0));
2370 // Set header in column B to empty string, effectively clearing the cell.
2371 rDocFunc
.SetStringCell(ScAddress(1, 0, 0), u
""_ustr
, true);
2372 // Verify that formula is still using the previous table column name.
2373 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", u
"=SUM(table[[#Data];[NewHeader2]])"_ustr
,
2374 m_pDoc
->GetFormula(1, 5, 0));
2376 // === header-less ===
2379 ScDBCollection
* pDBs
= m_pDoc
->GetDBCollection();
2380 CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs
);
2382 // Insert "headerless" database range definition for E10:F12, without headers.
2383 std::unique_ptr
<ScDBData
> pData(new ScDBData(u
"hltable"_ustr
, 0, 4, 9, 5, 11, true, false));
2384 bool bInserted
= pDBs
->getNamedDBs().insert(std::move(pData
));
2385 CPPUNIT_ASSERT_MESSAGE("Failed to insert \"hltable\" database range.", bInserted
);
2389 // Populate "hltable" database range with data in E10:F12
2390 std::vector
<std::vector
<const char*>> aData
2391 = { { "1", "2" }, { "4", "8" }, { "16", "32" } };
2392 ScAddress
aPos(4, 9, 0);
2393 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
2394 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
2397 // Named expressions that use header-less Table structured references.
2403 pCounta
; // expected result when used in row 10 (first data row) as argument to COUNTA()
2405 pSum3
; // expected result when used in row 11 (second data row) as argument to SUM().
2407 pSum4
; // expected result when used in row 12 (third data row) as argument to SUM().
2409 pSumX
; // expected result when used in row 13 (non-intersecting) as argument to SUM().
2411 = { { "hl_all", "hltable[[#All]]", "6", "63", "63", "63" },
2412 { "hl_data_implicit", "hltable[]", "6", "63", "63", "63" },
2413 { "hl_data", "hltable[[#Data]]", "6", "63", "63", "63" },
2414 { "hl_headers", "hltable[[#Headers]]", "1", "#REF!", "#REF!", "#REF!" },
2415 { "hl_column1", "hltable[[Column1]]", "3", "21", "21", "21" },
2416 { "hl_column2", "hltable[[Column2]]", "3", "42", "42", "42" },
2417 { "hl_data_column1", "hltable[[#Data];[Column1]]", "3", "21", "21", "21" },
2418 { "hl_data_column2", "hltable[[#Data];[Column2]]", "3", "42", "42", "42" },
2419 { "hl_this_row", "hltable[[#This Row]]", "2", "12", "48", "#VALUE!" },
2420 { "hl_this_row_column1", "hltable[[#This Row];[Column1]]", "1", "4", "16", "#VALUE!" },
2421 { "hl_this_row_column2", "hltable[[#This Row];[Column2]]", "1", "8", "32", "#VALUE!" },
2422 { "hl_this_row_range_column_1_to_2", "hltable[[#This Row];[Column1]:[Column2]]", "2",
2423 "12", "48", "#VALUE!" } };
2426 // Insert named expressions.
2427 ScRangeName
* pGlobalNames
= m_pDoc
->GetRangeName();
2428 CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames
);
2430 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2432 // Choose base position that does not intersect with the database
2433 // range definition to test later use of [#This Row] results in
2436 = new ScRangeData(*m_pDoc
, OUString::createFromAscii(aHlNames
[i
].pName
),
2437 OUString::createFromAscii(aHlNames
[i
].pExpr
), ScAddress(6, 12, 0),
2438 ScRangeData::Type::Name
, formula::FormulaGrammar::GRAM_NATIVE
);
2439 bool bInserted
= pGlobalNames
->insert(pName
);
2440 CPPUNIT_ASSERT_MESSAGE(OString(OString::Concat("Failed to insert named expression ")
2441 + aHlNames
[i
].pName
+ ".")
2447 // Use the named expressions in COUNTA() formulas, on row 10 that intersects.
2448 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2450 OUString
aFormula("=COUNTA(" + OUString::createFromAscii(aHlNames
[i
].pName
) + ")");
2451 ScAddress
aPos(7 + i
, 9, 0);
2452 m_pDoc
->SetString(aPos
, aFormula
);
2453 // For easier "debugability" have position and formula in assertion.
2454 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2455 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aHlNames
[i
].pCounta
)),
2456 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2459 // Use the named expressions in SUM() formulas, on row 11 that intersects.
2460 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2462 OUString
aFormula("=SUM(" + OUString::createFromAscii(aHlNames
[i
].pName
) + ")");
2463 ScAddress
aPos(7 + i
, 10, 0);
2464 m_pDoc
->SetString(aPos
, aFormula
);
2465 // For easier "debugability" have position and formula in assertion.
2466 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2467 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aHlNames
[i
].pSum3
)),
2468 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2471 // Use the named expressions in SUM() formulas, on row 12 that intersects.
2472 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2474 OUString
aFormula("=SUM(" + OUString::createFromAscii(aHlNames
[i
].pName
) + ")");
2475 ScAddress
aPos(7 + i
, 11, 0);
2476 m_pDoc
->SetString(aPos
, aFormula
);
2477 // For easier "debugability" have position and formula in assertion.
2478 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2479 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aHlNames
[i
].pSum4
)),
2480 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2483 // Use the named expressions in SUM() formulas, on row 13 that does not intersect.
2484 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2486 OUString
aFormula("=SUM(" + OUString::createFromAscii(aHlNames
[i
].pName
) + ")");
2487 ScAddress
aPos(7 + i
, 12, 0);
2488 m_pDoc
->SetString(aPos
, aFormula
);
2489 // For easier "debugability" have position and formula in assertion.
2490 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2491 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aHlNames
[i
].pSumX
)),
2492 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2495 // Insert a column at column F to extend database range from column E,F to
2496 // E,F,G. Use ScDocFunc so RefreshDirtyTableColumnNames() is called.
2497 rDocFunc
.InsertCells(ScRange(5, 0, 0, 5, m_pDoc
->MaxRow(), 0), &aMark
, INS_INSCOLS_BEFORE
,
2500 // Re-verify the named expression in SUM() formula, on row 12 that
2501 // intersects, now starting at column I, still works.
2503 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aHlNames
); ++i
)
2505 OUString
aFormula("=SUM(" + OUString::createFromAscii(aHlNames
[i
].pName
) + ")");
2506 ScAddress
aPos(8 + i
, 11, 0);
2507 // For easier "debugability" have position and formula in assertion.
2508 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2509 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ OUString::createFromAscii(aHlNames
[i
].pSum4
)),
2510 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2513 const char* pColumn3Formula
= "=SUM(hltable[[#Data];[Column3]])";
2515 // Populate "hltable" database range with data in newly inserted
2516 // column, F10:F12 plus a table formula in F14. The new header should
2517 // result in the internal table column name "Column3" that is used in
2519 std::vector
<std::vector
<const char*>> aData
2520 = { { "64" }, { "128" }, { "256" }, { "" }, { pColumn3Formula
} };
2521 ScAddress
aPos(5, 9, 0);
2522 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
2523 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
2526 // Verify the formula result in F14 (64+128+256=448).
2528 OUString
aFormula(OUString::createFromAscii(pColumn3Formula
));
2529 ScAddress
aPos(5, 13, 0);
2530 OUString
aPrefix(aPos
.Format(ScRefFlags::VALID
) + " " + aFormula
+ " : ");
2531 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix
+ "448"),
2532 OUString(aPrefix
+ m_pDoc
->GetString(aPos
)));
2535 m_pDoc
->DeleteTab(0);
2538 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncFTEST
)
2540 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
2542 m_pDoc
->InsertTab(0, u
"FTest"_ustr
);
2544 ScAddress
aPos(6, 0, 0);
2545 m_pDoc
->SetString(aPos
, u
"=FTEST(A1:C3;D1:F3)"_ustr
);
2546 m_pDoc
->SetValue(0, 0, 0, 9.0); // A1
2547 OUString aVal
= m_pDoc
->GetString(aPos
);
2548 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2549 u
"#VALUE!"_ustr
, aVal
);
2550 m_pDoc
->SetValue(0, 1, 0, 8.0); // A2
2551 aVal
= m_pDoc
->GetString(aPos
);
2552 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2553 u
"#VALUE!"_ustr
, aVal
);
2554 m_pDoc
->SetValue(3, 0, 0, 5.0); // D1
2555 aVal
= m_pDoc
->GetString(aPos
);
2556 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2557 u
"#VALUE!"_ustr
, aVal
);
2558 m_pDoc
->SetValue(3, 1, 0, 6.0); // D2
2559 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 1.0000,
2560 m_pDoc
->GetValue(aPos
), 10e-4);
2561 m_pDoc
->SetValue(1, 0, 0, 6.0); // B1
2562 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.6222,
2563 m_pDoc
->GetValue(aPos
), 10e-4);
2564 m_pDoc
->SetValue(1, 1, 0, 8.0); // B2
2565 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.7732,
2566 m_pDoc
->GetValue(aPos
), 10e-4);
2567 m_pDoc
->SetValue(4, 0, 0, 7.0); // E1
2568 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.8194,
2569 m_pDoc
->GetValue(aPos
), 10e-4);
2570 m_pDoc
->SetValue(4, 1, 0, 4.0); // E2
2571 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.9674,
2572 m_pDoc
->GetValue(aPos
), 10e-4);
2573 m_pDoc
->SetValue(2, 0, 0, 3.0); // C1
2574 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.3402,
2575 m_pDoc
->GetValue(aPos
), 10e-4);
2576 m_pDoc
->SetValue(5, 0, 0, 28.0); // F1
2577 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0161,
2578 m_pDoc
->GetValue(aPos
), 10e-4);
2579 m_pDoc
->SetValue(2, 1, 0, 9.0); // C2
2580 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0063,
2581 m_pDoc
->GetValue(aPos
), 10e-4);
2582 m_pDoc
->SetValue(5, 1, 0, 4.0); // F2
2583 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0081,
2584 m_pDoc
->GetValue(aPos
), 10e-4);
2585 m_pDoc
->SetValue(0, 2, 0, 2.0); // A3
2586 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0122,
2587 m_pDoc
->GetValue(aPos
), 10e-4);
2588 m_pDoc
->SetValue(3, 2, 0, 8.0); // D3
2589 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0178,
2590 m_pDoc
->GetValue(aPos
), 10e-4);
2591 m_pDoc
->SetValue(1, 2, 0, 4.0); // B3
2592 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0093,
2593 m_pDoc
->GetValue(aPos
), 10e-4);
2594 m_pDoc
->SetValue(4, 2, 0, 7.0); // E3
2595 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0132,
2596 m_pDoc
->GetValue(aPos
), 10e-4);
2597 m_pDoc
->SetValue(5, 2, 0, 5.0); // F3
2598 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0168,
2599 m_pDoc
->GetValue(aPos
), 10e-4);
2600 m_pDoc
->SetValue(2, 2, 0, 13.0); // C3
2601 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0422,
2602 m_pDoc
->GetValue(aPos
), 10e-4);
2604 m_pDoc
->SetString(0, 2, 0, u
"a"_ustr
); // A3
2605 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0334,
2606 m_pDoc
->GetValue(aPos
), 10e-4);
2607 m_pDoc
->SetString(2, 0, 0, u
"b"_ustr
); // C1
2608 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0261,
2609 m_pDoc
->GetValue(aPos
), 10e-4);
2610 m_pDoc
->SetString(5, 1, 0, u
"c"_ustr
); // F2
2611 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0219,
2612 m_pDoc
->GetValue(aPos
), 10e-4);
2613 m_pDoc
->SetString(4, 2, 0, u
"d"_ustr
); // E3
2614 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0161,
2615 m_pDoc
->GetValue(aPos
), 10e-4);
2616 m_pDoc
->SetString(3, 2, 0, u
"e"_ustr
); // D3
2617 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0110,
2618 m_pDoc
->GetValue(aPos
), 10e-4);
2620 m_pDoc
->DeleteTab(0);
2621 m_pDoc
->InsertTab(0, u
"FTest2"_ustr
);
2623 /* Summary of the following test
2624 A1:A5 = SQRT(C1*9/10)*{ 1.0, 1.0, 1.0, 1.0, 1.0 };
2625 A6:A10 = -SQRT(C1*9/10)*{ 1.0, 1.0, 1.0, 1.0, 1.0 };
2626 B1:B10 = SQRT(C2*19/20)*{ 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0 };
2627 B11:B20 = -SQRT(C2*19/20)*{ 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0 };
2628 C1 = POWER(1.5, D1) ; This is going to be the sample variance of the vector A1:A10
2629 C2 = POWER(1.5, D2) ; This is going to be the sample variance of the vector B1:B20
2630 D1 and D2 are varied over { -5.0, -4.0, -3.0, -2.0, -1.0, 0.0, 1.0, 2.0, 3.0, 4.0, 5.0 }
2632 Result of FTEST(A1:A10;B1:B20) in Calc is compared with that from Octave's var_test() function for each value of D1 and D2.
2634 The minimum variance ratio obtained in this way is 0.017342 and the maximum variance ratio is 57.665039
2637 const size_t nNumParams
= 11;
2638 const double fParameter
[nNumParams
]
2639 = { -5.0, -4.0, -3.0, -2.0, -1.0, 0.0, 1.0, 2.0, 3.0, 4.0, 5.0 };
2641 // Results of var_test() from Octave
2642 const double fResults
[nNumParams
][nNumParams
] = {
2643 { 0.9451191535603041, 0.5429768686792684, 0.213130093422756, 0.06607644828558357,
2644 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703, 0.0001435746909905777,
2645 2.566562398786942e-05, 4.436218417280813e-06, 7.495090956766148e-07 },
2646 { 0.4360331979746912, 0.9451191535603054, 0.5429768686792684, 0.2131300934227565,
2647 0.06607644828558357, 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703,
2648 0.0001435746909905777, 2.566562398786942e-05, 4.436218417280813e-06 },
2649 { 0.1309752286653509, 0.4360331979746914, 0.9451191535603058, 0.5429768686792684,
2650 0.2131300934227565, 0.06607644828558357, 0.0169804365506927, 0.003790723514148109,
2651 0.0007645345628801703, 0.0001435746909905777, 2.566562398786942e-05 },
2652 { 0.02453502500565108, 0.1309752286653514, 0.4360331979746914, 0.9451191535603058,
2653 0.5429768686792689, 0.2131300934227565, 0.06607644828558357, 0.0169804365506927,
2654 0.003790723514148109, 0.0007645345628801703, 0.0001435746909905777 },
2655 { 0.002886791075972228, 0.02453502500565108, 0.1309752286653514, 0.4360331979746914,
2656 0.9451191535603041, 0.5429768686792689, 0.2131300934227565, 0.06607644828558357,
2657 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703 },
2658 { 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108, 0.1309752286653509,
2659 0.4360331979746912, 0.9451191535603036, 0.5429768686792689, 0.2131300934227565,
2660 0.06607644828558357, 0.0169804365506927, 0.003790723514148109 },
2661 { 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108,
2662 0.1309752286653509, 0.4360331979746914, 0.9451191535603054, 0.5429768686792684,
2663 0.2131300934227565, 0.06607644828558357, 0.0169804365506927 },
2664 { 5.109390206481379e-07, 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228,
2665 0.02453502500565108, 0.1309752286653509, 0.4360331979746914, 0.9451191535603058,
2666 0.5429768686792684, 0.213130093422756, 0.06607644828558357 },
2667 { 1.739106880727093e-08, 5.109390206481379e-07, 1.224926820153627e-05,
2668 0.0002237196492846927, 0.002886791075972228, 0.02453502500565086, 0.1309752286653509,
2669 0.4360331979746914, 0.9451191535603041, 0.5429768686792684, 0.2131300934227565 },
2670 { 5.111255862999542e-10, 1.739106880727093e-08, 5.109390206481379e-07,
2671 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108,
2672 0.1309752286653516, 0.4360331979746914, 0.9451191535603058, 0.5429768686792684 },
2673 { 1.354649725726631e-11, 5.111255862999542e-10, 1.739106880727093e-08,
2674 5.109390206481379e-07, 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228,
2675 0.02453502500565108, 0.1309752286653509, 0.4360331979746914, 0.9451191535603054 }
2678 m_pDoc
->SetValue(3, 0, 0, fParameter
[0]); // D1
2679 m_pDoc
->SetValue(3, 1, 0, fParameter
[0]); // D2
2680 aPos
.Set(2, 0, 0); // C1
2681 m_pDoc
->SetString(aPos
, u
"=POWER(1.5;D1)"_ustr
); // C1
2682 aPos
.Set(2, 1, 0); // C2
2683 m_pDoc
->SetString(aPos
, u
"=POWER(1.5;D2)"_ustr
); // C2
2684 for (SCROW nRow
= 0; nRow
< 5;
2685 ++nRow
) // Set A1:A5 = SQRT(C1*9/10), and A6:A10 = -SQRT(C1*9/10)
2687 aPos
.Set(0, nRow
, 0);
2688 m_pDoc
->SetString(aPos
, u
"=SQRT(C1*9/10)"_ustr
);
2689 aPos
.Set(0, nRow
+ 5, 0);
2690 m_pDoc
->SetString(aPos
, u
"=-SQRT(C1*9/10)"_ustr
);
2693 for (SCROW nRow
= 0; nRow
< 10;
2694 ++nRow
) // Set B1:B10 = SQRT(C2*19/20), and B11:B20 = -SQRT(C2*19/20)
2696 aPos
.Set(1, nRow
, 0);
2697 m_pDoc
->SetString(aPos
, u
"=SQRT(C2*19/20)"_ustr
);
2698 aPos
.Set(1, nRow
+ 10, 0);
2699 m_pDoc
->SetString(aPos
, u
"=-SQRT(C2*19/20)"_ustr
);
2702 aPos
.Set(4, 0, 0); // E1
2703 m_pDoc
->SetString(aPos
, u
"=FTEST(A1:A10;B1:B20)"_ustr
);
2704 aPos
.Set(4, 1, 0); // E2
2705 m_pDoc
->SetString(aPos
, u
"=FTEST(B1:B20;A1:A10)"_ustr
);
2707 ScAddress
aPosRev(4, 1, 0); // E2
2708 aPos
.Set(4, 0, 0); // E1
2710 for (size_t nFirstIdx
= 0; nFirstIdx
< nNumParams
; ++nFirstIdx
)
2712 m_pDoc
->SetValue(3, 0, 0, fParameter
[nFirstIdx
]); // Set D1
2713 for (size_t nSecondIdx
= 0; nSecondIdx
< nNumParams
; ++nSecondIdx
)
2715 m_pDoc
->SetValue(3, 1, 0, fParameter
[nSecondIdx
]); // Set D2
2716 double fExpected
= fResults
[nFirstIdx
][nSecondIdx
];
2717 // Here a dynamic error limit is used. This is to handle correctly when the expected value is lower than the fixed error limit of 10e-5
2718 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", fExpected
,
2719 m_pDoc
->GetValue(aPos
),
2720 std::min(10e-5, fExpected
* 0.0001));
2721 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", fExpected
,
2722 m_pDoc
->GetValue(aPosRev
),
2723 std::min(10e-5, fExpected
* 0.0001));
2726 m_pDoc
->DeleteTab(0);
2729 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncFTESTBug
)
2731 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
2733 m_pDoc
->InsertTab(0, u
"FTest"_ustr
);
2735 ScAddress
aPos(9, 0, 0);
2736 m_pDoc
->SetString(aPos
, u
"=FTEST(H1:H3;I1:I3)"_ustr
);
2738 m_pDoc
->SetValue(7, 0, 0, 9.0); // H1
2739 m_pDoc
->SetValue(7, 1, 0, 8.0); // H2
2740 m_pDoc
->SetValue(7, 2, 0, 6.0); // H3
2741 m_pDoc
->SetValue(8, 0, 0, 5.0); // I1
2742 m_pDoc
->SetValue(8, 1, 0, 7.0); // I2
2744 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.9046,
2745 m_pDoc
->GetValue(aPos
), 10e-4);
2747 m_pDoc
->DeleteTab(0);
2750 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncCHITEST
)
2752 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
2754 m_pDoc
->InsertTab(0, u
"ChiTest"_ustr
);
2756 ScAddress
aPos(6, 0, 0);
2757 // 2x2 matrices test
2758 m_pDoc
->SetString(aPos
, u
"=CHITEST(A1:B2;D1:E2)"_ustr
);
2759 OUString aVal
= m_pDoc
->GetString(aPos
);
2760 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with empty cells",
2761 u
"Err:502"_ustr
, aVal
);
2763 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
2764 m_pDoc
->SetValue(0, 1, 0, 2.0); // A2
2765 m_pDoc
->SetValue(1, 0, 0, 2.0); // B1
2766 m_pDoc
->SetValue(1, 1, 0, 1.0); // B2
2767 aVal
= m_pDoc
->GetString(aPos
);
2768 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrix with empty cells",
2769 u
"Err:502"_ustr
, aVal
);
2771 m_pDoc
->SetValue(3, 0, 0, 2.0); // D1
2772 m_pDoc
->SetValue(3, 1, 0, 3.0); // D2
2773 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.3613,
2774 m_pDoc
->GetValue(aPos
), 10e-4);
2776 m_pDoc
->SetValue(4, 1, 0, 1.0); // E2
2777 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.3613,
2778 m_pDoc
->GetValue(aPos
), 10e-4);
2779 m_pDoc
->SetValue(4, 0, 0, 3.0); // E1
2780 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.2801,
2781 m_pDoc
->GetValue(aPos
), 10e-4);
2782 m_pDoc
->SetValue(4, 0, 0, 0.0); // E1
2783 aVal
= m_pDoc
->GetString(aPos
);
2784 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0 for expected values of 0",
2785 u
"#DIV/0!"_ustr
, aVal
);
2786 m_pDoc
->SetValue(4, 0, 0, 3.0); // E1
2787 m_pDoc
->SetValue(1, 1, 0, 0.0); // B2
2788 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1410,
2789 m_pDoc
->GetValue(aPos
), 10e-4);
2791 // 3x3 matrices test
2792 m_pDoc
->SetString(aPos
, u
"=CHITEST(A1:C3;D1:F3)"_ustr
);
2793 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.7051,
2794 m_pDoc
->GetValue(aPos
), 10e-4);
2796 m_pDoc
->SetValue(2, 0, 0, 3.0); // C1
2797 m_pDoc
->SetValue(2, 1, 0, 2.0); // C2
2798 m_pDoc
->SetValue(2, 2, 0, 3.0); // C3
2799 m_pDoc
->SetValue(0, 2, 0, 4.0); // A3
2800 m_pDoc
->SetValue(1, 2, 0, 2.0); // B3
2801 m_pDoc
->SetValue(5, 0, 0, 1.0); // F1
2802 m_pDoc
->SetValue(5, 1, 0, 2.0); // F2
2803 m_pDoc
->SetValue(5, 2, 0, 3.0); // F3
2804 m_pDoc
->SetValue(3, 2, 0, 3.0); // D3
2805 m_pDoc
->SetValue(4, 2, 0, 1.0); // E3
2806 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1117,
2807 m_pDoc
->GetValue(aPos
), 10e-4);
2809 // test with strings
2810 m_pDoc
->SetString(4, 2, 0, u
"a"_ustr
); // E3
2811 aVal
= m_pDoc
->GetString(aPos
);
2812 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2813 u
"Err:502"_ustr
, aVal
);
2814 m_pDoc
->SetString(1, 2, 0, u
"a"_ustr
); // B3
2815 aVal
= m_pDoc
->GetString(aPos
);
2816 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2817 u
"Err:502"_ustr
, aVal
);
2818 m_pDoc
->SetValue(4, 2, 0, 1.0); // E3
2819 aVal
= m_pDoc
->GetString(aPos
);
2820 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2821 u
"Err:502"_ustr
, aVal
);
2822 m_pDoc
->SetValue(1, 2, 0, 2.0); // B3
2823 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1117,
2824 m_pDoc
->GetValue(aPos
), 10e-4);
2826 m_pDoc
->SetValue(4, 1, 0, 5.0); // E2
2827 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0215,
2828 m_pDoc
->GetValue(aPos
), 10e-4);
2829 m_pDoc
->SetValue(1, 2, 0, 1.0); // B3
2830 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0328,
2831 m_pDoc
->GetValue(aPos
), 10e-4);
2832 m_pDoc
->SetValue(5, 0, 0, 3.0); // F1
2833 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1648,
2834 m_pDoc
->GetValue(aPos
), 10e-4);
2835 m_pDoc
->SetValue(0, 1, 0, 3.0); // A2
2836 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1870,
2837 m_pDoc
->GetValue(aPos
), 10e-4);
2838 m_pDoc
->SetValue(3, 1, 0, 5.0); // D2
2839 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1377,
2840 m_pDoc
->GetValue(aPos
), 10e-4);
2841 m_pDoc
->SetValue(3, 2, 0, 4.0); // D3
2842 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1566,
2843 m_pDoc
->GetValue(aPos
), 10e-4);
2845 m_pDoc
->SetValue(0, 0, 0, 0.0); // A1
2846 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0868,
2847 m_pDoc
->GetValue(aPos
), 10e-4);
2849 // no convergence error
2850 m_pDoc
->SetValue(4, 0, 0, 1.0E308
); // E1
2851 aVal
= m_pDoc
->GetString(aPos
);
2852 CPPUNIT_ASSERT_EQUAL(u
"Err:523"_ustr
, aVal
);
2853 m_pDoc
->SetValue(4, 0, 0, 3.0); // E1
2855 // zero in all cells
2856 m_pDoc
->SetValue(0, 1, 0, 0.0); // A2
2857 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0150,
2858 m_pDoc
->GetValue(aPos
), 10e-4);
2859 m_pDoc
->SetValue(0, 2, 0, 0.0); // A3
2860 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0026,
2861 m_pDoc
->GetValue(aPos
), 10e-4);
2862 m_pDoc
->SetValue(1, 0, 0, 0.0); // B1
2863 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.00079,
2864 m_pDoc
->GetValue(aPos
), 10e-5);
2865 m_pDoc
->SetValue(1, 2, 0, 0.0); // B3
2866 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0005,
2867 m_pDoc
->GetValue(aPos
), 10e-4);
2868 m_pDoc
->SetValue(2, 0, 0, 0.0); // C1
2869 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0001,
2870 m_pDoc
->GetValue(aPos
), 10e-4);
2871 m_pDoc
->SetValue(2, 1, 0, 0.0); // C2
2872 m_pDoc
->SetValue(2, 2, 0, 0.0); // C3
2873 m_pDoc
->SetValue(3, 0, 0, 0.0); // D1
2874 aVal
= m_pDoc
->GetString(aPos
);
2875 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0! for matrices with empty",
2876 u
"#DIV/0!"_ustr
, aVal
);
2877 m_pDoc
->SetValue(3, 1, 0, 0.0); // D2
2878 m_pDoc
->SetValue(3, 2, 0, 0.0); // D3
2879 m_pDoc
->SetValue(4, 0, 0, 0.0); // E1
2880 m_pDoc
->SetValue(4, 1, 0, 0.0); // E2
2881 m_pDoc
->SetValue(4, 2, 0, 0.0); // E3
2882 m_pDoc
->SetValue(5, 0, 0, 0.0); // F1
2883 m_pDoc
->SetValue(5, 1, 0, 0.0); // F2
2884 m_pDoc
->SetValue(5, 2, 0, 0.0); // F3
2885 aVal
= m_pDoc
->GetString(aPos
);
2886 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0! for matrices with empty",
2887 u
"#DIV/0!"_ustr
, aVal
);
2889 m_pDoc
->DeleteTab(0);
2892 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncTTEST
)
2894 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
2896 m_pDoc
->InsertTab(0, u
"TTest"_ustr
);
2898 ScAddress
aPos(6, 0, 0);
2899 // type 1, mode/tails 1
2900 m_pDoc
->SetString(aPos
, u
"=TTEST(A1:C3;D1:F3;1;1)"_ustr
);
2901 OUString aVal
= m_pDoc
->GetString(aPos
);
2902 CPPUNIT_ASSERT_EQUAL_MESSAGE("TTEST should return #VALUE! for empty matrices", u
"#VALUE!"_ustr
,
2905 m_pDoc
->SetValue(0, 0, 0, 8.0); // A1
2906 m_pDoc
->SetValue(1, 0, 0, 2.0); // B1
2907 m_pDoc
->SetValue(3, 0, 0, 3.0); // D1
2908 m_pDoc
->SetValue(4, 0, 0, 1.0); // E1
2909 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.18717,
2910 m_pDoc
->GetValue(aPos
), 10e-5);
2911 m_pDoc
->SetValue(2, 0, 0, 1.0); // C1
2912 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.18717,
2913 m_pDoc
->GetValue(aPos
), 10e-5);
2914 m_pDoc
->SetValue(5, 0, 0, 6.0); // F1
2915 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.45958,
2916 m_pDoc
->GetValue(aPos
), 10e-5);
2917 m_pDoc
->SetValue(0, 1, 0, -4.0); // A2
2918 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.45958,
2919 m_pDoc
->GetValue(aPos
), 10e-5);
2920 m_pDoc
->SetValue(3, 1, 0, 1.0); // D2
2921 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.35524,
2922 m_pDoc
->GetValue(aPos
), 10e-5);
2923 m_pDoc
->SetValue(1, 1, 0, 5.0); // B2
2924 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.35524,
2925 m_pDoc
->GetValue(aPos
), 10e-5);
2926 m_pDoc
->SetValue(4, 1, 0, -2.0); // E2
2927 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.41043,
2928 m_pDoc
->GetValue(aPos
), 10e-5);
2929 m_pDoc
->SetValue(2, 1, 0, -1.0); // C2
2930 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.41043,
2931 m_pDoc
->GetValue(aPos
), 10e-5);
2932 m_pDoc
->SetValue(5, 1, 0, -3.0); // F2
2933 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34990,
2934 m_pDoc
->GetValue(aPos
), 10e-5);
2935 m_pDoc
->SetValue(0, 2, 0, 10.0); // A3
2936 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34990,
2937 m_pDoc
->GetValue(aPos
), 10e-5);
2938 m_pDoc
->SetValue(3, 2, 0, 10.0); // D3
2939 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34686,
2940 m_pDoc
->GetValue(aPos
), 10e-5);
2941 m_pDoc
->SetValue(1, 2, 0, 3.0); // B3
2942 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34686,
2943 m_pDoc
->GetValue(aPos
), 10e-5);
2944 m_pDoc
->SetValue(4, 2, 0, 9.0); // E3
2945 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.47198,
2946 m_pDoc
->GetValue(aPos
), 10e-5);
2947 m_pDoc
->SetValue(2, 2, 0, -5.0); // C3
2948 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.47198,
2949 m_pDoc
->GetValue(aPos
), 10e-5);
2950 m_pDoc
->SetValue(5, 2, 0, 6.0); // F3
2951 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.25529,
2952 m_pDoc
->GetValue(aPos
), 10e-5);
2954 m_pDoc
->SetString(1, 1, 0, u
"a"_ustr
); // B2
2955 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12016,
2956 m_pDoc
->GetValue(aPos
), 10e-5);
2957 m_pDoc
->SetString(4, 1, 0, u
"b"_ustr
); // E2
2958 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12016,
2959 m_pDoc
->GetValue(aPos
), 10e-5);
2960 m_pDoc
->SetString(2, 2, 0, u
"c"_ustr
); // C3
2961 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.25030,
2962 m_pDoc
->GetValue(aPos
), 10e-5);
2963 m_pDoc
->SetString(5, 1, 0, u
"d"_ustr
); // F2
2964 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.19637,
2965 m_pDoc
->GetValue(aPos
), 10e-5);
2967 // type 1, mode/tails 2
2968 m_pDoc
->SetString(aPos
, u
"=TTEST(A1:C3;D1:F3;2;1)"_ustr
);
2969 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.39273,
2970 m_pDoc
->GetValue(aPos
), 10e-5);
2971 m_pDoc
->SetValue(1, 1, 0, 4.0); // B2
2972 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.39273,
2973 m_pDoc
->GetValue(aPos
), 10e-5);
2974 m_pDoc
->SetValue(4, 1, 0, 3.0); // E2
2975 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.43970,
2976 m_pDoc
->GetValue(aPos
), 10e-5);
2977 m_pDoc
->SetValue(2, 2, 0, -2.0); // C3
2978 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.22217,
2979 m_pDoc
->GetValue(aPos
), 10e-5);
2980 m_pDoc
->SetValue(5, 1, 0, -10.0); // F2
2981 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.64668,
2982 m_pDoc
->GetValue(aPos
), 10e-5);
2983 m_pDoc
->SetValue(0, 1, 0, 3.0); // A2
2984 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.95266,
2985 m_pDoc
->GetValue(aPos
), 10e-5);
2986 m_pDoc
->SetValue(3, 2, 0, -1.0); // D3
2987 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.62636,
2988 m_pDoc
->GetValue(aPos
), 10e-5);
2990 // type 2, mode/tails 2
2991 m_pDoc
->SetString(aPos
, u
"=TTEST(A1:C3;D1:F3;2;2)"_ustr
);
2992 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.62549,
2993 m_pDoc
->GetValue(aPos
), 10e-5);
2994 m_pDoc
->SetValue(5, 1, 0, -1.0); // F2
2995 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.94952,
2996 m_pDoc
->GetValue(aPos
), 10e-5);
2997 m_pDoc
->SetValue(2, 2, 0, 5.0); // C3
2998 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.58876,
2999 m_pDoc
->GetValue(aPos
), 10e-5);
3000 m_pDoc
->SetValue(2, 1, 0, 2.0); // C2
3001 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.43205,
3002 m_pDoc
->GetValue(aPos
), 10e-5);
3003 m_pDoc
->SetValue(3, 2, 0, -4.0); // D3
3004 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.36165,
3005 m_pDoc
->GetValue(aPos
), 10e-5);
3006 m_pDoc
->SetValue(0, 1, 0, 1.0); // A2
3007 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.44207,
3008 m_pDoc
->GetValue(aPos
), 10e-5);
3010 // type 3, mode/tails 1
3011 m_pDoc
->SetString(aPos
, u
"=TTEST(A1:C3;D1:F3;1;3)"_ustr
);
3012 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.22132,
3013 m_pDoc
->GetValue(aPos
), 10e-5);
3014 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
3015 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.36977,
3016 m_pDoc
->GetValue(aPos
), 10e-5);
3017 m_pDoc
->SetValue(0, 2, 0, -30.0); // A3
3018 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.16871,
3019 m_pDoc
->GetValue(aPos
), 10e-5);
3020 m_pDoc
->SetValue(3, 1, 0, 5.0); // D2
3021 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.14396,
3022 m_pDoc
->GetValue(aPos
), 10e-5);
3023 m_pDoc
->SetValue(5, 1, 0, 2.0); // F2
3024 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12590,
3025 m_pDoc
->GetValue(aPos
), 10e-5);
3026 m_pDoc
->SetValue(4, 2, 0, 2.0); // E3
3027 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.16424,
3028 m_pDoc
->GetValue(aPos
), 10e-5);
3029 m_pDoc
->SetValue(5, 0, 0, -1.0); // F1
3030 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.21472,
3031 m_pDoc
->GetValue(aPos
), 10e-5);
3033 m_pDoc
->DeleteTab(0);
3036 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncSUMX2PY2
)
3038 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3040 m_pDoc
->InsertTab(0, u
"SumX2PY2 Test"_ustr
);
3043 ScAddress
aPos(6, 0, 0);
3044 m_pDoc
->SetString(aPos
, u
"=SUMX2PY2(A1:C3;D1:F3)"_ustr
);
3045 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 0.0, m_pDoc
->GetValue(aPos
));
3047 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
3048 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 0.0, m_pDoc
->GetValue(aPos
));
3049 m_pDoc
->SetValue(3, 0, 0, 2.0); // D1
3050 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 5.0, m_pDoc
->GetValue(aPos
));
3051 m_pDoc
->SetValue(1, 0, 0, 2.0); // B1
3052 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 5.0, m_pDoc
->GetValue(aPos
));
3053 m_pDoc
->SetValue(4, 0, 0, 0.0); // E1
3054 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 9.0, m_pDoc
->GetValue(aPos
));
3055 m_pDoc
->SetValue(2, 0, 0, 3.0); // C1
3056 m_pDoc
->SetValue(5, 0, 0, 3.0); // F1
3057 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 27.0, m_pDoc
->GetValue(aPos
));
3058 m_pDoc
->SetValue(0, 1, 0, 10.0); // A2
3059 m_pDoc
->SetValue(3, 1, 0, -10.0); // D2
3060 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 227.0, m_pDoc
->GetValue(aPos
));
3061 m_pDoc
->SetValue(1, 1, 0, -5.0); // B2
3062 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 227.0, m_pDoc
->GetValue(aPos
));
3063 m_pDoc
->SetValue(4, 1, 0, -5.0); // E2
3064 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 277.0, m_pDoc
->GetValue(aPos
));
3065 m_pDoc
->SetValue(2, 1, 0, 0.0); // C2
3066 m_pDoc
->SetValue(5, 1, 0, 0.0); // F2
3067 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 277.0, m_pDoc
->GetValue(aPos
));
3068 m_pDoc
->SetValue(0, 2, 0, -8.0); // A3
3069 m_pDoc
->SetValue(3, 2, 0, 8.0); // D3
3070 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 405.0, m_pDoc
->GetValue(aPos
));
3071 m_pDoc
->SetValue(1, 2, 0, 0.0); // B3
3072 m_pDoc
->SetValue(4, 2, 0, 0.0); // E3
3073 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 405.0, m_pDoc
->GetValue(aPos
));
3074 m_pDoc
->SetValue(2, 2, 0, 1.0); // C3
3075 m_pDoc
->SetValue(5, 2, 0, 1.0); // F3
3076 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 407.0, m_pDoc
->GetValue(aPos
));
3079 m_pDoc
->SetString(4, 1, 0, u
"a"_ustr
); // E2
3080 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 357.0, m_pDoc
->GetValue(aPos
));
3081 m_pDoc
->SetString(1, 1, 0, u
"a"_ustr
); // B2
3082 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 357.0, m_pDoc
->GetValue(aPos
));
3083 m_pDoc
->SetString(0, 0, 0, u
"a"_ustr
); // A1
3084 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 352.0, m_pDoc
->GetValue(aPos
));
3085 m_pDoc
->SetString(3, 0, 0, u
"a"_ustr
); // D1
3086 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 352.0, m_pDoc
->GetValue(aPos
));
3088 m_pDoc
->SetString(aPos
, u
"=SUMX2PY2({1;2;3};{2;3;4})"_ustr
);
3089 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 43.0, m_pDoc
->GetValue(aPos
));
3090 m_pDoc
->SetString(aPos
, u
"=SUMX2PY2({1;2;3};{2;3})"_ustr
);
3091 aVal
= m_pDoc
->GetString(aPos
);
3092 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2PY2 should return #VALUE! for matrices with different sizes",
3093 u
"#VALUE!"_ustr
, aVal
);
3094 m_pDoc
->SetString(aPos
, u
"=SUMX2PY2({1;2;3})"_ustr
);
3095 aVal
= m_pDoc
->GetString(aPos
);
3096 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2PY2 needs two parameters", u
"Err:511"_ustr
, aVal
);
3098 m_pDoc
->DeleteTab(0);
3101 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncSUMX2MY2
)
3103 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3105 m_pDoc
->InsertTab(0, u
"SumX2MY2 Test"_ustr
);
3108 ScAddress
aPos(6, 0, 0);
3109 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2(A1:C3;D1:F3)"_ustr
);
3110 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 0.0, m_pDoc
->GetValue(aPos
));
3112 m_pDoc
->SetValue(0, 0, 0, 10.0); // A1
3113 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 0.0, m_pDoc
->GetValue(aPos
));
3114 m_pDoc
->SetValue(3, 0, 0, -9.0); // D1
3115 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 19.0, m_pDoc
->GetValue(aPos
));
3116 m_pDoc
->SetValue(1, 0, 0, 2.0); // B1
3117 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 19.0, m_pDoc
->GetValue(aPos
));
3118 m_pDoc
->SetValue(4, 0, 0, 1.0); // E1
3119 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3120 m_pDoc
->SetValue(2, 0, 0, 3.0); // C1
3121 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3122 m_pDoc
->SetValue(5, 0, 0, 3.0); // F1
3123 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3124 m_pDoc
->SetValue(0, 1, 0, 10.0); // A2
3125 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3126 m_pDoc
->SetValue(3, 1, 0, -10.0); // D2
3127 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3128 m_pDoc
->SetValue(1, 1, 0, -5.0); // B2
3129 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3130 m_pDoc
->SetValue(4, 1, 0, -5.0); // E2
3131 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3132 m_pDoc
->SetValue(2, 1, 0, -3.0); // C2
3133 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3134 m_pDoc
->SetValue(5, 1, 0, 3.0); // F2
3135 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3136 m_pDoc
->SetValue(0, 2, 0, -8.0); // A3
3137 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc
->GetValue(aPos
));
3138 m_pDoc
->SetValue(3, 2, 0, 3.0); // D3
3139 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 77.0, m_pDoc
->GetValue(aPos
));
3140 m_pDoc
->SetValue(1, 2, 0, 2.0); // B3
3141 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 77.0, m_pDoc
->GetValue(aPos
));
3142 m_pDoc
->SetValue(4, 2, 0, -6.0); // E3
3143 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc
->GetValue(aPos
));
3144 m_pDoc
->SetValue(2, 2, 0, -4.0); // C3
3145 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc
->GetValue(aPos
));
3146 m_pDoc
->SetValue(5, 2, 0, 6.0); // F3
3147 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 25.0, m_pDoc
->GetValue(aPos
));
3150 m_pDoc
->SetString(5, 2, 0, u
"a"_ustr
); // F3
3151 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc
->GetValue(aPos
));
3152 m_pDoc
->SetString(0, 2, 0, u
"a"_ustr
); // A3
3153 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -10.0, m_pDoc
->GetValue(aPos
));
3154 m_pDoc
->SetString(1, 0, 0, u
"a"_ustr
); // B1
3155 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -13.0, m_pDoc
->GetValue(aPos
));
3156 m_pDoc
->SetString(3, 0, 0, u
"a"_ustr
); // D1
3157 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -32.0, m_pDoc
->GetValue(aPos
));
3159 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2({1;3;5};{0;4;4})"_ustr
);
3160 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 3.0, m_pDoc
->GetValue(aPos
));
3161 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2({1;-3;-5};{0;-4;4})"_ustr
);
3162 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 3.0, m_pDoc
->GetValue(aPos
));
3163 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2({9;5;1};{3;-3;3})"_ustr
);
3164 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 80.0, m_pDoc
->GetValue(aPos
));
3165 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2({1;2;3};{2;3})"_ustr
);
3166 aVal
= m_pDoc
->GetString(aPos
);
3167 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2MY2 should return #VALUE! for matrices with different sizes",
3168 u
"#VALUE!"_ustr
, aVal
);
3169 m_pDoc
->SetString(aPos
, u
"=SUMX2MY2({1;2;3})"_ustr
);
3170 aVal
= m_pDoc
->GetString(aPos
);
3171 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2MY2 needs two parameters", u
"Err:511"_ustr
, aVal
);
3173 m_pDoc
->DeleteTab(0);
3176 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncGCD
)
3178 #ifndef DISABLE_NAN_TESTS
3179 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3181 m_pDoc
->InsertTab(0, u
"GCDTest"_ustr
);
3184 ScAddress
aPos(4, 0, 0);
3186 m_pDoc
->SetString(aPos
, u
"=GCD(A1)"_ustr
);
3187 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 0.0, m_pDoc
->GetValue(aPos
));
3188 m_pDoc
->SetValue(0, 0, 0, 10.0); // A1
3189 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc
->GetValue(aPos
));
3190 m_pDoc
->SetValue(0, 0, 0, -2.0); // A1
3191 aVal
= m_pDoc
->GetString(aPos
);
3192 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for values less than 0",
3193 u
"Err:502"_ustr
, aVal
);
3194 m_pDoc
->SetString(0, 0, 0, u
"a"_ustr
); // A1
3195 aVal
= m_pDoc
->GetString(aPos
);
3196 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return #VALUE! for a single string", u
"#VALUE!"_ustr
,
3199 m_pDoc
->SetString(aPos
, u
"=GCD(A1:B2)"_ustr
);
3200 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 0.0, m_pDoc
->GetValue(aPos
));
3201 m_pDoc
->SetValue(0, 1, 0, -12.0); // B1
3202 aVal
= m_pDoc
->GetString(aPos
);
3203 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for a matrix with values less than 0",
3204 u
"Err:502"_ustr
, aVal
);
3205 m_pDoc
->SetValue(0, 0, 0, 15.0); // A1
3206 m_pDoc
->SetValue(0, 1, 0, 0.0); // B1
3207 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 15.0, m_pDoc
->GetValue(aPos
));
3208 m_pDoc
->SetValue(1, 0, 0, 5.0); // B1
3209 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3210 m_pDoc
->SetValue(0, 1, 0, 10.0); // A2
3211 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3212 m_pDoc
->SetValue(1, 0, 0, 30.0); // B1
3213 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3214 m_pDoc
->SetValue(0, 0, 0, 20.0); // A1
3215 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc
->GetValue(aPos
));
3216 m_pDoc
->SetValue(1, 1, 0, 120.0); // B2
3217 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc
->GetValue(aPos
));
3218 m_pDoc
->SetValue(0, 1, 0, 80.0); // A2
3219 m_pDoc
->SetValue(1, 0, 0, 40.0); // B1
3220 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 20.0, m_pDoc
->GetValue(aPos
));
3221 m_pDoc
->SetValue(1, 0, 0, 45.0); // B1
3222 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3225 m_pDoc
->SetValue(1, 0, 0, 45.381); // B1
3226 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3227 m_pDoc
->SetValue(1, 1, 0, 120.895); // B2
3228 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3229 m_pDoc
->SetValue(0, 0, 0, 20.97); // A1
3230 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3231 m_pDoc
->SetValue(0, 1, 0, 10.15); // A2
3232 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc
->GetValue(aPos
));
3235 m_pDoc
->SetString(aPos
, u
"=GCD({3;6;9})"_ustr
);
3236 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 3.0, m_pDoc
->GetValue(aPos
));
3237 m_pDoc
->SetString(aPos
, u
"=GCD({150;0})"_ustr
);
3238 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 150.0, m_pDoc
->GetValue(aPos
));
3239 m_pDoc
->SetString(aPos
, u
"=GCD({-3;6;9})"_ustr
);
3240 aVal
= m_pDoc
->GetString(aPos
);
3241 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with values less than 0",
3242 u
"Err:502"_ustr
, aVal
);
3243 m_pDoc
->SetString(aPos
, u
"=GCD({\"a\";6;9})"_ustr
);
3244 aVal
= m_pDoc
->GetString(aPos
);
3245 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with strings",
3246 u
"Err:502"_ustr
, aVal
);
3249 m_pDoc
->SetString(aPos
, u
"=GCD({6;6;6};{3;6;9})"_ustr
);
3250 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 3.0, m_pDoc
->GetValue(aPos
));
3251 m_pDoc
->SetString(aPos
, u
"=GCD({300;300;300};{150;0})"_ustr
);
3252 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 150.0, m_pDoc
->GetValue(aPos
));
3253 m_pDoc
->SetString(aPos
, u
"=GCD({3;6;9};{3;-6;9})"_ustr
);
3254 aVal
= m_pDoc
->GetString(aPos
);
3255 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with values less than 0",
3256 u
"Err:502"_ustr
, aVal
);
3257 m_pDoc
->SetString(aPos
, u
"=GCD({3;6;9};{\"a\";6;9})"_ustr
);
3258 aVal
= m_pDoc
->GetString(aPos
);
3259 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with strings",
3260 u
"Err:502"_ustr
, aVal
);
3262 // inline list of values
3263 m_pDoc
->SetString(aPos
, u
"=GCD(12;24;36;48;60)"_ustr
);
3264 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 12.0, m_pDoc
->GetValue(aPos
));
3265 m_pDoc
->SetString(aPos
, u
"=GCD(0;12;24;36;48;60)"_ustr
);
3266 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 12.0, m_pDoc
->GetValue(aPos
));
3267 m_pDoc
->SetString(aPos
, u
"=GCD(\"a\";1)"_ustr
);
3268 aVal
= m_pDoc
->GetString(aPos
);
3269 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return #VALUE! for an array with strings",
3270 u
"#VALUE!"_ustr
, aVal
);
3272 m_pDoc
->DeleteTab(0);
3276 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncLCM
)
3278 #ifndef DISABLE_NAN_TESTS
3279 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3281 m_pDoc
->InsertTab(0, u
"LCMTest"_ustr
);
3284 ScAddress
aPos(4, 0, 0);
3286 m_pDoc
->SetString(aPos
, u
"=LCM(A1)"_ustr
);
3287 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3288 m_pDoc
->SetValue(0, 0, 0, 10.0); // A1
3289 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 10.0, m_pDoc
->GetValue(aPos
));
3290 m_pDoc
->SetValue(0, 0, 0, -2.0); // A1
3291 aVal
= m_pDoc
->GetString(aPos
);
3292 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for values less than 0",
3293 u
"Err:502"_ustr
, aVal
);
3294 m_pDoc
->SetString(0, 0, 0, u
"a"_ustr
); // A1
3295 aVal
= m_pDoc
->GetString(aPos
);
3296 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return #VALUE! for a single string", u
"#VALUE!"_ustr
,
3299 m_pDoc
->SetString(aPos
, u
"=LCM(A1:B2)"_ustr
);
3300 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 1.0, m_pDoc
->GetValue(aPos
));
3301 m_pDoc
->SetValue(0, 1, 0, -12.0); // B1
3302 aVal
= m_pDoc
->GetString(aPos
);
3303 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for a matrix with values less than 0",
3304 u
"Err:502"_ustr
, aVal
);
3305 m_pDoc
->SetValue(0, 0, 0, 15.0); // A1
3306 m_pDoc
->SetValue(0, 1, 0, 0.0); // A2
3307 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3308 m_pDoc
->SetValue(1, 0, 0, 5.0); // B1
3309 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3310 m_pDoc
->SetValue(0, 1, 0, 10.0); // A2
3311 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 30.0, m_pDoc
->GetValue(aPos
));
3312 m_pDoc
->SetValue(1, 0, 0, 30.0); // B1
3313 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 30.0, m_pDoc
->GetValue(aPos
));
3314 m_pDoc
->SetValue(0, 0, 0, 20.0); // A1
3315 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 60.0, m_pDoc
->GetValue(aPos
));
3316 m_pDoc
->SetValue(1, 1, 0, 125.0); // B2
3317 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1500.0, m_pDoc
->GetValue(aPos
));
3318 m_pDoc
->SetValue(1, 0, 0, 99.0); // B1
3319 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 49500.0, m_pDoc
->GetValue(aPos
));
3320 m_pDoc
->SetValue(0, 1, 0, 37.0); // A2
3321 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1831500.0,
3322 m_pDoc
->GetValue(aPos
));
3325 m_pDoc
->SetValue(1, 0, 0, 99.89); // B1
3326 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1831500.0,
3327 m_pDoc
->GetValue(aPos
));
3328 m_pDoc
->SetValue(1, 1, 0, 11.32); // B2
3329 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 73260.0, m_pDoc
->GetValue(aPos
));
3330 m_pDoc
->SetValue(0, 0, 0, 22.58); // A1
3331 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 7326.0, m_pDoc
->GetValue(aPos
));
3332 m_pDoc
->SetValue(0, 1, 0, 3.99); // A2
3333 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 198.0, m_pDoc
->GetValue(aPos
));
3336 m_pDoc
->SetString(aPos
, u
"=LCM({3;6;9})"_ustr
);
3337 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 18.0, m_pDoc
->GetValue(aPos
));
3338 m_pDoc
->SetString(aPos
, u
"=LCM({150;0})"_ustr
);
3339 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3340 m_pDoc
->SetString(aPos
, u
"=LCM({-3;6;9})"_ustr
);
3341 aVal
= m_pDoc
->GetString(aPos
);
3342 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with values less than 0",
3343 u
"Err:502"_ustr
, aVal
);
3344 m_pDoc
->SetString(aPos
, u
"=LCM({\"a\";6;9})"_ustr
);
3345 aVal
= m_pDoc
->GetString(aPos
);
3346 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with strings",
3347 u
"Err:502"_ustr
, aVal
);
3350 m_pDoc
->SetString(aPos
, u
"=LCM({6;6;6};{3;6;9})"_ustr
);
3351 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 18.0, m_pDoc
->GetValue(aPos
));
3352 m_pDoc
->SetString(aPos
, u
"=LCM({300;300;300};{150;0})"_ustr
);
3353 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3354 m_pDoc
->SetString(aPos
, u
"=LCM({3;6;9};{3;-6;9})"_ustr
);
3355 aVal
= m_pDoc
->GetString(aPos
);
3356 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with values less than 0",
3357 u
"Err:502"_ustr
, aVal
);
3358 m_pDoc
->SetString(aPos
, u
"=LCM({3;6;9};{\"a\";6;9})"_ustr
);
3359 aVal
= m_pDoc
->GetString(aPos
);
3360 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with strings",
3361 u
"Err:502"_ustr
, aVal
);
3363 m_pDoc
->SetString(aPos
, u
"=LCM(12;24;36;48;60)"_ustr
);
3364 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 720.0, m_pDoc
->GetValue(aPos
));
3365 m_pDoc
->SetString(aPos
, u
"=LCM(0;12;24;36;48;60)"_ustr
);
3366 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc
->GetValue(aPos
));
3367 m_pDoc
->SetString(aPos
, u
"=LCM(\"a\";1)"_ustr
);
3368 aVal
= m_pDoc
->GetString(aPos
);
3369 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return #VALUE! for an array with strings",
3370 u
"#VALUE!"_ustr
, aVal
);
3372 m_pDoc
->DeleteTab(0);
3376 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncSUMSQ
)
3378 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3380 m_pDoc
->InsertTab(0, u
"SUMSQTest"_ustr
);
3382 ScAddress
aPos(4, 0, 0);
3384 m_pDoc
->SetString(aPos
, u
"=SUMSQ(A1)"_ustr
);
3385 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 0.0, m_pDoc
->GetValue(aPos
));
3386 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
3387 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc
->GetValue(aPos
));
3388 m_pDoc
->SetValue(0, 0, 0, -1.0); // A1
3389 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc
->GetValue(aPos
));
3390 m_pDoc
->SetValue(0, 1, 0, -2.0); // A2
3391 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc
->GetValue(aPos
));
3393 m_pDoc
->SetString(aPos
, u
"=SUMSQ(A1:A3)"_ustr
);
3394 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 5.0, m_pDoc
->GetValue(aPos
));
3395 m_pDoc
->SetValue(1, 0, 0, 3.0); // B1
3396 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 5.0, m_pDoc
->GetValue(aPos
));
3397 m_pDoc
->SetString(aPos
, u
"=SUMSQ(A1:C3)"_ustr
);
3398 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 14.0, m_pDoc
->GetValue(aPos
));
3399 m_pDoc
->SetValue(1, 1, 0, -4.0); // B2
3400 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 30.0, m_pDoc
->GetValue(aPos
));
3401 m_pDoc
->SetString(1, 2, 0, u
"a"_ustr
); // B3
3402 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 30.0,
3403 m_pDoc
->GetValue(aPos
));
3404 m_pDoc
->SetValue(1, 2, 0, 0.0); // B3
3405 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 30.0,
3406 m_pDoc
->GetValue(aPos
));
3407 m_pDoc
->SetValue(0, 2, 0, 6.0); // A3
3408 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 66.0,
3409 m_pDoc
->GetValue(aPos
));
3410 m_pDoc
->SetValue(2, 0, 0, -5.0); // C1
3411 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 91.0,
3412 m_pDoc
->GetValue(aPos
));
3413 m_pDoc
->SetValue(2, 1, 0, 3.0); // C2
3414 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 100.0,
3415 m_pDoc
->GetValue(aPos
));
3416 m_pDoc
->SetValue(2, 2, 0, 2.0); // C3
3417 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 104.0,
3418 m_pDoc
->GetValue(aPos
));
3421 m_pDoc
->SetString(aPos
, u
"=SUMSQ({1;2;3})"_ustr
);
3422 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 14.0, m_pDoc
->GetValue(aPos
));
3423 m_pDoc
->SetString(aPos
, u
"=SUMSQ({3;6;9})"_ustr
);
3424 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 126.0, m_pDoc
->GetValue(aPos
));
3425 m_pDoc
->SetString(aPos
, u
"=SUMSQ({15;0})"_ustr
);
3426 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 225.0, m_pDoc
->GetValue(aPos
));
3427 m_pDoc
->SetString(aPos
, u
"=SUMSQ({-3;3;1})"_ustr
);
3428 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 19.0, m_pDoc
->GetValue(aPos
));
3429 m_pDoc
->SetString(aPos
, u
"=SUMSQ({\"a\";-4;-5})"_ustr
);
3430 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 41.0, m_pDoc
->GetValue(aPos
));
3432 m_pDoc
->SetString(aPos
, u
"=SUMSQ({2;3};{4;5})"_ustr
);
3433 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 54.0, m_pDoc
->GetValue(aPos
));
3434 m_pDoc
->SetString(aPos
, u
"=SUMSQ({-3;3;1};{-1})"_ustr
);
3435 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 20.0, m_pDoc
->GetValue(aPos
));
3436 m_pDoc
->SetString(aPos
, u
"=SUMSQ({-4};{1;4;2};{-5;7};{9})"_ustr
);
3437 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 192.0, m_pDoc
->GetValue(aPos
));
3438 m_pDoc
->SetString(aPos
, u
"=SUMSQ({-2;2};{1};{-1};{0;0;0;4})"_ustr
);
3439 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 26.0, m_pDoc
->GetValue(aPos
));
3441 m_pDoc
->SetString(aPos
, u
"=SUMSQ(4;1;-3)"_ustr
);
3442 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 26.0, m_pDoc
->GetValue(aPos
));
3443 m_pDoc
->SetString(aPos
, u
"=SUMSQ(0;5;13;-7;-4)"_ustr
);
3444 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 259.0, m_pDoc
->GetValue(aPos
));
3445 m_pDoc
->SetString(aPos
, u
"=SUMSQ(0;12;24;36;48;60)"_ustr
);
3446 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 7920.0, m_pDoc
->GetValue(aPos
));
3447 m_pDoc
->SetString(aPos
, u
"=SUMSQ(0;-12;-24;36;-48;60)"_ustr
);
3448 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 7920.0, m_pDoc
->GetValue(aPos
));
3449 m_pDoc
->SetString(aPos
, u
"=SUMSQ(\"a\";1;\"d\";-4;2)"_ustr
);
3450 OUString aVal
= m_pDoc
->GetString(aPos
);
3451 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMSQ should return #VALUE! for an array with strings",
3452 u
"#VALUE!"_ustr
, aVal
);
3454 m_pDoc
->DeleteTab(0);
3457 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncMDETERM
)
3459 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3461 m_pDoc
->InsertTab(0, u
"MDETERM_test"_ustr
);
3462 ScAddress
aPos(8, 0, 0);
3463 static constexpr std::u16string_view
aColCodes(u
"ABCDEFGH");
3464 OUStringBuffer
aFormulaBuffer("=MDETERM(A1:B2)");
3465 for (SCSIZE nSize
= 3; nSize
<= 8; nSize
++)
3468 // Generate a singular integer matrix
3469 for (SCROW nRow
= 0; nRow
< static_cast<SCROW
>(nSize
); nRow
++)
3471 for (SCCOL nCol
= 0; nCol
< static_cast<SCCOL
>(nSize
); nCol
++)
3473 m_pDoc
->SetValue(nCol
, nRow
, 0, fVal
);
3477 aFormulaBuffer
[12] = aColCodes
[nSize
- 1];
3478 aFormulaBuffer
[13] = static_cast<sal_Unicode
>('0' + nSize
);
3479 m_pDoc
->SetString(aPos
, aFormulaBuffer
.toString());
3481 #if SAL_TYPES_SIZEOFPOINTER == 4
3482 // On crappy 32-bit targets, presumably without extended precision on
3483 // interim results or optimization not catching it, this test fails
3484 // when comparing to 0.0, so have a narrow error margin. See also
3485 // commit message of 8140309d636d4a870875f2dd75ed3dfff2c0fbaf
3486 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3487 "Calculation of MDETERM incorrect for singular integer matrix", 0.0,
3488 m_pDoc
->GetValue(aPos
), 1e-12);
3490 // Even on one (and only one) x86_64 target the result was
3491 // 6.34413156928661e-17 instead of 0.0 (tdf#99730) so lower the bar to
3493 // Then again on aarch64, ppc64* and s390x it also fails.
3494 // Sigh... why do we even test this? The original complaint in tdf#32834
3495 // was about -9.51712667007776E-016
3496 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3497 "Calculation of MDETERM incorrect for singular integer matrix", 0.0,
3498 m_pDoc
->GetValue(aPos
), 1e-14);
3502 int const aVals
[] = { 23, 31, 13, 12, 34, 64, 34, 31, 98, 32, 33, 63, 45, 54, 65, 76 };
3504 for (SCROW nRow
= 0; nRow
< 4; nRow
++)
3505 for (SCCOL nCol
= 0; nCol
< 4; nCol
++)
3506 m_pDoc
->SetValue(nCol
, nRow
, 0, static_cast<double>(aVals
[nIdx
++]));
3507 m_pDoc
->SetString(aPos
, u
"=MDETERM(A1:D4)"_ustr
);
3508 // Following test is conservative in the sense that on Linux x86_64 the error is less that 1.0E-9
3509 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3510 "Calculation of MDETERM incorrect for non-singular integer matrix", -180655.0,
3511 m_pDoc
->GetValue(aPos
), 1.0E-6);
3512 m_pDoc
->DeleteTab(0);
3515 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaErrorPropagation
)
3517 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3519 m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
);
3521 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
3522 aMark
.SelectOneTable(0);
3523 ScAddress aPos
, aPos2
;
3524 static constexpr OUString
aTRUE(u
"TRUE"_ustr
);
3525 static constexpr OUString
aFALSE(u
"FALSE"_ustr
);
3527 aPos
.Set(0, 0, 0); // A1
3528 m_pDoc
->SetValue(aPos
, 1.0);
3529 aPos
.IncCol(); // B1
3530 m_pDoc
->SetValue(aPos
, 2.0);
3533 aPos
.IncRow(); // C2
3534 m_pDoc
->SetString(aPos
, u
"=ISERROR(A1:B1+3)"_ustr
);
3535 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3536 m_pDoc
->GetString(aPos
));
3538 aPos
.IncRow(); // C3
3539 m_pDoc
->SetString(aPos
, u
"=ISERROR(A1:B1+{3})"_ustr
);
3540 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3541 m_pDoc
->GetString(aPos
));
3542 aPos
.IncRow(); // C4
3544 aPos2
.IncCol(); // D4
3545 m_pDoc
->InsertMatrixFormula(aPos
.Col(), aPos
.Row(), aPos2
.Col(), aPos2
.Row(), aMark
,
3546 u
"=ISERROR(A1:B1+{3})"_ustr
);
3547 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3548 m_pDoc
->GetString(aPos
));
3549 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3550 m_pDoc
->GetString(aPos2
));
3552 aPos
.IncRow(); // C5
3553 m_pDoc
->SetString(aPos
, u
"=ISERROR({1;\"x\"}+{3;4})"_ustr
);
3554 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3555 m_pDoc
->GetString(aPos
));
3556 aPos
.IncRow(); // C6
3558 aPos2
.IncCol(); // D6
3559 m_pDoc
->InsertMatrixFormula(aPos
.Col(), aPos
.Row(), aPos2
.Col(), aPos2
.Row(), aMark
,
3560 u
"=ISERROR({1;\"x\"}+{3;4})"_ustr
);
3561 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3562 m_pDoc
->GetString(aPos
));
3563 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3564 m_pDoc
->GetString(aPos2
));
3566 aPos
.IncRow(); // C7
3567 m_pDoc
->SetString(aPos
, u
"=ISERROR({\"x\";2}+{3;4})"_ustr
);
3568 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3569 m_pDoc
->GetString(aPos
));
3570 aPos
.IncRow(); // C8
3572 aPos2
.IncCol(); // D8
3573 m_pDoc
->InsertMatrixFormula(aPos
.Col(), aPos
.Row(), aPos2
.Col(), aPos2
.Row(), aMark
,
3574 u
"=ISERROR({\"x\";2}+{3;4})"_ustr
);
3575 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3576 m_pDoc
->GetString(aPos
));
3577 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3578 m_pDoc
->GetString(aPos2
));
3580 aPos
.IncRow(); // C9
3581 m_pDoc
->SetString(aPos
, u
"=ISERROR(({1;\"x\"}+{3;4})-{5;6})"_ustr
);
3582 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3583 m_pDoc
->GetString(aPos
));
3584 aPos
.IncRow(); // C10
3586 aPos2
.IncCol(); // D10
3587 m_pDoc
->InsertMatrixFormula(aPos
.Col(), aPos
.Row(), aPos2
.Col(), aPos2
.Row(), aMark
,
3588 u
"=ISERROR(({1;\"x\"}+{3;4})-{5;6})"_ustr
);
3589 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3590 m_pDoc
->GetString(aPos
));
3591 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3592 m_pDoc
->GetString(aPos2
));
3594 aPos
.IncRow(); // C11
3595 m_pDoc
->SetString(aPos
, u
"=ISERROR(({\"x\";2}+{3;4})-{5;6})"_ustr
);
3596 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3597 m_pDoc
->GetString(aPos
));
3598 aPos
.IncRow(); // C12
3600 aPos2
.IncCol(); // D12
3601 m_pDoc
->InsertMatrixFormula(aPos
.Col(), aPos
.Row(), aPos2
.Col(), aPos2
.Row(), aMark
,
3602 u
"=ISERROR(({\"x\";2}+{3;4})-{5;6})"_ustr
);
3603 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aTRUE
,
3604 m_pDoc
->GetString(aPos
));
3605 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2
.Format(ScRefFlags::VALID
).toUtf8().getStr(), aFALSE
,
3606 m_pDoc
->GetString(aPos2
));
3608 m_pDoc
->DeleteTab(0);
3611 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf97369
)
3613 const SCROW TOTAL_ROWS
= 330;
3614 const SCROW ROW_RANGE
= 10;
3615 const SCROW START1
= 9;
3616 const SCROW END1
= 159;
3617 const SCROW START2
= 169;
3618 const SCROW END2
= 319;
3620 const double SHIFT1
= 200;
3621 const double SHIFT2
= 400;
3623 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"tdf97369"_ustr
));
3625 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
3627 // set up columns A, B, C
3628 for (SCROW i
= 0; i
< TOTAL_ROWS
; ++i
)
3630 m_pDoc
->SetValue(ScAddress(0, i
, 0), i
); // A
3631 m_pDoc
->SetValue(ScAddress(1, i
, 0), i
+ SHIFT1
); // B
3632 m_pDoc
->SetValue(ScAddress(2, i
, 0), i
+ SHIFT2
); // C
3635 const ColumnTest
columnTest(m_pDoc
, TOTAL_ROWS
, START1
, END1
, START2
, END2
);
3637 auto lExpectedinD
= [=](SCROW n
) { return 3.0 * (n
- START1
) + SHIFT1
+ SHIFT2
; };
3638 columnTest(3, u
"=SUM(A1:C1)"_ustr
, lExpectedinD
);
3640 auto lExpectedinE
= [=](SCROW
) { return SHIFT1
+ SHIFT2
; };
3641 columnTest(4, u
"=SUM(A$1:C$1)"_ustr
, lExpectedinE
);
3643 auto lExpectedinF
= [](SCROW n
) { return ((2 * n
+ 1 - ROW_RANGE
) * ROW_RANGE
) / 2.0; };
3644 columnTest(5, u
"=SUM(A1:A10)"_ustr
, lExpectedinF
);
3646 auto lExpectedinG
= [](SCROW n
) { return ((n
+ 1) * n
) / 2.0; };
3647 columnTest(6, u
"=SUM(A$1:A10)"_ustr
, lExpectedinG
);
3649 auto lExpectedinH
= [=](SCROW n
) {
3650 return 3.0 * (((2 * n
+ 1 - ROW_RANGE
) * ROW_RANGE
) / 2) + ROW_RANGE
* (SHIFT1
+ SHIFT2
);
3652 columnTest(7, u
"=SUM(A1:C10)"_ustr
, lExpectedinH
);
3654 auto lExpectedinI
= [=](SCROW
) {
3655 return 3.0 * (((2 * START1
+ 1 - ROW_RANGE
) * ROW_RANGE
) / 2)
3656 + ROW_RANGE
* (SHIFT1
+ SHIFT2
);
3658 columnTest(8, u
"=SUM(A$1:C$10)"_ustr
, lExpectedinI
);
3660 m_pDoc
->DeleteTab(0);
3663 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf97587
)
3665 const SCROW TOTAL_ROWS
= 150;
3666 const SCROW ROW_RANGE
= 10;
3668 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc
->InsertTab(0, u
"tdf97587"_ustr
));
3670 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn on auto calc.
3672 std::set
<SCROW
> emptyCells
= { 0, 100 };
3673 for (SCROW i
= 0; i
< ROW_RANGE
; ++i
)
3675 emptyCells
.insert(i
+ TOTAL_ROWS
/ 3);
3676 emptyCells
.insert(i
+ TOTAL_ROWS
);
3680 for (SCROW i
= 0; i
< TOTAL_ROWS
; ++i
)
3682 if (emptyCells
.find(i
) != emptyCells
.end())
3684 m_pDoc
->SetValue(ScAddress(0, i
, 0), 1.0);
3687 ScDocument
aClipDoc(SCDOCMODE_CLIP
);
3688 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
3690 ScAddress
aPos(1, 0, 0);
3691 m_pDoc
->SetString(aPos
, u
"=SUM(A1:A10)"_ustr
);
3693 // Copy formula cell to clipboard.
3694 ScClipParam
aClipParam(ScRange(aPos
), false);
3695 aMark
.SetMarkArea(ScRange(aPos
));
3696 m_pDoc
->CopyToClip(aClipParam
, &aClipDoc
, &aMark
, false, false);
3698 // Paste it to first range.
3699 ScRange
aDestRange(1, 1, 0, 1, TOTAL_ROWS
+ ROW_RANGE
, 0);
3700 aMark
.SetMarkArea(aDestRange
);
3701 m_pDoc
->CopyFromClip(aDestRange
, aMark
, InsertDeleteFlags::CONTENTS
, nullptr, &aClipDoc
);
3703 // Check the formula results in column B.
3704 for (SCROW i
= 0; i
< TOTAL_ROWS
+ 1; ++i
)
3706 int k
= std::count_if(emptyCells
.begin(), emptyCells
.end(),
3707 [=](SCROW n
) { return (i
<= n
&& n
< i
+ ROW_RANGE
); });
3708 double fExpected
= ROW_RANGE
- k
;
3709 ASSERT_DOUBLES_EQUAL(fExpected
, m_pDoc
->GetValue(ScAddress(1, i
, 0)));
3711 m_pDoc
->DeleteTab(0);
3714 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf93415
)
3716 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
));
3718 ScCalcConfig aConfig
;
3719 aConfig
.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1
);
3720 m_pDoc
->SetCalcConfig(aConfig
);
3723 ScAddress
aPos(0, 0, 0);
3724 m_pDoc
->SetString(aPos
, u
"=ADDRESS(1;1;;;\"Sheet1\")"_ustr
);
3726 // Without the fix in place, this would have failed with
3727 // - Expected: Sheet1!$A$1
3728 // - Actual : Sheet1.$A$1
3729 CPPUNIT_ASSERT_EQUAL(u
"Sheet1!$A$1"_ustr
, m_pDoc
->GetString(aPos
));
3731 m_pDoc
->DeleteTab(0);
3734 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf132519
)
3736 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
));
3738 ScCalcConfig aConfig
;
3739 aConfig
.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1
);
3740 m_pDoc
->SetCalcConfig(aConfig
);
3743 m_pDoc
->SetString(2, 0, 0, u
"X"_ustr
);
3744 m_pDoc
->SetString(1, 0, 0, u
"=CELL(\"ADDRESS\"; C1)"_ustr
);
3745 m_pDoc
->SetString(0, 0, 0, u
"=INDIRECT(B1)"_ustr
);
3747 // Without the fix in place, this test would have failed with
3750 CPPUNIT_ASSERT_EQUAL(u
"X"_ustr
, m_pDoc
->GetString(0, 0, 0));
3752 CPPUNIT_ASSERT_EQUAL(u
"R1C3"_ustr
, m_pDoc
->GetString(1, 0, 0));
3754 m_pDoc
->DeleteTab(0);
3757 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf127334
)
3759 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
));
3763 u
"= (((DATE(2019;9;17) + TIME(0;0;1)) - DATE(2019;9;17)) - TIME(0;0;1))/TIME(0;0;1)"_ustr
);
3765 // Without the fix in place, this test would have failed with
3767 // - Actual : 2.32e-07
3768 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(0, 0, 0));
3770 m_pDoc
->DeleteTab(0);
3773 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf100818
)
3775 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Sheet1"_ustr
));
3777 //Insert local range name
3778 ScRangeData
* pLocal
= new ScRangeData(*m_pDoc
, u
"local"_ustr
, u
"$Sheet1.$A$1"_ustr
);
3779 std::unique_ptr
<ScRangeName
> pLocalRangeName(new ScRangeName
);
3780 pLocalRangeName
->insert(pLocal
);
3781 m_pDoc
->SetRangeName(0, std::move(pLocalRangeName
));
3783 m_pDoc
->SetValue(0, 0, 0, 1.0);
3785 CPPUNIT_ASSERT(m_pDoc
->InsertTab(1, u
"Sheet2"_ustr
));
3787 m_pDoc
->SetString(0, 0, 1, u
"=INDIRECT(\"Sheet1.local\")"_ustr
);
3789 // Without the fix in place, this test would have failed with
3792 CPPUNIT_ASSERT_EQUAL(u
"1"_ustr
, m_pDoc
->GetString(0, 0, 1));
3794 m_pDoc
->DeleteTab(1);
3795 m_pDoc
->SetRangeName(0, nullptr); // Delete the names.
3796 m_pDoc
->DeleteTab(0);
3799 CPPUNIT_TEST_FIXTURE(TestFormula2
, testMatConcat
)
3801 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Test"_ustr
));
3803 for (SCCOL nCol
= 0; nCol
< 10; ++nCol
)
3805 for (SCROW nRow
= 0; nRow
< 10; ++nRow
)
3807 m_pDoc
->SetValue(ScAddress(nCol
, nRow
, 0), nCol
* nRow
);
3811 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
3812 aMark
.SelectOneTable(0);
3813 m_pDoc
->InsertMatrixFormula(0, 12, 9, 21, aMark
, u
"=A1:J10&A1:J10"_ustr
);
3815 for (SCCOL nCol
= 0; nCol
< 10; ++nCol
)
3817 for (SCROW nRow
= 12; nRow
< 22; ++nRow
)
3819 OUString aStr
= m_pDoc
->GetString(ScAddress(nCol
, nRow
, 0));
3820 CPPUNIT_ASSERT_EQUAL(OUString(OUString::number(nCol
* (nRow
- 12))
3821 + OUString::number(nCol
* (nRow
- 12))),
3826 { // Data in A12:B16
3827 std::vector
<std::vector
<const char*>> aData
= {
3828 { "q", "w" }, { "a", "" }, { "", "x" }, { "", "" }, { "e", "r" },
3831 ScAddress
aPos(0, 11, 0);
3832 ScRange aRange
= insertRangeData(m_pDoc
, aPos
, aData
);
3833 CPPUNIT_ASSERT_EQUAL(aPos
, aRange
.aStart
);
3835 // Matrix formula in C17:C21
3836 m_pDoc
->InsertMatrixFormula(2, 16, 2, 20, aMark
, u
"=A12:A16&B12:B16"_ustr
);
3837 // Check proper concatenation including empty cells.
3839 ScAddress
aPos(2, 16, 0);
3840 aStr
= m_pDoc
->GetString(aPos
);
3841 CPPUNIT_ASSERT_EQUAL(u
"qw"_ustr
, aStr
);
3843 aStr
= m_pDoc
->GetString(aPos
);
3844 CPPUNIT_ASSERT_EQUAL(u
"a"_ustr
, aStr
);
3846 aStr
= m_pDoc
->GetString(aPos
);
3847 CPPUNIT_ASSERT_EQUAL(u
"x"_ustr
, aStr
);
3849 aStr
= m_pDoc
->GetString(aPos
);
3850 CPPUNIT_ASSERT_EQUAL(OUString(), aStr
);
3852 aStr
= m_pDoc
->GetString(aPos
);
3853 CPPUNIT_ASSERT_EQUAL(u
"er"_ustr
, aStr
);
3855 m_pDoc
->DeleteTab(0);
3858 CPPUNIT_TEST_FIXTURE(TestFormula2
, testMatConcatReplication
)
3860 // if one of the matrices is a one column or row matrix
3861 // the matrix is replicated across the larger matrix
3862 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Test"_ustr
));
3864 for (SCCOL nCol
= 0; nCol
< 10; ++nCol
)
3866 for (SCROW nRow
= 0; nRow
< 10; ++nRow
)
3868 m_pDoc
->SetValue(ScAddress(nCol
, nRow
, 0), nCol
* nRow
);
3872 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
3873 aMark
.SelectOneTable(0);
3874 m_pDoc
->InsertMatrixFormula(0, 12, 9, 21, aMark
, u
"=A1:J10&A1:J1"_ustr
);
3876 for (SCCOL nCol
= 0; nCol
< 10; ++nCol
)
3878 for (SCROW nRow
= 12; nRow
< 22; ++nRow
)
3880 OUString aStr
= m_pDoc
->GetString(ScAddress(nCol
, nRow
, 0));
3881 CPPUNIT_ASSERT_EQUAL(OUString(OUString::number(nCol
* (nRow
- 12)) + "0"), aStr
);
3885 m_pDoc
->DeleteTab(0);
3888 CPPUNIT_TEST_FIXTURE(TestFormula2
, testRefR1C1WholeCol
)
3890 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Test"_ustr
));
3892 ScAddress
aPos(1, 1, 1);
3893 ScCompiler
aComp(*m_pDoc
, aPos
, FormulaGrammar::GRAM_ENGLISH_XL_R1C1
);
3894 std::unique_ptr
<ScTokenArray
> pTokens(aComp
.CompileString(u
"=C[10]"_ustr
));
3895 sc::TokenStringContext
aCxt(*m_pDoc
, formula::FormulaGrammar::GRAM_ENGLISH
);
3896 OUString aFormula
= pTokens
->CreateString(aCxt
, aPos
);
3898 CPPUNIT_ASSERT_EQUAL(u
"L:L"_ustr
, aFormula
);
3900 m_pDoc
->DeleteTab(0);
3903 CPPUNIT_TEST_FIXTURE(TestFormula2
, testRefR1C1WholeRow
)
3905 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Test"_ustr
));
3907 ScAddress
aPos(1, 1, 1);
3908 ScCompiler
aComp(*m_pDoc
, aPos
, FormulaGrammar::GRAM_ENGLISH_XL_R1C1
);
3909 std::unique_ptr
<ScTokenArray
> pTokens(aComp
.CompileString(u
"=R[3]"_ustr
));
3910 sc::TokenStringContext
aCxt(*m_pDoc
, formula::FormulaGrammar::GRAM_ENGLISH
);
3911 OUString aFormula
= pTokens
->CreateString(aCxt
, aPos
);
3913 CPPUNIT_ASSERT_EQUAL(u
"5:5"_ustr
, aFormula
);
3915 m_pDoc
->DeleteTab(0);
3918 CPPUNIT_TEST_FIXTURE(TestFormula2
, testSingleCellCopyColumnLabel
)
3920 ScDocOptions aOptions
= m_pDoc
->GetDocOptions();
3921 aOptions
.SetLookUpColRowNames(true);
3922 m_pDoc
->SetDocOptions(aOptions
);
3923 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
3925 m_pDoc
->SetString(0, 0, 0, u
"a"_ustr
);
3926 m_pDoc
->SetValue(0, 1, 0, 1.0);
3927 m_pDoc
->SetValue(0, 2, 0, 2.0);
3928 m_pDoc
->SetValue(0, 3, 0, 3.0);
3929 m_pDoc
->SetString(1, 1, 0, u
"='a'"_ustr
);
3931 double nVal
= m_pDoc
->GetValue(1, 1, 0);
3932 ASSERT_DOUBLES_EQUAL(1.0, nVal
);
3934 ScDocument
aClipDoc(SCDOCMODE_CLIP
);
3935 copyToClip(m_pDoc
, ScRange(1, 1, 0), &aClipDoc
);
3936 pasteOneCellFromClip(m_pDoc
, ScRange(1, 2, 0), &aClipDoc
);
3937 nVal
= m_pDoc
->GetValue(1, 2, 0);
3938 ASSERT_DOUBLES_EQUAL(2.0, nVal
);
3940 m_pDoc
->DeleteTab(0);
3943 // Significant whitespace operator intersection in Excel syntax, tdf#96426
3944 CPPUNIT_TEST_FIXTURE(TestFormula2
, testIntersectionOpExcel
)
3946 CPPUNIT_ASSERT(m_pDoc
->InsertTab(0, u
"Test"_ustr
));
3948 ScRangeName
* pGlobalNames
= m_pDoc
->GetRangeName();
3949 // Horizontal cell range covering C2.
3950 pGlobalNames
->insert(new ScRangeData(*m_pDoc
, u
"horz"_ustr
, u
"$B$2:$D$2"_ustr
));
3951 // Vertical cell range covering C2.
3952 pGlobalNames
->insert(new ScRangeData(*m_pDoc
, u
"vert"_ustr
, u
"$C$1:$C$3"_ustr
));
3954 m_pDoc
->SetValue(2, 1, 0, 1.0);
3956 FormulaGrammarSwitch
aFGSwitch(m_pDoc
, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1
);
3958 // Choose formula positions that don't intersect with those data ranges.
3959 ScAddress
aPos(0, 3, 0);
3960 m_pDoc
->SetString(aPos
, u
"=B2:D2 C1:C3"_ustr
);
3961 CPPUNIT_ASSERT_EQUAL_MESSAGE("A4 intersecting references failed", 1.0, m_pDoc
->GetValue(aPos
));
3963 m_pDoc
->SetString(aPos
, u
"=horz vert"_ustr
);
3964 CPPUNIT_ASSERT_EQUAL_MESSAGE("A5 intersecting named expressions failed", 1.0,
3965 m_pDoc
->GetValue(aPos
));
3967 m_pDoc
->SetString(aPos
, u
"=(horz vert)*2"_ustr
);
3968 CPPUNIT_ASSERT_EQUAL_MESSAGE("A6 calculating with intersecting named expressions failed", 2.0,
3969 m_pDoc
->GetValue(aPos
));
3971 m_pDoc
->SetString(aPos
, u
"=2*(horz vert)"_ustr
);
3972 CPPUNIT_ASSERT_EQUAL_MESSAGE("A7 calculating with intersecting named expressions failed", 2.0,
3973 m_pDoc
->GetValue(aPos
));
3975 m_pDoc
->DeleteTab(0);
3978 //Test Subtotal and Aggregate during hide rows #tdf93171
3979 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncRowsHidden
)
3981 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
3982 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
3983 m_pDoc
->SetValue(0, 0, 0, 1); //A1
3984 m_pDoc
->SetValue(0, 1, 0, 2); //A2
3985 m_pDoc
->SetValue(0, 2, 0, 4); //A3
3986 m_pDoc
->SetValue(0, 3, 0, 8); //A4
3987 m_pDoc
->SetValue(0, 4, 0, 16); //A5
3988 m_pDoc
->SetValue(0, 5, 0, 32); //A6
3990 ScAddress
aPos(0, 6, 0);
3991 m_pDoc
->SetString(aPos
, u
"=SUBTOTAL(109; A1:A6)"_ustr
);
3992 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc
->GetValue(aPos
));
3994 m_pDoc
->SetRowHidden(0, 0, 0, true);
3995 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 62.0, m_pDoc
->GetValue(aPos
));
3996 m_pDoc
->SetRowHidden(0, 0, 0, false);
3998 m_pDoc
->SetRowHidden(1, 2, 0, true);
3999 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 57.0, m_pDoc
->GetValue(aPos
));
4000 m_pDoc
->SetRowHidden(1, 2, 0, false);
4001 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc
->GetValue(aPos
));
4003 m_pDoc
->SetString(aPos
, u
"=AGGREGATE(9; 5; A1:A6)"_ustr
); //9=SUM 5=Ignore only hidden rows
4004 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc
->GetValue(aPos
));
4006 m_pDoc
->SetRowHidden(0, 0, 0, true);
4007 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 62.0, m_pDoc
->GetValue(aPos
));
4008 m_pDoc
->SetRowHidden(0, 0, 0, false);
4010 m_pDoc
->SetRowHidden(2, 4, 0, true);
4011 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 35.0, m_pDoc
->GetValue(aPos
));
4012 m_pDoc
->SetRowHidden(2, 4, 0, false);
4013 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc
->GetValue(aPos
));
4015 m_pDoc
->SetString(aPos
, u
"=SUM(A1:A6)"_ustr
);
4016 m_pDoc
->SetRowHidden(2, 4, 0, true);
4017 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUM failed", 63.0, m_pDoc
->GetValue(aPos
));
4019 m_pDoc
->DeleteTab(0);
4022 // Test COUNTIFS, SUMIFS, AVERAGEIFS in array context.
4023 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncSUMIFS
)
4025 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4026 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4028 // Data in A1:B7, query in A9:A11
4029 std::vector
<std::vector
<const char*>> aData
= {
4030 { "a", "1" }, { "b", "2" }, { "c", "4" }, { "d", "8" },
4031 { "a", "16" }, { "b", "32" }, { "c", "64" }, { "" }, // {} doesn't work with some compilers
4032 { "a" }, { "b" }, { "c" },
4035 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
);
4037 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
4038 aMark
.SelectOneTable(0);
4039 // Matrix formula in C8:C10 with SUMIFS
4040 m_pDoc
->InsertMatrixFormula(2, 7, 2, 9, aMark
, u
"=SUMIFS(B1:B7;A1:A7;A9:A11)"_ustr
);
4041 // Matrix formula in D8:D10 with COUNTIFS
4042 m_pDoc
->InsertMatrixFormula(3, 7, 3, 9, aMark
, u
"=COUNTIFS(A1:A7;A9:A11)"_ustr
);
4043 // Matrix formula in E8:E10 with AVERAGEIFS
4044 m_pDoc
->InsertMatrixFormula(4, 7, 4, 9, aMark
, u
"=AVERAGEIFS(B1:B7;A1:A7;A9:A11)"_ustr
);
4047 // Result B1+B5, B2+B6, B3+B7 and counts and averages.
4048 std::vector
<std::vector
<const char*>> aCheck
4049 = { { "17", "2", "8.5" }, { "34", "2", "17" }, { "68", "2", "34" } };
4050 bool bGood
= checkOutput(m_pDoc
, ScRange(2, 7, 0, 4, 9, 0), aCheck
,
4051 "SUMIFS, COUNTIFS and AVERAGEIFS in array context");
4052 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS in array context failed", bGood
);
4055 // Matrix formula in G8:G10 with SUMIFS and reference list arrays.
4056 m_pDoc
->InsertMatrixFormula(
4057 6, 7, 6, 9, aMark
, u
"=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4058 // Matrix formula in H8:H10 with COUNTIFS and reference list arrays.
4059 m_pDoc
->InsertMatrixFormula(7, 7, 7, 9, aMark
,
4060 u
"=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4061 // Matrix formula in I8:I10 with AVERAGEIFS and reference list arrays.
4062 m_pDoc
->InsertMatrixFormula(
4064 u
"=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4067 // Result sums, counts and averages.
4068 std::vector
<std::vector
<const char*>> aCheck
4069 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "24", "2", "12" } };
4070 bool bGood
= checkOutput(m_pDoc
, ScRange(6, 7, 0, 8, 9, 0), aCheck
,
4071 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays");
4072 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays failed",
4076 // Matrix formula in K8:K10 with SUMIFS and reference list array condition
4077 // and "normal" data range.
4078 m_pDoc
->InsertMatrixFormula(10, 7, 10, 9, aMark
,
4079 u
"=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4080 // Matrix formula in L8:L10 with AVERAGEIFS and reference list array
4081 // condition and "normal" data range.
4082 m_pDoc
->InsertMatrixFormula(11, 7, 11, 9, aMark
,
4083 u
"=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4086 // Result sums and averages.
4087 std::vector
<std::vector
<const char*>> aCheck
4088 = { { "0", "#DIV/0!" }, { "2", "2" }, { "3", "1.5" } };
4089 bool bGood
= checkOutput(
4090 m_pDoc
, ScRange(10, 7, 0, 11, 9, 0), aCheck
,
4091 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and normal range");
4092 CPPUNIT_ASSERT_MESSAGE(
4093 "SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and normal range failed",
4097 // Matrix formula in G18:G20 with SUMIFS and reference list arrays and a
4098 // "normal" criteria range.
4099 m_pDoc
->InsertMatrixFormula(
4100 6, 17, 6, 19, aMark
,
4101 u
"=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")"_ustr
);
4102 // Matrix formula in H18:H20 with COUNTIFS and reference list arrays and a
4103 // "normal" criteria range.
4104 m_pDoc
->InsertMatrixFormula(7, 17, 7, 19, aMark
,
4105 u
"=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")"_ustr
);
4106 // Matrix formula in I18:I20 with AVERAGEIFS and reference list arrays and
4107 // a "normal" criteria range.
4108 m_pDoc
->InsertMatrixFormula(
4109 8, 17, 8, 19, aMark
,
4110 u
"=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")"_ustr
);
4113 // Result sums, counts and averages.
4114 std::vector
<std::vector
<const char*>> aCheck
4115 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "16", "1", "16" } };
4116 bool bGood
= checkOutput(m_pDoc
, ScRange(6, 17, 0, 8, 19, 0), aCheck
,
4117 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a "
4118 "normal criteria range");
4119 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a "
4120 "normal criteria range failed",
4124 // Matrix formula in K18:K20 with SUMIFS and reference list array condition
4125 // and "normal" data range and a "normal" criteria range.
4126 m_pDoc
->InsertMatrixFormula(10, 17, 10, 19, aMark
,
4127 u
"=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")"_ustr
);
4128 // Matrix formula in L18:L20 with AVERAGEIFS and reference list array
4129 // condition and "normal" data range and a "normal" criteria range.
4130 m_pDoc
->InsertMatrixFormula(
4131 11, 17, 11, 19, aMark
,
4132 u
"=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")"_ustr
);
4135 // Result sums and averages.
4136 std::vector
<std::vector
<const char*>> aCheck
4137 = { { "0", "#DIV/0!" }, { "2", "2" }, { "2", "2" } };
4138 bool bGood
= checkOutput(m_pDoc
, ScRange(10, 17, 0, 11, 19, 0), aCheck
,
4139 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and "
4140 "normal data and criteria range");
4141 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and "
4142 "normal data and criteria range failed",
4146 // Same, but swapped normal and array criteria.
4148 // Matrix formula in G28:G30 with SUMIFS and reference list arrays and a
4149 // "normal" criteria range, swapped.
4150 m_pDoc
->InsertMatrixFormula(
4151 6, 27, 6, 29, aMark
,
4152 u
"=SUMIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4153 // Matrix formula in H28:H30 with COUNTIFS and reference list arrays and a
4154 // "normal" criteria range, swapped.
4155 m_pDoc
->InsertMatrixFormula(7, 27, 7, 29, aMark
,
4156 u
"=COUNTIFS(B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4157 // Matrix formula in I28:I30 with AVERAGEIFS and reference list arrays and
4158 // a "normal" criteria range, swapped.
4159 m_pDoc
->InsertMatrixFormula(
4160 8, 27, 8, 29, aMark
,
4161 u
"=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4164 // Result sums, counts and averages.
4165 std::vector
<std::vector
<const char*>> aCheck
4166 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "16", "1", "16" } };
4167 bool bGood
= checkOutput(m_pDoc
, ScRange(6, 27, 0, 8, 29, 0), aCheck
,
4168 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a "
4169 "normal criteria range, swapped");
4170 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a "
4171 "normal criteria range failed, swapped",
4175 // Matrix formula in K28:K30 with SUMIFS and reference list array condition
4176 // and "normal" data range and a "normal" criteria range, swapped.
4177 m_pDoc
->InsertMatrixFormula(10, 27, 10, 29, aMark
,
4178 u
"=SUMIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4179 // Matrix formula in L28:L30 with AVERAGEIFS and reference list array
4180 // condition and "normal" data range and a "normal" criteria range,
4182 m_pDoc
->InsertMatrixFormula(
4183 11, 27, 11, 29, aMark
,
4184 u
"=AVERAGEIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")"_ustr
);
4187 // Result sums and averages.
4188 std::vector
<std::vector
<const char*>> aCheck
4189 = { { "0", "#DIV/0!" }, { "2", "2" }, { "2", "2" } };
4190 bool bGood
= checkOutput(m_pDoc
, ScRange(10, 27, 0, 11, 29, 0), aCheck
,
4191 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and "
4192 "normal data and criteria range, swapped");
4193 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and "
4194 "normal data and criteria range failed, swapped",
4198 m_pDoc
->DeleteTab(0);
4201 // Test that COUNTIF counts properly empty cells if asked to.
4202 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncCOUNTIFEmpty
)
4204 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4205 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4208 std::vector
<std::vector
<const char*>> aData
4209 = { { "" }, { "a" }, { "b" }, { "c" }, { "d" }, { "a" }, { "" }, { "b" }, { "c" } };
4211 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
);
4213 constexpr SCROW maxRow
= 20; // so that the unittest is not slow in dbgutil builds
4215 SCROW endRow
= maxRow
;
4218 // ScSortedRangeCache would normally shrink data range to this.
4219 CPPUNIT_ASSERT(m_pDoc
->ShrinkToDataArea(0, startCol
, startRow
, endCol
, endRow
));
4220 CPPUNIT_ASSERT_EQUAL(SCROW(8), endRow
);
4222 // But not if matching empty cells.
4223 m_pDoc
->SetFormula(ScAddress(10, 0, 0),
4224 "=COUNTIFS($A1:$A" + OUString::number(maxRow
+ 1) + "; \"\")",
4225 formula::FormulaGrammar::GRAM_NATIVE_UI
);
4226 CPPUNIT_ASSERT_EQUAL(double(maxRow
+ 1 - 7), m_pDoc
->GetValue(ScAddress(10, 0, 0)));
4228 m_pDoc
->DeleteTab(0);
4231 // Test that COUNTIFS counts properly empty cells if asked to.
4232 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncCOUNTIFSRangeReduce
)
4234 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4235 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4238 std::vector
<std::vector
<const char*>> aData
= { { "" },
4246 { "c", "64", "7" } };
4248 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
);
4250 constexpr SCROW maxRow
= 20; // so that the unittest is not slow in dbgutil builds
4251 ScRange
aSubRange(ScAddress(0, 0, 0), ScAddress(2, maxRow
, 0));
4252 m_pDoc
->GetDataAreaSubrange(aSubRange
);
4253 // This is the range the data should be reduced to in ScInterpreter::IterateParametersIfs().
4254 CPPUNIT_ASSERT_EQUAL(SCROW(1), aSubRange
.aStart
.Row());
4255 CPPUNIT_ASSERT_EQUAL(SCROW(8), aSubRange
.aEnd
.Row());
4257 m_pDoc
->SetFormula(ScAddress(10, 0, 0),
4258 "=COUNTIFS($A1:$A" + OUString::number(maxRow
+ 1) + "; \"\"; $B1:$B"
4259 + OUString::number(maxRow
+ 1) + "; \"\"; $C1:$C"
4260 + OUString::number(maxRow
+ 1) + "; \"\")",
4261 formula::FormulaGrammar::GRAM_NATIVE_UI
);
4262 // But it should find out that it can't range reduce and must count all the empty rows.
4263 CPPUNIT_ASSERT_EQUAL(double(maxRow
+ 1 - 7), m_pDoc
->GetValue(ScAddress(10, 0, 0)));
4265 // Check also with criteria set as cell references, the middle one resulting in matching
4266 // empty cells (which should cause ScInterpreter::IterateParametersIfs() to undo
4267 // the range reduction). This should only match the A8-C8 row, but it also shouldn't crash.
4268 // Matching empty cells using a cell reference needs a formula to set the cell to
4269 // an empty string, plain empty cell wouldn't do, so use K2 for that.
4270 m_pDoc
->SetFormula(ScAddress(10, 1, 0), u
"=\"\""_ustr
, formula::FormulaGrammar::GRAM_NATIVE_UI
);
4271 m_pDoc
->SetFormula(ScAddress(10, 0, 0),
4272 "=COUNTIFS($A1:$A" + OUString::number(maxRow
+ 1) + "; A8; $B1:$B"
4273 + OUString::number(maxRow
+ 1) + "; K2; $C1:$C"
4274 + OUString::number(maxRow
+ 1) + "; C8)",
4275 formula::FormulaGrammar::GRAM_NATIVE_UI
);
4276 CPPUNIT_ASSERT_EQUAL(double(1), m_pDoc
->GetValue(ScAddress(10, 0, 0)));
4278 m_pDoc
->DeleteTab(0);
4281 // Test SUBTOTAL with reference lists in array context.
4282 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncRefListArraySUBTOTAL
)
4284 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4285 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4287 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
4288 m_pDoc
->SetValue(0, 1, 0, 2.0); // A2
4289 m_pDoc
->SetValue(0, 2, 0, 4.0); // A3
4290 m_pDoc
->SetValue(0, 3, 0, 8.0); // A4
4291 m_pDoc
->SetValue(0, 4, 0, 16.0); // A5
4292 m_pDoc
->SetValue(0, 5, 0, 32.0); // A6
4294 // Matrix in B7:B9, individual SUM of A2:A3, A3:A4 and A4:A5
4295 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
4296 aMark
.SelectOneTable(0);
4297 m_pDoc
->InsertMatrixFormula(1, 6, 1, 8, aMark
, u
"=SUBTOTAL(9;OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4298 ScAddress
aPos(1, 6, 0);
4299 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A2:A3 failed", 6.0, m_pDoc
->GetValue(aPos
));
4301 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A3:A4 failed", 12.0, m_pDoc
->GetValue(aPos
));
4303 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A4:A5 failed", 24.0, m_pDoc
->GetValue(aPos
));
4305 // Matrix in C7:C9, individual AVERAGE of A2:A3, A3:A4 and A4:A5
4306 m_pDoc
->InsertMatrixFormula(2, 6, 2, 8, aMark
, u
"=SUBTOTAL(1;OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4308 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A2:A3 failed", 3.0, m_pDoc
->GetValue(aPos
));
4310 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A3:A4 failed", 6.0, m_pDoc
->GetValue(aPos
));
4312 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A4:A5 failed", 12.0, m_pDoc
->GetValue(aPos
));
4314 // Matrix in D7:D9, individual MIN of A2:A3, A3:A4 and A4:A5
4315 m_pDoc
->InsertMatrixFormula(3, 6, 3, 8, aMark
, u
"=SUBTOTAL(5;OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4317 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A2:A3 failed", 2.0, m_pDoc
->GetValue(aPos
));
4319 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A3:A4 failed", 4.0, m_pDoc
->GetValue(aPos
));
4321 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A4:A5 failed", 8.0, m_pDoc
->GetValue(aPos
));
4323 // Matrix in E7:E9, individual MAX of A2:A3, A3:A4 and A4:A5
4324 m_pDoc
->InsertMatrixFormula(4, 6, 4, 8, aMark
, u
"=SUBTOTAL(4;OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4326 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A2:A3 failed", 4.0, m_pDoc
->GetValue(aPos
));
4328 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A3:A4 failed", 8.0, m_pDoc
->GetValue(aPos
));
4330 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A4:A5 failed", 16.0, m_pDoc
->GetValue(aPos
));
4332 // Matrix in F7:F9, individual STDEV of A2:A3, A3:A4 and A4:A5
4333 m_pDoc
->InsertMatrixFormula(5, 6, 5, 8, aMark
, u
"=SUBTOTAL(7;OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4335 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A2:A3 failed", 1.414214,
4336 m_pDoc
->GetValue(aPos
), 1e-6);
4338 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A3:A4 failed", 2.828427,
4339 m_pDoc
->GetValue(aPos
), 1e-6);
4341 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A4:A5 failed", 5.656854,
4342 m_pDoc
->GetValue(aPos
), 1e-6);
4344 // Matrix in G7:G9, individual AVERAGE of A2:A3, A3:A4 and A4:A5
4345 // Plus two "ordinary" ranges, one before and one after.
4346 m_pDoc
->InsertMatrixFormula(6, 6, 6, 8, aMark
,
4347 u
"=SUBTOTAL(1;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)"_ustr
);
4349 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A2:A3,A5:A6 failed", 9.5,
4350 m_pDoc
->GetValue(aPos
));
4352 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A3:A4,A5:A6 failed", 10.5,
4353 m_pDoc
->GetValue(aPos
));
4355 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A4:A5,A5:A6 failed", 12.5,
4356 m_pDoc
->GetValue(aPos
));
4358 // Matrix in H7:H9, individual MAX of A2:A3, A3:A4 and A4:A5
4359 // Plus two "ordinary" ranges, one before and one after.
4360 m_pDoc
->InsertMatrixFormula(7, 6, 7, 8, aMark
,
4361 u
"=SUBTOTAL(4;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)"_ustr
);
4363 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A2:A3,A5:A6 failed", 32.0,
4364 m_pDoc
->GetValue(aPos
));
4366 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A3:A4,A5:A6 failed", 32.0,
4367 m_pDoc
->GetValue(aPos
));
4369 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A4:A5,A5:A6 failed", 32.0,
4370 m_pDoc
->GetValue(aPos
));
4372 // Matrix in I7:I9, individual STDEV of A2:A3, A3:A4 and A4:A5
4373 // Plus two "ordinary" ranges, one before and one after.
4374 m_pDoc
->InsertMatrixFormula(8, 6, 8, 8, aMark
,
4375 u
"=SUBTOTAL(7;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)"_ustr
);
4377 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A2:A3,A5:A6 failed", 12.35718,
4378 m_pDoc
->GetValue(aPos
), 1e-5);
4380 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A3:A4,A5:A6 failed", 11.86170,
4381 m_pDoc
->GetValue(aPos
), 1e-5);
4383 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A4:A5,A5:A6 failed", 11.55422,
4384 m_pDoc
->GetValue(aPos
), 1e-5);
4386 // Empty two cells such that they affect two ranges.
4387 m_pDoc
->SetString(0, 1, 0, u
""_ustr
); // A2
4388 m_pDoc
->SetString(0, 2, 0, u
""_ustr
); // A3
4389 // Matrix in J7:J9, individual COUNTBLANK of A2:A3, A3:A4 and A4:A5
4390 m_pDoc
->InsertMatrixFormula(9, 6, 9, 8, aMark
, u
"=COUNTBLANK(OFFSET(A1;ROW(1:3);0;2))"_ustr
);
4392 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A2:A3,A5:A6 failed", 2.0,
4393 m_pDoc
->GetValue(aPos
));
4395 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A3:A4,A5:A6 failed", 1.0,
4396 m_pDoc
->GetValue(aPos
));
4398 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A4:A5,A5:A6 failed", 0.0,
4399 m_pDoc
->GetValue(aPos
));
4401 // Restore these two cell values so we'd catch failures below.
4402 m_pDoc
->SetValue(0, 1, 0, 2.0); // A2
4403 m_pDoc
->SetValue(0, 2, 0, 4.0); // A3
4404 // Hide rows 2 to 4.
4405 m_pDoc
->SetRowHidden(1, 3, 0, true);
4406 // Matrix in K7, array of references as OFFSET result.
4407 m_pDoc
->InsertMatrixFormula(10, 6, 10, 6, aMark
,
4408 u
"=SUM(SUBTOTAL(109;OFFSET(A1;ROW(A1:A7)-ROW(A1);;1)))"_ustr
);
4410 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUM SUBTOTAL failed", 49.0, m_pDoc
->GetValue(aPos
));
4412 // ForceArray in K8, array of references as OFFSET result.
4413 m_pDoc
->SetString(aPos
, u
"=SUMPRODUCT(SUBTOTAL(109;OFFSET(A1;ROW(A1:A7)-ROW(A1);;1)))"_ustr
);
4414 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMPRODUCT SUBTOTAL failed", 49.0, m_pDoc
->GetValue(aPos
));
4416 m_pDoc
->DeleteTab(0);
4419 // tdf#115493 jump commands return the matrix result instead of the reference
4421 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncJumpMatrixArrayIF
)
4423 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4424 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4426 m_pDoc
->SetString(0, 0, 0, u
"a"_ustr
); // A1
4427 std::vector
<std::vector
<const char*>> aData
4428 = { { "a", "1" }, { "b", "2" }, { "a", "4" } }; // A7:B9
4429 insertRangeData(m_pDoc
, ScAddress(0, 6, 0), aData
);
4431 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
4432 aMark
.SelectOneTable(0);
4434 // Matrix in C10, summing B7,B9
4435 m_pDoc
->InsertMatrixFormula(2, 9, 2, 9, aMark
, u
"=SUM(IF(EXACT(A7:A9;A$1);B7:B9;0))"_ustr
);
4436 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C10 failed", 5.0, m_pDoc
->GetValue(ScAddress(2, 9, 0)));
4438 // Matrix in C11, summing B7,B9
4439 m_pDoc
->InsertMatrixFormula(
4440 2, 10, 2, 10, aMark
,
4441 u
"=SUM(IF(EXACT(OFFSET(A7;0;0):OFFSET(A7;2;0);A$1);OFFSET(A7;0;1):OFFSET(A7;2;1);0))"_ustr
);
4442 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C11 failed", 5.0, m_pDoc
->GetValue(ScAddress(2, 10, 0)));
4444 m_pDoc
->DeleteTab(0);
4447 // tdf#123477 OFFSET() returns the matrix result instead of the reference list
4448 // array if result is not used as ReferenceOrRefArray.
4449 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFuncJumpMatrixArrayOFFSET
)
4451 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4452 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4454 std::vector
<std::vector
<const char*>> aData
= { { "abc" }, { "bcd" }, { "cde" } };
4455 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
); // A1:A3
4457 ScMarkData
aMark(m_pDoc
->GetSheetLimits());
4458 aMark
.SelectOneTable(0);
4460 // Matrix in C5:C7, COLUMN()-3 here offsets by 0 but the entire expression
4461 // is in array/matrix context.
4462 m_pDoc
->InsertMatrixFormula(2, 4, 2, 6, aMark
, u
"=FIND(\"c\";OFFSET(A1:A3;0;COLUMN()-3))"_ustr
);
4463 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C5 failed", 3.0, m_pDoc
->GetValue(ScAddress(2, 4, 0)));
4464 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C6 failed", 2.0, m_pDoc
->GetValue(ScAddress(2, 5, 0)));
4465 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C7 failed", 1.0, m_pDoc
->GetValue(ScAddress(2, 6, 0)));
4467 m_pDoc
->DeleteTab(0);
4470 // Test iterations with circular chain of references.
4471 CPPUNIT_TEST_FIXTURE(TestFormula2
, testIterations
)
4473 ScDocOptions aDocOpts
= m_pDoc
->GetDocOptions();
4474 aDocOpts
.SetIter(true);
4475 m_pDoc
->SetDocOptions(aDocOpts
);
4477 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4479 m_pDoc
->SetValue(0, 0, 0, 0.01); // A1
4480 m_pDoc
->SetString(0, 1, 0, u
"=A1"_ustr
); // A2
4481 m_pDoc
->SetString(0, 2, 0, u
"=COS(A2)"_ustr
); // A3
4484 // Establish reference cycle for the computation of the fixed point of COS() function
4485 m_pDoc
->SetString(0, 0, 0, u
"=A3"_ustr
); // A1
4488 CPPUNIT_ASSERT_EQUAL_MESSAGE("Cell A3 should not have any formula error", FormulaError::NONE
,
4489 m_pDoc
->GetErrCode(ScAddress(0, 2, 0)));
4490 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Iterations to calculate fixed point of cos() failed",
4491 0.7387, m_pDoc
->GetValue(0, 2, 0), 1e-4);
4493 // Modify the formula
4494 m_pDoc
->SetString(0, 2, 0, u
"=COS(A2)+0.001"_ustr
); // A3
4497 CPPUNIT_ASSERT_EQUAL_MESSAGE("Cell A3 should not have any formula error after perturbation",
4498 FormulaError::NONE
, m_pDoc
->GetErrCode(ScAddress(0, 2, 0)));
4499 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
4500 "Iterations to calculate perturbed fixed point of cos() failed", 0.7399,
4501 m_pDoc
->GetValue(0, 2, 0), 1e-4);
4503 m_pDoc
->DeleteTab(0);
4505 aDocOpts
.SetIter(false);
4506 m_pDoc
->SetDocOptions(aDocOpts
);
4509 // tdf#111428 CellStoreEvent and its counter used for quick "has a column
4510 // formula cells" must point to the correct column.
4511 CPPUNIT_TEST_FIXTURE(TestFormula2
, testInsertColCellStoreEventSwap
)
4513 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4514 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4516 m_pDoc
->SetValue(0, 0, 0, 1.0); // A1
4517 m_pDoc
->SetString(1, 0, 0, u
"=A1"_ustr
); // B1
4518 // Insert column left of B
4519 m_pDoc
->InsertCol(ScRange(1, 0, 0, 1, m_pDoc
->MaxRow(), 0));
4520 ScAddress
aPos(2, 0, 0); // C1, new formula position
4521 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should be formula cell having value", 1.0,
4522 m_pDoc
->GetValue(aPos
));
4523 // After having swapped in an empty column, editing or adding a formula
4524 // cell has to use the correct store context. To test this,
4525 // ScDocument::SetString() can't be used as it doesn't expose the behavior
4526 // in question, use ScDocFunc::SetFormulaCell() instead which actually is
4527 // also called when editing a cell and creating a formula cell.
4528 ScFormulaCell
* pCell
= new ScFormulaCell(*m_pDoc
, aPos
, u
"=A1+1"_ustr
);
4529 ScDocFunc
& rDocFunc
= m_xDocShell
->GetDocFunc();
4530 rDocFunc
.SetFormulaCell(aPos
, pCell
, false); // C1, change formula
4531 CPPUNIT_ASSERT_EQUAL_MESSAGE("Initial calculation failed", 2.0, m_pDoc
->GetValue(aPos
));
4532 m_pDoc
->SetValue(0, 0, 0, 2.0); // A1, change value
4533 CPPUNIT_ASSERT_EQUAL_MESSAGE("Recalculation failed", 3.0, m_pDoc
->GetValue(aPos
));
4535 m_pDoc
->DeleteTab(0);
4538 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf147398
)
4540 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4542 m_pDoc
->SetString(0, 0, 0, u
"=SUM(A3:A5)"_ustr
);
4543 m_pDoc
->SetString(0, 1, 0, u
"=COUNT(A3:A5)"_ustr
);
4544 m_pDoc
->SetString(1, 0, 0, u
"=SUM(B3:B5)"_ustr
);
4545 m_pDoc
->SetString(1, 1, 0, u
"=COUNT(B3:B5)"_ustr
);
4546 m_pDoc
->SetString(2, 0, 0, u
"=SUM(C3:C5)"_ustr
);
4547 m_pDoc
->SetString(2, 1, 0, u
"=COUNT(C3:C5)"_ustr
);
4548 m_pDoc
->SetString(3, 0, 0, u
"=SUM(D3:D5)"_ustr
);
4549 m_pDoc
->SetString(3, 1, 0, u
"=COUNT(D3:D5)"_ustr
);
4550 m_pDoc
->SetString(4, 0, 0, u
"=SUM(E3:E5)"_ustr
);
4551 m_pDoc
->SetString(4, 1, 0, u
"=COUNT(E3:E5)"_ustr
);
4553 m_pDoc
->SetString(5, 0, 0, u
"=SUM(A1:E1)/SUM(A2:E2)"_ustr
);
4555 m_pDoc
->SetValue(ScAddress(0, 2, 0), 50.0);
4556 m_pDoc
->SetValue(ScAddress(0, 3, 0), 100.0);
4558 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc
->GetValue(ScAddress(0, 0, 0)));
4559 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(0, 1, 0)));
4560 CPPUNIT_ASSERT_EQUAL(75.0, m_pDoc
->GetValue(ScAddress(5, 0, 0)));
4562 m_pDoc
->SetValue(ScAddress(1, 2, 0), 150.0);
4563 m_pDoc
->SetValue(ScAddress(1, 3, 0), 200.0);
4565 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc
->GetValue(ScAddress(0, 0, 0)));
4566 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(0, 1, 0)));
4567 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
4568 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(1, 1, 0)));
4569 CPPUNIT_ASSERT_EQUAL(125.0, m_pDoc
->GetValue(ScAddress(5, 0, 0)));
4571 m_pDoc
->SetValue(ScAddress(2, 2, 0), 250.0);
4572 m_pDoc
->SetValue(ScAddress(2, 3, 0), 300.0);
4574 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc
->GetValue(ScAddress(0, 0, 0)));
4575 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(0, 1, 0)));
4576 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
4577 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(1, 1, 0)));
4578 CPPUNIT_ASSERT_EQUAL(550.0, m_pDoc
->GetValue(ScAddress(2, 0, 0)));
4579 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(2, 1, 0)));
4580 CPPUNIT_ASSERT_EQUAL(175.0, m_pDoc
->GetValue(ScAddress(5, 0, 0)));
4582 m_pDoc
->SetValue(ScAddress(3, 2, 0), 350.0);
4583 m_pDoc
->SetValue(ScAddress(3, 3, 0), 400.0);
4585 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc
->GetValue(ScAddress(0, 0, 0)));
4586 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(0, 1, 0)));
4587 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
4588 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(1, 1, 0)));
4589 CPPUNIT_ASSERT_EQUAL(550.0, m_pDoc
->GetValue(ScAddress(2, 0, 0)));
4590 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(2, 1, 0)));
4591 CPPUNIT_ASSERT_EQUAL(750.0, m_pDoc
->GetValue(ScAddress(3, 0, 0)));
4592 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(3, 1, 0)));
4593 CPPUNIT_ASSERT_EQUAL(225.0, m_pDoc
->GetValue(ScAddress(5, 0, 0)));
4595 m_pDoc
->SetValue(ScAddress(4, 2, 0), 450.0);
4596 m_pDoc
->SetValue(ScAddress(4, 3, 0), 500.0);
4598 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc
->GetValue(ScAddress(0, 0, 0)));
4599 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(0, 1, 0)));
4600 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc
->GetValue(ScAddress(1, 0, 0)));
4601 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(1, 1, 0)));
4602 CPPUNIT_ASSERT_EQUAL(550.0, m_pDoc
->GetValue(ScAddress(2, 0, 0)));
4603 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(2, 1, 0)));
4604 CPPUNIT_ASSERT_EQUAL(750.0, m_pDoc
->GetValue(ScAddress(3, 0, 0)));
4605 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(3, 1, 0)));
4606 CPPUNIT_ASSERT_EQUAL(950.0, m_pDoc
->GetValue(ScAddress(4, 0, 0)));
4607 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc
->GetValue(ScAddress(4, 1, 0)));
4608 CPPUNIT_ASSERT_EQUAL(275.0, m_pDoc
->GetValue(ScAddress(5, 0, 0)));
4610 m_pDoc
->DeleteTab(0);
4614 CPPUNIT_TEST_FIXTURE(TestFormula2
, testTdf156985
)
4616 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4618 m_pDoc
->SetString(0, 0, 0, u
"=-170.87"_ustr
);
4619 m_pDoc
->SetString(0, 1, 0, u
"-223.73"_ustr
);
4620 m_pDoc
->SetString(0, 2, 0, u
"-12.58"_ustr
);
4621 m_pDoc
->SetString(0, 3, 0, u
"234.98"_ustr
);
4622 m_pDoc
->SetString(0, 4, 0, u
"172.2"_ustr
);
4623 m_pDoc
->SetString(0, 5, 0, u
"=SUM(A1:A5)"_ustr
);
4625 // Without the fix in place, this test would have failed with
4627 // - Actual : -1.59872115546023e-14
4628 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc
->GetValue(0, 5, 0));
4630 m_pDoc
->DeleteTab(0);
4634 CPPUNIT_TEST_FIXTURE(TestFormula2
, testFormulaAfterDeleteRows
)
4636 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true); // turn auto calc on.
4637 m_pDoc
->InsertTab(0, u
"Test"_ustr
);
4639 // Fill A1:A70000 with 1.0
4640 std::vector
<double> aVals(70000, 1.0);
4641 m_pDoc
->SetValues(ScAddress(0, 0, 0), aVals
);
4642 // Set A70001 with formula "=SUM(A1:A70000)"
4643 m_pDoc
->SetString(0, 70000, 0, u
"=SUM(A1:A70000)"_ustr
);
4645 // Delete rows 2:69998
4646 m_pDoc
->DeleteRow(ScRange(0, 1, 0, m_pDoc
->MaxCol(), 69997, 0));
4648 const ScAddress
aPos(0, 3, 0); // A4
4649 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in A4.", u
"=SUM(A1:A3)"_ustr
,
4650 m_pDoc
->GetFormula(aPos
.Col(), aPos
.Row(), aPos
.Tab()));
4652 ASSERT_DOUBLES_EQUAL_MESSAGE("Wrong value at A4", 3.0, m_pDoc
->GetValue(aPos
));
4655 CPPUNIT_TEST_FIXTURE(TestFormula2
, testRegexForXLOOKUP
)
4657 sc::AutoCalcSwitch
aACSwitch(*m_pDoc
, true);
4659 // Temporarily switch regex search mode.
4660 bool bOldRegex
= false;
4661 ScDocOptions aDocOpt
= m_pDoc
->GetDocOptions();
4662 if (aDocOpt
.IsFormulaRegexEnabled())
4664 aDocOpt
.SetFormulaRegexEnabled(false);
4665 m_pDoc
->SetDocOptions(aDocOpt
);
4669 m_pDoc
->InsertTab(0, u
"Test1"_ustr
);
4671 std::vector
<std::vector
<const char*>> aData
= { { "Element", "Relative Atomic Mass" },
4672 { "Hydrogen", "1.008" },
4673 { "Helium", "4.003" },
4674 { "Lithium", "6.94" },
4675 { "Beryllium", "9.012" },
4676 { "Boron", "10.81" },
4677 { "Carbon", "12.011" },
4678 { "Nitrogen", "14.007" },
4679 { "Oxygen", "15.999" },
4680 { "Florine", "18.998" },
4681 { "Neon", "20.18" } };
4683 insertRangeData(m_pDoc
, ScAddress(0, 0, 0), aData
); // A1:B11
4684 m_pDoc
->SetString(4, 14, 0, u
"^bo.*"_ustr
); // E15 - search regex string
4686 m_pDoc
->SetFormula(ScAddress(5, 14, 0), u
"=XLOOKUP(E15;A$2:A$11;B$2:B$11;;3)"_ustr
,
4687 formula::FormulaGrammar::GRAM_NATIVE_UI
); // F15
4689 // Without the fix in place, this test would have failed with
4690 // - Expected: 10.81
4692 CPPUNIT_ASSERT_EQUAL(10.81, m_pDoc
->GetValue(5, 14, 0));
4694 // Switch back to wildcard mode if necessary.
4697 aDocOpt
.SetFormulaRegexEnabled(true);
4698 m_pDoc
->SetDocOptions(aDocOpt
);
4700 m_pDoc
->DeleteTab(0);
4703 CPPUNIT_PLUGIN_IMPLEMENT();
4705 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */