?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Avoid potential negative array index access to cached text.
[LibreOffice.git] / sc / qa / unit / ucalc_formula2.cxx
blobe6606f126d8e2757616fc524b968a24f373de46c
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * This file is part of the LibreOffice project.
4 *
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/.
8 */
9
10 #include "helper/debughelper.hxx"
11 #include "helper/qahelper.hxx"
12 #include <clipparam.hxx>
13 #include <scopetools.hxx>
14 #include <formulacell.hxx>
15 #include <docfunc.hxx>
16 #include <tokenstringcontext.hxx>
17 #include <dbdata.hxx>
18 #include <scmatrix.hxx>
19 #include <docoptio.hxx>
20 #include <externalrefmgr.hxx>
21 #include <undomanager.hxx>
22 #include <broadcast.hxx>
23 #include <kahan.hxx>
24
25 #include <svl/broadcast.hxx>
26 #include <sfx2/docfile.hxx>
27
28 #include <memory>
29 #include <functional>
30 #include <set>
31 #include <algorithm>
32 #include <vector>
33
34 using namespace formula;
35 using ::std::vector;
36 using ::std::cerr;
37 using ::std::endl;
38
39 namespace
40 {
41 ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
42 {
43 ScRange aRange;
44
45 vector<SCROW> aRows;
46 pCacheTab->getAllRows(aRows);
47 bool bFirst = true;
48 for (const SCROW nRow : aRows)
49 {
50 vector<SCCOL> aCols;
51 pCacheTab->getAllCols(nRow, aCols);
52 for (const SCCOL nCol : aCols)
53 {
54 if (bFirst)
55 {
56 aRange.aStart = ScAddress(nCol, nRow, 0);
57 aRange.aEnd = aRange.aStart;
58 bFirst = false;
59 }
60 else
61 {
62 if (nCol < aRange.aStart.Col())
63 aRange.aStart.SetCol(nCol);
64 else if (aRange.aEnd.Col() < nCol)
65 aRange.aEnd.SetCol(nCol);
66
67 if (nRow < aRange.aStart.Row())
68 aRange.aStart.SetRow(nRow);
69 else if (aRange.aEnd.Row() < nRow)
70 aRange.aEnd.SetRow(nRow);
71 }
72 }
73 }
74 return aRange;
75 }
76
77 struct StrStrCheck
78 {
79 const char* pVal;
80 const char* pRes;
81 };
82
83 class ColumnTest
84 {
85 ScDocument* m_pDoc;
86
87 const SCROW m_nTotalRows;
88 const SCROW m_nStart1;
89 const SCROW m_nEnd1;
90 const SCROW m_nStart2;
91 const SCROW m_nEnd2;
92
93 public:
94 ColumnTest(ScDocument* pDoc, SCROW nTotalRows, SCROW nStart1, SCROW nEnd1, SCROW nStart2,
95 SCROW nEnd2)
96 : m_pDoc(pDoc)
97 , m_nTotalRows(nTotalRows)
98 , m_nStart1(nStart1)
99 , m_nEnd1(nEnd1)
100 , m_nStart2(nStart2)
101 , m_nEnd2(nEnd2)
102 {
103 }
104
105 void operator()(SCCOL nColumn, const OUString& rFormula,
106 std::function<double(SCROW)> const& lExpected) const
107 {
108 ScDocument aClipDoc(SCDOCMODE_CLIP);
109 ScMarkData aMark(m_pDoc->GetSheetLimits());
110
111 ScAddress aPos(nColumn, m_nStart1, 0);
112 m_pDoc->SetString(aPos, rFormula);
113 ASSERT_DOUBLES_EQUAL(lExpected(m_nStart1), m_pDoc->GetValue(aPos));
114
115 // Copy formula cell to clipboard.
116 ScClipParam aClipParam(aPos, false);
117 aMark.SetMarkArea(aPos);
118 m_pDoc->CopyToClip(aClipParam, &aClipDoc, &aMark, false, false);
119
120 // Paste it to first range.
121 InsertDeleteFlags nFlags = InsertDeleteFlags::CONTENTS;
122 ScRange aDestRange(nColumn, m_nStart1, 0, nColumn, m_nEnd1, 0);
123 aMark.SetMarkArea(aDestRange);
124 m_pDoc->CopyFromClip(aDestRange, aMark, nFlags, nullptr, &aClipDoc);
125
126 // Paste it second range.
127 aDestRange = ScRange(nColumn, m_nStart2, 0, nColumn, m_nEnd2, 0);
128 aMark.SetMarkArea(aDestRange);
129 m_pDoc->CopyFromClip(aDestRange, aMark, nFlags, nullptr, &aClipDoc);
130
131 // Check the formula results for passed column.
132 for (SCROW i = 0; i < m_nTotalRows; ++i)
133 {
134 if (!((m_nStart1 <= i && i <= m_nEnd1) || (m_nStart2 <= i && i <= m_nEnd2)))
135 continue;
136 double fExpected = lExpected(i);
137 ASSERT_DOUBLES_EQUAL(fExpected, m_pDoc->GetValue(ScAddress(nColumn, i, 0)));
138 }
139 }
140 };
141
142 } //namespace
143
144 class TestFormula2 : public ScUcalcTestBase
145 {
146 protected:
147 template <size_t DataSize, size_t FormulaSize, int Type>
148 void runTestMATCH(ScDocument* pDoc, const char* aData[DataSize],
149 const StrStrCheck aChecks[FormulaSize]);
150 template <size_t DataSize, size_t FormulaSize, int Type>
151 void runTestHorizontalMATCH(ScDocument* pDoc, const char* aData[DataSize],
152 const StrStrCheck aChecks[FormulaSize]);
153
154 void testExtRefFuncT(ScDocument* pDoc, ScDocument& rExtDoc);
155 void testExtRefFuncOFFSET(ScDocument* pDoc, ScDocument& rExtDoc);
156 void testExtRefFuncVLOOKUP(ScDocument* pDoc, ScDocument& rExtDoc);
157 void testExtRefConcat(ScDocument* pDoc, ScDocument& rExtDoc);
158 };
159
160 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncIF)
161 {
162 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
163
164 m_pDoc->InsertTab(0, "Formula");
165
166 m_pDoc->SetString(ScAddress(0, 0, 0), "=IF(B1=2;\"two\";\"not two\")");
167 CPPUNIT_ASSERT_EQUAL(OUString("not two"), m_pDoc->GetString(ScAddress(0, 0, 0)));
168 m_pDoc->SetValue(ScAddress(1, 0, 0), 2.0);
169 CPPUNIT_ASSERT_EQUAL(OUString("two"), m_pDoc->GetString(ScAddress(0, 0, 0)));
170 m_pDoc->SetValue(ScAddress(1, 0, 0), 3.0);
171 CPPUNIT_ASSERT_EQUAL(OUString("not two"), m_pDoc->GetString(ScAddress(0, 0, 0)));
172
173 // Test nested IF in array/matrix if the nested IF condition is a scalar.
174 ScMarkData aMark(m_pDoc->GetSheetLimits());
175 aMark.SelectOneTable(0);
176 m_pDoc->InsertMatrixFormula(0, 2, 1, 2, aMark, "=IF({1;0};IF(1;23);42)");
177 // Results must be 23 and 42.
178 CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(0, 2, 0)));
179 CPPUNIT_ASSERT_EQUAL(42.0, m_pDoc->GetValue(ScAddress(1, 2, 0)));
180
181 // Test nested IF in array/matrix if nested IF conditions are range
182 // references, data in A5:C8, matrix formula in D4 so there is no
183 // implicit intersection between formula and ranges.
184 {
185 std::vector<std::vector<const char*>> aData
186 = { { "1", "1", "16" }, { "0", "1", "32" }, { "1", "0", "64" }, { "0", "0", "128" } };
187 ScAddress aPos(0, 4, 0);
188 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
189 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
190 }
191 m_pDoc->InsertMatrixFormula(3, 3, 3, 3, aMark, "=SUM(IF(A5:A8;IF(B5:B8;C5:C8;0);0))");
192 // Result must be 16, only the first row matches all criteria.
193 CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(3, 3, 0)));
194
195 // A11:B11
196 // Test nested IF in array/matrix if the nested IF has no Else path.
197 m_pDoc->InsertMatrixFormula(0, 10, 1, 10, aMark, "=IF(IF({1;0};12);34;56)");
198 // Results must be 34 and 56.
199 CPPUNIT_ASSERT_EQUAL(34.0, m_pDoc->GetValue(ScAddress(0, 10, 0)));
200 CPPUNIT_ASSERT_EQUAL(56.0, m_pDoc->GetValue(ScAddress(1, 10, 0)));
201
202 m_pDoc->DeleteTab(0);
203 }
204
205 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncCHOOSE)
206 {
207 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
208
209 m_pDoc->InsertTab(0, "Formula");
210
211 m_pDoc->SetString(ScAddress(0, 0, 0), "=CHOOSE(B1;\"one\";\"two\";\"three\")");
212 FormulaError nError = m_pDoc->GetErrCode(ScAddress(0, 0, 0));
213 CPPUNIT_ASSERT_MESSAGE("Formula result should be an error since B1 is still empty.",
214 nError != FormulaError::NONE);
215 m_pDoc->SetValue(ScAddress(1, 0, 0), 1.0);
216 CPPUNIT_ASSERT_EQUAL(OUString("one"), m_pDoc->GetString(ScAddress(0, 0, 0)));
217 m_pDoc->SetValue(ScAddress(1, 0, 0), 2.0);
218 CPPUNIT_ASSERT_EQUAL(OUString("two"), m_pDoc->GetString(ScAddress(0, 0, 0)));
219 m_pDoc->SetValue(ScAddress(1, 0, 0), 3.0);
220 CPPUNIT_ASSERT_EQUAL(OUString("three"), m_pDoc->GetString(ScAddress(0, 0, 0)));
221 m_pDoc->SetValue(ScAddress(1, 0, 0), 4.0);
222 nError = m_pDoc->GetErrCode(ScAddress(0, 0, 0));
223 CPPUNIT_ASSERT_MESSAGE("Formula result should be an error due to out-of-bound input..",
224 nError != FormulaError::NONE);
225
226 m_pDoc->DeleteTab(0);
227 }
228
229 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncIFERROR)
230 {
231 // IFERROR/IFNA (fdo#56124)
232
233 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
234
235 // Empty A1:A39 first.
236 clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
237
238 // Raw data (rows 1 through 12)
239 const char* aData[] = { "1", "e", "=SQRT(4)", "=SQRT(-2)", "=A4", "=1/0",
240 "=NA()", "bar", "4", "gee", "=1/0", "23" };
241
242 SCROW nRows = SAL_N_ELEMENTS(aData);
243 for (SCROW i = 0; i < nRows; ++i)
244 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
245
246 printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows - 1, 0), "data range for IFERROR/IFNA");
247
248 // formulas and results
249 static const struct
250 {
251 const char* pFormula;
252 const char* pResult;
253 } aChecks[] = {
254 { "=IFERROR(A1;9)", "1" },
255 { "=IFERROR(A2;9)", "e" },
256 { "=IFERROR(A3;9)", "2" },
257 { "=IFERROR(A4;-7)", "-7" },
258 { "=IFERROR(A5;-7)", "-7" },
259 { "=IFERROR(A6;-7)", "-7" },
260 { "=IFERROR(A7;-7)", "-7" },
261 { "=IFNA(A6;9)", "#DIV/0!" },
262 { "=IFNA(A7;-7)", "-7" },
263 { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
264 { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
265 { "{=IFERROR(3*A11:A12;1998)}[0]",
266 "1998" }, // um... this is not the correct way to insert a
267 { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place holder, see below
268 };
269
270 nRows = SAL_N_ELEMENTS(aChecks);
271 for (SCROW i = 0; i < nRows - 2; ++i)
272 {
273 SCROW nRow = 20 + i;
274 m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
275 }
276
277 // Create a matrix range in last two rows of the range above, actual data
278 // of the placeholders.
279 ScMarkData aMark(m_pDoc->GetSheetLimits());
280 aMark.SelectOneTable(0);
281 m_pDoc->InsertMatrixFormula(0, 20 + nRows - 2, 0, 20 + nRows - 1, aMark,
282 "=IFERROR(3*A11:A12;1998)");
283
284 m_pDoc->CalcAll();
285
286 for (SCROW i = 0; i < nRows; ++i)
287 {
288 SCROW nRow = 20 + i;
289 OUString aResult = m_pDoc->GetString(0, nRow, 0);
290 CPPUNIT_ASSERT_EQUAL_MESSAGE(aChecks[i].pFormula,
291 OUString::createFromAscii(aChecks[i].pResult), aResult);
292 }
293
294 const SCCOL nCols = 3;
295 std::vector<std::vector<const char*>> aData2
296 = { { "1", "2", "3" }, { "4", "=1/0", "6" }, { "7", "8", "9" } };
297 const char* aCheck2[][nCols] = { { "1", "2", "3" }, { "4", "Error", "6" }, { "7", "8", "9" } };
298
299 // Data in C1:E3
300 ScAddress aPos(2, 0, 0);
301 ScRange aRange = insertRangeData(m_pDoc, aPos, aData2);
302 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
303
304 // Array formula in F4:H6
305 const SCROW nElems2 = SAL_N_ELEMENTS(aCheck2);
306 const SCCOL nStartCol = aPos.Col() + nCols;
307 const SCROW nStartRow = aPos.Row() + nElems2;
308 m_pDoc->InsertMatrixFormula(nStartCol, nStartRow, nStartCol + nCols - 1,
309 nStartRow + nElems2 - 1, aMark, "=IFERROR(C1:E3;\"Error\")");
310
311 m_pDoc->CalcAll();
312
313 for (SCCOL nCol = nStartCol; nCol < nStartCol + nCols; ++nCol)
314 {
315 for (SCROW nRow = nStartRow; nRow < nStartRow + nElems2; ++nRow)
316 {
317 OUString aResult = m_pDoc->GetString(nCol, nRow, 0);
318 CPPUNIT_ASSERT_EQUAL_MESSAGE(
319 "IFERROR array result",
320 OUString::createFromAscii(aCheck2[nRow - nStartRow][nCol - nStartCol]), aResult);
321 }
322 }
323
324 m_pDoc->DeleteTab(0);
325 }
326
327 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncSHEET)
328 {
329 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(SC_TAB_APPEND, "test1"));
330
331 m_pDoc->SetString(0, 0, 0, "=SHEETS()");
332 m_pDoc->CalcFormulaTree(false, false);
333 double original = m_pDoc->GetValue(0, 0, 0);
334
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());
338
339 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(SC_TAB_APPEND, "test2"));
340
341 double modified = m_pDoc->GetValue(0, 0, 0);
342 ASSERT_DOUBLES_EQUAL_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
343 1.0, modified - original);
344
345 SCTAB nTabCount = m_pDoc->GetTableCount();
346 m_pDoc->DeleteTab(--nTabCount);
347
348 modified = m_pDoc->GetValue(0, 0, 0);
349 ASSERT_DOUBLES_EQUAL_MESSAGE("result of SHEETS() did not get updated after sheet removal.", 0.0,
350 modified - original);
351
352 m_pDoc->DeleteTab(--nTabCount);
353 }
354
355 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncNOW)
356 {
357 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
358
359 double val = 1;
360 m_pDoc->SetValue(0, 0, 0, val);
361 m_pDoc->SetString(0, 1, 0, "=IF(A1>0;NOW();0");
362 double now1 = m_pDoc->GetValue(0, 1, 0);
363 CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
364
365 val = 0;
366 m_pDoc->SetValue(0, 0, 0, val);
367 m_pDoc->CalcFormulaTree(false, false);
368 double zero = m_pDoc->GetValue(0, 1, 0);
369 ASSERT_DOUBLES_EQUAL_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", 0.0,
370 zero);
371
372 val = 1;
373 m_pDoc->SetValue(0, 0, 0, val);
374 m_pDoc->CalcFormulaTree(false, false);
375 double now2 = m_pDoc->GetValue(0, 1, 0);
376 CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
377
378 m_pDoc->DeleteTab(0);
379 }
380
381 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncNUMBERVALUE)
382 {
383 // NUMBERVALUE fdo#57180
384
385 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
386
387 // Empty A1:A39 first.
388 clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
389
390 // Raw data (rows 1 through 6)
391 const char* aData[]
392 = { "1ag9a9b9", "1ag34 5g g6 78b9%%", "1 234d56E-2", "d4", "54.4", "1a2b3e1%" };
393
394 SCROW nRows = SAL_N_ELEMENTS(aData);
395 for (SCROW i = 0; i < nRows; ++i)
396 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
397
398 printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows - 1, 0), "data range for NUMBERVALUE");
399
400 // formulas and results
401 static const struct
402 {
403 const char* pFormula;
404 const char* pResult;
405 } aChecks[] = { { "=NUMBERVALUE(A1;\"b\";\"ag\")", "199.9" },
406 { "=NUMBERVALUE(A2;\"b\";\"ag\")", "134.56789" },
407 { "=NUMBERVALUE(A2;\"b\";\"g\")", "#VALUE!" },
408 { "=NUMBERVALUE(A3;\"d\")", "12.3456" },
409 { "=NUMBERVALUE(A4;\"d\";\"foo\")", "0.4" },
410 { "=NUMBERVALUE(A4;)", "Err:502" },
411 { "=NUMBERVALUE(A5;)", "Err:502" },
412 { "=NUMBERVALUE(A6;\"b\";\"a\")", "1.23" } };
413
414 nRows = SAL_N_ELEMENTS(aChecks);
415 for (SCROW i = 0; i < nRows; ++i)
416 {
417 SCROW nRow = 20 + i;
418 m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
419 }
420 m_pDoc->CalcAll();
421
422 for (SCROW i = 0; i < nRows; ++i)
423 {
424 SCROW nRow = 20 + i;
425 OUString aResult = m_pDoc->GetString(0, nRow, 0);
426 CPPUNIT_ASSERT_EQUAL_MESSAGE(aChecks[i].pFormula,
427 OUString::createFromAscii(aChecks[i].pResult), aResult);
428 }
429
430 m_pDoc->DeleteTab(0);
431 }
432
433 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncLEN)
434 {
435 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
436
437 m_pDoc->InsertTab(0, "Formula");
438
439 // Leave A1:A3 empty, and insert an array of LEN in B1:B3 that references
440 // these empty cells.
441
442 ScMarkData aMark(m_pDoc->GetSheetLimits());
443 aMark.SelectOneTable(0);
444 m_pDoc->InsertMatrixFormula(1, 0, 1, 2, aMark, "=LEN(A1:A3)");
445
446 ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1, 0, 0));
447 CPPUNIT_ASSERT(pFC);
448 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should be a matrix origin.", ScMatrixMode::Formula,
449 pFC->GetMatrixFlag());
450
451 // This should be a 1x3 matrix.
452 SCCOL nCols = -1;
453 SCROW nRows = -1;
454 pFC->GetMatColsRows(nCols, nRows);
455 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCols);
456 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), nRows);
457
458 // LEN value should be 0 for an empty cell.
459 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1, 0, 0)));
460 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1, 1, 0)));
461 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1, 2, 0)));
462
463 m_pDoc->DeleteTab(0);
464 }
465
466 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncLOOKUP)
467 {
468 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
469
470 m_pDoc->InsertTab(0, "Test");
471
472 // Raw data
473 const char* aData[][2] = {
474 { "=CONCATENATE(\"A\")", "1" },
475 { "=CONCATENATE(\"B\")", "2" },
476 { "=CONCATENATE(\"C\")", "3" },
477 { nullptr, nullptr } // terminator
478 };
479
480 // Insert raw data into A1:B3.
481 for (SCROW i = 0; aData[i][0]; ++i)
482 {
483 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
484 m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
485 }
486
487 const char* aData2[][2] = {
488 { "A", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
489 { "B", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
490 { "C", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
491 { nullptr, nullptr } // terminator
492 };
493
494 // Insert check formulas into A5:B7.
495 for (SCROW i = 0; aData2[i][0]; ++i)
496 {
497 m_pDoc->SetString(0, i + 4, 0, OUString::createFromAscii(aData2[i][0]));
498 m_pDoc->SetString(1, i + 4, 0, OUString::createFromAscii(aData2[i][1]));
499 }
500
501 printRange(m_pDoc, ScRange(0, 4, 0, 1, 6, 0), "Data range for LOOKUP.");
502
503 // Values for B5:B7 should be 1, 2, and 3.
504 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
505 static_cast<int>(m_pDoc->GetErrCode(ScAddress(1, 4, 0))));
506 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
507 static_cast<int>(m_pDoc->GetErrCode(ScAddress(1, 5, 0))));
508 CPPUNIT_ASSERT_EQUAL_MESSAGE("This formula should not have an error code.", 0,
509 static_cast<int>(m_pDoc->GetErrCode(ScAddress(1, 6, 0))));
510
511 ASSERT_DOUBLES_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1, 4, 0)));
512 ASSERT_DOUBLES_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1, 5, 0)));
513 ASSERT_DOUBLES_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1, 6, 0)));
514
515 m_pDoc->DeleteTab(0);
516 }
517
518 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncLOOKUParrayWithError)
519 {
520 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
521 m_pDoc->InsertTab(0, "Test");
522
523 std::vector<std::vector<const char*>> aData = { { "x", "y", "z" }, { "a", "b", "c" } };
524 insertRangeData(m_pDoc, ScAddress(2, 1, 0), aData); // C2:E3
525 m_pDoc->SetString(0, 0, 0, "=LOOKUP(2;1/(C2:E2<>\"\");C3:E3)"); // A1
526
527 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for last column.", OUString("c"),
528 m_pDoc->GetString(0, 0, 0));
529 m_pDoc->SetString(4, 1, 0, ""); // E2
530 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for second last column.", OUString("b"),
531 m_pDoc->GetString(0, 0, 0));
532
533 m_pDoc->SetString(6, 1, 0, "one"); // G2
534 m_pDoc->SetString(6, 5, 0, "two"); // G6
535 // Creates an interim array {1,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!}
536 m_pDoc->SetString(7, 8, 0, "=LOOKUP(2;1/(NOT(ISBLANK(G2:G9)));G2:G9)"); // H9
537 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find match for last row.", OUString("two"),
538 m_pDoc->GetString(7, 8, 0));
539
540 // Lookup on empty range.
541 m_pDoc->SetString(9, 8, 0, "=LOOKUP(2;1/(NOT(ISBLANK(I2:I9)));I2:I9)"); // J9
542 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should find no match.", OUString("#N/A"),
543 m_pDoc->GetString(9, 8, 0));
544
545 m_pDoc->DeleteTab(0);
546 }
547
548 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf141146)
549 {
550 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
551 m_pDoc->InsertTab(0, "Test1");
552 m_pDoc->InsertTab(1, "Test2");
553
554 std::vector<std::vector<const char*>> aData
555 = { { "k1", "value1" }, { "k2", "value2" }, { "k3", "value3" } };
556
557 insertRangeData(m_pDoc, ScAddress(0, 1, 1), aData); // A2:B4
558 m_pDoc->SetString(4, 0, 1, "k2"); // E1
559
560 m_pDoc->SetString(4, 1, 1, "=LOOKUP(1;1/(A$2:A$4=E$1);1)");
561 m_pDoc->SetString(4, 2, 1, "=LOOKUP(E1;A$2:A$4;B2:B4)");
562 m_pDoc->SetString(4, 3, 1, "=LOOKUP(1;1/(A$2:A$4=E$1);B2:B4)");
563
564 // Without the fix in place, this test would have failed with
565 // - Expected: #N/A
566 // - Actual :
567 CPPUNIT_ASSERT_EQUAL(OUString("#N/A"), m_pDoc->GetString(4, 1, 1));
568 CPPUNIT_ASSERT_EQUAL(OUString("value2"), m_pDoc->GetString(4, 2, 1));
569 CPPUNIT_ASSERT_EQUAL(OUString("value2"), m_pDoc->GetString(4, 3, 1));
570
571 m_pDoc->DeleteTab(1);
572 m_pDoc->DeleteTab(0);
573 }
574
575 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncVLOOKUP)
576 {
577 // VLOOKUP
578
579 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
580
581 // Clear A1:F40.
582 clearRange(m_pDoc, ScRange(0, 0, 0, 5, 39, 0));
583
584 // Raw data
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
590 };
591
592 // Insert raw data into A1:B14.
593 for (SCROW i = 0; aData[i][0]; ++i)
594 {
595 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
596 m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
597 }
598
599 printRange(m_pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
600
601 // Formula data
602 static const struct
603 {
604 const char* pLookup;
605 const char* pFormula;
606 const char* pRes;
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" } };
625
626 // Insert formula data into D1:E18.
627 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
628 {
629 m_pDoc->SetString(3, i, 0, OUString::createFromAscii(aChecks[i].pLookup));
630 m_pDoc->SetString(4, i, 0, OUString::createFromAscii(aChecks[i].pFormula));
631 }
632 m_pDoc->CalcAll();
633 printRange(m_pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
634
635 // Verify results.
636 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
637 {
638 if (i == 0)
639 // Skip the header row.
640 continue;
641
642 OUString aRes = m_pDoc->GetString(4, i, 0);
643 bool bGood = aRes.equalsAscii(aChecks[i].pRes);
644 if (!bGood)
645 {
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);
649 }
650 }
651
652 // Clear the sheet and start over.
653 clearSheet(m_pDoc, 0);
654
655 // Lookup on sorted data interspersed with empty cells.
656
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), "One");
662 m_pDoc->SetString(ScAddress(1, 4, 0), "Two");
663 m_pDoc->SetString(ScAddress(1, 7, 0), "Four");
664
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);
671
672 // E1:E5 contain formulas.
673 m_pDoc->SetString(ScAddress(4, 0, 0), "=VLOOKUP(D1;$A$1:$B$8;2)");
674 m_pDoc->SetString(ScAddress(4, 1, 0), "=VLOOKUP(D2;$A$1:$B$8;2)");
675 m_pDoc->SetString(ScAddress(4, 2, 0), "=VLOOKUP(D3;$A$1:$B$8;2)");
676 m_pDoc->SetString(ScAddress(4, 3, 0), "=VLOOKUP(D4;$A$1:$B$8;2)");
677 m_pDoc->SetString(ScAddress(4, 4, 0), "=VLOOKUP(D5;$A$1:$B$8;2)");
678 m_pDoc->CalcAll();
679
680 // Check the formula results in E1:E5.
681 CPPUNIT_ASSERT_EQUAL(OUString("One"), m_pDoc->GetString(ScAddress(4, 0, 0)));
682 CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4, 1, 0)));
683 CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4, 2, 0)));
684 CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4, 3, 0)));
685 CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4, 4, 0)));
686
687 // Start over again.
688 clearSheet(m_pDoc, 0);
689
690 // Set A,B,...,G to A1:A7.
691 m_pDoc->SetString(ScAddress(0, 0, 0), "A");
692 m_pDoc->SetString(ScAddress(0, 1, 0), "B");
693 m_pDoc->SetString(ScAddress(0, 2, 0), "C");
694 m_pDoc->SetString(ScAddress(0, 3, 0), "D");
695 m_pDoc->SetString(ScAddress(0, 4, 0), "E");
696 m_pDoc->SetString(ScAddress(0, 5, 0), "F");
697 m_pDoc->SetString(ScAddress(0, 6, 0), "G");
698
699 // Set the formula in C1.
700 m_pDoc->SetString(ScAddress(2, 0, 0), "=VLOOKUP(\"C\";A1:A16;1)");
701 CPPUNIT_ASSERT_EQUAL(OUString("C"), m_pDoc->GetString(ScAddress(2, 0, 0)));
702
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" } };
712
713 ScAddress aPos2(0, 20, 0);
714 ScRange aRange2 = insertRangeData(m_pDoc, aPos2, aData2);
715 CPPUNIT_ASSERT_EQUAL(aPos2, aRange2.aStart);
716
717 aPos2.SetCol(3); // column D formula results
718 for (size_t i = 0; i < aData2.size(); ++i)
719 {
720 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aData2[i][4]), m_pDoc->GetString(aPos2));
721 aPos2.IncRow();
722 }
723
724 m_pDoc->DeleteTab(0);
725 }
726
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])
730 {
731 size_t nDataSize = DataSize;
732 for (size_t i = 0; i < nDataSize; ++i)
733 pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
734
735 for (size_t i = 0; i < FormulaSize; ++i)
736 {
737 pDoc->SetString(1, i, 0, OUString::createFromAscii(aChecks[i].pVal));
738
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);
742 }
743
744 pDoc->CalcAll();
745 printRange(pDoc, ScRange(0, 0, 0, 2, FormulaSize - 1, 0), "MATCH");
746
747 // verify the results.
748 for (size_t i = 0; i < FormulaSize; ++i)
749 {
750 OUString aStr = pDoc->GetString(2, i, 0);
751 if (!aStr.equalsAscii(aChecks[i].pRes))
752 {
753 cerr << "row " << (i + 1) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr
754 << "'"
755 " criterion='"
756 << aChecks[i].pVal << "'" << endl;
757 CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
758 }
759 }
760 }
761
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])
765 {
766 size_t nDataSize = DataSize;
767 for (size_t i = 0; i < nDataSize; ++i)
768 pDoc->SetString(i, 0, 0, OUString::createFromAscii(aData[i]));
769
770 for (size_t i = 0; i < FormulaSize; ++i)
771 {
772 pDoc->SetString(i, 1, 0, OUString::createFromAscii(aChecks[i].pVal));
773
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);
779 }
780
781 pDoc->CalcAll();
782 printRange(pDoc, ScRange(0, 0, 0, FormulaSize - 1, 2, 0), "MATCH");
783
784 // verify the results.
785 for (size_t i = 0; i < FormulaSize; ++i)
786 {
787 OUString aStr = pDoc->GetString(i, 2, 0);
788 if (!aStr.equalsAscii(aChecks[i].pRes))
789 {
790 cerr << "column " << char('A' + i) << ": expected='" << aChecks[i].pRes << "' actual='"
791 << aStr
792 << "'"
793 " criterion='"
794 << aChecks[i].pVal << "'" << endl;
795 CPPUNIT_ASSERT_MESSAGE("Unexpected result for horizontal MATCH", false);
796 }
797 }
798 }
799
800 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncMATCH)
801 {
802 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
803
804 clearRange(m_pDoc, ScRange(0, 0, 0, 40, 40, 0));
805 {
806 // Ascending in-exact match
807
808 // data range (A1:A9)
809 const char* aData[] = {
810 "1", "2", "3", "4", "5", "6", "7", "8", "9", "B", "B", "C",
811 };
812
813 // formula (B1:C12)
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" } };
819
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,
823 aChecks);
824 clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
825 }
826
827 {
828 // Descending in-exact match
829
830 // data range (A1:A9)
831 const char* aData[] = { "D", "C", "B", "9", "8", "7", "6", "5", "4", "3", "2", "1" };
832
833 // formula (B1:C12)
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" } };
839
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,
843 aChecks);
844 clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
845 }
846
847 {
848 // search range contains leading and trailing empty cell ranges.
849
850 clearRange(m_pDoc, ScRange(0, 0, 0, 2, 100, 0));
851
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);
857
858 // Find value 2 which is in A6.
859 m_pDoc->SetString(ScAddress(1, 0, 0), "=MATCH(2;A1:A20)");
860 m_pDoc->CalcAll();
861
862 CPPUNIT_ASSERT_EQUAL(OUString("6"), m_pDoc->GetString(ScAddress(1, 0, 0)));
863 }
864
865 {
866 // Test the ReferenceOrForceArray parameter.
867
868 clearRange(m_pDoc, ScRange(0, 0, 0, 1, 7, 0));
869
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);
876
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), "=MATCH(\"33\";B1:B5&B1:B5)");
880 m_pDoc->CalcAll();
881 CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0, 5, 0)));
882 }
883
884 m_pDoc->DeleteTab(0);
885 }
886
887 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncCELL)
888 {
889 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
890
891 clearRange(m_pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
892
893 {
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;
897
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" } };
906
907 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
908 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aChecks[i].pVal));
909 m_pDoc->CalcAll();
910
911 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
912 {
913 OUString aVal = m_pDoc->GetString(0, i, 0);
914 CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
915 }
916 }
917
918 m_pDoc->DeleteTab(0);
919 }
920
921 /** See also test case document fdo#44456 sheet cpearson */
922 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncDATEDIF)
923 {
924 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
925
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
943 };
944
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,
949 aDataRange.aStart);
950
951 m_pDoc->CalcAll();
952
953 for (size_t i = 0; i < aData.size(); ++i)
954 {
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]));
958 }
959
960 m_pDoc->DeleteTab(0);
961 }
962
963 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncINDIRECT)
964 {
965 OUString aTabName("foo");
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
968
969 bool bGood = m_pDoc->GetName(0, aTabName);
970 CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
971
972 OUString aTest = "Test", aRefErr = "#REF!";
973 m_pDoc->SetString(0, 10, 0, aTest);
974 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected cell value.", aTest, m_pDoc->GetString(0, 10, 0));
975
976 OUString aPrefix = "=INDIRECT(\"";
977
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);
986
987 m_pDoc->CalcAll();
988 {
989 // Default (for new documents) is to use current formula syntax
990 // which is Calc A1
991 const OUString* aChecks[] = { &aTest, &aRefErr, &aRefErr, &aTest };
992
993 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
994 {
995 OUString aVal = m_pDoc->GetString(0, i, 0);
996 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
997 }
998 }
999
1000 ScCalcConfig aConfig;
1001 aConfig.SetStringRefSyntax(formula::FormulaGrammar::CONV_OOO);
1002 m_pDoc->SetCalcConfig(aConfig);
1003 m_pDoc->CalcAll();
1004 {
1005 // Explicit Calc A1 syntax
1006 const OUString* aChecks[] = { &aTest, &aRefErr, &aRefErr, &aTest };
1007
1008 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1009 {
1010 OUString aVal = m_pDoc->GetString(0, i, 0);
1011 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
1012 }
1013 }
1014
1015 aConfig.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_A1);
1016 m_pDoc->SetCalcConfig(aConfig);
1017 m_pDoc->CalcAll();
1018 {
1019 // Excel A1 syntax
1020 const OUString* aChecks[] = { &aRefErr, &aTest, &aRefErr, &aTest };
1021
1022 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1023 {
1024 OUString aVal = m_pDoc->GetString(0, i, 0);
1025 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
1026 }
1027 }
1028
1029 aConfig.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1);
1030 m_pDoc->SetCalcConfig(aConfig);
1031 m_pDoc->CalcAll();
1032 {
1033 // Excel R1C1 syntax
1034 const OUString* aChecks[] = { &aRefErr, &aRefErr, &aTest, &aTest };
1035
1036 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1037 {
1038 OUString aVal = m_pDoc->GetString(0, i, 0);
1039 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
1040 }
1041 }
1042
1043 m_pDoc->DeleteTab(0);
1044 }
1045
1046 // Test case for tdf#83365 - Access across spreadsheet returns Err:504
1047 //
1048 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncINDIRECT2)
1049 {
1050 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
1051 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(1, "bar"));
1052 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(2, "baz"));
1053
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);
1057
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);
1061
1062 // Test range triplet (absolute, relative, relative) : (absolute, relative, relative)
1063 m_pDoc->SetString(0, 0, 2, "=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$1),1)");
1064
1065 // Test range triplet (absolute, relative, relative) : (absolute, absolute, relative)
1066 m_pDoc->SetString(0, 1, 2, "=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$2),1)");
1067
1068 // Test range triplet (absolute, relative, relative) : (absolute, absolute, absolute)
1069 m_pDoc->SetString(0, 2, 2, "=COUNTIF(bar.$A1:INDIRECT(\"$A\"&foo.$A$3),1)");
1070
1071 // Test range triplet (absolute, absolute, relative) : (absolute, relative, relative)
1072 m_pDoc->SetString(0, 3, 2, "=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$1),1)");
1073
1074 // Test range triplet (absolute, absolute, relative) : (absolute, absolute, relative)
1075 m_pDoc->SetString(0, 4, 2, "=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$2),1)");
1076
1077 // Test range triplet (absolute, absolute, relative) : (absolute, absolute, relative)
1078 m_pDoc->SetString(0, 5, 2, "=COUNTIF(bar.$A$1:INDIRECT(\"$A\"&foo.$A$3),1)");
1079
1080 // Test range triplet (absolute, absolute, absolute) : (absolute, relative, relative)
1081 m_pDoc->SetString(0, 6, 2, "=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$1),1)");
1082
1083 // Test range triplet (absolute, absolute, absolute) : (absolute, absolute, relative)
1084 m_pDoc->SetString(0, 7, 2, "=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$2),1)");
1085
1086 // Check indirect reference "bar.$A\"&foo.$A$1
1087 m_pDoc->SetString(0, 8, 2, "=COUNTIF(bar.$A$1:INDIRECT(\"bar.$A\"&foo.$A$1),1)");
1088
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, "=COUNTIF($bar.$A$1:INDIRECT(\"$A\"&foo.$A$3),1)");
1093
1094 m_pDoc->CalcAll();
1095
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);
1101
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);
1107
1108 m_pDoc->DeleteTab(2);
1109 m_pDoc->DeleteTab(1);
1110 m_pDoc->DeleteTab(0);
1111 }
1112
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)
1116 {
1117 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
1118
1119 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1120
1121 ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
1122 ScRangeData* pRangeData = new ScRangeData(*m_pDoc, "RoleAssignment", "$D$4:$D$13");
1123 pGlobalNames->insert(pRangeData);
1124
1125 // D6: data to match, in 3rd row of named range.
1126 m_pDoc->SetString(3, 5, 0, "Test1");
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 "=MATCH(\"Test1\";INDIRECT(ADDRESS(ROW(RoleAssignment)+1;COLUMN("
1132 "RoleAssignment))&\":\"&ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;"
1133 "COLUMN(RoleAssignment)));0)");
1134
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));
1138
1139 m_pDoc->DeleteTab(0);
1140 }
1141
1142 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaDepTracking)
1143 {
1144 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
1145
1146 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1147
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);
1158
1159 // B2 listens on D2.
1160 m_pDoc->SetString(aB2, "=D2");
1161 double val = m_pDoc->GetValue(aB2);
1162 ASSERT_DOUBLES_EQUAL_MESSAGE("Referencing an empty cell should yield zero.", 0.0, val);
1163
1164 {
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));
1169 }
1170
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);
1175
1176 // And again.
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);
1180
1181 clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1182
1183 {
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());
1188 }
1189
1190 // Now, let's test the range dependency tracking.
1191
1192 // B2 listens on D2:E6.
1193 m_pDoc->SetString(aB2, "=SUM(D2:E6)");
1194 val = m_pDoc->GetValue(aB2);
1195 ASSERT_DOUBLES_EQUAL_MESSAGE("Summing an empty range should yield zero.", 0.0, val);
1196
1197 {
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));
1202 }
1203
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);
1208
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.
1212
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));
1217
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));
1223
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));
1229
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));
1235
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));
1241
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));
1247
1248 clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1249
1250 // Now, column-based dependency tracking. We now switch to the R1C1
1251 // syntax which is easier to use for repeated relative references.
1252
1253 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1254
1255 val = 0.0;
1256 for (SCROW nRow = 1; nRow <= 9; ++nRow)
1257 {
1258 // Static value in column 1.
1259 m_pDoc->SetValue(0, nRow, 0, ++val);
1260
1261 // Formula in column 2 that references cell to the left.
1262 m_pDoc->SetString(1, nRow, 0, "=RC[-1]");
1263
1264 // Formula in column 3 that references cell to the left.
1265 m_pDoc->SetString(2, nRow, 0, "=RC[-1]*2");
1266 }
1267
1268 // Check formula values.
1269 val = 0.0;
1270 for (SCROW nRow = 1; nRow <= 9; ++nRow)
1271 {
1272 ++val;
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));
1277 }
1278
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, "=R2C3");
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));
1285
1286 m_pDoc->DeleteTab(0);
1287 }
1288
1289 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaDepTracking2)
1290 {
1291 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "foo"));
1292
1293 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1294
1295 double val = 2.0;
1296 m_pDoc->SetValue(0, 0, 0, val);
1297 val = 4.0;
1298 m_pDoc->SetValue(1, 0, 0, val);
1299 val = 5.0;
1300 m_pDoc->SetValue(0, 1, 0, val);
1301 m_pDoc->SetString(2, 0, 0, "=A1/B1");
1302 m_pDoc->SetString(1, 1, 0, "=B1*C1");
1303
1304 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1, 1, 0)); // B2 should equal 2.
1305
1306 clearRange(m_pDoc, ScAddress(2, 0, 0)); // Delete C1.
1307
1308 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(1, 1, 0)); // B2 should now equal 0.
1309
1310 m_pDoc->DeleteTab(0);
1311 }
1312
1313 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaDepTracking3)
1314 {
1315 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1316
1317 m_pDoc->InsertTab(0, "Formula");
1318
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 },
1323 };
1324
1325 insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData);
1326
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)));
1332
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)));
1338
1339 m_pDoc->DeleteTab(0);
1340 }
1341
1342 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaDepTrackingDeleteRow)
1343 {
1344 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1345
1346 m_pDoc->InsertTab(0, "Test");
1347
1348 // Values in A1:A3.
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);
1352
1353 // SUM(A1:A3) in A5.
1354 m_pDoc->SetString(ScAddress(0, 4, 0), "=SUM(A1:A3)");
1355
1356 // A6 to reference A5.
1357 m_pDoc->SetString(ScAddress(0, 5, 0), "=A5*10");
1358 const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0, 5, 0));
1359 CPPUNIT_ASSERT(pFC);
1360
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);
1369
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)));
1373
1374 // Delete row 2.
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);
1379
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);
1389
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)));
1393
1394 m_pDoc->DeleteTab(0);
1395 }
1396
1397 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaDepTrackingDeleteCol)
1398 {
1399 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1400
1401 m_pDoc->InsertTab(0, "Formula");
1402
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
1408 };
1409
1410 ScAddress aPos(0, 0, 0);
1411 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
1412 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
1413
1414 // Check the initial values.
1415 for (SCCOL i = 0; i <= 2; ++i)
1416 {
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)));
1420 }
1421
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());
1427
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());
1432
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,
1438 true);
1439
1440 {
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!" },
1447 };
1448
1449 ScRange aCheckRange(0, 0, 0, 1, 3, 0);
1450 bool bSuccess
1451 = checkOutput(m_pDoc, aCheckRange, aOutputCheck, "Check after deleting column A");
1452 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1453 }
1454
1455 // Undo and check the result.
1456 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1457 CPPUNIT_ASSERT(pUndoMgr);
1458 pUndoMgr->Undo();
1459
1460 {
1461 // Expected output table content. 0 = empty cell
1462 std::vector<std::vector<const char*>> aOutputCheck = {
1463 { "2", "2", "2" },
1464 { nullptr, nullptr, nullptr },
1465 { "3", "3", "3" },
1466 { "4", "4", "4" },
1467 };
1468
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);
1472 }
1473
1474 // Redo and check.
1475 pUndoMgr->Redo();
1476 {
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!" },
1483 };
1484
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);
1488 }
1489
1490 // Undo and change the values in column A.
1491 pUndoMgr->Undo();
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);
1495
1496 {
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" },
1503 };
1504
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);
1509 }
1510
1511 m_pDoc->DeleteTab(0);
1512 }
1513
1514 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaMatrixResultUpdate)
1515 {
1516 m_pDoc->InsertTab(0, "Test");
1517
1518 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
1519
1520 // Set a numeric value to A1.
1521 m_pDoc->SetValue(ScAddress(0, 0, 0), 11.0);
1522
1523 ScMarkData aMark(m_pDoc->GetSheetLimits());
1524 aMark.SelectOneTable(0);
1525 m_pDoc->InsertMatrixFormula(1, 0, 1, 0, aMark, "=A1");
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);
1529 pFC->SetChanged(
1530 false); // Clear this flag to simulate displaying of formula cell value on screen.
1531
1532 m_pDoc->SetString(ScAddress(0, 0, 0), "ABC");
1533 CPPUNIT_ASSERT_EQUAL(OUString("ABC"), m_pDoc->GetString(ScAddress(1, 0, 0)));
1534 pFC->SetChanged(false);
1535
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)));
1539
1540 m_pDoc->DeleteTab(0);
1541 }
1542
1543 CPPUNIT_TEST_FIXTURE(TestFormula2, testExternalRef)
1544 {
1545 ScDocShellRef xExtDocSh = new ScDocShell;
1546 OUString aExtDocName("file:///extdata.fake");
1547 OUString aExtSh1Name("Data1");
1548 OUString aExtSh2Name("Data2");
1549 OUString aExtSh3Name("Data3");
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);
1554
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);
1560
1561 OUString constexpr name(u"Name"_ustr);
1562 OUString constexpr value(u"Value"_ustr);
1563
1564 // Sheet 1
1565 rExtDoc.SetString(0, 0, 0, name);
1566 rExtDoc.SetString(0, 1, 0, "Andy");
1567 rExtDoc.SetString(0, 2, 0, "Bruce");
1568 rExtDoc.SetString(0, 3, 0, "Charlie");
1569 rExtDoc.SetString(0, 4, 0, "David");
1570 rExtDoc.SetString(1, 0, 0, value);
1571 double val = 10;
1572 rExtDoc.SetValue(1, 1, 0, val);
1573 val = 11;
1574 rExtDoc.SetValue(1, 2, 0, val);
1575 val = 12;
1576 rExtDoc.SetValue(1, 3, 0, val);
1577 val = 13;
1578 rExtDoc.SetValue(1, 4, 0, val);
1579
1580 // Sheet 2 remains empty.
1581
1582 // Sheet 3
1583 rExtDoc.SetString(0, 0, 2, name);
1584 rExtDoc.SetString(0, 1, 2, "Edward");
1585 rExtDoc.SetString(0, 2, 2, "Frank");
1586 rExtDoc.SetString(0, 3, 2, "George");
1587 rExtDoc.SetString(0, 4, 2, "Henry");
1588 rExtDoc.SetString(1, 0, 2, value);
1589 val = 99;
1590 rExtDoc.SetValue(1, 1, 2, val);
1591 val = 98;
1592 rExtDoc.SetValue(1, 2, 2, val);
1593 val = 97;
1594 rExtDoc.SetValue(1, 3, 2, val);
1595 val = 96;
1596 rExtDoc.SetValue(1, 4, 2, val);
1597
1598 // Test external references on the main document while the external
1599 // document is still in memory.
1600 m_pDoc->InsertTab(0, "Test Sheet");
1601 m_pDoc->SetString(0, 0, 0, "='file:///extdata.fake'#Data1.A1");
1602 OUString test = m_pDoc->GetString(0, 0, 0);
1603 CPPUNIT_ASSERT_EQUAL_MESSAGE("Value is different from the original", name, test);
1604
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);
1617
1618 m_pDoc->SetString(1, 0, 0, "='file:///extdata.fake'#Data1.B1");
1619 test = m_pDoc->GetString(1, 0, 0);
1620 CPPUNIT_ASSERT_EQUAL_MESSAGE("Value is different from the original", value, test);
1621
1622 m_pDoc->SetString(0, 1, 0, "='file:///extdata.fake'#Data1.A2");
1623 m_pDoc->SetString(0, 2, 0, "='file:///extdata.fake'#Data1.A3");
1624 m_pDoc->SetString(0, 3, 0, "='file:///extdata.fake'#Data1.A4");
1625 m_pDoc->SetString(0, 4, 0, "='file:///extdata.fake'#Data1.A5");
1626 m_pDoc->SetString(0, 5, 0, "='file:///extdata.fake'#Data1.A6");
1627
1628 {
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)
1632 {
1633 test = m_pDoc->GetString(0, static_cast<SCROW>(i + 1), 0);
1634 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
1635 }
1636 }
1637 m_pDoc->SetString(1, 1, 0, "='file:///extdata.fake'#Data1.B2");
1638 m_pDoc->SetString(1, 2, 0, "='file:///extdata.fake'#Data1.B3");
1639 m_pDoc->SetString(1, 3, 0, "='file:///extdata.fake'#Data1.B4");
1640 m_pDoc->SetString(1, 4, 0, "='file:///extdata.fake'#Data1.B5");
1641 m_pDoc->SetString(1, 5, 0, "='file:///extdata.fake'#Data1.B6");
1642 {
1643 double pChecks[] = { 10, 11, 12, 13, 0 };
1644 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
1645 {
1646 val = m_pDoc->GetValue(1, static_cast<SCROW>(i + 1), 0);
1647 ASSERT_DOUBLES_EQUAL_MESSAGE("Unexpected cell value.", pChecks[i], val);
1648 }
1649 }
1650
1651 m_pDoc->SetString(2, 0, 0, "='file:///extdata.fake'#Data3.A1");
1652 m_pDoc->SetString(2, 1, 0, "='file:///extdata.fake'#Data3.A2");
1653 m_pDoc->SetString(2, 2, 0, "='file:///extdata.fake'#Data3.A3");
1654 m_pDoc->SetString(2, 3, 0, "='file:///extdata.fake'#Data3.A4");
1655 {
1656 const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
1657 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
1658 {
1659 test = m_pDoc->GetString(2, static_cast<SCROW>(i), 0);
1660 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
1661 }
1662 }
1663
1664 m_pDoc->SetString(3, 0, 0, "='file:///extdata.fake'#Data3.B1");
1665 m_pDoc->SetString(3, 1, 0, "='file:///extdata.fake'#Data3.B2");
1666 m_pDoc->SetString(3, 2, 0, "='file:///extdata.fake'#Data3.B3");
1667 m_pDoc->SetString(3, 3, 0, "='file:///extdata.fake'#Data3.B4");
1668 {
1669 const char* pChecks[] = { "Value", "99", "98", "97" };
1670 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
1671 {
1672 test = m_pDoc->GetString(3, static_cast<SCROW>(i), 0);
1673 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
1674 }
1675 }
1676
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());
1691
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);
1695
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());
1708
1709 // Unload the external document shell.
1710 xExtDocSh->DoClose();
1711 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1712 !findLoadedDocShellByName(aExtDocName));
1713
1714 m_pDoc->DeleteTab(0);
1715 }
1716
1717 CPPUNIT_TEST_FIXTURE(TestFormula2, testExternalRangeName)
1718 {
1719 ScDocShellRef xExtDocSh = new ScDocShell;
1720 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);
1725
1726 ScDocument& rExtDoc = xExtDocSh->GetDocument();
1727 rExtDoc.InsertTab(0, "Data1");
1728 rExtDoc.SetValue(0, 0, 0, 123.456);
1729
1730 ScRangeName* pRangeName = rExtDoc.GetRangeName();
1731 ScRangeData* pRangeData = new ScRangeData(rExtDoc, "ExternalName", "$Data1.$A$1");
1732 pRangeName->insert(pRangeData);
1733
1734 m_pDoc->InsertTab(0, "Test Sheet");
1735 m_pDoc->SetString(0, 1, 0, "='file:///extdata.fake'#ExternalName");
1736
1737 double nVal = m_pDoc->GetValue(0, 1, 0);
1738 ASSERT_DOUBLES_EQUAL(123.456, nVal);
1739
1740 xExtDocSh->DoClose();
1741 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1742 !findLoadedDocShellByName(aExtDocName));
1743 m_pDoc->DeleteTab(0);
1744 }
1745
1746 void TestFormula2::testExtRefFuncT(ScDocument* pDoc, ScDocument& rExtDoc)
1747 {
1748 clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
1749 clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
1750
1751 rExtDoc.SetString(0, 0, 0, "'1.2");
1752 rExtDoc.SetString(0, 1, 0, "Foo");
1753 rExtDoc.SetValue(0, 2, 0, 12.3);
1754 pDoc->SetString(0, 0, 0, "=T('file:///extdata.fake'#Data.A1)");
1755 pDoc->SetString(0, 1, 0, "=T('file:///extdata.fake'#Data.A2)");
1756 pDoc->SetString(0, 2, 0, "=T('file:///extdata.fake'#Data.A3)");
1757 pDoc->CalcAll();
1758
1759 OUString aRes = pDoc->GetString(0, 0, 0);
1760 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected result with T.", OUString("1.2"), aRes);
1761 aRes = pDoc->GetString(0, 1, 0);
1762 CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected result with T.", OUString("Foo"), aRes);
1763 aRes = pDoc->GetString(0, 2, 0);
1764 CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
1765 }
1766
1767 void TestFormula2::testExtRefFuncOFFSET(ScDocument* pDoc, ScDocument& rExtDoc)
1768 {
1769 clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
1770 clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
1771
1772 sc::AutoCalcSwitch aACSwitch(*pDoc, true);
1773
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), "=OFFSET('file:///extdata.fake'#Data.$A$1;1;0;1;1)");
1777 CPPUNIT_ASSERT_EQUAL(1.2, pDoc->GetValue(ScAddress(0, 0, 0)));
1778 }
1779
1780 void TestFormula2::testExtRefFuncVLOOKUP(ScDocument* pDoc, ScDocument& rExtDoc)
1781 {
1782 clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
1783 clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
1784
1785 // Populate the external document.
1786 rExtDoc.SetString(ScAddress(0, 0, 0), "A1");
1787 rExtDoc.SetString(ScAddress(0, 1, 0), "A2");
1788 rExtDoc.SetString(ScAddress(0, 2, 0), "A3");
1789 rExtDoc.SetString(ScAddress(0, 3, 0), "A4");
1790 rExtDoc.SetString(ScAddress(0, 4, 0), "A5");
1791
1792 rExtDoc.SetString(ScAddress(1, 0, 0), "B1");
1793 rExtDoc.SetString(ScAddress(1, 1, 0), "B2");
1794 rExtDoc.SetString(ScAddress(1, 2, 0), "B3");
1795 rExtDoc.SetString(ScAddress(1, 3, 0), "B4");
1796 rExtDoc.SetString(ScAddress(1, 4, 0), "B5");
1797
1798 // Put formula in the source document.
1799
1800 pDoc->SetString(ScAddress(0, 0, 0), "A2");
1801
1802 // Sort order TRUE
1803 pDoc->SetString(ScAddress(1, 0, 0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;1)");
1804 CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1, 0, 0)));
1805
1806 // Sort order FALSE. It should return the same result.
1807 pDoc->SetString(ScAddress(1, 0, 0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;0)");
1808 CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1, 0, 0)));
1809 }
1810
1811 void TestFormula2::testExtRefConcat(ScDocument* pDoc, ScDocument& rExtDoc)
1812 {
1813 clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
1814 clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
1815
1816 sc::AutoCalcSwitch aACSwitch(*pDoc, true);
1817
1818 // String and number
1819 rExtDoc.SetString(ScAddress(0, 0, 0), "Answer: ");
1820 rExtDoc.SetValue(ScAddress(0, 1, 0), 42);
1821
1822 // Concat operation should combine string and number converted to string
1823 pDoc->SetString(ScAddress(0, 0, 0),
1824 "='file:///extdata.fake'#Data.A1 & 'file:///extdata.fake'#Data.A2");
1825 CPPUNIT_ASSERT_EQUAL(OUString("Answer: 42"), pDoc->GetString(ScAddress(0, 0, 0)));
1826 }
1827
1828 CPPUNIT_TEST_FIXTURE(TestFormula2, testExternalRefFunctions)
1829 {
1830 ScDocShellRef xExtDocSh = new ScDocShell;
1831 OUString aExtDocName("file:///extdata.fake");
1832 SfxMedium* pMed = new SfxMedium(aExtDocName, StreamMode::STD_READWRITE);
1833 xExtDocSh->DoLoad(pMed);
1834 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
1835 findLoadedDocShellByName(aExtDocName) != nullptr);
1836
1837 ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
1838 CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
1839 sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
1840 const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
1841 CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.", pFileName);
1842 CPPUNIT_ASSERT_EQUAL_MESSAGE("file name registration has somehow failed.", aExtDocName,
1843 *pFileName);
1844
1845 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1846
1847 // Populate the external source document.
1848 ScDocument& rExtDoc = xExtDocSh->GetDocument();
1849 rExtDoc.InsertTab(0, "Data");
1850 double val = 1;
1851 rExtDoc.SetValue(0, 0, 0, val);
1852 // leave cell B1 empty.
1853 val = 2;
1854 rExtDoc.SetValue(0, 1, 0, val);
1855 rExtDoc.SetValue(1, 1, 0, val);
1856 val = 3;
1857 rExtDoc.SetValue(0, 2, 0, val);
1858 rExtDoc.SetValue(1, 2, 0, val);
1859 val = 4;
1860 rExtDoc.SetValue(0, 3, 0, val);
1861 rExtDoc.SetValue(1, 3, 0, val);
1862
1863 m_pDoc->InsertTab(0, "Test");
1864
1865 static const struct
1866 {
1867 const char* pFormula;
1868 double fResult;
1869 } aChecks[] = {
1870 { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
1871 { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
1872 { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
1873 { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
1874 { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
1875 { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 },
1876 // Should not crash, MUST be 0,m_pDoc->MaxRow() and/or 0,m_pDoc->MaxCol() range (here both)
1877 // to yield a result instead of 1x1 error matrix.
1878 { "=SUM('file:///extdata.fake'#Data.1:1048576)", 19 }
1879 };
1880
1881 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1882 {
1883 m_pDoc->SetString(0, 0, 0, OUString::createFromAscii(aChecks[i].pFormula));
1884 val = m_pDoc->GetValue(0, 0, 0);
1885 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("unexpected result involving external ranges.",
1886 aChecks[i].fResult, val, 1e-15);
1887 }
1888
1889 // A huge external range should not crash, the matrix generated from the
1890 // external range reference should be 1x1 and have one error value.
1891 // XXX NOTE: in case we supported sparse matrix that can hold this large
1892 // areas these tests may be adapted.
1893 m_pDoc->SetString(0, 0, 0, "=SUM('file:///extdata.fake'#Data.B1:AMJ1048575)");
1894 ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
1895 FormulaError nErr = pFC->GetErrCode();
1896 CPPUNIT_ASSERT_EQUAL_MESSAGE(
1897 "huge external range reference expected to yield FormulaError::MatrixSize",
1898 int(FormulaError::MatrixSize), static_cast<int>(nErr));
1899
1900 ScMarkData aMark(m_pDoc->GetSheetLimits());
1901 aMark.SelectOneTable(0);
1902 m_pDoc->InsertMatrixFormula(0, 0, 0, 0, aMark, "'file:///extdata.fake'#Data.B1:AMJ1048575");
1903 pFC = m_pDoc->GetFormulaCell(ScAddress(0, 0, 0));
1904 nErr = pFC->GetErrCode();
1905 CPPUNIT_ASSERT_EQUAL_MESSAGE(
1906 "huge external range reference expected to yield FormulaError::MatrixSize",
1907 int(FormulaError::MatrixSize), static_cast<int>(nErr));
1908 SCSIZE nMatCols, nMatRows;
1909 const ScMatrix* pMat = pFC->GetMatrix();
1910 CPPUNIT_ASSERT_MESSAGE("matrix expected", pMat != nullptr);
1911 pMat->GetDimensions(nMatCols, nMatRows);
1912 CPPUNIT_ASSERT_EQUAL_MESSAGE("1x1 matrix expected", SCSIZE(1), nMatCols);
1913 CPPUNIT_ASSERT_EQUAL_MESSAGE("1x1 matrix expected", SCSIZE(1), nMatRows);
1914
1915 pRefMgr->clearCache(nFileId);
1916 testExtRefFuncT(m_pDoc, rExtDoc);
1917 testExtRefFuncOFFSET(m_pDoc, rExtDoc);
1918 testExtRefFuncVLOOKUP(m_pDoc, rExtDoc);
1919 testExtRefConcat(m_pDoc, rExtDoc);
1920
1921 // Unload the external document shell.
1922 xExtDocSh->DoClose();
1923 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
1924 !findLoadedDocShellByName(aExtDocName));
1925
1926 m_pDoc->DeleteTab(0);
1927 }
1928
1929 CPPUNIT_TEST_FIXTURE(TestFormula2, testExternalRefUnresolved)
1930 {
1931 #if !defined(_WIN32) //FIXME
1932 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1933 m_pDoc->InsertTab(0, "Test");
1934
1935 // Test error propagation of unresolved (not existing document) external
1936 // references. Well, let's hope no build machine has such file with sheet...
1937
1938 std::vector<std::vector<const char*>> aData = {
1939 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1" },
1940 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1+23" },
1941 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1&\"W\"" },
1942 { "=ISREF('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1943 { "=ISERROR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1944 { "=ISERR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1945 { "=ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1946 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1947 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1)" },
1948 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1+23)" },
1949 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1&\"W\")" },
1950 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1=0" },
1951 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1=\"\"" },
1952 { "=INDIRECT(\"'file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1\")" },
1953 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2" },
1954 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2+23" },
1955 { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2&\"W\"" },
1956 { "=ISREF('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1957 { "=ISERROR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1958 { "=ISERR('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1959 { "=ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1960 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1961 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2)" },
1962 { "=ISNUMBER('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2+23)" },
1963 { "=ISTEXT('file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2&\"W\")" },
1964 // TODO: gives Err:504 FIXME { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2=0" },
1965 // TODO: gives Err:504 FIXME { "='file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2=\"\"" },
1966 { "=INDIRECT(\"'file:///NonExistingFilePath/AnyName.ods'#$NoSuchSheet.A1:A2\")" },
1967 };
1968
1969 ScAddress aPos(0, 0, 0);
1970 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
1971 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
1972
1973 std::vector<std::vector<const char*>> aOutputCheck = {
1974 { "#REF!" }, // plain single ref
1975 { "#REF!" }, // +23
1976 { "#REF!" }, // &"W"
1977 { "FALSE" }, // ISREF
1978 { "TRUE" }, // ISERROR
1979 { "TRUE" }, // ISERR
1980 { "FALSE" }, // ISBLANK
1981 { "FALSE" }, // ISNUMBER
1982 { "FALSE" }, // ISTEXT
1983 { "FALSE" }, // ISNUMBER
1984 { "FALSE" }, // ISTEXT
1985 { "#REF!" }, // =0
1986 { "#REF!" }, // =""
1987 { "#REF!" }, // INDIRECT
1988 { "#REF!" }, // A1:A2 range
1989 { "#REF!" }, // +23
1990 { "#REF!" }, // &"W"
1991 { "FALSE" }, // ISREF
1992 { "TRUE" }, // ISERROR
1993 { "TRUE" }, // ISERR
1994 { "FALSE" }, // ISBLANK
1995 { "FALSE" }, // ISNUMBER
1996 { "FALSE" }, // ISTEXT
1997 { "FALSE" }, // ISNUMBER
1998 { "FALSE" }, // ISTEXT
1999 // TODO: gives Err:504 FIXME { "#REF!" }, // =0
2000 // TODO: gives Err:504 FIXME { "#REF!" }, // =""
2001 { "#REF!" }, // INDIRECT
2002 };
2003
2004 bool bSuccess
2005 = checkOutput(m_pDoc, aRange, aOutputCheck, "Check unresolved external reference.");
2006 CPPUNIT_ASSERT_MESSAGE("Unresolved reference check failed", bSuccess);
2007
2008 m_pDoc->DeleteTab(0);
2009 #endif
2010 }
2011
2012 CPPUNIT_TEST_FIXTURE(TestFormula2, testMatrixOp)
2013 {
2014 m_pDoc->InsertTab(0, "Test");
2015
2016 for (SCROW nRow = 0; nRow < 4; ++nRow)
2017 {
2018 m_pDoc->SetValue(0, nRow, 0, nRow);
2019 }
2020 m_pDoc->SetValue(1, 0, 0, 2.0);
2021 m_pDoc->SetValue(3, 0, 0, 1.0);
2022 m_pDoc->SetValue(3, 1, 0, 2.0);
2023 m_pDoc->SetString(2, 0, 0, "=SUMPRODUCT((A1:A4)*B1+D1)");
2024 m_pDoc->SetString(2, 1, 0, "=SUMPRODUCT((A1:A4)*B1-D2)");
2025
2026 double nVal = m_pDoc->GetValue(2, 0, 0);
2027 CPPUNIT_ASSERT_EQUAL(16.0, nVal);
2028
2029 nVal = m_pDoc->GetValue(2, 1, 0);
2030 CPPUNIT_ASSERT_EQUAL(4.0, nVal);
2031
2032 m_pDoc->SetString(4, 0, 0, "=SUMPRODUCT({1;2;4}+8)");
2033 m_pDoc->SetString(4, 1, 0, "=SUMPRODUCT(8+{1;2;4})");
2034 m_pDoc->SetString(4, 2, 0, "=SUMPRODUCT({1;2;4}-8)");
2035 m_pDoc->SetString(4, 3, 0, "=SUMPRODUCT(8-{1;2;4})");
2036 m_pDoc->SetString(4, 4, 0, "=SUMPRODUCT({1;2;4}+{8;16;32})");
2037 m_pDoc->SetString(4, 5, 0, "=SUMPRODUCT({8;16;32}+{1;2;4})");
2038 m_pDoc->SetString(4, 6, 0, "=SUMPRODUCT({1;2;4}-{8;16;32})");
2039 m_pDoc->SetString(4, 7, 0, "=SUMPRODUCT({8;16;32}-{1;2;4})");
2040 double fResult[8] = { 31.0, 31.0, -17.0, 17.0, 63.0, 63.0, -49.0, 49.0 };
2041 for (size_t i = 0; i < SAL_N_ELEMENTS(fResult); ++i)
2042 {
2043 CPPUNIT_ASSERT_EQUAL(fResult[i], m_pDoc->GetValue(4, i, 0));
2044 }
2045
2046 m_pDoc->DeleteTab(0);
2047 }
2048
2049 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncRangeOp)
2050 {
2051 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2052
2053 m_pDoc->InsertTab(0, "Sheet1");
2054 m_pDoc->InsertTab(1, "Sheet2");
2055 m_pDoc->InsertTab(2, "Sheet3");
2056
2057 // Sheet1.B1:B3
2058 m_pDoc->SetValue(1, 0, 0, 1.0);
2059 m_pDoc->SetValue(1, 1, 0, 2.0);
2060 m_pDoc->SetValue(1, 2, 0, 4.0);
2061 // Sheet2.B1:B3
2062 m_pDoc->SetValue(1, 0, 1, 8.0);
2063 m_pDoc->SetValue(1, 1, 1, 16.0);
2064 m_pDoc->SetValue(1, 2, 1, 32.0);
2065 // Sheet3.B1:B3
2066 m_pDoc->SetValue(1, 0, 2, 64.0);
2067 m_pDoc->SetValue(1, 1, 2, 128.0);
2068 m_pDoc->SetValue(1, 2, 2, 256.0);
2069
2070 // Range operator should extend concatenated literal references during
2071 // parse time already, so with this we can test ScComplexRefData::Extend()
2072
2073 // Current sheet is Sheet1, so B1:B2 implies relative Sheet1.B1:B2
2074
2075 ScAddress aPos(0, 0, 0);
2076 m_pDoc->SetString(aPos, "=SUM(B1:B2:B3)");
2077 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(B1:B3)"),
2078 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2079 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
2080
2081 aPos.IncRow();
2082 m_pDoc->SetString(aPos, "=SUM(B1:B3:B2)");
2083 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(B1:B3)"),
2084 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2085 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
2086
2087 aPos.IncRow();
2088 m_pDoc->SetString(aPos, "=SUM(B2:B3:B1)");
2089 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(B1:B3)"),
2090 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2091 CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
2092
2093 aPos.IncRow();
2094 m_pDoc->SetString(aPos, "=SUM(Sheet2.B1:B2:B3)");
2095 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(Sheet2.B1:B3)"),
2096 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2097 CPPUNIT_ASSERT_EQUAL(56.0, m_pDoc->GetValue(aPos));
2098
2099 aPos.IncRow();
2100 m_pDoc->SetString(aPos, "=SUM(B2:B2:Sheet1.B2)");
2101 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(Sheet1.B2:B2)"),
2102 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2103 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(aPos));
2104
2105 aPos.IncRow();
2106 m_pDoc->SetString(aPos, "=SUM(B2:B3:Sheet2.B1)");
2107 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(Sheet1.B1:Sheet2.B3)"),
2108 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2109 CPPUNIT_ASSERT_EQUAL(63.0, m_pDoc->GetValue(aPos));
2110
2111 aPos.IncRow();
2112 m_pDoc->SetString(aPos, "=SUM(Sheet1.B1:Sheet2.B2:Sheet3.B3)");
2113 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(Sheet1.B1:Sheet3.B3)"),
2114 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2115 CPPUNIT_ASSERT_EQUAL(511.0, m_pDoc->GetValue(aPos));
2116
2117 // B1:Sheet2.B2 would be ambiguous, Sheet1.B1:Sheet2.B2 or Sheet2.B1:B2
2118 // The actual representation of the error case may change, so this test may
2119 // have to be adapted.
2120 aPos.IncRow();
2121 m_pDoc->SetString(aPos, "=SUM(B1:Sheet2.B2:Sheet3.B3)");
2122 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(b1:sheet2.b2:Sheet3.B3)"),
2123 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2124 CPPUNIT_ASSERT_EQUAL(OUString("#NAME?"), m_pDoc->GetString(aPos));
2125
2126 aPos.IncRow();
2127 m_pDoc->SetString(aPos, "=SUM(Sheet1.B1:Sheet3.B2:Sheet2.B3)");
2128 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(Sheet1.B1:Sheet3.B3)"),
2129 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2130 CPPUNIT_ASSERT_EQUAL(511.0, m_pDoc->GetValue(aPos));
2131
2132 aPos.IncRow();
2133 m_pDoc->SetString(aPos, "=SUM(B$2:B$2:B2)");
2134 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula.", OUString("=SUM(B$2:B2)"),
2135 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
2136 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(aPos));
2137
2138 m_pDoc->DeleteTab(2);
2139 m_pDoc->DeleteTab(1);
2140 m_pDoc->DeleteTab(0);
2141 }
2142
2143 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncFORMULA)
2144 {
2145 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2146
2147 m_pDoc->InsertTab(0, "Sheet1");
2148
2149 // Data in B1:D3
2150 std::vector<std::vector<const char*>> aData = {
2151 { "=A1", "=FORMULA(B1)", "=FORMULA(B1:B3)" },
2152 { nullptr, "=FORMULA(B2)", "=FORMULA(B1:B3)" },
2153 { "=A3", "=FORMULA(B3)", "=FORMULA(B1:B3)" },
2154 };
2155
2156 ScAddress aPos(1, 0, 0);
2157 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
2158 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
2159
2160 // Checks of C1:D3, where Cy==Dy, and D4:D6
2161 const char* aChecks[] = {
2162 "=A1",
2163 "#N/A",
2164 "=A3",
2165 };
2166 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2167 {
2168 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks[i]), m_pDoc->GetString(2, i, 0));
2169 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks[i]), m_pDoc->GetString(3, i, 0));
2170 }
2171
2172 // Matrix in D4:D6, no intersection with B1:B3
2173 ScMarkData aMark(m_pDoc->GetSheetLimits());
2174 aMark.SelectOneTable(0);
2175 m_pDoc->InsertMatrixFormula(3, 3, 3, 5, aMark, "=FORMULA(B1:B3)");
2176 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2177 {
2178 CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aChecks[i]), m_pDoc->GetString(3, i + 3, 0));
2179 }
2180
2181 m_pDoc->DeleteTab(0);
2182 }
2183
2184 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncTableRef)
2185 {
2186 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2187
2188 m_pDoc->InsertTab(0, "Sheet1");
2189 ScMarkData aMark(m_pDoc->GetSheetLimits());
2190 aMark.SelectOneTable(0);
2191 ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
2192
2193 {
2194 ScDBCollection* pDBs = m_pDoc->GetDBCollection();
2195 CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
2196
2197 // Insert "table" database range definition for A1:B4, with default
2198 // HasHeader=true and HasTotals=false.
2199 std::unique_ptr<ScDBData> pData(new ScDBData("table", 0, 0, 0, 1, 3));
2200 bool bInserted = pDBs->getNamedDBs().insert(std::move(pData));
2201 CPPUNIT_ASSERT_MESSAGE("Failed to insert \"table\" database range.", bInserted);
2202 }
2203
2204 {
2205 // Populate "table" database range with headers and data in A1:B4
2206 std::vector<std::vector<const char*>> aData
2207 = { { "Header1", "Header2" }, { "1", "2" }, { "4", "8" }, { "16", "32" } };
2208 ScAddress aPos(0, 0, 0);
2209 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
2210 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
2211 }
2212
2213 // Named expressions that use Table structured references.
2214 /* TODO: should the item/header separator really be equal to the parameter
2215 * separator, thus be locale dependent and ';' semicolon here, or should it
2216 * be a fixed ',' comma instead? */
2217 static const struct
2218 {
2219 const char* pName;
2220 const char* pExpr;
2221 const char*
2222 pCounta; // expected result when used in row 2 (first data row) as argument to COUNTA()
2223 const char*
2224 pSum3; // expected result when used in row 3 (second data row) as argument to SUM().
2225 const char*
2226 pSum4; // expected result when used in row 4 (third data row) as argument to SUM().
2227 const char*
2228 pSumX; // expected result when used in row 5 (non-intersecting) as argument to SUM().
2229 } aNames[]
2230 = { { "all", "table[[#All]]", "8", "63", "63", "63" },
2231 { "data_implicit", "table[]", "6", "63", "63", "63" },
2232 { "data", "table[[#Data]]", "6", "63", "63", "63" },
2233 { "headers", "table[[#Headers]]", "2", "0", "0", "0" },
2234 { "header1", "table[[Header1]]", "3", "21", "21", "21" },
2235 { "header2", "table[[Header2]]", "3", "42", "42", "42" },
2236 { "data_header1", "table[[#Data];[Header1]]", "3", "21", "21", "21" },
2237 { "data_header2", "table[[#Data];[Header2]]", "3", "42", "42", "42" },
2238 { "this_row", "table[[#This Row]]", "2", "12", "48", "#VALUE!" },
2239 { "this_row_header1", "table[[#This Row];[Header1]]", "1", "4", "16", "#VALUE!" },
2240 { "this_row_header2", "table[[#This Row];[Header2]]", "1", "8", "32", "#VALUE!" },
2241 { "this_row_range_header_1_to_2", "table[[#This Row];[Header1]:[Header2]]", "2", "12",
2242 "48", "#VALUE!" } };
2243
2244 {
2245 // Insert named expressions.
2246 ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
2247 CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
2248
2249 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2250 {
2251 // Choose base position that does not intersect with the database
2252 // range definition to test later use of [#This Row] results in
2253 // proper rows.
2254 ScRangeData* pName
2255 = new ScRangeData(*m_pDoc, OUString::createFromAscii(aNames[i].pName),
2256 OUString::createFromAscii(aNames[i].pExpr), ScAddress(2, 4, 0),
2257 ScRangeData::Type::Name, formula::FormulaGrammar::GRAM_NATIVE);
2258 bool bInserted = pGlobalNames->insert(pName);
2259 CPPUNIT_ASSERT_MESSAGE(OString(OString::Concat("Failed to insert named expression ")
2260 + aNames[i].pName + ".")
2261 .getStr(),
2262 bInserted);
2263 }
2264 }
2265
2266 // Use the named expressions in COUNTA() formulas, on row 2 that intersects.
2267 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2268 {
2269 OUString aFormula("=COUNTA(" + OUString::createFromAscii(aNames[i].pName) + ")");
2270 ScAddress aPos(3 + i, 1, 0);
2271 m_pDoc->SetString(aPos, aFormula);
2272 // For easier "debugability" have position and formula in assertion.
2273 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2274 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aNames[i].pCounta)),
2275 OUString(aPrefix + m_pDoc->GetString(aPos)));
2276 }
2277
2278 // Use the named expressions in SUM() formulas, on row 3 that intersects.
2279 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2280 {
2281 OUString aFormula("=SUM(" + OUString::createFromAscii(aNames[i].pName) + ")");
2282 ScAddress aPos(3 + i, 2, 0);
2283 m_pDoc->SetString(aPos, aFormula);
2284 // For easier "debugability" have position and formula in assertion.
2285 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2286 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aNames[i].pSum3)),
2287 OUString(aPrefix + m_pDoc->GetString(aPos)));
2288 }
2289
2290 // Use the named expressions in SUM() formulas, on row 4 that intersects.
2291 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2292 {
2293 OUString aFormula("=SUM(" + OUString::createFromAscii(aNames[i].pName) + ")");
2294 ScAddress aPos(3 + i, 3, 0);
2295 m_pDoc->SetString(aPos, aFormula);
2296 // For easier "debugability" have position and formula in assertion.
2297 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2298 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aNames[i].pSum4)),
2299 OUString(aPrefix + m_pDoc->GetString(aPos)));
2300 }
2301
2302 // Use the named expressions in SUM() formulas, on row 5 that does not intersect.
2303 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2304 {
2305 OUString aFormula("=SUM(" + OUString::createFromAscii(aNames[i].pName) + ")");
2306 ScAddress aPos(3 + i, 4, 0);
2307 m_pDoc->SetString(aPos, aFormula);
2308 // For easier "debugability" have position and formula in assertion.
2309 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2310 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aNames[i].pSumX)),
2311 OUString(aPrefix + m_pDoc->GetString(aPos)));
2312 }
2313
2314 // Insert a column at column B to extend database range from column A,B to
2315 // A,B,C. Use ScDocFunc so RefreshDirtyTableColumnNames() is called.
2316 rDocFunc.InsertCells(ScRange(1, 0, 0, 1, m_pDoc->MaxRow(), 0), &aMark, INS_INSCOLS_BEFORE,
2317 false, true);
2318
2319 // Re-verify the named expression in SUM() formula, on row 4 that
2320 // intersects, now starting at column E, still works.
2321 m_pDoc->CalcAll();
2322 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
2323 {
2324 OUString aFormula("=SUM(" + OUString::createFromAscii(aNames[i].pName) + ")");
2325 ScAddress aPos(4 + i, 3, 0);
2326 // For easier "debugability" have position and formula in assertion.
2327 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2328 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aNames[i].pSum4)),
2329 OUString(aPrefix + m_pDoc->GetString(aPos)));
2330 }
2331
2332 const char* pColumn2Formula = "=SUM(table[[#Data];[Column2]])";
2333 {
2334 // Populate "table" database range with empty header and data in newly
2335 // inserted column, B1:B4 plus a table formula in B6. The empty header
2336 // should result in the internal table column name "Column2" that is
2337 // used in the formula.
2338 std::vector<std::vector<const char*>> aData
2339 = { { "" }, { "64" }, { "128" }, { "256" }, { "" }, { pColumn2Formula } };
2340 ScAddress aPos(1, 0, 0);
2341 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
2342 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
2343 }
2344
2345 // Verify the formula result in B6 (64+128+256=448).
2346 {
2347 OUString aFormula(OUString::createFromAscii(pColumn2Formula));
2348 ScAddress aPos(1, 5, 0);
2349 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2350 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + "448"),
2351 OUString(aPrefix + m_pDoc->GetString(aPos)));
2352 }
2353
2354 // Set header in column B. Use ScDocFunc to have table column names refreshed.
2355 rDocFunc.SetStringCell(ScAddress(1, 0, 0), "NewHeader", true);
2356 // Verify that formula adapted using the updated table column names.
2357 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM(table[[#Data];[NewHeader]])"),
2358 m_pDoc->GetFormula(1, 5, 0));
2359
2360 // Set header in column A to identical string. Internal table column name
2361 // for B should get a "2" appended.
2362 rDocFunc.SetStringCell(ScAddress(0, 0, 0), "NewHeader", true);
2363 // Verify that formula adapted using the updated table column names.
2364 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM(table[[#Data];[NewHeader2]])"),
2365 m_pDoc->GetFormula(1, 5, 0));
2366
2367 // Set header in column B to empty string, effectively clearing the cell.
2368 rDocFunc.SetStringCell(ScAddress(1, 0, 0), "", true);
2369 // Verify that formula is still using the previous table column name.
2370 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM(table[[#Data];[NewHeader2]])"),
2371 m_pDoc->GetFormula(1, 5, 0));
2372
2373 // === header-less ===
2374
2375 {
2376 ScDBCollection* pDBs = m_pDoc->GetDBCollection();
2377 CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
2378
2379 // Insert "headerless" database range definition for E10:F12, without headers.
2380 std::unique_ptr<ScDBData> pData(new ScDBData("hltable", 0, 4, 9, 5, 11, true, false));
2381 bool bInserted = pDBs->getNamedDBs().insert(std::move(pData));
2382 CPPUNIT_ASSERT_MESSAGE("Failed to insert \"hltable\" database range.", bInserted);
2383 }
2384
2385 {
2386 // Populate "hltable" database range with data in E10:F12
2387 std::vector<std::vector<const char*>> aData
2388 = { { "1", "2" }, { "4", "8" }, { "16", "32" } };
2389 ScAddress aPos(4, 9, 0);
2390 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
2391 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
2392 }
2393
2394 // Named expressions that use header-less Table structured references.
2395 static const struct
2396 {
2397 const char* pName;
2398 const char* pExpr;
2399 const char*
2400 pCounta; // expected result when used in row 10 (first data row) as argument to COUNTA()
2401 const char*
2402 pSum3; // expected result when used in row 11 (second data row) as argument to SUM().
2403 const char*
2404 pSum4; // expected result when used in row 12 (third data row) as argument to SUM().
2405 const char*
2406 pSumX; // expected result when used in row 13 (non-intersecting) as argument to SUM().
2407 } aHlNames[]
2408 = { { "hl_all", "hltable[[#All]]", "6", "63", "63", "63" },
2409 { "hl_data_implicit", "hltable[]", "6", "63", "63", "63" },
2410 { "hl_data", "hltable[[#Data]]", "6", "63", "63", "63" },
2411 { "hl_headers", "hltable[[#Headers]]", "1", "#REF!", "#REF!", "#REF!" },
2412 { "hl_column1", "hltable[[Column1]]", "3", "21", "21", "21" },
2413 { "hl_column2", "hltable[[Column2]]", "3", "42", "42", "42" },
2414 { "hl_data_column1", "hltable[[#Data];[Column1]]", "3", "21", "21", "21" },
2415 { "hl_data_column2", "hltable[[#Data];[Column2]]", "3", "42", "42", "42" },
2416 { "hl_this_row", "hltable[[#This Row]]", "2", "12", "48", "#VALUE!" },
2417 { "hl_this_row_column1", "hltable[[#This Row];[Column1]]", "1", "4", "16", "#VALUE!" },
2418 { "hl_this_row_column2", "hltable[[#This Row];[Column2]]", "1", "8", "32", "#VALUE!" },
2419 { "hl_this_row_range_column_1_to_2", "hltable[[#This Row];[Column1]:[Column2]]", "2",
2420 "12", "48", "#VALUE!" } };
2421
2422 {
2423 // Insert named expressions.
2424 ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
2425 CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
2426
2427 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2428 {
2429 // Choose base position that does not intersect with the database
2430 // range definition to test later use of [#This Row] results in
2431 // proper rows.
2432 ScRangeData* pName
2433 = new ScRangeData(*m_pDoc, OUString::createFromAscii(aHlNames[i].pName),
2434 OUString::createFromAscii(aHlNames[i].pExpr), ScAddress(6, 12, 0),
2435 ScRangeData::Type::Name, formula::FormulaGrammar::GRAM_NATIVE);
2436 bool bInserted = pGlobalNames->insert(pName);
2437 CPPUNIT_ASSERT_MESSAGE(OString(OString::Concat("Failed to insert named expression ")
2438 + aHlNames[i].pName + ".")
2439 .getStr(),
2440 bInserted);
2441 }
2442 }
2443
2444 // Use the named expressions in COUNTA() formulas, on row 10 that intersects.
2445 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2446 {
2447 OUString aFormula("=COUNTA(" + OUString::createFromAscii(aHlNames[i].pName) + ")");
2448 ScAddress aPos(7 + i, 9, 0);
2449 m_pDoc->SetString(aPos, aFormula);
2450 // For easier "debugability" have position and formula in assertion.
2451 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2452 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aHlNames[i].pCounta)),
2453 OUString(aPrefix + m_pDoc->GetString(aPos)));
2454 }
2455
2456 // Use the named expressions in SUM() formulas, on row 11 that intersects.
2457 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2458 {
2459 OUString aFormula("=SUM(" + OUString::createFromAscii(aHlNames[i].pName) + ")");
2460 ScAddress aPos(7 + i, 10, 0);
2461 m_pDoc->SetString(aPos, aFormula);
2462 // For easier "debugability" have position and formula in assertion.
2463 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2464 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aHlNames[i].pSum3)),
2465 OUString(aPrefix + m_pDoc->GetString(aPos)));
2466 }
2467
2468 // Use the named expressions in SUM() formulas, on row 12 that intersects.
2469 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2470 {
2471 OUString aFormula("=SUM(" + OUString::createFromAscii(aHlNames[i].pName) + ")");
2472 ScAddress aPos(7 + i, 11, 0);
2473 m_pDoc->SetString(aPos, aFormula);
2474 // For easier "debugability" have position and formula in assertion.
2475 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2476 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aHlNames[i].pSum4)),
2477 OUString(aPrefix + m_pDoc->GetString(aPos)));
2478 }
2479
2480 // Use the named expressions in SUM() formulas, on row 13 that does not intersect.
2481 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2482 {
2483 OUString aFormula("=SUM(" + OUString::createFromAscii(aHlNames[i].pName) + ")");
2484 ScAddress aPos(7 + i, 12, 0);
2485 m_pDoc->SetString(aPos, aFormula);
2486 // For easier "debugability" have position and formula in assertion.
2487 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2488 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aHlNames[i].pSumX)),
2489 OUString(aPrefix + m_pDoc->GetString(aPos)));
2490 }
2491
2492 // Insert a column at column F to extend database range from column E,F to
2493 // E,F,G. Use ScDocFunc so RefreshDirtyTableColumnNames() is called.
2494 rDocFunc.InsertCells(ScRange(5, 0, 0, 5, m_pDoc->MaxRow(), 0), &aMark, INS_INSCOLS_BEFORE,
2495 false, true);
2496
2497 // Re-verify the named expression in SUM() formula, on row 12 that
2498 // intersects, now starting at column I, still works.
2499 m_pDoc->CalcAll();
2500 for (size_t i = 0; i < SAL_N_ELEMENTS(aHlNames); ++i)
2501 {
2502 OUString aFormula("=SUM(" + OUString::createFromAscii(aHlNames[i].pName) + ")");
2503 ScAddress aPos(8 + i, 11, 0);
2504 // For easier "debugability" have position and formula in assertion.
2505 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2506 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + OUString::createFromAscii(aHlNames[i].pSum4)),
2507 OUString(aPrefix + m_pDoc->GetString(aPos)));
2508 }
2509
2510 const char* pColumn3Formula = "=SUM(hltable[[#Data];[Column3]])";
2511 {
2512 // Populate "hltable" database range with data in newly inserted
2513 // column, F10:F12 plus a table formula in F14. The new header should
2514 // result in the internal table column name "Column3" that is used in
2515 // the formula.
2516 std::vector<std::vector<const char*>> aData
2517 = { { "64" }, { "128" }, { "256" }, { "" }, { pColumn3Formula } };
2518 ScAddress aPos(5, 9, 0);
2519 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
2520 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
2521 }
2522
2523 // Verify the formula result in F14 (64+128+256=448).
2524 {
2525 OUString aFormula(OUString::createFromAscii(pColumn3Formula));
2526 ScAddress aPos(5, 13, 0);
2527 OUString aPrefix(aPos.Format(ScRefFlags::VALID) + " " + aFormula + " : ");
2528 CPPUNIT_ASSERT_EQUAL(OUString(aPrefix + "448"),
2529 OUString(aPrefix + m_pDoc->GetString(aPos)));
2530 }
2531
2532 m_pDoc->DeleteTab(0);
2533 }
2534
2535 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncFTEST)
2536 {
2537 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2538
2539 m_pDoc->InsertTab(0, "FTest");
2540
2541 ScAddress aPos(6, 0, 0);
2542 m_pDoc->SetString(aPos, "=FTEST(A1:C3;D1:F3)");
2543 m_pDoc->SetValue(0, 0, 0, 9.0); // A1
2544 OUString aVal = m_pDoc->GetString(aPos);
2545 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2546 OUString("#VALUE!"), aVal);
2547 m_pDoc->SetValue(0, 1, 0, 8.0); // A2
2548 aVal = m_pDoc->GetString(aPos);
2549 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2550 OUString("#VALUE!"), aVal);
2551 m_pDoc->SetValue(3, 0, 0, 5.0); // D1
2552 aVal = m_pDoc->GetString(aPos);
2553 CPPUNIT_ASSERT_EQUAL_MESSAGE("FTEST should return #VALUE! for less than 2 values",
2554 OUString("#VALUE!"), aVal);
2555 m_pDoc->SetValue(3, 1, 0, 6.0); // D2
2556 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 1.0000,
2557 m_pDoc->GetValue(aPos), 10e-4);
2558 m_pDoc->SetValue(1, 0, 0, 6.0); // B1
2559 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.6222,
2560 m_pDoc->GetValue(aPos), 10e-4);
2561 m_pDoc->SetValue(1, 1, 0, 8.0); // B2
2562 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.7732,
2563 m_pDoc->GetValue(aPos), 10e-4);
2564 m_pDoc->SetValue(4, 0, 0, 7.0); // E1
2565 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.8194,
2566 m_pDoc->GetValue(aPos), 10e-4);
2567 m_pDoc->SetValue(4, 1, 0, 4.0); // E2
2568 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.9674,
2569 m_pDoc->GetValue(aPos), 10e-4);
2570 m_pDoc->SetValue(2, 0, 0, 3.0); // C1
2571 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.3402,
2572 m_pDoc->GetValue(aPos), 10e-4);
2573 m_pDoc->SetValue(5, 0, 0, 28.0); // F1
2574 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0161,
2575 m_pDoc->GetValue(aPos), 10e-4);
2576 m_pDoc->SetValue(2, 1, 0, 9.0); // C2
2577 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0063,
2578 m_pDoc->GetValue(aPos), 10e-4);
2579 m_pDoc->SetValue(5, 1, 0, 4.0); // F2
2580 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0081,
2581 m_pDoc->GetValue(aPos), 10e-4);
2582 m_pDoc->SetValue(0, 2, 0, 2.0); // A3
2583 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0122,
2584 m_pDoc->GetValue(aPos), 10e-4);
2585 m_pDoc->SetValue(3, 2, 0, 8.0); // D3
2586 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0178,
2587 m_pDoc->GetValue(aPos), 10e-4);
2588 m_pDoc->SetValue(1, 2, 0, 4.0); // B3
2589 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0093,
2590 m_pDoc->GetValue(aPos), 10e-4);
2591 m_pDoc->SetValue(4, 2, 0, 7.0); // E3
2592 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0132,
2593 m_pDoc->GetValue(aPos), 10e-4);
2594 m_pDoc->SetValue(5, 2, 0, 5.0); // F3
2595 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0168,
2596 m_pDoc->GetValue(aPos), 10e-4);
2597 m_pDoc->SetValue(2, 2, 0, 13.0); // C3
2598 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0422,
2599 m_pDoc->GetValue(aPos), 10e-4);
2600
2601 m_pDoc->SetString(0, 2, 0, "a"); // A3
2602 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0334,
2603 m_pDoc->GetValue(aPos), 10e-4);
2604 m_pDoc->SetString(2, 0, 0, "b"); // C1
2605 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0261,
2606 m_pDoc->GetValue(aPos), 10e-4);
2607 m_pDoc->SetString(5, 1, 0, "c"); // F2
2608 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0219,
2609 m_pDoc->GetValue(aPos), 10e-4);
2610 m_pDoc->SetString(4, 2, 0, "d"); // E3
2611 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0161,
2612 m_pDoc->GetValue(aPos), 10e-4);
2613 m_pDoc->SetString(3, 2, 0, "e"); // D3
2614 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.0110,
2615 m_pDoc->GetValue(aPos), 10e-4);
2616
2617 m_pDoc->DeleteTab(0);
2618 m_pDoc->InsertTab(0, "FTest2");
2619
2620 /* Summary of the following test
2621 A1:A5 = SQRT(C1*9/10)*{ 1.0, 1.0, 1.0, 1.0, 1.0 };
2622 A6:A10 = -SQRT(C1*9/10)*{ 1.0, 1.0, 1.0, 1.0, 1.0 };
2623 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 };
2624 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 };
2625 C1 = POWER(1.5, D1) ; This is going to be the sample variance of the vector A1:A10
2626 C2 = POWER(1.5, D2) ; This is going to be the sample variance of the vector B1:B20
2627 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 }
2628
2629 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.
2630
2631 The minimum variance ratio obtained in this way is 0.017342 and the maximum variance ratio is 57.665039
2632 */
2633
2634 const size_t nNumParams = 11;
2635 const double fParameter[nNumParams]
2636 = { -5.0, -4.0, -3.0, -2.0, -1.0, 0.0, 1.0, 2.0, 3.0, 4.0, 5.0 };
2637
2638 // Results of var_test() from Octave
2639 const double fResults[nNumParams][nNumParams] = {
2640 { 0.9451191535603041, 0.5429768686792684, 0.213130093422756, 0.06607644828558357,
2641 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703, 0.0001435746909905777,
2642 2.566562398786942e-05, 4.436218417280813e-06, 7.495090956766148e-07 },
2643 { 0.4360331979746912, 0.9451191535603054, 0.5429768686792684, 0.2131300934227565,
2644 0.06607644828558357, 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703,
2645 0.0001435746909905777, 2.566562398786942e-05, 4.436218417280813e-06 },
2646 { 0.1309752286653509, 0.4360331979746914, 0.9451191535603058, 0.5429768686792684,
2647 0.2131300934227565, 0.06607644828558357, 0.0169804365506927, 0.003790723514148109,
2648 0.0007645345628801703, 0.0001435746909905777, 2.566562398786942e-05 },
2649 { 0.02453502500565108, 0.1309752286653514, 0.4360331979746914, 0.9451191535603058,
2650 0.5429768686792689, 0.2131300934227565, 0.06607644828558357, 0.0169804365506927,
2651 0.003790723514148109, 0.0007645345628801703, 0.0001435746909905777 },
2652 { 0.002886791075972228, 0.02453502500565108, 0.1309752286653514, 0.4360331979746914,
2653 0.9451191535603041, 0.5429768686792689, 0.2131300934227565, 0.06607644828558357,
2654 0.0169804365506927, 0.003790723514148109, 0.0007645345628801703 },
2655 { 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108, 0.1309752286653509,
2656 0.4360331979746912, 0.9451191535603036, 0.5429768686792689, 0.2131300934227565,
2657 0.06607644828558357, 0.0169804365506927, 0.003790723514148109 },
2658 { 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108,
2659 0.1309752286653509, 0.4360331979746914, 0.9451191535603054, 0.5429768686792684,
2660 0.2131300934227565, 0.06607644828558357, 0.0169804365506927 },
2661 { 5.109390206481379e-07, 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228,
2662 0.02453502500565108, 0.1309752286653509, 0.4360331979746914, 0.9451191535603058,
2663 0.5429768686792684, 0.213130093422756, 0.06607644828558357 },
2664 { 1.739106880727093e-08, 5.109390206481379e-07, 1.224926820153627e-05,
2665 0.0002237196492846927, 0.002886791075972228, 0.02453502500565086, 0.1309752286653509,
2666 0.4360331979746914, 0.9451191535603041, 0.5429768686792684, 0.2131300934227565 },
2667 { 5.111255862999542e-10, 1.739106880727093e-08, 5.109390206481379e-07,
2668 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228, 0.02453502500565108,
2669 0.1309752286653516, 0.4360331979746914, 0.9451191535603058, 0.5429768686792684 },
2670 { 1.354649725726631e-11, 5.111255862999542e-10, 1.739106880727093e-08,
2671 5.109390206481379e-07, 1.224926820153627e-05, 0.0002237196492846927, 0.002886791075972228,
2672 0.02453502500565108, 0.1309752286653509, 0.4360331979746914, 0.9451191535603054 }
2673 };
2674
2675 m_pDoc->SetValue(3, 0, 0, fParameter[0]); // D1
2676 m_pDoc->SetValue(3, 1, 0, fParameter[0]); // D2
2677 aPos.Set(2, 0, 0); // C1
2678 m_pDoc->SetString(aPos, "=POWER(1.5;D1)"); // C1
2679 aPos.Set(2, 1, 0); // C2
2680 m_pDoc->SetString(aPos, "=POWER(1.5;D2)"); // C2
2681 for (SCROW nRow = 0; nRow < 5;
2682 ++nRow) // Set A1:A5 = SQRT(C1*9/10), and A6:A10 = -SQRT(C1*9/10)
2683 {
2684 aPos.Set(0, nRow, 0);
2685 m_pDoc->SetString(aPos, "=SQRT(C1*9/10)");
2686 aPos.Set(0, nRow + 5, 0);
2687 m_pDoc->SetString(aPos, "=-SQRT(C1*9/10)");
2688 }
2689
2690 for (SCROW nRow = 0; nRow < 10;
2691 ++nRow) // Set B1:B10 = SQRT(C2*19/20), and B11:B20 = -SQRT(C2*19/20)
2692 {
2693 aPos.Set(1, nRow, 0);
2694 m_pDoc->SetString(aPos, "=SQRT(C2*19/20)");
2695 aPos.Set(1, nRow + 10, 0);
2696 m_pDoc->SetString(aPos, "=-SQRT(C2*19/20)");
2697 }
2698
2699 aPos.Set(4, 0, 0); // E1
2700 m_pDoc->SetString(aPos, "=FTEST(A1:A10;B1:B20)");
2701 aPos.Set(4, 1, 0); // E2
2702 m_pDoc->SetString(aPos, "=FTEST(B1:B20;A1:A10)");
2703
2704 ScAddress aPosRev(4, 1, 0); // E2
2705 aPos.Set(4, 0, 0); // E1
2706
2707 for (size_t nFirstIdx = 0; nFirstIdx < nNumParams; ++nFirstIdx)
2708 {
2709 m_pDoc->SetValue(3, 0, 0, fParameter[nFirstIdx]); // Set D1
2710 for (size_t nSecondIdx = 0; nSecondIdx < nNumParams; ++nSecondIdx)
2711 {
2712 m_pDoc->SetValue(3, 1, 0, fParameter[nSecondIdx]); // Set D2
2713 double fExpected = fResults[nFirstIdx][nSecondIdx];
2714 // 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
2715 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", fExpected,
2716 m_pDoc->GetValue(aPos),
2717 std::min(10e-5, fExpected * 0.0001));
2718 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", fExpected,
2719 m_pDoc->GetValue(aPosRev),
2720 std::min(10e-5, fExpected * 0.0001));
2721 }
2722 }
2723 m_pDoc->DeleteTab(0);
2724 }
2725
2726 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncFTESTBug)
2727 {
2728 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2729
2730 m_pDoc->InsertTab(0, "FTest");
2731
2732 ScAddress aPos(9, 0, 0);
2733 m_pDoc->SetString(aPos, "=FTEST(H1:H3;I1:I3)");
2734
2735 m_pDoc->SetValue(7, 0, 0, 9.0); // H1
2736 m_pDoc->SetValue(7, 1, 0, 8.0); // H2
2737 m_pDoc->SetValue(7, 2, 0, 6.0); // H3
2738 m_pDoc->SetValue(8, 0, 0, 5.0); // I1
2739 m_pDoc->SetValue(8, 1, 0, 7.0); // I2
2740 // tdf#93329
2741 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of FTEST failed", 0.9046,
2742 m_pDoc->GetValue(aPos), 10e-4);
2743
2744 m_pDoc->DeleteTab(0);
2745 }
2746
2747 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncCHITEST)
2748 {
2749 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2750
2751 m_pDoc->InsertTab(0, "ChiTest");
2752
2753 ScAddress aPos(6, 0, 0);
2754 // 2x2 matrices test
2755 m_pDoc->SetString(aPos, "=CHITEST(A1:B2;D1:E2)");
2756 OUString aVal = m_pDoc->GetString(aPos);
2757 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with empty cells",
2758 OUString("Err:502"), aVal);
2759
2760 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
2761 m_pDoc->SetValue(0, 1, 0, 2.0); // A2
2762 m_pDoc->SetValue(1, 0, 0, 2.0); // B1
2763 m_pDoc->SetValue(1, 1, 0, 1.0); // B2
2764 aVal = m_pDoc->GetString(aPos);
2765 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrix with empty cells",
2766 OUString("Err:502"), aVal);
2767
2768 m_pDoc->SetValue(3, 0, 0, 2.0); // D1
2769 m_pDoc->SetValue(3, 1, 0, 3.0); // D2
2770 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.3613,
2771 m_pDoc->GetValue(aPos), 10e-4);
2772
2773 m_pDoc->SetValue(4, 1, 0, 1.0); // E2
2774 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.3613,
2775 m_pDoc->GetValue(aPos), 10e-4);
2776 m_pDoc->SetValue(4, 0, 0, 3.0); // E1
2777 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.2801,
2778 m_pDoc->GetValue(aPos), 10e-4);
2779 m_pDoc->SetValue(4, 0, 0, 0.0); // E1
2780 aVal = m_pDoc->GetString(aPos);
2781 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0 for expected values of 0",
2782 OUString("#DIV/0!"), aVal);
2783 m_pDoc->SetValue(4, 0, 0, 3.0); // E1
2784 m_pDoc->SetValue(1, 1, 0, 0.0); // B2
2785 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1410,
2786 m_pDoc->GetValue(aPos), 10e-4);
2787
2788 // 3x3 matrices test
2789 m_pDoc->SetString(aPos, "=CHITEST(A1:C3;D1:F3)");
2790 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.7051,
2791 m_pDoc->GetValue(aPos), 10e-4);
2792
2793 m_pDoc->SetValue(2, 0, 0, 3.0); // C1
2794 m_pDoc->SetValue(2, 1, 0, 2.0); // C2
2795 m_pDoc->SetValue(2, 2, 0, 3.0); // C3
2796 m_pDoc->SetValue(0, 2, 0, 4.0); // A3
2797 m_pDoc->SetValue(1, 2, 0, 2.0); // B3
2798 m_pDoc->SetValue(5, 0, 0, 1.0); // F1
2799 m_pDoc->SetValue(5, 1, 0, 2.0); // F2
2800 m_pDoc->SetValue(5, 2, 0, 3.0); // F3
2801 m_pDoc->SetValue(3, 2, 0, 3.0); // D3
2802 m_pDoc->SetValue(4, 2, 0, 1.0); // E3
2803 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1117,
2804 m_pDoc->GetValue(aPos), 10e-4);
2805
2806 // test with strings
2807 m_pDoc->SetString(4, 2, 0, "a"); // E3
2808 aVal = m_pDoc->GetString(aPos);
2809 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2810 OUString("Err:502"), aVal);
2811 m_pDoc->SetString(1, 2, 0, "a"); // B3
2812 aVal = m_pDoc->GetString(aPos);
2813 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2814 OUString("Err:502"), aVal);
2815 m_pDoc->SetValue(4, 2, 0, 1.0); // E3
2816 aVal = m_pDoc->GetString(aPos);
2817 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return Err:502 for matrices with strings",
2818 OUString("Err:502"), aVal);
2819 m_pDoc->SetValue(1, 2, 0, 2.0); // B3
2820 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1117,
2821 m_pDoc->GetValue(aPos), 10e-4);
2822
2823 m_pDoc->SetValue(4, 1, 0, 5.0); // E2
2824 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0215,
2825 m_pDoc->GetValue(aPos), 10e-4);
2826 m_pDoc->SetValue(1, 2, 0, 1.0); // B3
2827 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0328,
2828 m_pDoc->GetValue(aPos), 10e-4);
2829 m_pDoc->SetValue(5, 0, 0, 3.0); // F1
2830 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1648,
2831 m_pDoc->GetValue(aPos), 10e-4);
2832 m_pDoc->SetValue(0, 1, 0, 3.0); // A2
2833 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1870,
2834 m_pDoc->GetValue(aPos), 10e-4);
2835 m_pDoc->SetValue(3, 1, 0, 5.0); // D2
2836 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1377,
2837 m_pDoc->GetValue(aPos), 10e-4);
2838 m_pDoc->SetValue(3, 2, 0, 4.0); // D3
2839 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.1566,
2840 m_pDoc->GetValue(aPos), 10e-4);
2841
2842 m_pDoc->SetValue(0, 0, 0, 0.0); // A1
2843 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0868,
2844 m_pDoc->GetValue(aPos), 10e-4);
2845
2846 // no convergence error
2847 m_pDoc->SetValue(4, 0, 0, 1.0E308); // E1
2848 aVal = m_pDoc->GetString(aPos);
2849 CPPUNIT_ASSERT_EQUAL(OUString("Err:523"), aVal);
2850 m_pDoc->SetValue(4, 0, 0, 3.0); // E1
2851
2852 // zero in all cells
2853 m_pDoc->SetValue(0, 1, 0, 0.0); // A2
2854 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0150,
2855 m_pDoc->GetValue(aPos), 10e-4);
2856 m_pDoc->SetValue(0, 2, 0, 0.0); // A3
2857 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0026,
2858 m_pDoc->GetValue(aPos), 10e-4);
2859 m_pDoc->SetValue(1, 0, 0, 0.0); // B1
2860 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.00079,
2861 m_pDoc->GetValue(aPos), 10e-5);
2862 m_pDoc->SetValue(1, 2, 0, 0.0); // B3
2863 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0005,
2864 m_pDoc->GetValue(aPos), 10e-4);
2865 m_pDoc->SetValue(2, 0, 0, 0.0); // C1
2866 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of CHITEST failed", 0.0001,
2867 m_pDoc->GetValue(aPos), 10e-4);
2868 m_pDoc->SetValue(2, 1, 0, 0.0); // C2
2869 m_pDoc->SetValue(2, 2, 0, 0.0); // C3
2870 m_pDoc->SetValue(3, 0, 0, 0.0); // D1
2871 aVal = m_pDoc->GetString(aPos);
2872 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0! for matrices with empty",
2873 OUString("#DIV/0!"), aVal);
2874 m_pDoc->SetValue(3, 1, 0, 0.0); // D2
2875 m_pDoc->SetValue(3, 2, 0, 0.0); // D3
2876 m_pDoc->SetValue(4, 0, 0, 0.0); // E1
2877 m_pDoc->SetValue(4, 1, 0, 0.0); // E2
2878 m_pDoc->SetValue(4, 2, 0, 0.0); // E3
2879 m_pDoc->SetValue(5, 0, 0, 0.0); // F1
2880 m_pDoc->SetValue(5, 1, 0, 0.0); // F2
2881 m_pDoc->SetValue(5, 2, 0, 0.0); // F3
2882 aVal = m_pDoc->GetString(aPos);
2883 CPPUNIT_ASSERT_EQUAL_MESSAGE("CHITEST should return #DIV/0! for matrices with empty",
2884 OUString("#DIV/0!"), aVal);
2885
2886 m_pDoc->DeleteTab(0);
2887 }
2888
2889 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncTTEST)
2890 {
2891 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2892
2893 m_pDoc->InsertTab(0, "TTest");
2894
2895 ScAddress aPos(6, 0, 0);
2896 // type 1, mode/tails 1
2897 m_pDoc->SetString(aPos, "=TTEST(A1:C3;D1:F3;1;1)");
2898 OUString aVal = m_pDoc->GetString(aPos);
2899 CPPUNIT_ASSERT_EQUAL_MESSAGE("TTEST should return #VALUE! for empty matrices",
2900 OUString("#VALUE!"), aVal);
2901
2902 m_pDoc->SetValue(0, 0, 0, 8.0); // A1
2903 m_pDoc->SetValue(1, 0, 0, 2.0); // B1
2904 m_pDoc->SetValue(3, 0, 0, 3.0); // D1
2905 m_pDoc->SetValue(4, 0, 0, 1.0); // E1
2906 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.18717,
2907 m_pDoc->GetValue(aPos), 10e-5);
2908 m_pDoc->SetValue(2, 0, 0, 1.0); // C1
2909 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.18717,
2910 m_pDoc->GetValue(aPos), 10e-5);
2911 m_pDoc->SetValue(5, 0, 0, 6.0); // F1
2912 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.45958,
2913 m_pDoc->GetValue(aPos), 10e-5);
2914 m_pDoc->SetValue(0, 1, 0, -4.0); // A2
2915 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.45958,
2916 m_pDoc->GetValue(aPos), 10e-5);
2917 m_pDoc->SetValue(3, 1, 0, 1.0); // D2
2918 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.35524,
2919 m_pDoc->GetValue(aPos), 10e-5);
2920 m_pDoc->SetValue(1, 1, 0, 5.0); // B2
2921 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.35524,
2922 m_pDoc->GetValue(aPos), 10e-5);
2923 m_pDoc->SetValue(4, 1, 0, -2.0); // E2
2924 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.41043,
2925 m_pDoc->GetValue(aPos), 10e-5);
2926 m_pDoc->SetValue(2, 1, 0, -1.0); // C2
2927 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.41043,
2928 m_pDoc->GetValue(aPos), 10e-5);
2929 m_pDoc->SetValue(5, 1, 0, -3.0); // F2
2930 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34990,
2931 m_pDoc->GetValue(aPos), 10e-5);
2932 m_pDoc->SetValue(0, 2, 0, 10.0); // A3
2933 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34990,
2934 m_pDoc->GetValue(aPos), 10e-5);
2935 m_pDoc->SetValue(3, 2, 0, 10.0); // D3
2936 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34686,
2937 m_pDoc->GetValue(aPos), 10e-5);
2938 m_pDoc->SetValue(1, 2, 0, 3.0); // B3
2939 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.34686,
2940 m_pDoc->GetValue(aPos), 10e-5);
2941 m_pDoc->SetValue(4, 2, 0, 9.0); // E3
2942 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.47198,
2943 m_pDoc->GetValue(aPos), 10e-5);
2944 m_pDoc->SetValue(2, 2, 0, -5.0); // C3
2945 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.47198,
2946 m_pDoc->GetValue(aPos), 10e-5);
2947 m_pDoc->SetValue(5, 2, 0, 6.0); // F3
2948 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.25529,
2949 m_pDoc->GetValue(aPos), 10e-5);
2950
2951 m_pDoc->SetString(1, 1, 0, "a"); // B2
2952 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12016,
2953 m_pDoc->GetValue(aPos), 10e-5);
2954 m_pDoc->SetString(4, 1, 0, "b"); // E2
2955 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12016,
2956 m_pDoc->GetValue(aPos), 10e-5);
2957 m_pDoc->SetString(2, 2, 0, "c"); // C3
2958 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.25030,
2959 m_pDoc->GetValue(aPos), 10e-5);
2960 m_pDoc->SetString(5, 1, 0, "d"); // F2
2961 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.19637,
2962 m_pDoc->GetValue(aPos), 10e-5);
2963
2964 // type 1, mode/tails 2
2965 m_pDoc->SetString(aPos, "=TTEST(A1:C3;D1:F3;2;1)");
2966 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.39273,
2967 m_pDoc->GetValue(aPos), 10e-5);
2968 m_pDoc->SetValue(1, 1, 0, 4.0); // B2
2969 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.39273,
2970 m_pDoc->GetValue(aPos), 10e-5);
2971 m_pDoc->SetValue(4, 1, 0, 3.0); // E2
2972 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.43970,
2973 m_pDoc->GetValue(aPos), 10e-5);
2974 m_pDoc->SetValue(2, 2, 0, -2.0); // C3
2975 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.22217,
2976 m_pDoc->GetValue(aPos), 10e-5);
2977 m_pDoc->SetValue(5, 1, 0, -10.0); // F2
2978 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.64668,
2979 m_pDoc->GetValue(aPos), 10e-5);
2980 m_pDoc->SetValue(0, 1, 0, 3.0); // A2
2981 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.95266,
2982 m_pDoc->GetValue(aPos), 10e-5);
2983 m_pDoc->SetValue(3, 2, 0, -1.0); // D3
2984 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.62636,
2985 m_pDoc->GetValue(aPos), 10e-5);
2986
2987 // type 2, mode/tails 2
2988 m_pDoc->SetString(aPos, "=TTEST(A1:C3;D1:F3;2;2)");
2989 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.62549,
2990 m_pDoc->GetValue(aPos), 10e-5);
2991 m_pDoc->SetValue(5, 1, 0, -1.0); // F2
2992 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.94952,
2993 m_pDoc->GetValue(aPos), 10e-5);
2994 m_pDoc->SetValue(2, 2, 0, 5.0); // C3
2995 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.58876,
2996 m_pDoc->GetValue(aPos), 10e-5);
2997 m_pDoc->SetValue(2, 1, 0, 2.0); // C2
2998 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.43205,
2999 m_pDoc->GetValue(aPos), 10e-5);
3000 m_pDoc->SetValue(3, 2, 0, -4.0); // D3
3001 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.36165,
3002 m_pDoc->GetValue(aPos), 10e-5);
3003 m_pDoc->SetValue(0, 1, 0, 1.0); // A2
3004 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.44207,
3005 m_pDoc->GetValue(aPos), 10e-5);
3006
3007 // type 3, mode/tails 1
3008 m_pDoc->SetString(aPos, "=TTEST(A1:C3;D1:F3;1;3)");
3009 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.22132,
3010 m_pDoc->GetValue(aPos), 10e-5);
3011 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
3012 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.36977,
3013 m_pDoc->GetValue(aPos), 10e-5);
3014 m_pDoc->SetValue(0, 2, 0, -30.0); // A3
3015 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.16871,
3016 m_pDoc->GetValue(aPos), 10e-5);
3017 m_pDoc->SetValue(3, 1, 0, 5.0); // D2
3018 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.14396,
3019 m_pDoc->GetValue(aPos), 10e-5);
3020 m_pDoc->SetValue(5, 1, 0, 2.0); // F2
3021 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.12590,
3022 m_pDoc->GetValue(aPos), 10e-5);
3023 m_pDoc->SetValue(4, 2, 0, 2.0); // E3
3024 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.16424,
3025 m_pDoc->GetValue(aPos), 10e-5);
3026 m_pDoc->SetValue(5, 0, 0, -1.0); // F1
3027 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Calculation of TTEST failed", 0.21472,
3028 m_pDoc->GetValue(aPos), 10e-5);
3029
3030 m_pDoc->DeleteTab(0);
3031 }
3032
3033 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncSUMX2PY2)
3034 {
3035 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3036
3037 m_pDoc->InsertTab(0, "SumX2PY2 Test");
3038
3039 OUString aVal;
3040 ScAddress aPos(6, 0, 0);
3041 m_pDoc->SetString(aPos, "=SUMX2PY2(A1:C3;D1:F3)");
3042 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 0.0, m_pDoc->GetValue(aPos));
3043
3044 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
3045 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 0.0, m_pDoc->GetValue(aPos));
3046 m_pDoc->SetValue(3, 0, 0, 2.0); // D1
3047 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 5.0, m_pDoc->GetValue(aPos));
3048 m_pDoc->SetValue(1, 0, 0, 2.0); // B1
3049 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 5.0, m_pDoc->GetValue(aPos));
3050 m_pDoc->SetValue(4, 0, 0, 0.0); // E1
3051 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 9.0, m_pDoc->GetValue(aPos));
3052 m_pDoc->SetValue(2, 0, 0, 3.0); // C1
3053 m_pDoc->SetValue(5, 0, 0, 3.0); // F1
3054 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 27.0, m_pDoc->GetValue(aPos));
3055 m_pDoc->SetValue(0, 1, 0, 10.0); // A2
3056 m_pDoc->SetValue(3, 1, 0, -10.0); // D2
3057 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 227.0, m_pDoc->GetValue(aPos));
3058 m_pDoc->SetValue(1, 1, 0, -5.0); // B2
3059 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 227.0, m_pDoc->GetValue(aPos));
3060 m_pDoc->SetValue(4, 1, 0, -5.0); // E2
3061 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 277.0, m_pDoc->GetValue(aPos));
3062 m_pDoc->SetValue(2, 1, 0, 0.0); // C2
3063 m_pDoc->SetValue(5, 1, 0, 0.0); // F2
3064 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 277.0, m_pDoc->GetValue(aPos));
3065 m_pDoc->SetValue(0, 2, 0, -8.0); // A3
3066 m_pDoc->SetValue(3, 2, 0, 8.0); // D3
3067 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 405.0, m_pDoc->GetValue(aPos));
3068 m_pDoc->SetValue(1, 2, 0, 0.0); // B3
3069 m_pDoc->SetValue(4, 2, 0, 0.0); // E3
3070 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 405.0, m_pDoc->GetValue(aPos));
3071 m_pDoc->SetValue(2, 2, 0, 1.0); // C3
3072 m_pDoc->SetValue(5, 2, 0, 1.0); // F3
3073 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 407.0, m_pDoc->GetValue(aPos));
3074
3075 // add some strings
3076 m_pDoc->SetString(4, 1, 0, "a"); // E2
3077 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 357.0, m_pDoc->GetValue(aPos));
3078 m_pDoc->SetString(1, 1, 0, "a"); // B2
3079 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 357.0, m_pDoc->GetValue(aPos));
3080 m_pDoc->SetString(0, 0, 0, "a"); // A1
3081 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 352.0, m_pDoc->GetValue(aPos));
3082 m_pDoc->SetString(3, 0, 0, "a"); // D1
3083 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 352.0, m_pDoc->GetValue(aPos));
3084
3085 m_pDoc->SetString(aPos, "=SUMX2PY2({1;2;3};{2;3;4})");
3086 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2PY2 failed", 43.0, m_pDoc->GetValue(aPos));
3087 m_pDoc->SetString(aPos, "=SUMX2PY2({1;2;3};{2;3})");
3088 aVal = m_pDoc->GetString(aPos);
3089 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2PY2 should return #VALUE! for matrices with different sizes",
3090 OUString("#VALUE!"), aVal);
3091 m_pDoc->SetString(aPos, "=SUMX2PY2({1;2;3})");
3092 aVal = m_pDoc->GetString(aPos);
3093 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2PY2 needs two parameters", OUString("Err:511"), aVal);
3094
3095 m_pDoc->DeleteTab(0);
3096 }
3097
3098 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncSUMX2MY2)
3099 {
3100 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3101
3102 m_pDoc->InsertTab(0, "SumX2MY2 Test");
3103
3104 OUString aVal;
3105 ScAddress aPos(6, 0, 0);
3106 m_pDoc->SetString(aPos, "=SUMX2MY2(A1:C3;D1:F3)");
3107 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 0.0, m_pDoc->GetValue(aPos));
3108
3109 m_pDoc->SetValue(0, 0, 0, 10.0); // A1
3110 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 0.0, m_pDoc->GetValue(aPos));
3111 m_pDoc->SetValue(3, 0, 0, -9.0); // D1
3112 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 19.0, m_pDoc->GetValue(aPos));
3113 m_pDoc->SetValue(1, 0, 0, 2.0); // B1
3114 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 19.0, m_pDoc->GetValue(aPos));
3115 m_pDoc->SetValue(4, 0, 0, 1.0); // E1
3116 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3117 m_pDoc->SetValue(2, 0, 0, 3.0); // C1
3118 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3119 m_pDoc->SetValue(5, 0, 0, 3.0); // F1
3120 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3121 m_pDoc->SetValue(0, 1, 0, 10.0); // A2
3122 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3123 m_pDoc->SetValue(3, 1, 0, -10.0); // D2
3124 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3125 m_pDoc->SetValue(1, 1, 0, -5.0); // B2
3126 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3127 m_pDoc->SetValue(4, 1, 0, -5.0); // E2
3128 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3129 m_pDoc->SetValue(2, 1, 0, -3.0); // C2
3130 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3131 m_pDoc->SetValue(5, 1, 0, 3.0); // F2
3132 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3133 m_pDoc->SetValue(0, 2, 0, -8.0); // A3
3134 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 22.0, m_pDoc->GetValue(aPos));
3135 m_pDoc->SetValue(3, 2, 0, 3.0); // D3
3136 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 77.0, m_pDoc->GetValue(aPos));
3137 m_pDoc->SetValue(1, 2, 0, 2.0); // B3
3138 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 77.0, m_pDoc->GetValue(aPos));
3139 m_pDoc->SetValue(4, 2, 0, -6.0); // E3
3140 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc->GetValue(aPos));
3141 m_pDoc->SetValue(2, 2, 0, -4.0); // C3
3142 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc->GetValue(aPos));
3143 m_pDoc->SetValue(5, 2, 0, 6.0); // F3
3144 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 25.0, m_pDoc->GetValue(aPos));
3145
3146 // add some strings
3147 m_pDoc->SetString(5, 2, 0, "a"); // F3
3148 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 45.0, m_pDoc->GetValue(aPos));
3149 m_pDoc->SetString(0, 2, 0, "a"); // A3
3150 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -10.0, m_pDoc->GetValue(aPos));
3151 m_pDoc->SetString(1, 0, 0, "a"); // B1
3152 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -13.0, m_pDoc->GetValue(aPos));
3153 m_pDoc->SetString(3, 0, 0, "a"); // D1
3154 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", -32.0, m_pDoc->GetValue(aPos));
3155
3156 m_pDoc->SetString(aPos, "=SUMX2MY2({1;3;5};{0;4;4})");
3157 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 3.0, m_pDoc->GetValue(aPos));
3158 m_pDoc->SetString(aPos, "=SUMX2MY2({1;-3;-5};{0;-4;4})");
3159 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 3.0, m_pDoc->GetValue(aPos));
3160 m_pDoc->SetString(aPos, "=SUMX2MY2({9;5;1};{3;-3;3})");
3161 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMX2MY2 failed", 80.0, m_pDoc->GetValue(aPos));
3162 m_pDoc->SetString(aPos, "=SUMX2MY2({1;2;3};{2;3})");
3163 aVal = m_pDoc->GetString(aPos);
3164 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2MY2 should return #VALUE! for matrices with different sizes",
3165 OUString("#VALUE!"), aVal);
3166 m_pDoc->SetString(aPos, "=SUMX2MY2({1;2;3})");
3167 aVal = m_pDoc->GetString(aPos);
3168 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMX2MY2 needs two parameters", OUString("Err:511"), aVal);
3169
3170 m_pDoc->DeleteTab(0);
3171 }
3172
3173 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncGCD)
3174 {
3175 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3176
3177 m_pDoc->InsertTab(0, "GCDTest");
3178
3179 OUString aVal;
3180 ScAddress aPos(4, 0, 0);
3181
3182 m_pDoc->SetString(aPos, "=GCD(A1)");
3183 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 0.0, m_pDoc->GetValue(aPos));
3184 m_pDoc->SetValue(0, 0, 0, 10.0); // A1
3185 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc->GetValue(aPos));
3186 m_pDoc->SetValue(0, 0, 0, -2.0); // A1
3187 aVal = m_pDoc->GetString(aPos);
3188 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for values less than 0",
3189 OUString("Err:502"), aVal);
3190 m_pDoc->SetString(0, 0, 0, "a"); // A1
3191 aVal = m_pDoc->GetString(aPos);
3192 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return #VALUE! for a single string",
3193 OUString("#VALUE!"), aVal);
3194
3195 m_pDoc->SetString(aPos, "=GCD(A1:B2)");
3196 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 0.0, m_pDoc->GetValue(aPos));
3197 m_pDoc->SetValue(0, 1, 0, -12.0); // B1
3198 aVal = m_pDoc->GetString(aPos);
3199 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for a matrix with values less than 0",
3200 OUString("Err:502"), aVal);
3201 m_pDoc->SetValue(0, 0, 0, 15.0); // A1
3202 m_pDoc->SetValue(0, 1, 0, 0.0); // B1
3203 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 15.0, m_pDoc->GetValue(aPos));
3204 m_pDoc->SetValue(1, 0, 0, 5.0); // B1
3205 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3206 m_pDoc->SetValue(0, 1, 0, 10.0); // A2
3207 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3208 m_pDoc->SetValue(1, 0, 0, 30.0); // B1
3209 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3210 m_pDoc->SetValue(0, 0, 0, 20.0); // A1
3211 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc->GetValue(aPos));
3212 m_pDoc->SetValue(1, 1, 0, 120.0); // B2
3213 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 10.0, m_pDoc->GetValue(aPos));
3214 m_pDoc->SetValue(0, 1, 0, 80.0); // A2
3215 m_pDoc->SetValue(1, 0, 0, 40.0); // B1
3216 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 20.0, m_pDoc->GetValue(aPos));
3217 m_pDoc->SetValue(1, 0, 0, 45.0); // B1
3218 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3219
3220 // with floor
3221 m_pDoc->SetValue(1, 0, 0, 45.381); // B1
3222 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3223 m_pDoc->SetValue(1, 1, 0, 120.895); // B2
3224 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3225 m_pDoc->SetValue(0, 0, 0, 20.97); // A1
3226 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3227 m_pDoc->SetValue(0, 1, 0, 10.15); // A2
3228 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 5.0, m_pDoc->GetValue(aPos));
3229
3230 // inline array
3231 m_pDoc->SetString(aPos, "=GCD({3;6;9})");
3232 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 3.0, m_pDoc->GetValue(aPos));
3233 m_pDoc->SetString(aPos, "=GCD({150;0})");
3234 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 150.0, m_pDoc->GetValue(aPos));
3235 m_pDoc->SetString(aPos, "=GCD({-3;6;9})");
3236 aVal = m_pDoc->GetString(aPos);
3237 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with values less than 0",
3238 OUString("Err:502"), aVal);
3239 m_pDoc->SetString(aPos, "=GCD({\"a\";6;9})");
3240 aVal = m_pDoc->GetString(aPos);
3241 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with strings",
3242 OUString("Err:502"), aVal);
3243
3244 //many inline array
3245 m_pDoc->SetString(aPos, "=GCD({6;6;6};{3;6;9})");
3246 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 3.0, m_pDoc->GetValue(aPos));
3247 m_pDoc->SetString(aPos, "=GCD({300;300;300};{150;0})");
3248 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 150.0, m_pDoc->GetValue(aPos));
3249 m_pDoc->SetString(aPos, "=GCD({3;6;9};{3;-6;9})");
3250 aVal = m_pDoc->GetString(aPos);
3251 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with values less than 0",
3252 OUString("Err:502"), aVal);
3253 m_pDoc->SetString(aPos, "=GCD({3;6;9};{\"a\";6;9})");
3254 aVal = m_pDoc->GetString(aPos);
3255 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return Err:502 for an array with strings",
3256 OUString("Err:502"), aVal);
3257
3258 // inline list of values
3259 m_pDoc->SetString(aPos, "=GCD(12;24;36;48;60)");
3260 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 12.0, m_pDoc->GetValue(aPos));
3261 m_pDoc->SetString(aPos, "=GCD(0;12;24;36;48;60)");
3262 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 12.0, m_pDoc->GetValue(aPos));
3263 m_pDoc->SetString(aPos, "=GCD(\"a\";1)");
3264 aVal = m_pDoc->GetString(aPos);
3265 CPPUNIT_ASSERT_EQUAL_MESSAGE("GCD should return #VALUE! for an array with strings",
3266 OUString("#VALUE!"), aVal);
3267
3268 m_pDoc->DeleteTab(0);
3269 }
3270
3271 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncLCM)
3272 {
3273 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3274
3275 m_pDoc->InsertTab(0, "LCMTest");
3276
3277 OUString aVal;
3278 ScAddress aPos(4, 0, 0);
3279
3280 m_pDoc->SetString(aPos, "=LCM(A1)");
3281 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3282 m_pDoc->SetValue(0, 0, 0, 10.0); // A1
3283 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 10.0, m_pDoc->GetValue(aPos));
3284 m_pDoc->SetValue(0, 0, 0, -2.0); // A1
3285 aVal = m_pDoc->GetString(aPos);
3286 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for values less than 0",
3287 OUString("Err:502"), aVal);
3288 m_pDoc->SetString(0, 0, 0, "a"); // A1
3289 aVal = m_pDoc->GetString(aPos);
3290 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return #VALUE! for a single string",
3291 OUString("#VALUE!"), aVal);
3292
3293 m_pDoc->SetString(aPos, "=LCM(A1:B2)");
3294 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of GCD for failed", 1.0, m_pDoc->GetValue(aPos));
3295 m_pDoc->SetValue(0, 1, 0, -12.0); // B1
3296 aVal = m_pDoc->GetString(aPos);
3297 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for a matrix with values less than 0",
3298 OUString("Err:502"), aVal);
3299 m_pDoc->SetValue(0, 0, 0, 15.0); // A1
3300 m_pDoc->SetValue(0, 1, 0, 0.0); // A2
3301 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3302 m_pDoc->SetValue(1, 0, 0, 5.0); // B1
3303 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3304 m_pDoc->SetValue(0, 1, 0, 10.0); // A2
3305 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 30.0, m_pDoc->GetValue(aPos));
3306 m_pDoc->SetValue(1, 0, 0, 30.0); // B1
3307 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 30.0, m_pDoc->GetValue(aPos));
3308 m_pDoc->SetValue(0, 0, 0, 20.0); // A1
3309 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 60.0, m_pDoc->GetValue(aPos));
3310 m_pDoc->SetValue(1, 1, 0, 125.0); // B2
3311 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1500.0, m_pDoc->GetValue(aPos));
3312 m_pDoc->SetValue(1, 0, 0, 99.0); // B1
3313 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 49500.0, m_pDoc->GetValue(aPos));
3314 m_pDoc->SetValue(0, 1, 0, 37.0); // A2
3315 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1831500.0,
3316 m_pDoc->GetValue(aPos));
3317
3318 // with floor
3319 m_pDoc->SetValue(1, 0, 0, 99.89); // B1
3320 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 1831500.0,
3321 m_pDoc->GetValue(aPos));
3322 m_pDoc->SetValue(1, 1, 0, 11.32); // B2
3323 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 73260.0, m_pDoc->GetValue(aPos));
3324 m_pDoc->SetValue(0, 0, 0, 22.58); // A1
3325 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 7326.0, m_pDoc->GetValue(aPos));
3326 m_pDoc->SetValue(0, 1, 0, 3.99); // A2
3327 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 198.0, m_pDoc->GetValue(aPos));
3328
3329 // inline array
3330 m_pDoc->SetString(aPos, "=LCM({3;6;9})");
3331 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 18.0, m_pDoc->GetValue(aPos));
3332 m_pDoc->SetString(aPos, "=LCM({150;0})");
3333 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3334 m_pDoc->SetString(aPos, "=LCM({-3;6;9})");
3335 aVal = m_pDoc->GetString(aPos);
3336 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with values less than 0",
3337 OUString("Err:502"), aVal);
3338 m_pDoc->SetString(aPos, "=LCM({\"a\";6;9})");
3339 aVal = m_pDoc->GetString(aPos);
3340 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with strings",
3341 OUString("Err:502"), aVal);
3342
3343 //many inline array
3344 m_pDoc->SetString(aPos, "=LCM({6;6;6};{3;6;9})");
3345 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 18.0, m_pDoc->GetValue(aPos));
3346 m_pDoc->SetString(aPos, "=LCM({300;300;300};{150;0})");
3347 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3348 m_pDoc->SetString(aPos, "=LCM({3;6;9};{3;-6;9})");
3349 aVal = m_pDoc->GetString(aPos);
3350 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with values less than 0",
3351 OUString("Err:502"), aVal);
3352 m_pDoc->SetString(aPos, "=LCM({3;6;9};{\"a\";6;9})");
3353 aVal = m_pDoc->GetString(aPos);
3354 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return Err:502 for an array with strings",
3355 OUString("Err:502"), aVal);
3356
3357 m_pDoc->SetString(aPos, "=LCM(12;24;36;48;60)");
3358 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 720.0, m_pDoc->GetValue(aPos));
3359 m_pDoc->SetString(aPos, "=LCM(0;12;24;36;48;60)");
3360 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of LCM for failed", 0.0, m_pDoc->GetValue(aPos));
3361 m_pDoc->SetString(aPos, "=LCM(\"a\";1)");
3362 aVal = m_pDoc->GetString(aPos);
3363 CPPUNIT_ASSERT_EQUAL_MESSAGE("LCM should return #VALUE! for an array with strings",
3364 OUString("#VALUE!"), aVal);
3365
3366 m_pDoc->DeleteTab(0);
3367 }
3368
3369 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncSUMSQ)
3370 {
3371 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3372
3373 m_pDoc->InsertTab(0, "SUMSQTest");
3374
3375 ScAddress aPos(4, 0, 0);
3376
3377 m_pDoc->SetString(aPos, "=SUMSQ(A1)");
3378 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 0.0, m_pDoc->GetValue(aPos));
3379 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
3380 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc->GetValue(aPos));
3381 m_pDoc->SetValue(0, 0, 0, -1.0); // A1
3382 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc->GetValue(aPos));
3383 m_pDoc->SetValue(0, 1, 0, -2.0); // A2
3384 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 1.0, m_pDoc->GetValue(aPos));
3385
3386 m_pDoc->SetString(aPos, "=SUMSQ(A1:A3)");
3387 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 5.0, m_pDoc->GetValue(aPos));
3388 m_pDoc->SetValue(1, 0, 0, 3.0); // B1
3389 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 5.0, m_pDoc->GetValue(aPos));
3390 m_pDoc->SetString(aPos, "=SUMSQ(A1:C3)");
3391 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 14.0, m_pDoc->GetValue(aPos));
3392 m_pDoc->SetValue(1, 1, 0, -4.0); // B2
3393 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 30.0, m_pDoc->GetValue(aPos));
3394 m_pDoc->SetString(1, 2, 0, "a"); // B3
3395 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 30.0,
3396 m_pDoc->GetValue(aPos));
3397 m_pDoc->SetValue(1, 2, 0, 0.0); // B3
3398 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 30.0,
3399 m_pDoc->GetValue(aPos));
3400 m_pDoc->SetValue(0, 2, 0, 6.0); // A3
3401 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 66.0,
3402 m_pDoc->GetValue(aPos));
3403 m_pDoc->SetValue(2, 0, 0, -5.0); // C1
3404 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 91.0,
3405 m_pDoc->GetValue(aPos));
3406 m_pDoc->SetValue(2, 1, 0, 3.0); // C2
3407 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 100.0,
3408 m_pDoc->GetValue(aPos));
3409 m_pDoc->SetValue(2, 2, 0, 2.0); // C3
3410 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ with a string for failed", 104.0,
3411 m_pDoc->GetValue(aPos));
3412
3413 // inline array
3414 m_pDoc->SetString(aPos, "=SUMSQ({1;2;3})");
3415 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 14.0, m_pDoc->GetValue(aPos));
3416 m_pDoc->SetString(aPos, "=SUMSQ({3;6;9})");
3417 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 126.0, m_pDoc->GetValue(aPos));
3418 m_pDoc->SetString(aPos, "=SUMSQ({15;0})");
3419 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 225.0, m_pDoc->GetValue(aPos));
3420 m_pDoc->SetString(aPos, "=SUMSQ({-3;3;1})");
3421 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 19.0, m_pDoc->GetValue(aPos));
3422 m_pDoc->SetString(aPos, "=SUMSQ({\"a\";-4;-5})");
3423 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 41.0, m_pDoc->GetValue(aPos));
3424
3425 m_pDoc->SetString(aPos, "=SUMSQ({2;3};{4;5})");
3426 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 54.0, m_pDoc->GetValue(aPos));
3427 m_pDoc->SetString(aPos, "=SUMSQ({-3;3;1};{-1})");
3428 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 20.0, m_pDoc->GetValue(aPos));
3429 m_pDoc->SetString(aPos, "=SUMSQ({-4};{1;4;2};{-5;7};{9})");
3430 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 192.0, m_pDoc->GetValue(aPos));
3431 m_pDoc->SetString(aPos, "=SUMSQ({-2;2};{1};{-1};{0;0;0;4})");
3432 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 26.0, m_pDoc->GetValue(aPos));
3433
3434 m_pDoc->SetString(aPos, "=SUMSQ(4;1;-3)");
3435 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 26.0, m_pDoc->GetValue(aPos));
3436 m_pDoc->SetString(aPos, "=SUMSQ(0;5;13;-7;-4)");
3437 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 259.0, m_pDoc->GetValue(aPos));
3438 m_pDoc->SetString(aPos, "=SUMSQ(0;12;24;36;48;60)");
3439 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 7920.0, m_pDoc->GetValue(aPos));
3440 m_pDoc->SetString(aPos, "=SUMSQ(0;-12;-24;36;-48;60)");
3441 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUMSQ for failed", 7920.0, m_pDoc->GetValue(aPos));
3442 m_pDoc->SetString(aPos, "=SUMSQ(\"a\";1;\"d\";-4;2)");
3443 OUString aVal = m_pDoc->GetString(aPos);
3444 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMSQ should return #VALUE! for an array with strings",
3445 OUString("#VALUE!"), aVal);
3446
3447 m_pDoc->DeleteTab(0);
3448 }
3449
3450 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncMDETERM)
3451 {
3452 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3453
3454 m_pDoc->InsertTab(0, "MDETERM_test");
3455 ScAddress aPos(8, 0, 0);
3456 static constexpr std::u16string_view aColCodes(u"ABCDEFGH");
3457 OUStringBuffer aFormulaBuffer("=MDETERM(A1:B2)");
3458 for (SCSIZE nSize = 3; nSize <= 8; nSize++)
3459 {
3460 double fVal = 1.0;
3461 // Generate a singular integer matrix
3462 for (SCROW nRow = 0; nRow < static_cast<SCROW>(nSize); nRow++)
3463 {
3464 for (SCCOL nCol = 0; nCol < static_cast<SCCOL>(nSize); nCol++)
3465 {
3466 m_pDoc->SetValue(nCol, nRow, 0, fVal);
3467 fVal += 1.0;
3468 }
3469 }
3470 aFormulaBuffer[12] = aColCodes[nSize - 1];
3471 aFormulaBuffer[13] = static_cast<sal_Unicode>('0' + nSize);
3472 m_pDoc->SetString(aPos, aFormulaBuffer.toString());
3473
3474 #if SAL_TYPES_SIZEOFPOINTER == 4
3475 // On crappy 32-bit targets, presumably without extended precision on
3476 // interim results or optimization not catching it, this test fails
3477 // when comparing to 0.0, so have a narrow error margin. See also
3478 // commit message of 8140309d636d4a870875f2dd75ed3dfff2c0fbaf
3479 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3480 "Calculation of MDETERM incorrect for singular integer matrix", 0.0,
3481 m_pDoc->GetValue(aPos), 1e-12);
3482 #else
3483 // Even on one (and only one) x86_64 target the result was
3484 // 6.34413156928661e-17 instead of 0.0 (tdf#99730) so lower the bar to
3485 // 10e-14.
3486 // Then again on aarch64, ppc64* and s390x it also fails.
3487 // Sigh... why do we even test this? The original complaint in tdf#32834
3488 // was about -9.51712667007776E-016
3489 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3490 "Calculation of MDETERM incorrect for singular integer matrix", 0.0,
3491 m_pDoc->GetValue(aPos), 1e-14);
3492 #endif
3493 }
3494
3495 int const aVals[] = { 23, 31, 13, 12, 34, 64, 34, 31, 98, 32, 33, 63, 45, 54, 65, 76 };
3496 int nIdx = 0;
3497 for (SCROW nRow = 0; nRow < 4; nRow++)
3498 for (SCCOL nCol = 0; nCol < 4; nCol++)
3499 m_pDoc->SetValue(nCol, nRow, 0, static_cast<double>(aVals[nIdx++]));
3500 m_pDoc->SetString(aPos, "=MDETERM(A1:D4)");
3501 // Following test is conservative in the sense that on Linux x86_64 the error is less that 1.0E-9
3502 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
3503 "Calculation of MDETERM incorrect for non-singular integer matrix", -180655.0,
3504 m_pDoc->GetValue(aPos), 1.0E-6);
3505 m_pDoc->DeleteTab(0);
3506 }
3507
3508 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaErrorPropagation)
3509 {
3510 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3511
3512 m_pDoc->InsertTab(0, "Sheet1");
3513
3514 ScMarkData aMark(m_pDoc->GetSheetLimits());
3515 aMark.SelectOneTable(0);
3516 ScAddress aPos, aPos2;
3517 constexpr OUString aTRUE(u"TRUE"_ustr);
3518 constexpr OUString aFALSE(u"FALSE"_ustr);
3519
3520 aPos.Set(0, 0, 0); // A1
3521 m_pDoc->SetValue(aPos, 1.0);
3522 aPos.IncCol(); // B1
3523 m_pDoc->SetValue(aPos, 2.0);
3524 aPos.IncCol();
3525
3526 aPos.IncRow(); // C2
3527 m_pDoc->SetString(aPos, "=ISERROR(A1:B1+3)");
3528 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3529 m_pDoc->GetString(aPos));
3530
3531 aPos.IncRow(); // C3
3532 m_pDoc->SetString(aPos, "=ISERROR(A1:B1+{3})");
3533 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3534 m_pDoc->GetString(aPos));
3535 aPos.IncRow(); // C4
3536 aPos2 = aPos;
3537 aPos2.IncCol(); // D4
3538 m_pDoc->InsertMatrixFormula(aPos.Col(), aPos.Row(), aPos2.Col(), aPos2.Row(), aMark,
3539 "=ISERROR(A1:B1+{3})");
3540 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3541 m_pDoc->GetString(aPos));
3542 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3543 m_pDoc->GetString(aPos2));
3544
3545 aPos.IncRow(); // C5
3546 m_pDoc->SetString(aPos, "=ISERROR({1;\"x\"}+{3;4})");
3547 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3548 m_pDoc->GetString(aPos));
3549 aPos.IncRow(); // C6
3550 aPos2 = aPos;
3551 aPos2.IncCol(); // D6
3552 m_pDoc->InsertMatrixFormula(aPos.Col(), aPos.Row(), aPos2.Col(), aPos2.Row(), aMark,
3553 "=ISERROR({1;\"x\"}+{3;4})");
3554 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3555 m_pDoc->GetString(aPos));
3556 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3557 m_pDoc->GetString(aPos2));
3558
3559 aPos.IncRow(); // C7
3560 m_pDoc->SetString(aPos, "=ISERROR({\"x\";2}+{3;4})");
3561 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3562 m_pDoc->GetString(aPos));
3563 aPos.IncRow(); // C8
3564 aPos2 = aPos;
3565 aPos2.IncCol(); // D8
3566 m_pDoc->InsertMatrixFormula(aPos.Col(), aPos.Row(), aPos2.Col(), aPos2.Row(), aMark,
3567 "=ISERROR({\"x\";2}+{3;4})");
3568 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3569 m_pDoc->GetString(aPos));
3570 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3571 m_pDoc->GetString(aPos2));
3572
3573 aPos.IncRow(); // C9
3574 m_pDoc->SetString(aPos, "=ISERROR(({1;\"x\"}+{3;4})-{5;6})");
3575 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3576 m_pDoc->GetString(aPos));
3577 aPos.IncRow(); // C10
3578 aPos2 = aPos;
3579 aPos2.IncCol(); // D10
3580 m_pDoc->InsertMatrixFormula(aPos.Col(), aPos.Row(), aPos2.Col(), aPos2.Row(), aMark,
3581 "=ISERROR(({1;\"x\"}+{3;4})-{5;6})");
3582 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3583 m_pDoc->GetString(aPos));
3584 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3585 m_pDoc->GetString(aPos2));
3586
3587 aPos.IncRow(); // C11
3588 m_pDoc->SetString(aPos, "=ISERROR(({\"x\";2}+{3;4})-{5;6})");
3589 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3590 m_pDoc->GetString(aPos));
3591 aPos.IncRow(); // C12
3592 aPos2 = aPos;
3593 aPos2.IncCol(); // D12
3594 m_pDoc->InsertMatrixFormula(aPos.Col(), aPos.Row(), aPos2.Col(), aPos2.Row(), aMark,
3595 "=ISERROR(({\"x\";2}+{3;4})-{5;6})");
3596 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos.Format(ScRefFlags::VALID).toUtf8().getStr(), aTRUE,
3597 m_pDoc->GetString(aPos));
3598 CPPUNIT_ASSERT_EQUAL_MESSAGE(aPos2.Format(ScRefFlags::VALID).toUtf8().getStr(), aFALSE,
3599 m_pDoc->GetString(aPos2));
3600
3601 m_pDoc->DeleteTab(0);
3602 }
3603
3604 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf97369)
3605 {
3606 const SCROW TOTAL_ROWS = 330;
3607 const SCROW ROW_RANGE = 10;
3608 const SCROW START1 = 9;
3609 const SCROW END1 = 159;
3610 const SCROW START2 = 169;
3611 const SCROW END2 = 319;
3612
3613 const double SHIFT1 = 200;
3614 const double SHIFT2 = 400;
3615
3616 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "tdf97369"));
3617
3618 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
3619
3620 // set up columns A, B, C
3621 for (SCROW i = 0; i < TOTAL_ROWS; ++i)
3622 {
3623 m_pDoc->SetValue(ScAddress(0, i, 0), i); // A
3624 m_pDoc->SetValue(ScAddress(1, i, 0), i + SHIFT1); // B
3625 m_pDoc->SetValue(ScAddress(2, i, 0), i + SHIFT2); // C
3626 }
3627
3628 const ColumnTest columnTest(m_pDoc, TOTAL_ROWS, START1, END1, START2, END2);
3629
3630 auto lExpectedinD = [=](SCROW n) { return 3.0 * (n - START1) + SHIFT1 + SHIFT2; };
3631 columnTest(3, "=SUM(A1:C1)", lExpectedinD);
3632
3633 auto lExpectedinE = [=](SCROW) { return SHIFT1 + SHIFT2; };
3634 columnTest(4, "=SUM(A$1:C$1)", lExpectedinE);
3635
3636 auto lExpectedinF = [](SCROW n) { return ((2 * n + 1 - ROW_RANGE) * ROW_RANGE) / 2.0; };
3637 columnTest(5, "=SUM(A1:A10)", lExpectedinF);
3638
3639 auto lExpectedinG = [](SCROW n) { return ((n + 1) * n) / 2.0; };
3640 columnTest(6, "=SUM(A$1:A10)", lExpectedinG);
3641
3642 auto lExpectedinH = [=](SCROW n) {
3643 return 3.0 * (((2 * n + 1 - ROW_RANGE) * ROW_RANGE) / 2) + ROW_RANGE * (SHIFT1 + SHIFT2);
3644 };
3645 columnTest(7, "=SUM(A1:C10)", lExpectedinH);
3646
3647 auto lExpectedinI = [=](SCROW) {
3648 return 3.0 * (((2 * START1 + 1 - ROW_RANGE) * ROW_RANGE) / 2)
3649 + ROW_RANGE * (SHIFT1 + SHIFT2);
3650 };
3651 columnTest(8, "=SUM(A$1:C$10)", lExpectedinI);
3652
3653 m_pDoc->DeleteTab(0);
3654 }
3655
3656 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf97587)
3657 {
3658 const SCROW TOTAL_ROWS = 150;
3659 const SCROW ROW_RANGE = 10;
3660
3661 CPPUNIT_ASSERT_MESSAGE("failed to insert sheet", m_pDoc->InsertTab(0, "tdf97587"));
3662
3663 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
3664
3665 std::set<SCROW> emptyCells = { 0, 100 };
3666 for (SCROW i = 0; i < ROW_RANGE; ++i)
3667 {
3668 emptyCells.insert(i + TOTAL_ROWS / 3);
3669 emptyCells.insert(i + TOTAL_ROWS);
3670 }
3671
3672 // set up columns A
3673 for (SCROW i = 0; i < TOTAL_ROWS; ++i)
3674 {
3675 if (emptyCells.find(i) != emptyCells.end())
3676 continue;
3677 m_pDoc->SetValue(ScAddress(0, i, 0), 1.0);
3678 }
3679
3680 ScDocument aClipDoc(SCDOCMODE_CLIP);
3681 ScMarkData aMark(m_pDoc->GetSheetLimits());
3682
3683 ScAddress aPos(1, 0, 0);
3684 m_pDoc->SetString(aPos, "=SUM(A1:A10)");
3685
3686 // Copy formula cell to clipboard.
3687 ScClipParam aClipParam(aPos, false);
3688 aMark.SetMarkArea(aPos);
3689 m_pDoc->CopyToClip(aClipParam, &aClipDoc, &aMark, false, false);
3690
3691 // Paste it to first range.
3692 ScRange aDestRange(1, 1, 0, 1, TOTAL_ROWS + ROW_RANGE, 0);
3693 aMark.SetMarkArea(aDestRange);
3694 m_pDoc->CopyFromClip(aDestRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
3695
3696 // Check the formula results in column B.
3697 for (SCROW i = 0; i < TOTAL_ROWS + 1; ++i)
3698 {
3699 int k = std::count_if(emptyCells.begin(), emptyCells.end(),
3700 [=](SCROW n) { return (i <= n && n < i + ROW_RANGE); });
3701 double fExpected = ROW_RANGE - k;
3702 ASSERT_DOUBLES_EQUAL(fExpected, m_pDoc->GetValue(ScAddress(1, i, 0)));
3703 }
3704 m_pDoc->DeleteTab(0);
3705 }
3706
3707 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf93415)
3708 {
3709 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Sheet1"));
3710
3711 ScCalcConfig aConfig;
3712 aConfig.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1);
3713 m_pDoc->SetCalcConfig(aConfig);
3714 m_pDoc->CalcAll();
3715
3716 ScAddress aPos(0, 0, 0);
3717 m_pDoc->SetString(aPos, "=ADDRESS(1;1;;;\"Sheet1\")");
3718
3719 // Without the fix in place, this would have failed with
3720 // - Expected: Sheet1!$A$1
3721 // - Actual : Sheet1.$A$1
3722 CPPUNIT_ASSERT_EQUAL(OUString("Sheet1!$A$1"), m_pDoc->GetString(aPos));
3723
3724 m_pDoc->DeleteTab(0);
3725 }
3726
3727 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf132519)
3728 {
3729 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Sheet1"));
3730
3731 ScCalcConfig aConfig;
3732 aConfig.SetStringRefSyntax(formula::FormulaGrammar::CONV_XL_R1C1);
3733 m_pDoc->SetCalcConfig(aConfig);
3734 m_pDoc->CalcAll();
3735
3736 m_pDoc->SetString(2, 0, 0, "X");
3737 m_pDoc->SetString(1, 0, 0, "=CELL(\"ADDRESS\"; C1)");
3738 m_pDoc->SetString(0, 0, 0, "=INDIRECT(B1)");
3739
3740 // Without the fix in place, this test would have failed with
3741 // - Expected: X
3742 // - Actual : #REF!
3743 CPPUNIT_ASSERT_EQUAL(OUString("X"), m_pDoc->GetString(0, 0, 0));
3744
3745 CPPUNIT_ASSERT_EQUAL(OUString("R1C3"), m_pDoc->GetString(1, 0, 0));
3746
3747 m_pDoc->DeleteTab(0);
3748 }
3749
3750 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf127334)
3751 {
3752 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Sheet1"));
3753
3754 m_pDoc->SetString(
3755 0, 0, 0,
3756 "= (((DATE(2019;9;17) + TIME(0;0;1)) - DATE(2019;9;17)) - TIME(0;0;1))/TIME(0;0;1)");
3757
3758 // Without the fix in place, this test would have failed with
3759 // - Expected: 0
3760 // - Actual : 2.32e-07
3761 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(0, 0, 0));
3762
3763 m_pDoc->DeleteTab(0);
3764 }
3765
3766 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf100818)
3767 {
3768 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Sheet1"));
3769
3770 //Insert local range name
3771 ScRangeData* pLocal = new ScRangeData(*m_pDoc, "local", "$Sheet1.$A$1");
3772 std::unique_ptr<ScRangeName> pLocalRangeName(new ScRangeName);
3773 pLocalRangeName->insert(pLocal);
3774 m_pDoc->SetRangeName(0, std::move(pLocalRangeName));
3775
3776 m_pDoc->SetValue(0, 0, 0, 1.0);
3777
3778 CPPUNIT_ASSERT(m_pDoc->InsertTab(1, "Sheet2"));
3779
3780 m_pDoc->SetString(0, 0, 1, "=INDIRECT(\"Sheet1.local\")");
3781
3782 // Without the fix in place, this test would have failed with
3783 // - Expected: 1
3784 // - Actual : #REF!
3785 CPPUNIT_ASSERT_EQUAL(OUString("1"), m_pDoc->GetString(0, 0, 1));
3786
3787 m_pDoc->DeleteTab(1);
3788 m_pDoc->SetRangeName(0, nullptr); // Delete the names.
3789 m_pDoc->DeleteTab(0);
3790 }
3791
3792 CPPUNIT_TEST_FIXTURE(TestFormula2, testMatConcat)
3793 {
3794 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Test"));
3795
3796 for (SCCOL nCol = 0; nCol < 10; ++nCol)
3797 {
3798 for (SCROW nRow = 0; nRow < 10; ++nRow)
3799 {
3800 m_pDoc->SetValue(ScAddress(nCol, nRow, 0), nCol * nRow);
3801 }
3802 }
3803
3804 ScMarkData aMark(m_pDoc->GetSheetLimits());
3805 aMark.SelectOneTable(0);
3806 m_pDoc->InsertMatrixFormula(0, 12, 9, 21, aMark, "=A1:J10&A1:J10");
3807
3808 for (SCCOL nCol = 0; nCol < 10; ++nCol)
3809 {
3810 for (SCROW nRow = 12; nRow < 22; ++nRow)
3811 {
3812 OUString aStr = m_pDoc->GetString(ScAddress(nCol, nRow, 0));
3813 CPPUNIT_ASSERT_EQUAL(OUString(OUString::number(nCol * (nRow - 12))
3814 + OUString::number(nCol * (nRow - 12))),
3815 aStr);
3816 }
3817 }
3818
3819 { // Data in A12:B16
3820 std::vector<std::vector<const char*>> aData = {
3821 { "q", "w" }, { "a", "" }, { "", "x" }, { "", "" }, { "e", "r" },
3822 };
3823
3824 ScAddress aPos(0, 11, 0);
3825 ScRange aRange = insertRangeData(m_pDoc, aPos, aData);
3826 CPPUNIT_ASSERT_EQUAL(aPos, aRange.aStart);
3827 }
3828 // Matrix formula in C17:C21
3829 m_pDoc->InsertMatrixFormula(2, 16, 2, 20, aMark, "=A12:A16&B12:B16");
3830 // Check proper concatenation including empty cells.
3831 OUString aStr;
3832 ScAddress aPos(2, 16, 0);
3833 aStr = m_pDoc->GetString(aPos);
3834 CPPUNIT_ASSERT_EQUAL(OUString("qw"), aStr);
3835 aPos.IncRow();
3836 aStr = m_pDoc->GetString(aPos);
3837 CPPUNIT_ASSERT_EQUAL(OUString("a"), aStr);
3838 aPos.IncRow();
3839 aStr = m_pDoc->GetString(aPos);
3840 CPPUNIT_ASSERT_EQUAL(OUString("x"), aStr);
3841 aPos.IncRow();
3842 aStr = m_pDoc->GetString(aPos);
3843 CPPUNIT_ASSERT_EQUAL(OUString(), aStr);
3844 aPos.IncRow();
3845 aStr = m_pDoc->GetString(aPos);
3846 CPPUNIT_ASSERT_EQUAL(OUString("er"), aStr);
3847
3848 m_pDoc->DeleteTab(0);
3849 }
3850
3851 CPPUNIT_TEST_FIXTURE(TestFormula2, testMatConcatReplication)
3852 {
3853 // if one of the matrices is a one column or row matrix
3854 // the matrix is replicated across the larger matrix
3855 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Test"));
3856
3857 for (SCCOL nCol = 0; nCol < 10; ++nCol)
3858 {
3859 for (SCROW nRow = 0; nRow < 10; ++nRow)
3860 {
3861 m_pDoc->SetValue(ScAddress(nCol, nRow, 0), nCol * nRow);
3862 }
3863 }
3864
3865 ScMarkData aMark(m_pDoc->GetSheetLimits());
3866 aMark.SelectOneTable(0);
3867 m_pDoc->InsertMatrixFormula(0, 12, 9, 21, aMark, "=A1:J10&A1:J1");
3868
3869 for (SCCOL nCol = 0; nCol < 10; ++nCol)
3870 {
3871 for (SCROW nRow = 12; nRow < 22; ++nRow)
3872 {
3873 OUString aStr = m_pDoc->GetString(ScAddress(nCol, nRow, 0));
3874 CPPUNIT_ASSERT_EQUAL(OUString(OUString::number(nCol * (nRow - 12)) + "0"), aStr);
3875 }
3876 }
3877
3878 m_pDoc->DeleteTab(0);
3879 }
3880
3881 CPPUNIT_TEST_FIXTURE(TestFormula2, testRefR1C1WholeCol)
3882 {
3883 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Test"));
3884
3885 ScAddress aPos(1, 1, 1);
3886 ScCompiler aComp(*m_pDoc, aPos, FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
3887 std::unique_ptr<ScTokenArray> pTokens(aComp.CompileString("=C[10]"));
3888 sc::TokenStringContext aCxt(*m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);
3889 OUString aFormula = pTokens->CreateString(aCxt, aPos);
3890
3891 CPPUNIT_ASSERT_EQUAL(OUString("L:L"), aFormula);
3892
3893 m_pDoc->DeleteTab(0);
3894 }
3895
3896 CPPUNIT_TEST_FIXTURE(TestFormula2, testRefR1C1WholeRow)
3897 {
3898 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Test"));
3899
3900 ScAddress aPos(1, 1, 1);
3901 ScCompiler aComp(*m_pDoc, aPos, FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
3902 std::unique_ptr<ScTokenArray> pTokens(aComp.CompileString("=R[3]"));
3903 sc::TokenStringContext aCxt(*m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);
3904 OUString aFormula = pTokens->CreateString(aCxt, aPos);
3905
3906 CPPUNIT_ASSERT_EQUAL(OUString("5:5"), aFormula);
3907
3908 m_pDoc->DeleteTab(0);
3909 }
3910
3911 CPPUNIT_TEST_FIXTURE(TestFormula2, testSingleCellCopyColumnLabel)
3912 {
3913 ScDocOptions aOptions = m_pDoc->GetDocOptions();
3914 aOptions.SetLookUpColRowNames(true);
3915 m_pDoc->SetDocOptions(aOptions);
3916 m_pDoc->InsertTab(0, "Test");
3917
3918 m_pDoc->SetString(0, 0, 0, "a");
3919 m_pDoc->SetValue(0, 1, 0, 1.0);
3920 m_pDoc->SetValue(0, 2, 0, 2.0);
3921 m_pDoc->SetValue(0, 3, 0, 3.0);
3922 m_pDoc->SetString(1, 1, 0, "='a'");
3923
3924 double nVal = m_pDoc->GetValue(1, 1, 0);
3925 ASSERT_DOUBLES_EQUAL(1.0, nVal);
3926
3927 ScDocument aClipDoc(SCDOCMODE_CLIP);
3928 copyToClip(m_pDoc, ScRange(1, 1, 0), &aClipDoc);
3929 pasteOneCellFromClip(m_pDoc, ScRange(1, 2, 0), &aClipDoc);
3930 nVal = m_pDoc->GetValue(1, 2, 0);
3931 ASSERT_DOUBLES_EQUAL(2.0, nVal);
3932
3933 m_pDoc->DeleteTab(0);
3934 }
3935
3936 // Significant whitespace operator intersection in Excel syntax, tdf#96426
3937 CPPUNIT_TEST_FIXTURE(TestFormula2, testIntersectionOpExcel)
3938 {
3939 CPPUNIT_ASSERT(m_pDoc->InsertTab(0, "Test"));
3940
3941 ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
3942 // Horizontal cell range covering C2.
3943 pGlobalNames->insert(new ScRangeData(*m_pDoc, "horz", "$B$2:$D$2"));
3944 // Vertical cell range covering C2.
3945 pGlobalNames->insert(new ScRangeData(*m_pDoc, "vert", "$C$1:$C$3"));
3946 // Data in C2.
3947 m_pDoc->SetValue(2, 1, 0, 1.0);
3948
3949 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1);
3950
3951 // Choose formula positions that don't intersect with those data ranges.
3952 ScAddress aPos(0, 3, 0);
3953 m_pDoc->SetString(aPos, "=B2:D2 C1:C3");
3954 CPPUNIT_ASSERT_EQUAL_MESSAGE("A4 intersecting references failed", 1.0, m_pDoc->GetValue(aPos));
3955 aPos.IncRow();
3956 m_pDoc->SetString(aPos, "=horz vert");
3957 CPPUNIT_ASSERT_EQUAL_MESSAGE("A5 intersecting named expressions failed", 1.0,
3958 m_pDoc->GetValue(aPos));
3959 aPos.IncRow();
3960 m_pDoc->SetString(aPos, "=(horz vert)*2");
3961 CPPUNIT_ASSERT_EQUAL_MESSAGE("A6 calculating with intersecting named expressions failed", 2.0,
3962 m_pDoc->GetValue(aPos));
3963 aPos.IncRow();
3964 m_pDoc->SetString(aPos, "=2*(horz vert)");
3965 CPPUNIT_ASSERT_EQUAL_MESSAGE("A7 calculating with intersecting named expressions failed", 2.0,
3966 m_pDoc->GetValue(aPos));
3967
3968 m_pDoc->DeleteTab(0);
3969 }
3970
3971 //Test Subtotal and Aggregate during hide rows #tdf93171
3972 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncRowsHidden)
3973 {
3974 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3975 m_pDoc->InsertTab(0, "Test");
3976 m_pDoc->SetValue(0, 0, 0, 1); //A1
3977 m_pDoc->SetValue(0, 1, 0, 2); //A2
3978 m_pDoc->SetValue(0, 2, 0, 4); //A3
3979 m_pDoc->SetValue(0, 3, 0, 8); //A4
3980 m_pDoc->SetValue(0, 4, 0, 16); //A5
3981 m_pDoc->SetValue(0, 5, 0, 32); //A6
3982
3983 ScAddress aPos(0, 6, 0);
3984 m_pDoc->SetString(aPos, "=SUBTOTAL(109; A1:A6)");
3985 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc->GetValue(aPos));
3986 //Hide row 1
3987 m_pDoc->SetRowHidden(0, 0, 0, true);
3988 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 62.0, m_pDoc->GetValue(aPos));
3989 m_pDoc->SetRowHidden(0, 0, 0, false);
3990 //Hide row 2 and 3
3991 m_pDoc->SetRowHidden(1, 2, 0, true);
3992 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 57.0, m_pDoc->GetValue(aPos));
3993 m_pDoc->SetRowHidden(1, 2, 0, false);
3994 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc->GetValue(aPos));
3995
3996 m_pDoc->SetString(aPos, "=AGGREGATE(9; 5; A1:A6)"); //9=SUM 5=Ignore only hidden rows
3997 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc->GetValue(aPos));
3998 //Hide row 1
3999 m_pDoc->SetRowHidden(0, 0, 0, true);
4000 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 62.0, m_pDoc->GetValue(aPos));
4001 m_pDoc->SetRowHidden(0, 0, 0, false);
4002 //Hide rows 3 to 5
4003 m_pDoc->SetRowHidden(2, 4, 0, true);
4004 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 35.0, m_pDoc->GetValue(aPos));
4005 m_pDoc->SetRowHidden(2, 4, 0, false);
4006 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc->GetValue(aPos));
4007
4008 m_pDoc->SetString(aPos, "=SUM(A1:A6)");
4009 m_pDoc->SetRowHidden(2, 4, 0, true);
4010 CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUM failed", 63.0, m_pDoc->GetValue(aPos));
4011
4012 m_pDoc->DeleteTab(0);
4013 }
4014
4015 // Test COUNTIFS, SUMIFS, AVERAGEIFS in array context.
4016 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncSUMIFS)
4017 {
4018 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4019 m_pDoc->InsertTab(0, "Test");
4020
4021 // Data in A1:B7, query in A9:A11
4022 std::vector<std::vector<const char*>> aData = {
4023 { "a", "1" }, { "b", "2" }, { "c", "4" }, { "d", "8" },
4024 { "a", "16" }, { "b", "32" }, { "c", "64" }, { "" }, // {} doesn't work with some compilers
4025 { "a" }, { "b" }, { "c" },
4026 };
4027
4028 insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData);
4029
4030 ScMarkData aMark(m_pDoc->GetSheetLimits());
4031 aMark.SelectOneTable(0);
4032 // Matrix formula in C8:C10 with SUMIFS
4033 m_pDoc->InsertMatrixFormula(2, 7, 2, 9, aMark, "=SUMIFS(B1:B7;A1:A7;A9:A11)");
4034 // Matrix formula in D8:D10 with COUNTIFS
4035 m_pDoc->InsertMatrixFormula(3, 7, 3, 9, aMark, "=COUNTIFS(A1:A7;A9:A11)");
4036 // Matrix formula in E8:E10 with AVERAGEIFS
4037 m_pDoc->InsertMatrixFormula(4, 7, 4, 9, aMark, "=AVERAGEIFS(B1:B7;A1:A7;A9:A11)");
4038
4039 {
4040 // Result B1+B5, B2+B6, B3+B7 and counts and averages.
4041 std::vector<std::vector<const char*>> aCheck
4042 = { { "17", "2", "8.5" }, { "34", "2", "17" }, { "68", "2", "34" } };
4043 bool bGood = checkOutput(m_pDoc, ScRange(2, 7, 0, 4, 9, 0), aCheck,
4044 "SUMIFS, COUNTIFS and AVERAGEIFS in array context");
4045 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS in array context failed", bGood);
4046 }
4047
4048 // Matrix formula in G8:G10 with SUMIFS and reference list arrays.
4049 m_pDoc->InsertMatrixFormula(6, 7, 6, 9, aMark,
4050 "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")");
4051 // Matrix formula in H8:H10 with COUNTIFS and reference list arrays.
4052 m_pDoc->InsertMatrixFormula(7, 7, 7, 9, aMark, "=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\")");
4053 // Matrix formula in I8:I10 with AVERAGEIFS and reference list arrays.
4054 m_pDoc->InsertMatrixFormula(
4055 8, 7, 8, 9, aMark, "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")");
4056
4057 {
4058 // Result sums, counts and averages.
4059 std::vector<std::vector<const char*>> aCheck
4060 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "24", "2", "12" } };
4061 bool bGood = checkOutput(m_pDoc, ScRange(6, 7, 0, 8, 9, 0), aCheck,
4062 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays");
4063 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays failed",
4064 bGood);
4065 }
4066
4067 // Matrix formula in K8:K10 with SUMIFS and reference list array condition
4068 // and "normal" data range.
4069 m_pDoc->InsertMatrixFormula(10, 7, 10, 9, aMark,
4070 "=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")");
4071 // Matrix formula in L8:L10 with AVERAGEIFS and reference list array
4072 // condition and "normal" data range.
4073 m_pDoc->InsertMatrixFormula(11, 7, 11, 9, aMark,
4074 "=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")");
4075
4076 {
4077 // Result sums and averages.
4078 std::vector<std::vector<const char*>> aCheck
4079 = { { "0", "#DIV/0!" }, { "2", "2" }, { "3", "1.5" } };
4080 bool bGood = checkOutput(
4081 m_pDoc, ScRange(10, 7, 0, 11, 9, 0), aCheck,
4082 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and normal range");
4083 CPPUNIT_ASSERT_MESSAGE(
4084 "SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and normal range failed",
4085 bGood);
4086 }
4087
4088 // Matrix formula in G18:G20 with SUMIFS and reference list arrays and a
4089 // "normal" criteria range.
4090 m_pDoc->InsertMatrixFormula(
4091 6, 17, 6, 19, aMark,
4092 "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
4093 // Matrix formula in H18:H20 with COUNTIFS and reference list arrays and a
4094 // "normal" criteria range.
4095 m_pDoc->InsertMatrixFormula(7, 17, 7, 19, aMark,
4096 "=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
4097 // Matrix formula in I18:I20 with AVERAGEIFS and reference list arrays and
4098 // a "normal" criteria range.
4099 m_pDoc->InsertMatrixFormula(
4100 8, 17, 8, 19, aMark,
4101 "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
4102
4103 {
4104 // Result sums, counts and averages.
4105 std::vector<std::vector<const char*>> aCheck
4106 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "16", "1", "16" } };
4107 bool bGood = checkOutput(m_pDoc, ScRange(6, 17, 0, 8, 19, 0), aCheck,
4108 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a "
4109 "normal criteria range");
4110 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a "
4111 "normal criteria range failed",
4112 bGood);
4113 }
4114
4115 // Matrix formula in K18:K20 with SUMIFS and reference list array condition
4116 // and "normal" data range and a "normal" criteria range.
4117 m_pDoc->InsertMatrixFormula(10, 17, 10, 19, aMark,
4118 "=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
4119 // Matrix formula in L18:L20 with AVERAGEIFS and reference list array
4120 // condition and "normal" data range and a "normal" criteria range.
4121 m_pDoc->InsertMatrixFormula(11, 17, 11, 19, aMark,
4122 "=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
4123
4124 {
4125 // Result sums and averages.
4126 std::vector<std::vector<const char*>> aCheck
4127 = { { "0", "#DIV/0!" }, { "2", "2" }, { "2", "2" } };
4128 bool bGood = checkOutput(m_pDoc, ScRange(10, 17, 0, 11, 19, 0), aCheck,
4129 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and "
4130 "normal data and criteria range");
4131 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and "
4132 "normal data and criteria range failed",
4133 bGood);
4134 }
4135
4136 // Same, but swapped normal and array criteria.
4137
4138 // Matrix formula in G28:G30 with SUMIFS and reference list arrays and a
4139 // "normal" criteria range, swapped.
4140 m_pDoc->InsertMatrixFormula(
4141 6, 27, 6, 29, aMark,
4142 "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
4143 // Matrix formula in H28:H30 with COUNTIFS and reference list arrays and a
4144 // "normal" criteria range, swapped.
4145 m_pDoc->InsertMatrixFormula(7, 27, 7, 29, aMark,
4146 "=COUNTIFS(B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
4147 // Matrix formula in I28:I30 with AVERAGEIFS and reference list arrays and
4148 // a "normal" criteria range, swapped.
4149 m_pDoc->InsertMatrixFormula(
4150 8, 27, 8, 29, aMark,
4151 "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
4152
4153 {
4154 // Result sums, counts and averages.
4155 std::vector<std::vector<const char*>> aCheck
4156 = { { "0", "0", "#DIV/0!" }, { "8", "1", "8" }, { "16", "1", "16" } };
4157 bool bGood = checkOutput(m_pDoc, ScRange(6, 27, 0, 8, 29, 0), aCheck,
4158 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a "
4159 "normal criteria range, swapped");
4160 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a "
4161 "normal criteria range failed, swapped",
4162 bGood);
4163 }
4164
4165 // Matrix formula in K28:K30 with SUMIFS and reference list array condition
4166 // and "normal" data range and a "normal" criteria range, swapped.
4167 m_pDoc->InsertMatrixFormula(10, 27, 10, 29, aMark,
4168 "=SUMIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
4169 // Matrix formula in L28:L30 with AVERAGEIFS and reference list array
4170 // condition and "normal" data range and a "normal" criteria range,
4171 // swapped.
4172 m_pDoc->InsertMatrixFormula(11, 27, 11, 29, aMark,
4173 "=AVERAGEIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
4174
4175 {
4176 // Result sums and averages.
4177 std::vector<std::vector<const char*>> aCheck
4178 = { { "0", "#DIV/0!" }, { "2", "2" }, { "2", "2" } };
4179 bool bGood = checkOutput(m_pDoc, ScRange(10, 27, 0, 11, 29, 0), aCheck,
4180 "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and "
4181 "normal data and criteria range, swapped");
4182 CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and "
4183 "normal data and criteria range failed, swapped",
4184 bGood);
4185 }
4186
4187 m_pDoc->DeleteTab(0);
4188 }
4189
4190 // Test that COUNTIF counts properly empty cells if asked to.
4191 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncCOUNTIFEmpty)
4192 {
4193 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4194 m_pDoc->InsertTab(0, "Test");
4195
4196 // Data in A1:A9.
4197 std::vector<std::vector<const char*>> aData
4198 = { { "" }, { "a" }, { "b" }, { "c" }, { "d" }, { "a" }, { "" }, { "b" }, { "c" } };
4199
4200 insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData);
4201
4202 constexpr SCROW maxRow = 20; // so that the unittest is not slow in dbgutil builds
4203 SCROW startRow = 0;
4204 SCROW endRow = maxRow;
4205 SCCOL startCol = 0;
4206 SCCOL endCol = 0;
4207 // ScSortedRangeCache would normally shrink data range to this.
4208 CPPUNIT_ASSERT(m_pDoc->ShrinkToDataArea(0, startCol, startRow, endCol, endRow));
4209 CPPUNIT_ASSERT_EQUAL(SCROW(8), endRow);
4210
4211 // But not if matching empty cells.
4212 m_pDoc->SetFormula(ScAddress(10, 0, 0),
4213 "=COUNTIFS($A1:$A" + OUString::number(maxRow + 1) + "; \"\")",
4214 formula::FormulaGrammar::GRAM_NATIVE_UI);
4215 CPPUNIT_ASSERT_EQUAL(double(maxRow + 1 - 7), m_pDoc->GetValue(ScAddress(10, 0, 0)));
4216
4217 m_pDoc->DeleteTab(0);
4218 }
4219
4220 // Test that COUNTIFS counts properly empty cells if asked to.
4221 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncCOUNTIFSRangeReduce)
4222 {
4223 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4224 m_pDoc->InsertTab(0, "Test");
4225
4226 // Data in A1:C9.
4227 std::vector<std::vector<const char*>> aData = { { "" },
4228 { "a", "1", "1" },
4229 { "b", "2", "2" },
4230 { "c", "4", "3" },
4231 { "d", "8", "4" },
4232 { "a", "16", "5" },
4233 { "" },
4234 { "b", "", "6" },
4235 { "c", "64", "7" } };
4236
4237 insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData);
4238
4239 constexpr SCROW maxRow = 20; // so that the unittest is not slow in dbgutil builds
4240 ScRange aSubRange(ScAddress(0, 0, 0), ScAddress(2, maxRow, 0));
4241 m_pDoc->GetDataAreaSubrange(aSubRange);
4242 // This is the range the data should be reduced to in ScInterpreter::IterateParametersIfs().
4243 CPPUNIT_ASSERT_EQUAL(SCROW(1), aSubRange.aStart.Row());
4244 CPPUNIT_ASSERT_EQUAL(SCROW(8), aSubRange.aEnd.Row());
4245
4246 m_pDoc->SetFormula(ScAddress(10, 0, 0),
4247 "=COUNTIFS($A1:$A" + OUString::number(maxRow + 1) + "; \"\"; $B1:$B"
4248 + OUString::number(maxRow + 1) + "; \"\"; $C1:$C"
4249 + OUString::number(maxRow + 1) + "; \"\")",
4250 formula::FormulaGrammar::GRAM_NATIVE_UI);
4251 // But it should find out that it can't range reduce and must count all the empty rows.
4252 CPPUNIT_ASSERT_EQUAL(double(maxRow + 1 - 7), m_pDoc->GetValue(ScAddress(10, 0, 0)));
4253
4254 // Check also with criteria set as cell references, the middle one resulting in matching
4255 // empty cells (which should cause ScInterpreter::IterateParametersIfs() to undo
4256 // the range reduction). This should only match the A8-C8 row, but it also shouldn't crash.
4257 // Matching empty cells using a cell reference needs a formula to set the cell to
4258 // an empty string, plain empty cell wouldn't do, so use K2 for that.
4259 m_pDoc->SetFormula(ScAddress(10, 1, 0), "=\"\"", formula::FormulaGrammar::GRAM_NATIVE_UI);
4260 m_pDoc->SetFormula(ScAddress(10, 0, 0),
4261 "=COUNTIFS($A1:$A" + OUString::number(maxRow + 1) + "; A8; $B1:$B"
4262 + OUString::number(maxRow + 1) + "; K2; $C1:$C"
4263 + OUString::number(maxRow + 1) + "; C8)",
4264 formula::FormulaGrammar::GRAM_NATIVE_UI);
4265 CPPUNIT_ASSERT_EQUAL(double(1), m_pDoc->GetValue(ScAddress(10, 0, 0)));
4266
4267 m_pDoc->DeleteTab(0);
4268 }
4269
4270 // Test SUBTOTAL with reference lists in array context.
4271 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncRefListArraySUBTOTAL)
4272 {
4273 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4274 m_pDoc->InsertTab(0, "Test");
4275
4276 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
4277 m_pDoc->SetValue(0, 1, 0, 2.0); // A2
4278 m_pDoc->SetValue(0, 2, 0, 4.0); // A3
4279 m_pDoc->SetValue(0, 3, 0, 8.0); // A4
4280 m_pDoc->SetValue(0, 4, 0, 16.0); // A5
4281 m_pDoc->SetValue(0, 5, 0, 32.0); // A6
4282
4283 // Matrix in B7:B9, individual SUM of A2:A3, A3:A4 and A4:A5
4284 ScMarkData aMark(m_pDoc->GetSheetLimits());
4285 aMark.SelectOneTable(0);
4286 m_pDoc->InsertMatrixFormula(1, 6, 1, 8, aMark, "=SUBTOTAL(9;OFFSET(A1;ROW(1:3);0;2))");
4287 ScAddress aPos(1, 6, 0);
4288 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A2:A3 failed", 6.0, m_pDoc->GetValue(aPos));
4289 aPos.IncRow();
4290 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A3:A4 failed", 12.0, m_pDoc->GetValue(aPos));
4291 aPos.IncRow();
4292 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL SUM for A4:A5 failed", 24.0, m_pDoc->GetValue(aPos));
4293
4294 // Matrix in C7:C9, individual AVERAGE of A2:A3, A3:A4 and A4:A5
4295 m_pDoc->InsertMatrixFormula(2, 6, 2, 8, aMark, "=SUBTOTAL(1;OFFSET(A1;ROW(1:3);0;2))");
4296 aPos.Set(2, 6, 0);
4297 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A2:A3 failed", 3.0, m_pDoc->GetValue(aPos));
4298 aPos.IncRow();
4299 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A3:A4 failed", 6.0, m_pDoc->GetValue(aPos));
4300 aPos.IncRow();
4301 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A4:A5 failed", 12.0, m_pDoc->GetValue(aPos));
4302
4303 // Matrix in D7:D9, individual MIN of A2:A3, A3:A4 and A4:A5
4304 m_pDoc->InsertMatrixFormula(3, 6, 3, 8, aMark, "=SUBTOTAL(5;OFFSET(A1;ROW(1:3);0;2))");
4305 aPos.Set(3, 6, 0);
4306 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A2:A3 failed", 2.0, m_pDoc->GetValue(aPos));
4307 aPos.IncRow();
4308 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A3:A4 failed", 4.0, m_pDoc->GetValue(aPos));
4309 aPos.IncRow();
4310 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MIN for A4:A5 failed", 8.0, m_pDoc->GetValue(aPos));
4311
4312 // Matrix in E7:E9, individual MAX of A2:A3, A3:A4 and A4:A5
4313 m_pDoc->InsertMatrixFormula(4, 6, 4, 8, aMark, "=SUBTOTAL(4;OFFSET(A1;ROW(1:3);0;2))");
4314 aPos.Set(4, 6, 0);
4315 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A2:A3 failed", 4.0, m_pDoc->GetValue(aPos));
4316 aPos.IncRow();
4317 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A3:A4 failed", 8.0, m_pDoc->GetValue(aPos));
4318 aPos.IncRow();
4319 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A4:A5 failed", 16.0, m_pDoc->GetValue(aPos));
4320
4321 // Matrix in F7:F9, individual STDEV of A2:A3, A3:A4 and A4:A5
4322 m_pDoc->InsertMatrixFormula(5, 6, 5, 8, aMark, "=SUBTOTAL(7;OFFSET(A1;ROW(1:3);0;2))");
4323 aPos.Set(5, 6, 0);
4324 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A2:A3 failed", 1.414214,
4325 m_pDoc->GetValue(aPos), 1e-6);
4326 aPos.IncRow();
4327 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A3:A4 failed", 2.828427,
4328 m_pDoc->GetValue(aPos), 1e-6);
4329 aPos.IncRow();
4330 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A4:A5 failed", 5.656854,
4331 m_pDoc->GetValue(aPos), 1e-6);
4332
4333 // Matrix in G7:G9, individual AVERAGE of A2:A3, A3:A4 and A4:A5
4334 // Plus two "ordinary" ranges, one before and one after.
4335 m_pDoc->InsertMatrixFormula(6, 6, 6, 8, aMark,
4336 "=SUBTOTAL(1;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)");
4337 aPos.Set(6, 6, 0);
4338 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A2:A3,A5:A6 failed", 9.5,
4339 m_pDoc->GetValue(aPos));
4340 aPos.IncRow();
4341 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A3:A4,A5:A6 failed", 10.5,
4342 m_pDoc->GetValue(aPos));
4343 aPos.IncRow();
4344 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL AVERAGE for A1:A2,A4:A5,A5:A6 failed", 12.5,
4345 m_pDoc->GetValue(aPos));
4346
4347 // Matrix in H7:H9, individual MAX of A2:A3, A3:A4 and A4:A5
4348 // Plus two "ordinary" ranges, one before and one after.
4349 m_pDoc->InsertMatrixFormula(7, 6, 7, 8, aMark,
4350 "=SUBTOTAL(4;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)");
4351 aPos.Set(7, 6, 0);
4352 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A2:A3,A5:A6 failed", 32.0,
4353 m_pDoc->GetValue(aPos));
4354 aPos.IncRow();
4355 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A3:A4,A5:A6 failed", 32.0,
4356 m_pDoc->GetValue(aPos));
4357 aPos.IncRow();
4358 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUBTOTAL MAX for A1:A2,A4:A5,A5:A6 failed", 32.0,
4359 m_pDoc->GetValue(aPos));
4360
4361 // Matrix in I7:I9, individual STDEV of A2:A3, A3:A4 and A4:A5
4362 // Plus two "ordinary" ranges, one before and one after.
4363 m_pDoc->InsertMatrixFormula(8, 6, 8, 8, aMark,
4364 "=SUBTOTAL(7;A1:A2;OFFSET(A1;ROW(1:3);0;2);A5:A6)");
4365 aPos.Set(8, 6, 0);
4366 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A2:A3,A5:A6 failed", 12.35718,
4367 m_pDoc->GetValue(aPos), 1e-5);
4368 aPos.IncRow();
4369 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A3:A4,A5:A6 failed", 11.86170,
4370 m_pDoc->GetValue(aPos), 1e-5);
4371 aPos.IncRow();
4372 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("SUBTOTAL STDEV for A1:A2,A4:A5,A5:A6 failed", 11.55422,
4373 m_pDoc->GetValue(aPos), 1e-5);
4374
4375 // Empty two cells such that they affect two ranges.
4376 m_pDoc->SetString(0, 1, 0, ""); // A2
4377 m_pDoc->SetString(0, 2, 0, ""); // A3
4378 // Matrix in J7:J9, individual COUNTBLANK of A2:A3, A3:A4 and A4:A5
4379 m_pDoc->InsertMatrixFormula(9, 6, 9, 8, aMark, "=COUNTBLANK(OFFSET(A1;ROW(1:3);0;2))");
4380 aPos.Set(9, 6, 0);
4381 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A2:A3,A5:A6 failed", 2.0,
4382 m_pDoc->GetValue(aPos));
4383 aPos.IncRow();
4384 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A3:A4,A5:A6 failed", 1.0,
4385 m_pDoc->GetValue(aPos));
4386 aPos.IncRow();
4387 CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTBLANK for A1:A2,A4:A5,A5:A6 failed", 0.0,
4388 m_pDoc->GetValue(aPos));
4389
4390 // Restore these two cell values so we'd catch failures below.
4391 m_pDoc->SetValue(0, 1, 0, 2.0); // A2
4392 m_pDoc->SetValue(0, 2, 0, 4.0); // A3
4393 // Hide rows 2 to 4.
4394 m_pDoc->SetRowHidden(1, 3, 0, true);
4395 // Matrix in K7, array of references as OFFSET result.
4396 m_pDoc->InsertMatrixFormula(10, 6, 10, 6, aMark,
4397 "=SUM(SUBTOTAL(109;OFFSET(A1;ROW(A1:A7)-ROW(A1);;1)))");
4398 aPos.Set(10, 6, 0);
4399 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUM SUBTOTAL failed", 49.0, m_pDoc->GetValue(aPos));
4400 aPos.IncRow();
4401 // ForceArray in K8, array of references as OFFSET result.
4402 m_pDoc->SetString(aPos, "=SUMPRODUCT(SUBTOTAL(109;OFFSET(A1;ROW(A1:A7)-ROW(A1);;1)))");
4403 CPPUNIT_ASSERT_EQUAL_MESSAGE("SUMPRODUCT SUBTOTAL failed", 49.0, m_pDoc->GetValue(aPos));
4404
4405 m_pDoc->DeleteTab(0);
4406 }
4407
4408 // tdf#115493 jump commands return the matrix result instead of the reference
4409 // list array.
4410 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncJumpMatrixArrayIF)
4411 {
4412 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4413 m_pDoc->InsertTab(0, "Test");
4414
4415 m_pDoc->SetString(0, 0, 0, "a"); // A1
4416 std::vector<std::vector<const char*>> aData
4417 = { { "a", "1" }, { "b", "2" }, { "a", "4" } }; // A7:B9
4418 insertRangeData(m_pDoc, ScAddress(0, 6, 0), aData);
4419
4420 ScMarkData aMark(m_pDoc->GetSheetLimits());
4421 aMark.SelectOneTable(0);
4422
4423 // Matrix in C10, summing B7,B9
4424 m_pDoc->InsertMatrixFormula(2, 9, 2, 9, aMark, "=SUM(IF(EXACT(A7:A9;A$1);B7:B9;0))");
4425 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C10 failed", 5.0, m_pDoc->GetValue(ScAddress(2, 9, 0)));
4426
4427 // Matrix in C11, summing B7,B9
4428 m_pDoc->InsertMatrixFormula(
4429 2, 10, 2, 10, aMark,
4430 "=SUM(IF(EXACT(OFFSET(A7;0;0):OFFSET(A7;2;0);A$1);OFFSET(A7;0;1):OFFSET(A7;2;1);0))");
4431 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C11 failed", 5.0, m_pDoc->GetValue(ScAddress(2, 10, 0)));
4432
4433 m_pDoc->DeleteTab(0);
4434 }
4435
4436 // tdf#123477 OFFSET() returns the matrix result instead of the reference list
4437 // array if result is not used as ReferenceOrRefArray.
4438 CPPUNIT_TEST_FIXTURE(TestFormula2, testFuncJumpMatrixArrayOFFSET)
4439 {
4440 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4441 m_pDoc->InsertTab(0, "Test");
4442
4443 std::vector<std::vector<const char*>> aData = { { "abc" }, { "bcd" }, { "cde" } };
4444 insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData); // A1:A3
4445
4446 ScMarkData aMark(m_pDoc->GetSheetLimits());
4447 aMark.SelectOneTable(0);
4448
4449 // Matrix in C5:C7, COLUMN()-3 here offsets by 0 but the entire expression
4450 // is in array/matrix context.
4451 m_pDoc->InsertMatrixFormula(2, 4, 2, 6, aMark, "=FIND(\"c\";OFFSET(A1:A3;0;COLUMN()-3))");
4452 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C5 failed", 3.0, m_pDoc->GetValue(ScAddress(2, 4, 0)));
4453 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C6 failed", 2.0, m_pDoc->GetValue(ScAddress(2, 5, 0)));
4454 CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula C7 failed", 1.0, m_pDoc->GetValue(ScAddress(2, 6, 0)));
4455
4456 m_pDoc->DeleteTab(0);
4457 }
4458
4459 // Test iterations with circular chain of references.
4460 CPPUNIT_TEST_FIXTURE(TestFormula2, testIterations)
4461 {
4462 ScDocOptions aDocOpts = m_pDoc->GetDocOptions();
4463 aDocOpts.SetIter(true);
4464 m_pDoc->SetDocOptions(aDocOpts);
4465
4466 m_pDoc->InsertTab(0, "Test");
4467
4468 m_pDoc->SetValue(0, 0, 0, 0.01); // A1
4469 m_pDoc->SetString(0, 1, 0, "=A1"); // A2
4470 m_pDoc->SetString(0, 2, 0, "=COS(A2)"); // A3
4471 m_pDoc->CalcAll();
4472
4473 // Establish reference cycle for the computation of the fixed point of COS() function
4474 m_pDoc->SetString(0, 0, 0, "=A3"); // A1
4475 m_pDoc->CalcAll();
4476
4477 CPPUNIT_ASSERT_EQUAL_MESSAGE("Cell A3 should not have any formula error", FormulaError::NONE,
4478 m_pDoc->GetErrCode(ScAddress(0, 2, 0)));
4479 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("Iterations to calculate fixed point of cos() failed",
4480 0.7387, m_pDoc->GetValue(0, 2, 0), 1e-4);
4481
4482 // Modify the formula
4483 m_pDoc->SetString(0, 2, 0, "=COS(A2)+0.001"); // A3
4484 m_pDoc->CalcAll();
4485
4486 CPPUNIT_ASSERT_EQUAL_MESSAGE("Cell A3 should not have any formula error after perturbation",
4487 FormulaError::NONE, m_pDoc->GetErrCode(ScAddress(0, 2, 0)));
4488 CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE(
4489 "Iterations to calculate perturbed fixed point of cos() failed", 0.7399,
4490 m_pDoc->GetValue(0, 2, 0), 1e-4);
4491
4492 m_pDoc->DeleteTab(0);
4493
4494 aDocOpts.SetIter(false);
4495 m_pDoc->SetDocOptions(aDocOpts);
4496 }
4497
4498 // tdf#111428 CellStoreEvent and its counter used for quick "has a column
4499 // formula cells" must point to the correct column.
4500 CPPUNIT_TEST_FIXTURE(TestFormula2, testInsertColCellStoreEventSwap)
4501 {
4502 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4503 m_pDoc->InsertTab(0, "Test");
4504
4505 m_pDoc->SetValue(0, 0, 0, 1.0); // A1
4506 m_pDoc->SetString(1, 0, 0, "=A1"); // B1
4507 // Insert column left of B
4508 m_pDoc->InsertCol(ScRange(1, 0, 0, 1, m_pDoc->MaxRow(), 0));
4509 ScAddress aPos(2, 0, 0); // C1, new formula position
4510 CPPUNIT_ASSERT_EQUAL_MESSAGE("Should be formula cell having value", 1.0,
4511 m_pDoc->GetValue(aPos));
4512 // After having swapped in an empty column, editing or adding a formula
4513 // cell has to use the correct store context. To test this,
4514 // ScDocument::SetString() can't be used as it doesn't expose the behavior
4515 // in question, use ScDocFunc::SetFormulaCell() instead which actually is
4516 // also called when editing a cell and creating a formula cell.
4517 ScFormulaCell* pCell = new ScFormulaCell(*m_pDoc, aPos, "=A1+1");
4518 ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
4519 rDocFunc.SetFormulaCell(aPos, pCell, false); // C1, change formula
4520 CPPUNIT_ASSERT_EQUAL_MESSAGE("Initial calculation failed", 2.0, m_pDoc->GetValue(aPos));
4521 m_pDoc->SetValue(0, 0, 0, 2.0); // A1, change value
4522 CPPUNIT_ASSERT_EQUAL_MESSAGE("Recalculation failed", 3.0, m_pDoc->GetValue(aPos));
4523
4524 m_pDoc->DeleteTab(0);
4525 }
4526
4527 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf147398)
4528 {
4529 m_pDoc->InsertTab(0, "Test");
4530
4531 m_pDoc->SetString(0, 0, 0, "=SUM(A3:A5)");
4532 m_pDoc->SetString(0, 1, 0, "=COUNT(A3:A5)");
4533 m_pDoc->SetString(1, 0, 0, "=SUM(B3:B5)");
4534 m_pDoc->SetString(1, 1, 0, "=COUNT(B3:B5)");
4535 m_pDoc->SetString(2, 0, 0, "=SUM(C3:C5)");
4536 m_pDoc->SetString(2, 1, 0, "=COUNT(C3:C5)");
4537 m_pDoc->SetString(3, 0, 0, "=SUM(D3:D5)");
4538 m_pDoc->SetString(3, 1, 0, "=COUNT(D3:D5)");
4539 m_pDoc->SetString(4, 0, 0, "=SUM(E3:E5)");
4540 m_pDoc->SetString(4, 1, 0, "=COUNT(E3:E5)");
4541
4542 m_pDoc->SetString(5, 0, 0, "=SUM(A1:E1)/SUM(A2:E2)");
4543
4544 m_pDoc->SetValue(ScAddress(0, 2, 0), 50.0);
4545 m_pDoc->SetValue(ScAddress(0, 3, 0), 100.0);
4546
4547 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
4548 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
4549 CPPUNIT_ASSERT_EQUAL(75.0, m_pDoc->GetValue(ScAddress(5, 0, 0)));
4550
4551 m_pDoc->SetValue(ScAddress(1, 2, 0), 150.0);
4552 m_pDoc->SetValue(ScAddress(1, 3, 0), 200.0);
4553
4554 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
4555 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
4556 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc->GetValue(ScAddress(1, 0, 0)));
4557 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1, 1, 0)));
4558 CPPUNIT_ASSERT_EQUAL(125.0, m_pDoc->GetValue(ScAddress(5, 0, 0)));
4559
4560 m_pDoc->SetValue(ScAddress(2, 2, 0), 250.0);
4561 m_pDoc->SetValue(ScAddress(2, 3, 0), 300.0);
4562
4563 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
4564 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
4565 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc->GetValue(ScAddress(1, 0, 0)));
4566 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1, 1, 0)));
4567 CPPUNIT_ASSERT_EQUAL(550.0, m_pDoc->GetValue(ScAddress(2, 0, 0)));
4568 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2, 1, 0)));
4569 CPPUNIT_ASSERT_EQUAL(175.0, m_pDoc->GetValue(ScAddress(5, 0, 0)));
4570
4571 m_pDoc->SetValue(ScAddress(3, 2, 0), 350.0);
4572 m_pDoc->SetValue(ScAddress(3, 3, 0), 400.0);
4573
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(750.0, m_pDoc->GetValue(ScAddress(3, 0, 0)));
4581 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3, 1, 0)));
4582 CPPUNIT_ASSERT_EQUAL(225.0, m_pDoc->GetValue(ScAddress(5, 0, 0)));
4583
4584 m_pDoc->SetValue(ScAddress(4, 2, 0), 450.0);
4585 m_pDoc->SetValue(ScAddress(4, 3, 0), 500.0);
4586
4587 CPPUNIT_ASSERT_EQUAL(150.0, m_pDoc->GetValue(ScAddress(0, 0, 0)));
4588 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0, 1, 0)));
4589 CPPUNIT_ASSERT_EQUAL(350.0, m_pDoc->GetValue(ScAddress(1, 0, 0)));
4590 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1, 1, 0)));
4591 CPPUNIT_ASSERT_EQUAL(550.0, m_pDoc->GetValue(ScAddress(2, 0, 0)));
4592 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2, 1, 0)));
4593 CPPUNIT_ASSERT_EQUAL(750.0, m_pDoc->GetValue(ScAddress(3, 0, 0)));
4594 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3, 1, 0)));
4595 CPPUNIT_ASSERT_EQUAL(950.0, m_pDoc->GetValue(ScAddress(4, 0, 0)));
4596 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(4, 1, 0)));
4597 CPPUNIT_ASSERT_EQUAL(275.0, m_pDoc->GetValue(ScAddress(5, 0, 0)));
4598
4599 m_pDoc->DeleteTab(0);
4600 }
4601
4602 #if SC_USE_SSE2
4603 CPPUNIT_TEST_FIXTURE(TestFormula2, testTdf156985)
4604 {
4605 m_pDoc->InsertTab(0, "Test");
4606
4607 m_pDoc->SetString(0, 0, 0, "=-170.87");
4608 m_pDoc->SetString(0, 1, 0, "-223.73");
4609 m_pDoc->SetString(0, 2, 0, "-12.58");
4610 m_pDoc->SetString(0, 3, 0, "234.98");
4611 m_pDoc->SetString(0, 4, 0, "172.2");
4612 m_pDoc->SetString(0, 5, 0, "=SUM(A1:A5)");
4613
4614 // Without the fix in place, this test would have failed with
4615 // - Expected: 0
4616 // - Actual : -1.59872115546023e-14
4617 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(0, 5, 0));
4618
4619 m_pDoc->DeleteTab(0);
4620 }
4621 #endif
4622
4623 CPPUNIT_TEST_FIXTURE(TestFormula2, testFormulaAfterDeleteRows)
4624 {
4625 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
4626 m_pDoc->InsertTab(0, "Test");
4627
4628 // Fill A1:A70000 with 1.0
4629 std::vector<double> aVals(70000, 1.0);
4630 m_pDoc->SetValues(ScAddress(0, 0, 0), aVals);
4631 // Set A70001 with formula "=SUM(A1:A70000)"
4632 m_pDoc->SetString(0, 70000, 0, "=SUM(A1:A70000)");
4633
4634 // Delete rows 2:69998
4635 m_pDoc->DeleteRow(ScRange(0, 1, 0, m_pDoc->MaxCol(), 69997, 0));
4636
4637 const ScAddress aPos(0, 3, 0); // A4
4638 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in A4.", OUString("=SUM(A1:A3)"),
4639 m_pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
4640
4641 ASSERT_DOUBLES_EQUAL_MESSAGE("Wrong value at A4", 3.0, m_pDoc->GetValue(aPos));
4642 }
4643
4644 CPPUNIT_PLUGIN_IMPLEMENT();
4645
4646 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
FREE OFFICE SUITE