Line data Source code
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 "ucalc.hxx"
11 : #include "markdata.hxx"
12 : #include "calcconfig.hxx"
13 : #include "interpre.hxx"
14 : #include "compiler.hxx"
15 : #include "tokenarray.hxx"
16 : #include "refdata.hxx"
17 : #include "scopetools.hxx"
18 : #include "formulacell.hxx"
19 : #include "formulagroup.hxx"
20 : #include "scmod.hxx"
21 : #include "docsh.hxx"
22 : #include "docfunc.hxx"
23 : #include "paramisc.hxx"
24 : #include "tokenstringcontext.hxx"
25 : #include "dbdata.hxx"
26 : #include <validat.hxx>
27 : #include <scitems.hxx>
28 : #include <patattr.hxx>
29 : #include <docpool.hxx>
30 :
31 : #include <formula/vectortoken.hxx>
32 : #include <svl/broadcast.hxx>
33 :
34 : #include <boost/scoped_ptr.hpp>
35 :
36 : using namespace formula;
37 :
38 : namespace {
39 :
40 4 : ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
41 : {
42 4 : ScRange aRange;
43 :
44 4 : vector<SCROW> aRows;
45 4 : pCacheTab->getAllRows(aRows);
46 4 : vector<SCROW>::const_iterator itrRow = aRows.begin(), itrRowEnd = aRows.end();
47 4 : bool bFirst = true;
48 22 : for (; itrRow != itrRowEnd; ++itrRow)
49 : {
50 18 : SCROW nRow = *itrRow;
51 18 : vector<SCCOL> aCols;
52 18 : pCacheTab->getAllCols(nRow, aCols);
53 18 : vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end();
54 54 : for (; itrCol != itrColEnd; ++itrCol)
55 : {
56 36 : SCCOL nCol = *itrCol;
57 36 : if (bFirst)
58 : {
59 4 : aRange.aStart = ScAddress(nCol, nRow, 0);
60 4 : aRange.aEnd = aRange.aStart;
61 4 : bFirst = false;
62 : }
63 : else
64 : {
65 32 : if (nCol < aRange.aStart.Col())
66 0 : aRange.aStart.SetCol(nCol);
67 32 : else if (aRange.aEnd.Col() < nCol)
68 4 : aRange.aEnd.SetCol(nCol);
69 :
70 32 : if (nRow < aRange.aStart.Row())
71 0 : aRange.aStart.SetRow(nRow);
72 32 : else if (aRange.aEnd.Row() < nRow)
73 14 : aRange.aEnd.SetRow(nRow);
74 : }
75 : }
76 18 : }
77 4 : return aRange;
78 : }
79 :
80 : }
81 :
82 2 : void Test::testFormulaCreateStringFromTokens()
83 : {
84 : // Insert sheets.
85 2 : OUString aTabName1("Test");
86 4 : OUString aTabName2("Kevin's Data");
87 4 : OUString aTabName3("Past Data");
88 4 : OUString aTabName4("2013");
89 2 : m_pDoc->InsertTab(0, aTabName1);
90 2 : m_pDoc->InsertTab(1, aTabName2);
91 2 : m_pDoc->InsertTab(2, aTabName3);
92 2 : m_pDoc->InsertTab(3, aTabName4);
93 :
94 : // Insert named ranges.
95 : struct {
96 : bool bGlobal;
97 : const char* pName;
98 : const char* pExpr;
99 : } aNames[] = {
100 : { true, "x", "Test.H1" },
101 : { true, "y", "Test.H2" },
102 : { true, "z", "Test.H3" },
103 :
104 : { false, "sheetx", "Test.J1" }
105 2 : };
106 :
107 2 : ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
108 2 : ScRangeName* pSheetNames = m_pDoc->GetRangeName(0);
109 2 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
110 2 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain sheet-local named expression object.", pSheetNames);
111 :
112 10 : for (size_t i = 0, n = SAL_N_ELEMENTS(aNames); i < n; ++i)
113 : {
114 : ScRangeData* pName = new ScRangeData(
115 : m_pDoc, OUString::createFromAscii(aNames[i].pName), OUString::createFromAscii(aNames[i].pExpr),
116 8 : ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
117 :
118 8 : if (aNames[i].bGlobal)
119 : {
120 6 : bool bInserted = pGlobalNames->insert(pName);
121 6 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
122 : }
123 : else
124 : {
125 2 : bool bInserted = pSheetNames->insert(pName);
126 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
127 : }
128 : }
129 :
130 : // Insert DB ranges.
131 : struct {
132 : const char* pName;
133 : SCTAB nTab;
134 : SCCOL nCol1;
135 : SCROW nRow1;
136 : SCCOL nCol2;
137 : SCROW nRow2;
138 : } aDBs[] = {
139 : { "Table1", 0, 0, 0, 10, 10 },
140 : { "Table2", 1, 0, 0, 10, 10 },
141 : { "Table3", 2, 0, 0, 10, 10 }
142 2 : };
143 :
144 2 : ScDBCollection* pDBs = m_pDoc->GetDBCollection();
145 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
146 :
147 8 : for (size_t i = 0, n = SAL_N_ELEMENTS(aDBs); i < n; ++i)
148 : {
149 : ScDBData* pData = new ScDBData(
150 : OUString::createFromAscii(
151 6 : aDBs[i].pName), aDBs[i].nTab, aDBs[i].nCol1, aDBs[i].nRow1, aDBs[i].nCol2,aDBs[i].nRow2);
152 6 : bool bInserted = pDBs->getNamedDBs().insert(pData);
153 12 : CPPUNIT_ASSERT_MESSAGE(
154 : OString(
155 : "Failed to insert \"" + OString(aDBs[i].pName) + "\"").getStr(),
156 6 : bInserted);
157 : }
158 :
159 : const char* aTests[] = {
160 : "1+2",
161 : "SUM(A1:A10;B1:B10;C5;D6)",
162 : "IF(Test.B10<>10;\"Good\";\"Bad\")",
163 : "AVERAGE('2013'.B10:C20)",
164 : "'Kevin''s Data'.B10",
165 : "'Past Data'.B1+'2013'.B2*(1+'Kevin''s Data'.C10)",
166 : "x+y*z", // named ranges
167 : "SUM(sheetx;x;y;z)", // sheet local and global named ranges mixed
168 : "MAX(Table1)+MIN(Table2)*SUM(Table3)", // database ranges
169 : "{1;TRUE;3|FALSE;5;\"Text\"|;;}", // inline matrix
170 : "SUM('file:///path/to/fake.file'#$Sheet.A1:B10)",
171 2 : };
172 : (void) aTests;
173 :
174 4 : boost::scoped_ptr<ScTokenArray> pArray;
175 :
176 4 : sc::TokenStringContext aCxt(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);
177 :
178 : // Artificially add external refererence data after the context object is
179 : // initialized.
180 2 : aCxt.maExternalFileNames.push_back("file:///path/to/fake.file");
181 4 : std::vector<OUString> aExtTabNames;
182 2 : aExtTabNames.push_back("Sheet");
183 : aCxt.maExternalCachedTabNames.insert(
184 2 : sc::TokenStringContext::IndexNamesMapType::value_type(0, aExtTabNames));
185 :
186 2 : ScAddress aPos(0,0,0);
187 :
188 24 : for (size_t i = 0, n = SAL_N_ELEMENTS(aTests); i < n; ++i)
189 : {
190 : #if 0
191 : OUString aFormula = OUString::createFromAscii(aTests[i]);
192 : #endif
193 22 : ScCompiler aComp(m_pDoc, aPos);
194 22 : aComp.SetGrammar(FormulaGrammar::GRAM_ENGLISH);
195 : #if 0 // TODO: This call to CompileString() causes the cppunittester to somehow fail on Windows.
196 : pArray.reset(aComp.CompileString(aFormula));
197 : CPPUNIT_ASSERT_MESSAGE("Failed to compile formula string.", pArray.get());
198 :
199 : OUString aCheck = pArray->CreateString(aCxt, aPos);
200 : CPPUNIT_ASSERT_EQUAL(aFormula, aCheck);
201 : #endif
202 22 : }
203 :
204 2 : m_pDoc->DeleteTab(3);
205 2 : m_pDoc->DeleteTab(2);
206 2 : m_pDoc->DeleteTab(1);
207 4 : m_pDoc->DeleteTab(0);
208 2 : }
209 :
210 : namespace {
211 :
212 36 : bool isEmpty( const formula::VectorRefArray& rArray, size_t nPos )
213 : {
214 36 : if (rArray.mpStringArray)
215 : {
216 14 : if (rArray.mpStringArray[nPos])
217 0 : return false;
218 : }
219 :
220 36 : if (rArray.mpNumericArray)
221 34 : return rtl::math::isNan(rArray.mpNumericArray[nPos]);
222 : else
223 2 : return true;
224 : }
225 :
226 70 : bool equals( const formula::VectorRefArray& rArray, size_t nPos, double fVal )
227 : {
228 70 : if (rArray.mpStringArray && rArray.mpStringArray[nPos])
229 : // This is a string cell.
230 0 : return false;
231 :
232 70 : if (rArray.mpNumericArray && rArray.mpNumericArray[nPos] == fVal)
233 70 : return true;
234 :
235 0 : return false;
236 : }
237 :
238 32 : bool equals( const formula::VectorRefArray& rArray, size_t nPos, const OUString& rVal )
239 : {
240 32 : if (!rArray.mpStringArray)
241 0 : return false;
242 :
243 32 : bool bEquals = OUString(rArray.mpStringArray[nPos]).equalsIgnoreAsciiCase(rVal);
244 32 : if (!bEquals)
245 : {
246 0 : cerr << "Expected: " << rVal.toAsciiUpperCase() << " (upcased)" << endl;
247 0 : cerr << "Actual: " << OUString(rArray.mpStringArray[nPos]) << " (upcased)" << endl;
248 : }
249 32 : return bEquals;
250 : }
251 :
252 : }
253 :
254 2 : void Test::testFormulaParseReference()
255 : {
256 4 : OUString aTab1("90's Music"), aTab2("90's and 70's"), aTab3("All Others"), aTab4("NoQuote");
257 2 : m_pDoc->InsertTab(0, "Dummy"); // just to shift the sheet indices...
258 2 : m_pDoc->InsertTab(1, aTab1); // name with a single quote.
259 2 : m_pDoc->InsertTab(2, aTab2); // name with 2 single quotes.
260 2 : m_pDoc->InsertTab(3, aTab3); // name without single quotes.
261 2 : m_pDoc->InsertTab(4, aTab4); // name that doesn't require to be quoted.
262 :
263 4 : OUString aTabName;
264 2 : m_pDoc->GetName(1, aTabName);
265 2 : CPPUNIT_ASSERT_EQUAL(aTab1, aTabName);
266 2 : m_pDoc->GetName(2, aTabName);
267 2 : CPPUNIT_ASSERT_EQUAL(aTab2, aTabName);
268 2 : m_pDoc->GetName(3, aTabName);
269 2 : CPPUNIT_ASSERT_EQUAL(aTab3, aTabName);
270 2 : m_pDoc->GetName(4, aTabName);
271 2 : CPPUNIT_ASSERT_EQUAL(aTab4, aTabName);
272 :
273 : // Make sure the formula input and output match.
274 : {
275 : const char* aChecks[] = {
276 : "'90''s Music'.B12",
277 : "'90''s and 70''s'.$AB$100",
278 : "'All Others'.Z$100",
279 : "NoQuote.$C111"
280 2 : };
281 :
282 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
283 : {
284 : // Use the 'Dummy' sheet for this.
285 8 : OUString aInput("=");
286 8 : aInput += OUString::createFromAscii(aChecks[i]);
287 8 : m_pDoc->SetString(ScAddress(0,0,0), aInput);
288 8 : if (!checkFormula(*m_pDoc, ScAddress(0,0,0), aChecks[i]))
289 0 : CPPUNIT_FAIL("Wrong formula");
290 8 : }
291 : }
292 :
293 2 : ScAddress aPos;
294 4 : ScAddress::ExternalInfo aExtInfo;
295 2 : sal_uInt16 nRes = aPos.Parse("'90''s Music'.D10", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
296 2 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
297 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(1), aPos.Tab());
298 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), aPos.Col());
299 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), aPos.Row());
300 2 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
301 :
302 2 : nRes = aPos.Parse("'90''s and 70''s'.C100", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
303 2 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
304 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(2), aPos.Tab());
305 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), aPos.Col());
306 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(99), aPos.Row());
307 2 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
308 :
309 2 : nRes = aPos.Parse("'All Others'.B3", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
310 2 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
311 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(3), aPos.Tab());
312 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aPos.Col());
313 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), aPos.Row());
314 2 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
315 :
316 2 : nRes = aPos.Parse("NoQuote.E13", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
317 2 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
318 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(4), aPos.Tab());
319 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(4), aPos.Col());
320 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), aPos.Row());
321 2 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
322 :
323 2 : m_pDoc->DeleteTab(4);
324 2 : m_pDoc->DeleteTab(3);
325 2 : m_pDoc->DeleteTab(2);
326 2 : m_pDoc->DeleteTab(1);
327 4 : m_pDoc->DeleteTab(0);
328 2 : }
329 :
330 2 : void Test::testFetchVectorRefArray()
331 : {
332 2 : m_pDoc->InsertTab(0, "Test");
333 :
334 : // All numeric cells in Column A.
335 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
336 2 : m_pDoc->SetValue(ScAddress(0,1,0), 2);
337 2 : m_pDoc->SetValue(ScAddress(0,2,0), 3);
338 2 : m_pDoc->SetValue(ScAddress(0,3,0), 4);
339 :
340 2 : formula::VectorRefArray aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 4);
341 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
342 2 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
343 2 : CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
344 2 : CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
345 2 : CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
346 2 : CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);
347 :
348 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 5);
349 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
350 2 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
351 2 : CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
352 2 : CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
353 2 : CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
354 2 : CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);
355 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
356 :
357 : // All string cells in Column B. Note that the fetched string arrays are
358 : // only to be compared case-insensitively. Right now, we use upper cased
359 : // strings to achieve case-insensitive-ness, but that may change. So,
360 : // don't count on that.
361 2 : m_pDoc->SetString(ScAddress(1,0,0), "Andy");
362 2 : m_pDoc->SetString(ScAddress(1,1,0), "Bruce");
363 2 : m_pDoc->SetString(ScAddress(1,2,0), "Charlie");
364 2 : m_pDoc->SetString(ScAddress(1,3,0), "David");
365 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 5);
366 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
367 2 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be string cells only.", !aArray.mpNumericArray);
368 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "Andy"));
369 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "Bruce"));
370 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "Charlie"));
371 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "David"));
372 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
373 :
374 : // Mixture of numeric, string, and empty cells in Column C.
375 2 : m_pDoc->SetString(ScAddress(2,0,0), "Header");
376 2 : m_pDoc->SetValue(ScAddress(2,1,0), 11);
377 2 : m_pDoc->SetValue(ScAddress(2,2,0), 12);
378 2 : m_pDoc->SetValue(ScAddress(2,3,0), 13);
379 2 : m_pDoc->SetString(ScAddress(2,5,0), "=SUM(C2:C4)");
380 2 : m_pDoc->CalcAll();
381 :
382 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(2,0,0), 7);
383 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
384 2 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
385 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "Header"));
386 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 11));
387 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 12));
388 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 13));
389 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
390 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 36));
391 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));
392 :
393 : // Mixed type again in Column D, but it starts with a numeric cell.
394 2 : m_pDoc->SetValue(ScAddress(3,0,0), 10);
395 2 : m_pDoc->SetString(ScAddress(3,1,0), "Below 10");
396 : // Leave 2 empty cells.
397 2 : m_pDoc->SetValue(ScAddress(3,4,0), 11);
398 2 : m_pDoc->SetString(ScAddress(3,5,0), "=12");
399 2 : m_pDoc->SetString(ScAddress(3,6,0), "=13");
400 2 : m_pDoc->SetString(ScAddress(3,7,0), "=CONCATENATE(\"A\";\"B\";\"C\")");
401 2 : m_pDoc->CalcAll();
402 :
403 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(3,0,0), 8);
404 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
405 2 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
406 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 10));
407 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "Below 10"));
408 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 2));
409 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));
410 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 11));
411 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 12));
412 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 13));
413 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 7, "ABC"));
414 :
415 : // Column E consists of formula cells whose results are all numeric.
416 16 : for (SCROW i = 0; i <= 6; ++i)
417 14 : m_pDoc->SetString(ScAddress(4,i,0), "=ROW()");
418 2 : m_pDoc->CalcAll();
419 :
420 : // Leave row 7 empty.
421 2 : m_pDoc->SetString(ScAddress(4,8,0), "Andy");
422 2 : m_pDoc->SetValue(ScAddress(4,9,0), 123);
423 :
424 : // This array fits within a single formula block.
425 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 5);
426 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
427 2 : CPPUNIT_ASSERT_MESSAGE("Array should be purely numeric.", aArray.mpNumericArray && !aArray.mpStringArray);
428 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
429 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
430 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
431 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
432 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));
433 :
434 : // This array spans over multiple blocks.
435 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 11);
436 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
437 2 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
438 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
439 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
440 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
441 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
442 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));
443 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 6));
444 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 7));
445 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
446 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 8, "Andy"));
447 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 9, 123));
448 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 10));
449 :
450 : // Hit the cache but at a different start row.
451 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,2,0), 3);
452 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
453 2 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
454 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 3));
455 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 4));
456 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 5));
457 :
458 : // Column F begins with empty rows at the top.
459 2 : m_pDoc->SetValue(ScAddress(5,2,0), 1.1);
460 2 : m_pDoc->SetValue(ScAddress(5,3,0), 1.2);
461 2 : m_pDoc->SetString(ScAddress(5,4,0), "=2*8");
462 2 : m_pDoc->CalcAll();
463 :
464 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 4);
465 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
466 2 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
467 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1.1));
468 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 1.2));
469 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 16));
470 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));
471 :
472 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 3);
473 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
474 2 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
475 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
476 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
477 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));
478 :
479 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 10);
480 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
481 2 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
482 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
483 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
484 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));
485 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 1.2));
486 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 16));
487 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 5));
488 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));
489 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
490 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 8));
491 2 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 9));
492 :
493 : // Get the array for F3:F4. This array should only consist of numeric array.
494 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 3);
495 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
496 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
497 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
498 :
499 : // Column G consists only of strings.
500 2 : m_pDoc->SetString(ScAddress(6,0,0), "Title");
501 2 : m_pDoc->SetString(ScAddress(6,1,0), "foo");
502 2 : m_pDoc->SetString(ScAddress(6,2,0), "bar");
503 2 : m_pDoc->SetString(ScAddress(6,3,0), "foo");
504 2 : m_pDoc->SetString(ScAddress(6,4,0), "baz");
505 2 : m_pDoc->SetString(ScAddress(6,5,0), "quack");
506 2 : m_pDoc->SetString(ScAddress(6,6,0), "beep");
507 2 : m_pDoc->SetString(ScAddress(6,7,0), "kerker");
508 :
509 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,1,0), 4); // G2:G5
510 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
511 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
512 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
513 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "foo"));
514 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "bar"));
515 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "foo"));
516 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "baz"));
517 :
518 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,2,0), 4); // G3:G6
519 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
520 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
521 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
522 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "bar"));
523 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "foo"));
524 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "baz"));
525 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "quack"));
526 :
527 : // Column H starts with formula cells.
528 22 : for (SCROW i = 0; i < 10; ++i)
529 20 : m_pDoc->SetString(ScAddress(7,i,0), "=ROW()");
530 :
531 2 : m_pDoc->CalcAll();
532 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,3,0), 3); // H4:H6
533 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
534 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
535 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
536 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 4.0));
537 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, 5.0));
538 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, 6.0));
539 :
540 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,4,0), 10); // H5:H15
541 2 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
542 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
543 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
544 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 5.0));
545 :
546 : // Clear everything and start over.
547 2 : clearRange(m_pDoc, ScRange(0,0,0,MAXCOL,MAXROW,0));
548 2 : m_pDoc->ClearFormulaContext();
549 :
550 : // Totally empty range in a totally empty column (Column A).
551 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 3); // A1:A3
552 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
553 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
554 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
555 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
556 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
557 :
558 : // Totally empty range in a non-empty column (Column B).
559 2 : m_pDoc->SetString(ScAddress(1,10,0), "Some text"); // B11
560 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 3); // B1:B3
561 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
562 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
563 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
564 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
565 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
566 :
567 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,12,0), 3); // B13:B15
568 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
569 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
570 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
571 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
572 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
573 :
574 : // These values come from a cache because of the call above.
575 2 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,1,0), 3); // B2:B4
576 2 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
577 2 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
578 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
579 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
580 2 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
581 :
582 2 : m_pDoc->DeleteTab(0);
583 2 : }
584 :
585 2 : void Test::testFormulaHashAndTag()
586 : {
587 2 : m_pDoc->InsertTab(0, "Test");
588 :
589 2 : ScAddress aPos1(0,0,0), aPos2(1,0,0);
590 :
591 : // Test formula hashing.
592 :
593 : struct {
594 : const char* pFormula1; const char* pFormula2; bool bEqual;
595 : } aHashTests[] = {
596 : { "=1", "=2", false }, // different constants
597 : { "=SUM(1;2;3;4;5)", "=AVERAGE(1;2;3;4;5)", false }, // different functions
598 : { "=C2*3", "=D2*3", true }, // relative references
599 : { "=C2*3", "=D2*4", false }, // different constants
600 : { "=C2*4", "=D2*4", true }, // relative references
601 : { "=3*4*5", "=3*4*\"foo\"", false }, // numeric vs string constants
602 : { "=$C3/2", "=$C3/2", true }, // absolute column references
603 : { "=C$3/2", "=D$3/2", true }, // absolute row references
604 : { "=$E$30/2", "=$E$30/2", true }, // absolute references
605 : { "=X20", "=$X$20", false }, // absolute vs relative
606 : { "=X20", "=X$20", false }, // absolute vs relative
607 : { "=X20", "=$X20", false }, // absolute vs relative
608 : { "=X$20", "=$X20", false }, // column absolute vs row absolute
609 : // similar enough for merging ...
610 : { "=A1", "=B1", true },
611 : { "=$A$1", "=$B$1", true },
612 : { "=A1", "=C2", true },
613 : { "=SUM(A1)", "=SUM(B1)", true },
614 : { "=A1+3", "=B1+3", true },
615 : { "=A1+7", "=B1+42", false },
616 2 : };
617 :
618 40 : for (size_t i = 0; i < SAL_N_ELEMENTS(aHashTests); ++i)
619 : {
620 38 : m_pDoc->SetString(aPos1, OUString::createFromAscii(aHashTests[i].pFormula1));
621 38 : m_pDoc->SetString(aPos2, OUString::createFromAscii(aHashTests[i].pFormula2));
622 38 : size_t nHashVal1 = m_pDoc->GetFormulaHash(aPos1);
623 38 : size_t nHashVal2 = m_pDoc->GetFormulaHash(aPos2);
624 :
625 38 : std::ostringstream os;
626 38 : os << "(expr1:" << aHashTests[i].pFormula1 << "; expr2:" << aHashTests[i].pFormula2 << ")";
627 38 : if (aHashTests[i].bEqual)
628 : {
629 20 : os << " Error: these hashes should be equal." << endl;
630 20 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), nHashVal1 == nHashVal2);
631 : }
632 : else
633 : {
634 18 : os << " Error: these hashes should differ." << endl;
635 18 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), nHashVal1 != nHashVal2);
636 : }
637 :
638 38 : aPos1.IncRow();
639 38 : aPos2.IncRow();
640 38 : }
641 :
642 : // Go back to row 1.
643 2 : aPos1.SetRow(0);
644 2 : aPos2.SetRow(0);
645 :
646 : // Test formula vectorization state.
647 :
648 : struct {
649 : const char* pFormula; ScFormulaVectorState eState;
650 : } aVectorTests[] = {
651 : { "=SUM(1;2;3;4;5)", FormulaVectorEnabled },
652 : { "=NOW()", FormulaVectorDisabled },
653 : { "=AVERAGE(X1:Y200)", FormulaVectorCheckReference },
654 : { "=MAX(X1:Y200;10;20)", FormulaVectorCheckReference },
655 : { "=MIN(10;11;22)", FormulaVectorEnabled },
656 : { "=H4", FormulaVectorCheckReference },
657 2 : };
658 :
659 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aVectorTests); ++i)
660 : {
661 12 : m_pDoc->SetString(aPos1, OUString::createFromAscii(aVectorTests[i].pFormula));
662 12 : ScFormulaVectorState eState = m_pDoc->GetFormulaVectorState(aPos1);
663 :
664 12 : if (eState != aVectorTests[i].eState)
665 : {
666 0 : std::ostringstream os;
667 0 : os << "Unexpected vectorization state: expr:" << aVectorTests[i].pFormula;
668 0 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), false);
669 : }
670 12 : aPos1.IncRow();
671 : }
672 :
673 2 : m_pDoc->DeleteTab(0);
674 2 : }
675 :
676 2 : void Test::testFormulaTokenEquality()
677 : {
678 : struct FormulaTokenEqualityTest
679 : {
680 : const char* mpFormula1;
681 : const char* mpFormula2;
682 : bool mbEqual;
683 : };
684 :
685 : FormulaTokenEqualityTest aTests[] = {
686 : { "R1C2", "R1C2", true },
687 : { "R1C2", "R1C3", false },
688 : { "R1C2", "R2C2", false },
689 : { "RC2", "RC[1]", false },
690 : { "R1C2:R10C2", "R1C2:R10C2", true },
691 : { "R1C2:R10C2", "R1C2:R11C2", false },
692 : { "1", "2", false },
693 : { "RC[1]+1.2", "RC[1]+1.2", true },
694 : { "RC[1]*0.2", "RC[1]*0.5", false },
695 : { "\"Test1\"", "\"Test2\"", false },
696 : { "\"Test\"", "\"Test\"", true },
697 : { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test1\")", true },
698 : { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test2\")", false },
699 2 : };
700 :
701 2 : formula::FormulaGrammar::Grammar eGram = formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1;
702 28 : for (size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
703 : {
704 26 : ScFormulaCell aCell1(m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula1), eGram);
705 52 : ScFormulaCell aCell2(m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula2), eGram);
706 :
707 26 : ScFormulaCell::CompareState eComp = aCell1.CompareByTokenArray(aCell2);
708 26 : if (aTests[i].mbEqual)
709 : {
710 10 : if (eComp == ScFormulaCell::NotEqual)
711 : {
712 0 : std::ostringstream os;
713 0 : os << "These two formulas should be evaluated equal: '"
714 0 : << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
715 0 : CPPUNIT_FAIL(os.str().c_str());
716 : }
717 : }
718 : else
719 : {
720 16 : if (eComp != ScFormulaCell::NotEqual)
721 : {
722 0 : std::ostringstream os;
723 0 : os << "These two formulas should be evaluated non-equal: '"
724 0 : << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
725 0 : CPPUNIT_FAIL(os.str().c_str());
726 : }
727 : }
728 26 : }
729 2 : }
730 :
731 2 : void Test::testFormulaRefData()
732 : {
733 2 : ScAddress aAddr(4,5,3), aPos(2,2,2);
734 : ScSingleRefData aRef;
735 2 : aRef.InitAddress(aAddr);
736 2 : CPPUNIT_ASSERT_MESSAGE("Wrong ref data state.", !aRef.IsRowRel() && !aRef.IsColRel() && !aRef.IsTabRel());
737 2 : ASSERT_EQUAL_TYPE(SCCOL, 4, aRef.Col());
738 2 : ASSERT_EQUAL_TYPE(SCROW, 5, aRef.Row());
739 2 : ASSERT_EQUAL_TYPE(SCTAB, 3, aRef.Tab());
740 :
741 2 : aRef.SetRowRel(true);
742 2 : aRef.SetColRel(true);
743 2 : aRef.SetTabRel(true);
744 2 : aRef.SetAddress(aAddr, aPos);
745 2 : ASSERT_EQUAL_TYPE(SCCOL, 2, aRef.Col());
746 2 : ASSERT_EQUAL_TYPE(SCROW, 3, aRef.Row());
747 2 : ASSERT_EQUAL_TYPE(SCTAB, 1, aRef.Tab());
748 :
749 : // Test extension of range reference.
750 :
751 : ScComplexRefData aDoubleRef;
752 2 : aDoubleRef.InitRange(ScRange(2,2,0,4,4,0));
753 :
754 2 : aRef.InitAddress(ScAddress(6,5,0));
755 :
756 2 : aDoubleRef.Extend(aRef, ScAddress());
757 2 : ScRange aTest = aDoubleRef.toAbs(ScAddress());
758 2 : CPPUNIT_ASSERT_MESSAGE("Wrong start position of extended range.", aTest.aStart == ScAddress(2,2,0));
759 2 : CPPUNIT_ASSERT_MESSAGE("Wrong end position of extended range.", aTest.aEnd == ScAddress(6,5,0));
760 :
761 : ScComplexRefData aDoubleRef2;
762 2 : aDoubleRef2.InitRangeRel(ScRange(1,2,0,8,6,0), ScAddress(5,5,0));
763 2 : aDoubleRef.Extend(aDoubleRef2, ScAddress(5,5,0));
764 2 : aTest = aDoubleRef.toAbs(ScAddress(5,5,0));
765 :
766 2 : CPPUNIT_ASSERT_MESSAGE("Wrong start position of extended range.", aTest.aStart == ScAddress(1,2,0));
767 2 : CPPUNIT_ASSERT_MESSAGE("Wrong end position of extended range.", aTest.aEnd == ScAddress(8,6,0));
768 2 : }
769 :
770 2 : void Test::testFormulaCompiler()
771 : {
772 : struct {
773 : const char* pInput; FormulaGrammar::Grammar eInputGram;
774 : const char* pOutput; FormulaGrammar::Grammar eOutputGram;
775 : } aTests[] = {
776 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "[.B1]-[.$C2]+[.D$3]-[.$E$4]", FormulaGrammar::GRAM_ODFF },
777 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE },
778 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE_XL_A1 },
779 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "RC[1]-R[1]C3+R3C[3]-R4C5", FormulaGrammar::GRAM_NATIVE_XL_R1C1 },
780 2 : };
781 :
782 10 : for (size_t i = 0, n = SAL_N_ELEMENTS(aTests); i < n; ++i)
783 : {
784 8 : boost::scoped_ptr<ScTokenArray> pArray;
785 : {
786 8 : pArray.reset(compileFormula(m_pDoc, OUString::createFromAscii(aTests[i].pInput), NULL, aTests[i].eInputGram));
787 8 : CPPUNIT_ASSERT_MESSAGE("Token array shouldn't be NULL!", pArray.get());
788 : }
789 :
790 16 : OUString aFormula = toString(*m_pDoc, ScAddress(), *pArray, aTests[i].eOutputGram);
791 8 : CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aTests[i].pOutput), aFormula);
792 8 : }
793 2 : }
794 :
795 2 : void Test::testFormulaCompilerJumpReordering()
796 : {
797 : struct TokenCheck
798 : {
799 : OpCode meOp;
800 : StackVar meType;
801 : };
802 :
803 : // Set separators first.
804 2 : ScFormulaOptions aOptions;
805 2 : aOptions.SetFormulaSepArg(";");
806 2 : aOptions.SetFormulaSepArrayCol(";");
807 2 : aOptions.SetFormulaSepArrayRow("|");
808 2 : getDocShell().SetFormulaOptions(aOptions);
809 :
810 : {
811 2 : OUString aInput("=IF(B1;12;\"text\")");
812 :
813 : // Compile formula string first.
814 4 : boost::scoped_ptr<ScTokenArray> pCode(compileFormula(m_pDoc, aInput));
815 2 : CPPUNIT_ASSERT(pCode.get());
816 :
817 : // Then generate RPN tokens.
818 4 : ScCompiler aCompRPN(m_pDoc, ScAddress(), *pCode);
819 2 : aCompRPN.SetGrammar(FormulaGrammar::GRAM_NATIVE);
820 2 : aCompRPN.CompileTokenArray();
821 :
822 : // RPN tokens should be ordered: B1, ocIf, C1, ocSep, D1, ocClose.
823 : TokenCheck aCheckRPN[] =
824 : {
825 : { ocPush, svSingleRef },
826 : { ocIf, static_cast<formula::StackVar>(0) },
827 : { ocPush, svDouble },
828 : { ocSep, static_cast<formula::StackVar>(0) },
829 : { ocPush, svString },
830 : { ocClose, static_cast<formula::StackVar>(0) },
831 2 : };
832 :
833 2 : sal_uInt16 nLen = pCode->GetCodeLen();
834 2 : CPPUNIT_ASSERT_MESSAGE("Wrong RPN token count.", nLen == SAL_N_ELEMENTS(aCheckRPN));
835 :
836 2 : FormulaToken** ppTokens = pCode->GetCode();
837 14 : for (sal_uInt16 i = 0; i < nLen; ++i)
838 : {
839 12 : const FormulaToken* p = ppTokens[i];
840 12 : CPPUNIT_ASSERT_EQUAL(aCheckRPN[i].meOp, p->GetOpCode());
841 12 : if (aCheckRPN[i].meOp == ocPush)
842 6 : CPPUNIT_ASSERT_EQUAL(static_cast<int>(aCheckRPN[i].meType), static_cast<int>(p->GetType()));
843 : }
844 :
845 : // Generate RPN tokens again, but this time no jump command reordering.
846 2 : pCode->DelRPN();
847 4 : ScCompiler aCompRPN2(m_pDoc, ScAddress(), *pCode);
848 2 : aCompRPN2.SetGrammar(FormulaGrammar::GRAM_NATIVE);
849 2 : aCompRPN2.EnableJumpCommandReorder(false);
850 2 : aCompRPN2.CompileTokenArray();
851 :
852 : TokenCheck aCheckRPN2[] =
853 : {
854 : { ocPush, svSingleRef },
855 : { ocPush, svDouble },
856 : { ocPush, svString },
857 : { ocIf, static_cast<formula::StackVar>(0) },
858 2 : };
859 :
860 2 : nLen = pCode->GetCodeLen();
861 2 : CPPUNIT_ASSERT_MESSAGE("Wrong RPN token count.", nLen == SAL_N_ELEMENTS(aCheckRPN2));
862 2 : ppTokens = pCode->GetCode();
863 10 : for (sal_uInt16 i = 0; i < nLen; ++i)
864 : {
865 8 : const FormulaToken* p = ppTokens[i];
866 8 : CPPUNIT_ASSERT_EQUAL(aCheckRPN2[i].meOp, p->GetOpCode());
867 8 : if (aCheckRPN[i].meOp == ocPush)
868 4 : CPPUNIT_ASSERT_EQUAL(static_cast<int>(aCheckRPN2[i].meType), static_cast<int>(p->GetType()));
869 2 : }
870 2 : }
871 2 : }
872 :
873 2 : void Test::testFormulaRefUpdate()
874 : {
875 2 : m_pDoc->InsertTab(0, "Formula");
876 :
877 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
878 :
879 2 : m_pDoc->SetValue(ScAddress(0,0,0), 2.0); // A1
880 2 : m_pDoc->SetString(ScAddress(2,2,0), "=A1"); // C3
881 2 : m_pDoc->SetString(ScAddress(2,3,0), "=$A$1"); // C4
882 :
883 2 : ScAddress aPos(2,2,0);
884 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
885 0 : CPPUNIT_FAIL("Wrong formula in C3.");
886 :
887 2 : aPos = ScAddress(2,3,0);
888 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
889 0 : CPPUNIT_FAIL("Wrong formula in C4.");
890 :
891 : // Delete row 2 to push formula cells up (to C2:C3).
892 2 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,1,0));
893 :
894 2 : aPos = ScAddress(2,1,0);
895 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
896 0 : CPPUNIT_FAIL("Wrong formula in C2.");
897 :
898 2 : aPos = ScAddress(2,2,0);
899 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
900 0 : CPPUNIT_FAIL("Wrong formula in C3.");
901 :
902 : // Insert one row at row 2 to move them back.
903 2 : m_pDoc->InsertRow(ScRange(0,1,0,MAXCOL,1,0));
904 :
905 2 : aPos = ScAddress(2,2,0);
906 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
907 0 : CPPUNIT_FAIL("Wrong formula in C3.");
908 :
909 2 : aPos = ScAddress(2,3,0);
910 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
911 0 : CPPUNIT_FAIL("Wrong formula in C4.");
912 :
913 : // Insert 2 rows at row 1 to shift all of A1 and C3:C4 down.
914 2 : m_pDoc->InsertRow(ScRange(0,0,0,MAXCOL,1,0));
915 :
916 2 : aPos = ScAddress(2,4,0);
917 2 : if (!checkFormula(*m_pDoc, aPos, "A3"))
918 0 : CPPUNIT_FAIL("Wrong formula in C5.");
919 :
920 2 : aPos = ScAddress(2,5,0);
921 2 : if (!checkFormula(*m_pDoc, aPos, "$A$3"))
922 0 : CPPUNIT_FAIL("Wrong formula in C6.");
923 :
924 : // Delete 2 rows at row 1 to shift them back.
925 2 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,1,0));
926 :
927 2 : aPos = ScAddress(2,2,0);
928 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
929 0 : CPPUNIT_FAIL("Wrong formula in C3.");
930 :
931 2 : aPos = ScAddress(2,3,0);
932 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
933 0 : CPPUNIT_FAIL("Wrong formula in C4.");
934 :
935 : // Insert 3 columns at column B. to shift C3:C4 to F3:F4.
936 2 : m_pDoc->InsertCol(ScRange(1,0,0,3,MAXROW,0));
937 :
938 2 : aPos = ScAddress(5,2,0);
939 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
940 0 : CPPUNIT_FAIL("Wrong formula in F3.");
941 :
942 2 : aPos = ScAddress(5,3,0);
943 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
944 0 : CPPUNIT_FAIL("Wrong formula in F4.");
945 :
946 : // Delete columns B:D to shift them back.
947 2 : m_pDoc->DeleteCol(ScRange(1,0,0,3,MAXROW,0));
948 :
949 2 : aPos = ScAddress(2,2,0);
950 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
951 0 : CPPUNIT_FAIL("Wrong formula in C3.");
952 :
953 2 : aPos = ScAddress(2,3,0);
954 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
955 0 : CPPUNIT_FAIL("Wrong formula in C4.");
956 :
957 : // Insert cells over A1:A3 to only shift A1 down to A4.
958 2 : m_pDoc->InsertRow(ScRange(0,0,0,0,2,0));
959 :
960 2 : aPos = ScAddress(2,2,0);
961 2 : if (!checkFormula(*m_pDoc, aPos, "A4"))
962 0 : CPPUNIT_FAIL("Wrong formula in C3.");
963 :
964 2 : aPos = ScAddress(2,3,0);
965 2 : if (!checkFormula(*m_pDoc, aPos, "$A$4"))
966 0 : CPPUNIT_FAIL("Wrong formula in C4.");
967 :
968 : // .. and back.
969 2 : m_pDoc->DeleteRow(ScRange(0,0,0,0,2,0));
970 :
971 2 : aPos = ScAddress(2,2,0);
972 2 : if (!checkFormula(*m_pDoc, aPos, "A1"))
973 0 : CPPUNIT_FAIL("Wrong formula in C3.");
974 :
975 2 : aPos = ScAddress(2,3,0);
976 2 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
977 0 : CPPUNIT_FAIL("Wrong formula in C4.");
978 :
979 : // Delete row 1 which will delete the value cell (A1).
980 2 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,0,0));
981 :
982 2 : aPos = ScAddress(2,1,0);
983 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
984 2 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
985 2 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
986 2 : aPos = ScAddress(2,2,0);
987 2 : pFC = m_pDoc->GetFormulaCell(aPos);
988 2 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
989 2 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
990 :
991 : // Clear all and start over.
992 2 : clearRange(m_pDoc, ScRange(0,0,0,10,10,0));
993 :
994 : // Test range updates
995 :
996 : // Fill B2:C3 with values.
997 2 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
998 2 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
999 2 : m_pDoc->SetValue(ScAddress(2,1,0), 3);
1000 2 : m_pDoc->SetValue(ScAddress(2,2,0), 4);
1001 :
1002 2 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM(B2:C3)");
1003 2 : m_pDoc->SetString(ScAddress(0,6,0), "=SUM($B$2:$C$3)");
1004 :
1005 2 : aPos = ScAddress(0,5,0);
1006 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
1007 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1008 :
1009 2 : aPos = ScAddress(0,6,0);
1010 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
1011 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1012 :
1013 : // Insert a row at row 1.
1014 2 : m_pDoc->InsertRow(ScRange(0,0,0,MAXCOL,0,0));
1015 :
1016 2 : aPos = ScAddress(0,6,0);
1017 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B3:C4)"))
1018 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1019 :
1020 2 : aPos = ScAddress(0,7,0);
1021 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$3:$C$4)"))
1022 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1023 :
1024 : // ... and back.
1025 2 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,0,0));
1026 :
1027 2 : aPos = ScAddress(0,5,0);
1028 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
1029 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1030 :
1031 2 : aPos = ScAddress(0,6,0);
1032 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
1033 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1034 :
1035 : // Insert columns B:C to shift only the value range.
1036 2 : m_pDoc->InsertCol(ScRange(1,0,0,2,MAXROW,0));
1037 :
1038 2 : aPos = ScAddress(0,5,0);
1039 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(D2:E3)"))
1040 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1041 :
1042 2 : aPos = ScAddress(0,6,0);
1043 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($D$2:$E$3)"))
1044 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1045 :
1046 : // ... and back.
1047 2 : m_pDoc->DeleteCol(ScRange(1,0,0,2,MAXROW,0));
1048 :
1049 2 : aPos = ScAddress(0,5,0);
1050 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
1051 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1052 :
1053 2 : aPos = ScAddress(0,6,0);
1054 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
1055 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1056 :
1057 : // Insert rows 5:6 to shift the formula cells only.
1058 2 : m_pDoc->InsertRow(ScRange(0,4,0,MAXCOL,5,0));
1059 :
1060 2 : aPos = ScAddress(0,7,0);
1061 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
1062 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1063 :
1064 2 : aPos = ScAddress(0,8,0);
1065 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
1066 0 : CPPUNIT_FAIL("Wrong formula in A9.");
1067 :
1068 : // ... and back.
1069 2 : m_pDoc->DeleteRow(ScRange(0,4,0,MAXCOL,5,0));
1070 :
1071 2 : aPos = ScAddress(0,5,0);
1072 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
1073 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1074 :
1075 2 : aPos = ScAddress(0,6,0);
1076 2 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
1077 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1078 :
1079 : // Check the values of the formula cells in A6:A7.
1080 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1081 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1082 :
1083 : // Insert cells over B1:B2 to partially shift value range.
1084 2 : m_pDoc->InsertRow(ScRange(1,0,0,1,1,0));
1085 :
1086 : // Check the values of the formula cells in A6:A7 again.
1087 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1088 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1089 :
1090 : // ... and shift them back.
1091 2 : m_pDoc->DeleteRow(ScRange(1,0,0,1,1,0));
1092 :
1093 : // The formula cell results should be back too.
1094 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1095 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1096 :
1097 : // Delete rows 2:3 to completely remove the referenced range.
1098 2 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,2,0));
1099 :
1100 : // Both A4 and A5 should show #REF! errors.
1101 2 : pFC = m_pDoc->GetFormulaCell(ScAddress(0,3,0));
1102 2 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1103 2 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
1104 :
1105 2 : pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
1106 2 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1107 2 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
1108 :
1109 2 : m_pDoc->DeleteTab(0);
1110 2 : }
1111 :
1112 2 : void Test::testFormulaRefUpdateRange()
1113 : {
1114 2 : m_pDoc->InsertTab(0, "Formula");
1115 :
1116 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1117 :
1118 2 : setExpandRefs(false);
1119 :
1120 : // Set values to B2:C5.
1121 2 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
1122 2 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
1123 2 : m_pDoc->SetValue(ScAddress(1,3,0), 3);
1124 2 : m_pDoc->SetValue(ScAddress(1,4,0), 4);
1125 2 : m_pDoc->SetValue(ScAddress(2,1,0), 5);
1126 2 : m_pDoc->SetValue(ScAddress(2,2,0), 6);
1127 2 : m_pDoc->SetValue(ScAddress(2,3,0), 7);
1128 2 : m_pDoc->SetValue(ScAddress(2,4,0), 8);
1129 :
1130 : // Set formula cells to A7 and A8.
1131 2 : m_pDoc->SetString(ScAddress(0,6,0), "=SUM(B2:C5)");
1132 2 : m_pDoc->SetString(ScAddress(0,7,0), "=SUM($B$2:$C$5)");
1133 :
1134 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM(B2:C5)"))
1135 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1136 :
1137 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM($B$2:$C$5)"))
1138 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1139 :
1140 2 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1141 2 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1142 :
1143 : // Delete row 3. This should shrink the range references by one row.
1144 2 : m_pDoc->DeleteRow(ScRange(0,2,0,MAXCOL,2,0));
1145 :
1146 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM(B2:C4)"))
1147 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1148 :
1149 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM($B$2:$C$4)"))
1150 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1151 :
1152 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1153 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1154 :
1155 : // Delete row 4 - bottom of range
1156 2 : m_pDoc->DeleteRow(ScRange(0,3,0,MAXCOL,3,0));
1157 :
1158 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(B2:C3)"))
1159 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1160 :
1161 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($B$2:$C$3)"))
1162 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1163 :
1164 2 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1165 2 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1166 :
1167 : // Delete row 2 - top of range
1168 2 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,1,0));
1169 :
1170 2 : if (!checkFormula(*m_pDoc, ScAddress(0,3,0), "SUM(B2:C2)"))
1171 0 : CPPUNIT_FAIL("Wrong formula in A4.");
1172 :
1173 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM($B$2:$C$2)"))
1174 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1175 :
1176 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1177 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1178 :
1179 : // Clear the range and start over.
1180 2 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1181 :
1182 : // Fill C2:F3 with values.
1183 2 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
1184 2 : m_pDoc->SetValue(ScAddress(3,1,0), 2);
1185 2 : m_pDoc->SetValue(ScAddress(4,1,0), 3);
1186 2 : m_pDoc->SetValue(ScAddress(5,1,0), 4);
1187 2 : m_pDoc->SetValue(ScAddress(2,2,0), 5);
1188 2 : m_pDoc->SetValue(ScAddress(3,2,0), 6);
1189 2 : m_pDoc->SetValue(ScAddress(4,2,0), 7);
1190 2 : m_pDoc->SetValue(ScAddress(5,2,0), 8);
1191 :
1192 : // Set formulas to A2 and A3.
1193 2 : m_pDoc->SetString(ScAddress(0,1,0), "=SUM(C2:F3)");
1194 2 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM($C$2:$F$3)");
1195 :
1196 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:F3)"))
1197 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1198 :
1199 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$F$3)"))
1200 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1201 :
1202 2 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1203 2 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1204 :
1205 : // Delete column D.
1206 2 : m_pDoc->DeleteCol(ScRange(3,0,0,3,MAXROW,0));
1207 :
1208 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:E3)"))
1209 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1210 :
1211 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$E$3)"))
1212 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1213 :
1214 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1215 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1216 :
1217 : // Delete column E - the right edge of reference range.
1218 2 : m_pDoc->DeleteCol(ScRange(4,0,0,4,MAXROW,0));
1219 :
1220 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:D3)"))
1221 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1222 :
1223 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$D$3)"))
1224 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1225 :
1226 2 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1227 2 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1228 :
1229 : // Delete column C - the left edge of reference range.
1230 2 : m_pDoc->DeleteCol(ScRange(2,0,0,2,MAXROW,0));
1231 :
1232 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:C3)"))
1233 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1234 :
1235 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$C$3)"))
1236 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1237 :
1238 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1239 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1240 :
1241 : // Clear the range and start over.
1242 2 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1243 :
1244 : // Disable expansion of range reference on insertion in adjacent areas.
1245 2 : setExpandRefs(false);
1246 :
1247 : // Fill C2:D3 with values.
1248 2 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
1249 2 : m_pDoc->SetValue(ScAddress(3,1,0), 2);
1250 2 : m_pDoc->SetValue(ScAddress(2,2,0), 3);
1251 2 : m_pDoc->SetValue(ScAddress(3,2,0), 4);
1252 :
1253 : // Set formulas at A5 and A6.
1254 2 : m_pDoc->SetString(ScAddress(0,4,0), "=SUM(C2:D3)");
1255 2 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM($C$2:$D$3)");
1256 :
1257 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:D3)"))
1258 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1259 :
1260 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$D$3)"))
1261 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1262 :
1263 : // Insert a column at column C. This should simply shift the reference without expansion.
1264 2 : m_pDoc->InsertCol(ScRange(2,0,0,2,MAXROW,0));
1265 :
1266 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(D2:E3)"))
1267 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1268 :
1269 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($D$2:$E$3)"))
1270 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1271 :
1272 : // Shift it back.
1273 2 : m_pDoc->DeleteCol(ScRange(2,0,0,2,MAXROW,0));
1274 :
1275 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:D3)"))
1276 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1277 :
1278 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$D$3)"))
1279 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1280 :
1281 : // Insert at column D. This should expand the reference by one column length.
1282 2 : m_pDoc->InsertCol(ScRange(3,0,0,3,MAXROW,0));
1283 :
1284 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:E3)"))
1285 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1286 :
1287 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$E$3)"))
1288 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1289 :
1290 : // Insert at column F. No expansion should occur since the edge expansion is turned off.
1291 2 : m_pDoc->InsertCol(ScRange(5,0,0,5,MAXROW,0));
1292 :
1293 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:E3)"))
1294 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1295 :
1296 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$E$3)"))
1297 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1298 :
1299 : // Insert at row 2. No expansion should occur with edge expansion turned off.
1300 2 : m_pDoc->InsertRow(ScRange(0,1,0,MAXCOL,1,0));
1301 :
1302 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM(C3:E4)"))
1303 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1304 :
1305 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM($C$3:$E$4)"))
1306 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1307 :
1308 : // Insert at row 4 to expand the reference range.
1309 2 : m_pDoc->InsertRow(ScRange(0,3,0,MAXCOL,3,0));
1310 :
1311 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM(C3:E5)"))
1312 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1313 :
1314 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM($C$3:$E$5)"))
1315 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1316 :
1317 : // Insert at row 6. No expansion with edge expansion turned off.
1318 2 : m_pDoc->InsertRow(ScRange(0,5,0,MAXCOL,5,0));
1319 :
1320 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM(C3:E5)"))
1321 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1322 :
1323 2 : if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "SUM($C$3:$E$5)"))
1324 0 : CPPUNIT_FAIL("Wrong formula in A9.");
1325 :
1326 : // Clear the range and start over.
1327 2 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1328 :
1329 : // Turn edge expansion on.
1330 2 : setExpandRefs(true);
1331 :
1332 : // Fill C6:D7 with values.
1333 2 : m_pDoc->SetValue(ScAddress(2,5,0), 1);
1334 2 : m_pDoc->SetValue(ScAddress(2,6,0), 2);
1335 2 : m_pDoc->SetValue(ScAddress(3,5,0), 3);
1336 2 : m_pDoc->SetValue(ScAddress(3,6,0), 4);
1337 :
1338 : // Set formulas at A2 and A3.
1339 2 : m_pDoc->SetString(ScAddress(0,1,0), "=SUM(C6:D7)");
1340 2 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM($C$6:$D$7)");
1341 :
1342 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:D7)"))
1343 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1344 :
1345 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$D$7)"))
1346 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1347 :
1348 : // Insert at column E. This should expand the reference range by one column.
1349 2 : m_pDoc->InsertCol(ScRange(4,0,0,4,MAXROW,0));
1350 :
1351 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:E7)"))
1352 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1353 :
1354 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$E$7)"))
1355 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1356 :
1357 : // Insert at column C to edge-expand the reference range.
1358 2 : m_pDoc->InsertCol(ScRange(2,0,0,2,MAXROW,0));
1359 :
1360 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F7)"))
1361 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1362 :
1363 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$7)"))
1364 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1365 :
1366 : // Insert at row 8 to edge-expand.
1367 2 : m_pDoc->InsertRow(ScRange(0,7,0,MAXCOL,7,0));
1368 :
1369 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F8)"))
1370 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1371 :
1372 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$8)"))
1373 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1374 :
1375 : // Insert at row 6 to edge-expand.
1376 2 : m_pDoc->InsertRow(ScRange(0,5,0,MAXCOL,5,0));
1377 :
1378 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F9)"))
1379 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1380 :
1381 2 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$9)"))
1382 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1383 :
1384 2 : m_pDoc->DeleteTab(0);
1385 2 : }
1386 :
1387 2 : void Test::testFormulaRefUpdateSheets()
1388 : {
1389 2 : m_pDoc->InsertTab(0, "Sheet1");
1390 2 : m_pDoc->InsertTab(1, "Sheet2");
1391 :
1392 2 : OUString aName;
1393 2 : m_pDoc->GetName(0, aName);
1394 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1395 2 : m_pDoc->GetName(1, aName);
1396 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1397 :
1398 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1399 :
1400 : // Set values to B2:C3 on sheet Sheet1.
1401 2 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
1402 2 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
1403 2 : m_pDoc->SetValue(ScAddress(2,1,0), 3);
1404 2 : m_pDoc->SetValue(ScAddress(2,2,0), 4);
1405 :
1406 : // Set formulas to B2 and B3 on sheet Sheet2.
1407 2 : m_pDoc->SetString(ScAddress(1,1,1), "=SUM(Sheet1.B2:C3)");
1408 2 : m_pDoc->SetString(ScAddress(1,2,1), "=SUM($Sheet1.$B$2:$C$3)");
1409 :
1410 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1411 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1412 :
1413 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1414 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1415 :
1416 : // Swap the sheets.
1417 2 : m_pDoc->MoveTab(0, 1);
1418 2 : m_pDoc->GetName(0, aName);
1419 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1420 2 : m_pDoc->GetName(1, aName);
1421 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1422 :
1423 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(Sheet1.B2:C3)"))
1424 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1425 :
1426 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($Sheet1.$B$2:$C$3)"))
1427 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1428 :
1429 : // Swap back.
1430 2 : m_pDoc->MoveTab(0, 1);
1431 2 : m_pDoc->GetName(0, aName);
1432 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1433 2 : m_pDoc->GetName(1, aName);
1434 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1435 :
1436 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1437 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1438 :
1439 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1440 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1441 :
1442 : // Insert a new sheet between the two.
1443 2 : m_pDoc->InsertTab(1, "Temp");
1444 :
1445 2 : m_pDoc->GetName(1, aName);
1446 2 : CPPUNIT_ASSERT_EQUAL(OUString("Temp"), aName);
1447 2 : m_pDoc->GetName(2, aName);
1448 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1449 :
1450 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1451 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1452 :
1453 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1454 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1455 :
1456 : // Move the last sheet (Sheet2) to the first position.
1457 2 : m_pDoc->MoveTab(2, 0);
1458 :
1459 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(Sheet1.B2:C3)"))
1460 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1461 :
1462 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($Sheet1.$B$2:$C$3)"))
1463 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1464 :
1465 : // Move back.
1466 2 : m_pDoc->MoveTab(0, 2);
1467 :
1468 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1469 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1470 :
1471 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1472 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1473 :
1474 : // Move the "Temp" sheet to the last position.
1475 2 : m_pDoc->MoveTab(1, 2);
1476 :
1477 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1478 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1479 :
1480 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1481 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1482 :
1483 : // Move back.
1484 2 : m_pDoc->MoveTab(2, 1);
1485 :
1486 : // Delete the temporary sheet.
1487 2 : m_pDoc->DeleteTab(1);
1488 :
1489 2 : m_pDoc->GetName(1, aName);
1490 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1491 :
1492 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1493 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1494 :
1495 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1496 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1497 :
1498 : // Insert a new sheet before the first one.
1499 2 : m_pDoc->InsertTab(0, "Temp");
1500 :
1501 2 : m_pDoc->GetName(1, aName);
1502 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1503 2 : m_pDoc->GetName(2, aName);
1504 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1505 :
1506 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1507 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1508 :
1509 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1510 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1511 :
1512 : // Delete the temporary sheet.
1513 2 : m_pDoc->DeleteTab(0);
1514 :
1515 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1516 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1517 :
1518 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1519 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1520 :
1521 : // Append a bunch of sheets.
1522 2 : m_pDoc->InsertTab(2, "Temp1");
1523 2 : m_pDoc->InsertTab(3, "Temp2");
1524 2 : m_pDoc->InsertTab(4, "Temp3");
1525 :
1526 : // Move these tabs around. This shouldn't affects the first 2 sheets.
1527 2 : m_pDoc->MoveTab(2, 4);
1528 2 : m_pDoc->MoveTab(3, 2);
1529 :
1530 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1531 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1532 :
1533 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1534 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1535 :
1536 : // Delete the temp sheets.
1537 2 : m_pDoc->DeleteTab(4);
1538 2 : m_pDoc->DeleteTab(3);
1539 2 : m_pDoc->DeleteTab(2);
1540 :
1541 : // Delete Sheet1.
1542 2 : m_pDoc->DeleteTab(0);
1543 2 : m_pDoc->GetName(0, aName);
1544 2 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1545 :
1546 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(#REF!.B2:C3)"))
1547 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1548 :
1549 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($#REF!.$B$2:$C$3)"))
1550 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1551 :
1552 4 : m_pDoc->DeleteTab(0);
1553 2 : }
1554 :
1555 2 : void Test::testFormulaRefUpdateInsertRows()
1556 : {
1557 2 : setExpandRefs(false);
1558 :
1559 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1560 2 : m_pDoc->InsertTab(0, "Formula");
1561 :
1562 : // Insert raw values in B2:B4.
1563 2 : m_pDoc->SetValue(ScAddress(1,1,0), 1.0);
1564 2 : m_pDoc->SetValue(ScAddress(1,2,0), 2.0);
1565 2 : m_pDoc->SetValue(ScAddress(1,3,0), 3.0);
1566 :
1567 : // Insert a formula in B5 to sum up B2:B4.
1568 2 : m_pDoc->SetString(ScAddress(1,4,0), "=SUM(B2:B4)");
1569 :
1570 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1571 :
1572 : // Insert rows over rows 1:2.
1573 4 : ScMarkData aMark;
1574 2 : aMark.SelectOneTable(0);
1575 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1576 2 : rFunc.InsertCells(ScRange(0,0,0,MAXCOL,1,0), &aMark, INS_INSROWS, false, true, false);
1577 :
1578 : // The raw data should have shifted to B4:B6.
1579 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1580 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1581 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,5,0)));
1582 :
1583 2 : if (!checkFormula(*m_pDoc, ScAddress(1,6,0), "SUM(B4:B6)"))
1584 0 : CPPUNIT_FAIL("Wrong formula!");
1585 :
1586 : // Clear and start over.
1587 2 : clearSheet(m_pDoc, 0);
1588 :
1589 : // Set raw values in A4:A6.
1590 2 : m_pDoc->SetValue(ScAddress(0,3,0), 1.0);
1591 2 : m_pDoc->SetValue(ScAddress(0,4,0), 2.0);
1592 2 : m_pDoc->SetValue(ScAddress(0,5,0), 3.0);
1593 :
1594 : // Set formula in A3 to reference A4:A6.
1595 2 : m_pDoc->SetString(ScAddress(0,2,0), "=MAX(A4:A6)");
1596 :
1597 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1598 :
1599 : // Insert 3 rows over 2:4. This should push A3:A6 to A6:A9.
1600 2 : rFunc.InsertCells(ScRange(0,1,0,MAXCOL,3,0), &aMark, INS_INSROWS, false, true, false);
1601 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,5,0));
1602 2 : CPPUNIT_ASSERT(pFC);
1603 2 : CPPUNIT_ASSERT_MESSAGE("This formula cell should not be an error.", pFC->GetErrCode() == 0);
1604 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1605 :
1606 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "MAX(A7:A9)"))
1607 0 : CPPUNIT_FAIL("Wrong formula!");
1608 :
1609 4 : m_pDoc->DeleteTab(0);
1610 2 : }
1611 :
1612 2 : void Test::testFormulaRefUpdateInsertColumns()
1613 : {
1614 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1615 2 : setExpandRefs(false);
1616 :
1617 2 : m_pDoc->InsertTab(0, "Formula");
1618 :
1619 : // Set values in B1:B3.
1620 2 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0);
1621 2 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0);
1622 2 : m_pDoc->SetValue(ScAddress(1,2,0), 3.0);
1623 :
1624 : // Reference them in B4.
1625 2 : m_pDoc->SetString(ScAddress(1,3,0), "=SUM(B1:B3)");
1626 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1627 :
1628 : // Inert columns over A:B.
1629 4 : ScMarkData aMark;
1630 2 : aMark.SelectOneTable(0);
1631 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1632 2 : rFunc.InsertCells(ScRange(0,0,0,1,MAXROW,0), &aMark, INS_INSCOLS, false, true, false);
1633 :
1634 : // Now, the original column B has moved to column D.
1635 2 : if (!checkFormula(*m_pDoc, ScAddress(3,3,0), "SUM(D1:D3)"))
1636 0 : CPPUNIT_FAIL("Wrong formula in D4 after column insertion.");
1637 :
1638 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1639 :
1640 4 : m_pDoc->DeleteTab(0);
1641 2 : }
1642 :
1643 2 : void Test::testFormulaRefUpdateMove()
1644 : {
1645 2 : m_pDoc->InsertTab(0, "Sheet1");
1646 :
1647 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1648 :
1649 : // Set value to B4:B6.
1650 2 : m_pDoc->SetValue(ScAddress(1,3,0), 1);
1651 2 : m_pDoc->SetValue(ScAddress(1,4,0), 2);
1652 2 : m_pDoc->SetValue(ScAddress(1,5,0), 3);
1653 :
1654 : // Set formulas to A9:A12 that references B4:B6.
1655 2 : m_pDoc->SetString(ScAddress(0,8,0), "=SUM(B4:B6)");
1656 2 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM($B$4:$B$6)");
1657 2 : m_pDoc->SetString(ScAddress(0,10,0), "=B5");
1658 2 : m_pDoc->SetString(ScAddress(0,11,0), "=$B$6");
1659 :
1660 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,8,0));
1661 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,9,0));
1662 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(0,10,0));
1663 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(0,11,0));
1664 :
1665 : // Move B4:B6 to D4 (two columsn to the right).
1666 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1667 2 : bool bMoved = rFunc.MoveBlock(ScRange(1,3,0,1,5,0), ScAddress(3,3,0), true, false, false, false);
1668 2 : CPPUNIT_ASSERT_MESSAGE("Failed to move B4:B6.", bMoved);
1669 :
1670 : // The results of the formula cells that reference the moved range should remain the same.
1671 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,8,0));
1672 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,9,0));
1673 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(0,10,0));
1674 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(0,11,0));
1675 :
1676 2 : if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "SUM(D4:D6)"))
1677 0 : CPPUNIT_FAIL("Wrong formula.");
1678 :
1679 2 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM($D$4:$D$6)"))
1680 0 : CPPUNIT_FAIL("Wrong formula.");
1681 :
1682 2 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "D5"))
1683 0 : CPPUNIT_FAIL("Wrong formula.");
1684 :
1685 2 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "$D$6"))
1686 0 : CPPUNIT_FAIL("Wrong formula.");
1687 :
1688 : // Move A9:A12 to B10:B13.
1689 2 : bMoved = rFunc.MoveBlock(ScRange(0,8,0,0,11,0), ScAddress(1,9,0), true, false, false, false);
1690 2 : CPPUNIT_ASSERT_MESSAGE("Failed to move A9:A12 to B10:B13", bMoved);
1691 :
1692 : // The results of these formula cells should still stay the same.
1693 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(1,9,0));
1694 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(1,10,0));
1695 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1,11,0));
1696 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,12,0));
1697 :
1698 : // Displayed formulas should stay the same since the referenced range hasn't moved.
1699 2 : if (!checkFormula(*m_pDoc, ScAddress(1,9,0), "SUM(D4:D6)"))
1700 0 : CPPUNIT_FAIL("Wrong formula.");
1701 :
1702 2 : if (!checkFormula(*m_pDoc, ScAddress(1,10,0), "SUM($D$4:$D$6)"))
1703 0 : CPPUNIT_FAIL("Wrong formula.");
1704 :
1705 2 : if (!checkFormula(*m_pDoc, ScAddress(1,11,0), "D5"))
1706 0 : CPPUNIT_FAIL("Wrong formula.");
1707 :
1708 2 : if (!checkFormula(*m_pDoc, ScAddress(1,12,0), "$D$6"))
1709 0 : CPPUNIT_FAIL("Wrong formula.");
1710 :
1711 : // The value cells are in D4:D6. Move D4:D5 to the right but leave D6
1712 : // where it is.
1713 2 : bMoved = rFunc.MoveBlock(ScRange(3,3,0,3,4,0), ScAddress(4,3,0), true, false, false, false);
1714 2 : CPPUNIT_ASSERT_MESSAGE("Failed to move D4:D5 to E4:E5", bMoved);
1715 :
1716 : // Only the values of B10 and B11 should be updated.
1717 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,9,0));
1718 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,10,0));
1719 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1,11,0));
1720 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,12,0));
1721 :
1722 2 : if (!checkFormula(*m_pDoc, ScAddress(1,9,0), "SUM(D4:D6)"))
1723 0 : CPPUNIT_FAIL("Wrong formula.");
1724 :
1725 2 : if (!checkFormula(*m_pDoc, ScAddress(1,10,0), "SUM($D$4:$D$6)"))
1726 0 : CPPUNIT_FAIL("Wrong formula.");
1727 :
1728 2 : if (!checkFormula(*m_pDoc, ScAddress(1,11,0), "E5"))
1729 0 : CPPUNIT_FAIL("Wrong formula.");
1730 :
1731 2 : if (!checkFormula(*m_pDoc, ScAddress(1,12,0), "$D$6"))
1732 0 : CPPUNIT_FAIL("Wrong formula.");
1733 :
1734 2 : m_pDoc->DeleteTab(0);
1735 2 : }
1736 :
1737 2 : void Test::testFormulaRefUpdateMoveUndo()
1738 : {
1739 2 : m_pDoc->InsertTab(0, "Test");
1740 :
1741 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1742 :
1743 : // Set values in A1:A4.
1744 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1745 2 : m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1746 2 : m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1747 2 : m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
1748 :
1749 : // Set formulas with single cell references in A6:A8.
1750 2 : m_pDoc->SetString(ScAddress(0,5,0), "=A1");
1751 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1752 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A1"))
1753 0 : CPPUNIT_FAIL("Wrong formula.");
1754 :
1755 2 : m_pDoc->SetString(ScAddress(0,6,0), "=A1+A2+A3");
1756 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1757 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "A1+A2+A3"))
1758 0 : CPPUNIT_FAIL("Wrong formula.");
1759 :
1760 2 : m_pDoc->SetString(ScAddress(0,7,0), "=A1+A3+A4");
1761 2 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1762 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "A1+A3+A4"))
1763 0 : CPPUNIT_FAIL("Wrong formula.");
1764 :
1765 : // Set formulas with range references in A10:A12.
1766 2 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM(A1:A2)");
1767 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1768 2 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(A1:A2)"))
1769 0 : CPPUNIT_FAIL("Wrong formula.");
1770 :
1771 2 : m_pDoc->SetString(ScAddress(0,10,0), "=SUM(A1:A3)");
1772 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1773 2 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(A1:A3)"))
1774 0 : CPPUNIT_FAIL("Wrong formula.");
1775 :
1776 2 : m_pDoc->SetString(ScAddress(0,11,0), "=SUM(A1:A4)");
1777 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1778 2 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1779 0 : CPPUNIT_FAIL("Wrong formula.");
1780 :
1781 : // Move A1:A3 to C1:C3. Note that A4 remains.
1782 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1783 2 : bool bMoved = rFunc.MoveBlock(ScRange(0,0,0,0,2,0), ScAddress(2,0,0), true, true, false, true);
1784 2 : CPPUNIT_ASSERT(bMoved);
1785 :
1786 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1787 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "C1"))
1788 0 : CPPUNIT_FAIL("Wrong formula.");
1789 :
1790 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1791 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "C1+C2+C3"))
1792 0 : CPPUNIT_FAIL("Wrong formula.");
1793 :
1794 2 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1795 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "C1+C3+A4"))
1796 0 : CPPUNIT_FAIL("Wrong formula.");
1797 :
1798 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1799 2 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(C1:C2)"))
1800 0 : CPPUNIT_FAIL("Wrong formula.");
1801 :
1802 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1803 2 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(C1:C3)"))
1804 0 : CPPUNIT_FAIL("Wrong formula.");
1805 :
1806 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1807 2 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1808 0 : CPPUNIT_FAIL("Wrong formula.");
1809 :
1810 : // Undo the move.
1811 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1812 2 : CPPUNIT_ASSERT(pUndoMgr);
1813 2 : pUndoMgr->Undo();
1814 :
1815 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1816 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A1"))
1817 0 : CPPUNIT_FAIL("Wrong formula.");
1818 :
1819 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1820 2 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "A1+A2+A3"))
1821 0 : CPPUNIT_FAIL("Wrong formula.");
1822 :
1823 2 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1824 2 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "A1+A3+A4"))
1825 0 : CPPUNIT_FAIL("Wrong formula.");
1826 :
1827 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1828 2 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(A1:A2)"))
1829 0 : CPPUNIT_FAIL("Wrong formula.");
1830 :
1831 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1832 2 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(A1:A3)"))
1833 0 : CPPUNIT_FAIL("Wrong formula.");
1834 :
1835 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1836 2 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1837 0 : CPPUNIT_FAIL("Wrong formula.");
1838 :
1839 : // Make sure the broadcasters are still valid by changing the value of A1.
1840 2 : m_pDoc->SetValue(ScAddress(0,0,0), 20);
1841 :
1842 2 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1843 2 : CPPUNIT_ASSERT_EQUAL(25.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1844 2 : CPPUNIT_ASSERT_EQUAL(27.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1845 :
1846 2 : CPPUNIT_ASSERT_EQUAL(22.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1847 2 : CPPUNIT_ASSERT_EQUAL(25.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1848 2 : CPPUNIT_ASSERT_EQUAL(29.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1849 :
1850 2 : m_pDoc->DeleteTab(0);
1851 2 : }
1852 :
1853 2 : void Test::testFormulaRefUpdateMoveToSheet()
1854 : {
1855 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1856 :
1857 2 : m_pDoc->InsertTab(0, "Sheet1");
1858 2 : m_pDoc->InsertTab(1, "Sheet2");
1859 :
1860 : // Set values to A1:A2 on Sheet1, and B1:B2 to reference them.
1861 2 : m_pDoc->SetValue(ScAddress(0,0,0), 11);
1862 2 : m_pDoc->SetValue(ScAddress(0,1,0), 12);
1863 2 : m_pDoc->SetString(ScAddress(1,0,0), "=A1");
1864 2 : m_pDoc->SetString(ScAddress(1,1,0), "=A2");
1865 :
1866 2 : if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A1"))
1867 0 : CPPUNIT_FAIL("Wrong formula");
1868 :
1869 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A2"))
1870 0 : CPPUNIT_FAIL("Wrong formula");
1871 :
1872 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1873 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1874 :
1875 : // Move A1:A2 on Sheet1 to B3:B4 on Sheet2.
1876 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1877 2 : bool bMoved = rFunc.MoveBlock(ScRange(0,0,0,0,1,0), ScAddress(1,2,1), true, true, false, true);
1878 2 : CPPUNIT_ASSERT(bMoved);
1879 :
1880 2 : if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "Sheet2.B3"))
1881 0 : CPPUNIT_FAIL("Wrong formula");
1882 :
1883 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "Sheet2.B4"))
1884 0 : CPPUNIT_FAIL("Wrong formula");
1885 :
1886 : // Undo and check again.
1887 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1888 2 : pUndoMgr->Undo();
1889 :
1890 2 : if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A1"))
1891 0 : CPPUNIT_FAIL("Wrong formula");
1892 :
1893 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A2"))
1894 0 : CPPUNIT_FAIL("Wrong formula");
1895 :
1896 : // Redo and check.
1897 2 : pUndoMgr->Redo();
1898 :
1899 2 : if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "Sheet2.B3"))
1900 0 : CPPUNIT_FAIL("Wrong formula");
1901 :
1902 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "Sheet2.B4"))
1903 0 : CPPUNIT_FAIL("Wrong formula");
1904 :
1905 2 : m_pDoc->DeleteTab(1);
1906 2 : m_pDoc->DeleteTab(0);
1907 2 : }
1908 :
1909 2 : void Test::testFormulaRefUpdateDeleteContent()
1910 : {
1911 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1912 :
1913 2 : m_pDoc->InsertTab(0, "Test");
1914 :
1915 : // Set value in B2.
1916 2 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0);
1917 : // Set formula in C2 to reference B2.
1918 2 : m_pDoc->SetString(ScAddress(2,1,0), "=B2");
1919 :
1920 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1921 :
1922 : // Delete B2.
1923 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1924 4 : ScMarkData aMark;
1925 2 : aMark.SetMarkArea(ScAddress(1,1,0));
1926 2 : rFunc.DeleteContents(aMark, IDF_CONTENTS, true, true);
1927 :
1928 2 : CPPUNIT_ASSERT_MESSAGE("B2 should be empty.", m_pDoc->GetCellType(ScAddress(1,1,0)) == CELLTYPE_NONE);
1929 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1930 :
1931 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1932 2 : CPPUNIT_ASSERT(pUndoMgr);
1933 :
1934 : // Undo and check the result of C2.
1935 2 : pUndoMgr->Undo();
1936 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0))); // B2
1937 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,1,0))); // C2
1938 :
1939 : // Redo and check.
1940 2 : pUndoMgr->Redo();
1941 2 : CPPUNIT_ASSERT_MESSAGE("B2 should be empty.", m_pDoc->GetCellType(ScAddress(1,1,0)) == CELLTYPE_NONE);
1942 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1943 :
1944 4 : m_pDoc->DeleteTab(0);
1945 2 : }
1946 :
1947 2 : void Test::testFormulaRefUpdateDeleteAndShiftLeft()
1948 : {
1949 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1950 :
1951 2 : m_pDoc->InsertTab(0, "Test");
1952 :
1953 : // Insert 1,2,3,4,5 in C1:G1.
1954 12 : for (SCCOL i = 0; i <= 4; ++i)
1955 10 : m_pDoc->SetValue(ScAddress(i+2,0,0), i+1);
1956 :
1957 : // Insert formula in H1.
1958 2 : ScAddress aPos(7,0,0);
1959 2 : m_pDoc->SetString(aPos, "=SUM(C1:G1)");
1960 :
1961 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
1962 :
1963 : // Delete columns D:E (middle of the reference).
1964 4 : ScMarkData aMark;
1965 2 : aMark.SelectOneTable(0);
1966 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1967 2 : bool bDeleted = rFunc.DeleteCells(ScRange(3,0,0,4,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
1968 2 : CPPUNIT_ASSERT(bDeleted);
1969 :
1970 2 : aPos.IncCol(-2);
1971 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(aPos));
1972 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:E1)"))
1973 0 : CPPUNIT_FAIL("Wrong formula!");
1974 :
1975 : // Undo and check.
1976 2 : SfxUndoManager* pUndo = m_pDoc->GetUndoManager();
1977 2 : CPPUNIT_ASSERT(pUndo);
1978 :
1979 2 : pUndo->Undo();
1980 2 : aPos.IncCol(2);
1981 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
1982 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:G1)"))
1983 0 : CPPUNIT_FAIL("Wrong formula!");
1984 :
1985 : // Delete columns C:D (left end of the reference).
1986 2 : bDeleted = rFunc.DeleteCells(ScRange(2,0,0,3,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
1987 2 : CPPUNIT_ASSERT(bDeleted);
1988 :
1989 2 : aPos.IncCol(-2);
1990 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(aPos));
1991 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:E1)"))
1992 0 : CPPUNIT_FAIL("Wrong formula!");
1993 :
1994 : // Undo and check again.
1995 2 : pUndo->Undo();
1996 2 : aPos.IncCol(2);
1997 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
1998 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:G1)"))
1999 0 : CPPUNIT_FAIL("Wrong formula!");
2000 :
2001 : // Delete columns B:E (overlaps on the left).
2002 2 : bDeleted = rFunc.DeleteCells(ScRange(1,0,0,4,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
2003 2 : CPPUNIT_ASSERT(bDeleted);
2004 :
2005 2 : aPos.IncCol(-4);
2006 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(aPos));
2007 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(B1:C1)"))
2008 0 : CPPUNIT_FAIL("Wrong formula!");
2009 :
2010 : // Undo and check again.
2011 2 : pUndo->Undo();
2012 2 : aPos.IncCol(4);
2013 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
2014 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:G1)"))
2015 0 : CPPUNIT_FAIL("Wrong formula!");
2016 :
2017 : // Start over with a new scenario.
2018 2 : clearSheet(m_pDoc, 0);
2019 :
2020 : // Insert 1,2,3,4,5,6 into C1:H1.
2021 14 : for (SCCOL i = 0; i <= 5; ++i)
2022 12 : m_pDoc->SetValue(ScAddress(i+2,0,0), i+1);
2023 :
2024 : // Set formula in B1.
2025 2 : aPos = ScAddress(1,0,0);
2026 2 : m_pDoc->SetString(aPos, "=SUM(C1:H1)");
2027 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2028 :
2029 : // Delete columns F:H (right end of the reference).
2030 2 : bDeleted = rFunc.DeleteCells(ScRange(5,0,0,7,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
2031 2 : CPPUNIT_ASSERT(bDeleted);
2032 :
2033 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(aPos));
2034 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:E1)"))
2035 0 : CPPUNIT_FAIL("Wrong formula!");
2036 :
2037 : // Undo and check.
2038 2 : pUndo->Undo();
2039 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2040 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:H1)"))
2041 0 : CPPUNIT_FAIL("Wrong formula!");
2042 :
2043 : // Delete columns G:I (overlaps on the right).
2044 2 : bDeleted = rFunc.DeleteCells(ScRange(6,0,0,8,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
2045 2 : CPPUNIT_ASSERT(bDeleted);
2046 :
2047 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(aPos));
2048 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:F1)"))
2049 0 : CPPUNIT_FAIL("Wrong formula!");
2050 :
2051 : // Undo and check again.
2052 2 : pUndo->Undo();
2053 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2054 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(C1:H1)"))
2055 0 : CPPUNIT_FAIL("Wrong formula!");
2056 :
2057 4 : m_pDoc->DeleteTab(0);
2058 2 : }
2059 :
2060 2 : void Test::testFormulaRefUpdateDeleteAndShiftUp()
2061 : {
2062 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2063 :
2064 2 : m_pDoc->InsertTab(0, "Test");
2065 :
2066 : // Insert 1,2,3,4,5 in A3:A7.
2067 12 : for (SCROW i = 0; i <= 4; ++i)
2068 10 : m_pDoc->SetValue(ScAddress(0,i+2,0), i+1);
2069 :
2070 : // Insert formula in A8.
2071 2 : ScAddress aPos(0,7,0);
2072 2 : m_pDoc->SetString(aPos, "=SUM(A3:A7)");
2073 :
2074 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
2075 :
2076 : // Delete rows 4:5 (middle of the reference).
2077 4 : ScMarkData aMark;
2078 2 : aMark.SelectOneTable(0);
2079 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2080 2 : bool bDeleted = rFunc.DeleteCells(ScRange(0,3,0,MAXCOL,4,0), &aMark, DEL_CELLSUP, true, true);
2081 2 : CPPUNIT_ASSERT(bDeleted);
2082 :
2083 2 : aPos.IncRow(-2);
2084 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(aPos));
2085 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A5)"))
2086 0 : CPPUNIT_FAIL("Wrong formula!");
2087 :
2088 : // Undo and check.
2089 2 : SfxUndoManager* pUndo = m_pDoc->GetUndoManager();
2090 2 : CPPUNIT_ASSERT(pUndo);
2091 :
2092 2 : pUndo->Undo();
2093 2 : aPos.IncRow(2);
2094 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
2095 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A7)"))
2096 0 : CPPUNIT_FAIL("Wrong formula!");
2097 :
2098 : // Delete rows 3:4 (top end of the reference).
2099 2 : bDeleted = rFunc.DeleteCells(ScRange(0,2,0,MAXCOL,3,0), &aMark, DEL_CELLSUP, true, true);
2100 2 : CPPUNIT_ASSERT(bDeleted);
2101 :
2102 2 : aPos.IncRow(-2);
2103 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(aPos));
2104 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A5)"))
2105 0 : CPPUNIT_FAIL("Wrong formula!");
2106 :
2107 : // Undo and check again.
2108 2 : pUndo->Undo();
2109 2 : aPos.IncRow(2);
2110 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
2111 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A7)"))
2112 0 : CPPUNIT_FAIL("Wrong formula!");
2113 :
2114 : // Delete rows 2:5 (overlaps on the top).
2115 2 : bDeleted = rFunc.DeleteCells(ScRange(0,1,0,MAXCOL,4,0), &aMark, DEL_CELLSUP, true, true);
2116 2 : CPPUNIT_ASSERT(bDeleted);
2117 :
2118 2 : aPos.IncRow(-4);
2119 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(aPos));
2120 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A2:A3)"))
2121 0 : CPPUNIT_FAIL("Wrong formula!");
2122 :
2123 : // Undo and check again.
2124 2 : pUndo->Undo();
2125 2 : aPos.IncRow(4);
2126 2 : CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(aPos));
2127 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A7)"))
2128 0 : CPPUNIT_FAIL("Wrong formula!");
2129 :
2130 : // Start over with a new scenario.
2131 2 : clearSheet(m_pDoc, 0);
2132 :
2133 : // Insert 1,2,3,4,5,6 into A3:A8.
2134 14 : for (SCROW i = 0; i <= 5; ++i)
2135 12 : m_pDoc->SetValue(ScAddress(0,i+2,0), i+1);
2136 :
2137 : // Set formula in B1.
2138 2 : aPos = ScAddress(0,1,0);
2139 2 : m_pDoc->SetString(aPos, "=SUM(A3:A8)");
2140 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2141 :
2142 : // Delete rows 6:8 (bottom end of the reference).
2143 2 : bDeleted = rFunc.DeleteCells(ScRange(0,5,0,MAXCOL,7,0), &aMark, DEL_CELLSUP, true, true);
2144 2 : CPPUNIT_ASSERT(bDeleted);
2145 :
2146 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(aPos));
2147 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A5)"))
2148 0 : CPPUNIT_FAIL("Wrong formula!");
2149 :
2150 : // Undo and check.
2151 2 : pUndo->Undo();
2152 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2153 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A8)"))
2154 0 : CPPUNIT_FAIL("Wrong formula!");
2155 :
2156 : // Delete rows 7:9 (overlaps on the bottom).
2157 2 : bDeleted = rFunc.DeleteCells(ScRange(0,6,0,MAXCOL,8,0), &aMark, DEL_CELLSUP, true, true);
2158 2 : CPPUNIT_ASSERT(bDeleted);
2159 :
2160 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(aPos));
2161 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A6)"))
2162 0 : CPPUNIT_FAIL("Wrong formula!");
2163 :
2164 : // Undo and check again.
2165 2 : pUndo->Undo();
2166 2 : CPPUNIT_ASSERT_EQUAL(21.0, m_pDoc->GetValue(aPos));
2167 2 : if (!checkFormula(*m_pDoc, aPos, "SUM(A3:A8)"))
2168 0 : CPPUNIT_FAIL("Wrong formula!");
2169 :
2170 4 : m_pDoc->DeleteTab(0);
2171 2 : }
2172 :
2173 2 : void Test::testFormulaRefUpdateName()
2174 : {
2175 2 : m_pDoc->InsertTab(0, "Formula");
2176 :
2177 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2178 :
2179 : // Fill C2:C5 with values.
2180 2 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
2181 2 : m_pDoc->SetValue(ScAddress(2,2,0), 2);
2182 2 : m_pDoc->SetValue(ScAddress(2,3,0), 3);
2183 2 : m_pDoc->SetValue(ScAddress(2,4,0), 4);
2184 :
2185 : // Add a named expression that references the immediate left cell.
2186 2 : ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
2187 2 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
2188 : ScRangeData* pName = new ScRangeData(
2189 2 : m_pDoc, "ToLeft", "RC[-1]", ScAddress(2,1,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1);
2190 :
2191 2 : bool bInserted = pGlobalNames->insert(pName);
2192 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
2193 :
2194 : // Insert formulas in D2:D5 using the named expression.
2195 2 : m_pDoc->SetString(ScAddress(3,1,0), "=ToLeft");
2196 2 : m_pDoc->SetString(ScAddress(3,2,0), "=ToLeft");
2197 2 : m_pDoc->SetString(ScAddress(3,3,0), "=ToLeft");
2198 2 : m_pDoc->SetString(ScAddress(3,4,0), "=ToLeft");
2199 :
2200 : // Make sure the results are correct.
2201 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,1,0));
2202 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,2,0));
2203 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,3,0));
2204 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(3,4,0));
2205 :
2206 : // Push cells in column C down by one cell.
2207 2 : m_pDoc->InsertRow(ScRange(2,0,0,2,0,0));
2208 :
2209 : // Make sure the results change accordingly.
2210 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(3,1,0));
2211 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,2,0));
2212 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,3,0));
2213 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,4,0));
2214 :
2215 : // Move cells back.
2216 2 : m_pDoc->DeleteRow(ScRange(2,0,0,2,0,0));
2217 :
2218 : // Make sure the results are back as well.
2219 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,1,0));
2220 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,2,0));
2221 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,3,0));
2222 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(3,4,0));
2223 :
2224 : // Fill B10:B12 with values.
2225 2 : m_pDoc->SetValue(ScAddress(1,9,0), 10);
2226 2 : m_pDoc->SetValue(ScAddress(1,10,0), 11);
2227 2 : m_pDoc->SetValue(ScAddress(1,11,0), 12);
2228 :
2229 : // Insert a new named expression that references these values as absolute range.
2230 : pName = new ScRangeData(
2231 2 : m_pDoc, "MyRange", "$B$10:$B$12", ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
2232 2 : bInserted = pGlobalNames->insert(pName);
2233 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
2234 :
2235 : // Set formula at C8 that references this named expression.
2236 2 : m_pDoc->SetString(ScAddress(2,7,0), "=SUM(MyRange)");
2237 2 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,0)));
2238 :
2239 : // Shift B10:B12 to right by 2 columns.
2240 2 : m_pDoc->InsertCol(ScRange(1,9,0,2,11,0));
2241 :
2242 : // This should shift the absolute range B10:B12 that MyRange references.
2243 2 : pName = pGlobalNames->findByUpperName("MYRANGE");
2244 2 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
2245 4 : OUString aExpr;
2246 2 : pName->GetSymbol(aExpr);
2247 2 : CPPUNIT_ASSERT_EQUAL(OUString("$D$10:$D$12"), aExpr);
2248 :
2249 : // This move shouldn't affect the value of C8.
2250 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,7,0));
2251 2 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
2252 2 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,0)));
2253 :
2254 : // Update the value of D10 and make sure C8 gets updated.
2255 2 : m_pDoc->SetValue(ScAddress(3,9,0), 20);
2256 2 : CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(2,7,0)));
2257 :
2258 : // Insert a new sheet before the current.
2259 2 : m_pDoc->InsertTab(0, "New");
2260 4 : OUString aName;
2261 2 : m_pDoc->GetName(1, aName);
2262 2 : CPPUNIT_ASSERT_EQUAL(OUString("Formula"), aName);
2263 :
2264 2 : pName = pGlobalNames->findByUpperName("MYRANGE");
2265 2 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
2266 :
2267 2 : m_pDoc->SetValue(ScAddress(3,9,1), 10);
2268 2 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,1)));
2269 :
2270 : // Delete the inserted sheet, which will shift the 'Formula' sheet to the left.
2271 2 : m_pDoc->DeleteTab(0);
2272 :
2273 2 : aName = OUString();
2274 2 : m_pDoc->GetName(0, aName);
2275 2 : CPPUNIT_ASSERT_EQUAL(OUString("Formula"), aName);
2276 :
2277 2 : pName = pGlobalNames->findByUpperName("MYRANGE");
2278 2 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
2279 :
2280 2 : m_pDoc->SetValue(ScAddress(3,9,0), 11);
2281 2 : CPPUNIT_ASSERT_EQUAL(34.0, m_pDoc->GetValue(ScAddress(2,7,0)));
2282 :
2283 : // Clear all and start over.
2284 2 : clearRange(m_pDoc, ScRange(0,0,0,100,100,0));
2285 2 : pGlobalNames->clear();
2286 :
2287 : pName = new ScRangeData(
2288 2 : m_pDoc, "MyRange", "$B$1:$C$6", ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
2289 2 : bInserted = pGlobalNames->insert(pName);
2290 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
2291 2 : pName->GetSymbol(aExpr);
2292 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$1:$C$6"), aExpr);
2293 :
2294 : // Insert range of cells to shift right. The range partially overlaps the named range.
2295 2 : m_pDoc->InsertCol(ScRange(2,4,0,3,8,0));
2296 :
2297 : // This should not alter the range.
2298 2 : pName->GetSymbol(aExpr);
2299 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$1:$C$6"), aExpr);
2300 :
2301 4 : m_pDoc->DeleteTab(0);
2302 2 : }
2303 :
2304 2 : void Test::testFormulaRefUpdateNameMove()
2305 : {
2306 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2307 :
2308 2 : m_pDoc->InsertTab(0, "Test");
2309 :
2310 : // Set values to B2:B4.
2311 2 : m_pDoc->SetValue(ScAddress(1,1,0), 1.0);
2312 2 : m_pDoc->SetValue(ScAddress(1,2,0), 2.0);
2313 2 : m_pDoc->SetValue(ScAddress(1,3,0), 3.0);
2314 :
2315 : // Set named range for B2:B4.
2316 2 : bool bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$Test.$B$2:$B$4");
2317 2 : CPPUNIT_ASSERT(bInserted);
2318 :
2319 : // Set formula in A10.
2320 2 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM(MyRange)");
2321 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
2322 :
2323 2 : ScRangeData* pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2324 2 : CPPUNIT_ASSERT(pData);
2325 4 : OUString aSymbol;
2326 2 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2327 2 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$B$2:$B$4"), aSymbol);
2328 :
2329 : // Move B2:B4 to D3.
2330 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2331 2 : bool bMoved = rFunc.MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(3,2,0), true, true, false, true);
2332 2 : CPPUNIT_ASSERT(bMoved);
2333 :
2334 : // The named range should have moved as well.
2335 2 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2336 2 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$D$3:$D$5"), aSymbol);
2337 :
2338 : // The value of A10 should remain unchanged.
2339 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
2340 :
2341 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
2342 2 : CPPUNIT_ASSERT(pUndoMgr);
2343 :
2344 : // Undo and check.
2345 2 : pUndoMgr->Undo();
2346 :
2347 2 : pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2348 2 : CPPUNIT_ASSERT(pData);
2349 2 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2350 2 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$B$2:$B$4"), aSymbol);
2351 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
2352 :
2353 : // Redo and check.
2354 2 : pUndoMgr->Redo();
2355 :
2356 2 : pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2357 2 : CPPUNIT_ASSERT(pData);
2358 2 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2359 2 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$D$3:$D$5"), aSymbol);
2360 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
2361 :
2362 : // Undo again to bring it back to the initial condition, and clear the undo buffer.
2363 2 : pUndoMgr->Undo();
2364 2 : pUndoMgr->Clear();
2365 :
2366 : // Add an identical formula to A11 and make a formula group over A10:A11.
2367 2 : m_pDoc->SetString(ScAddress(0,10,0), "=SUM(MyRange)");
2368 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,9,0));
2369 2 : CPPUNIT_ASSERT(pFC);
2370 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedTopRow());
2371 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2372 :
2373 : // Move B2:B4 to D3 again.
2374 2 : bMoved = rFunc.MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(3,2,0), true, true, false, true);
2375 2 : CPPUNIT_ASSERT(bMoved);
2376 :
2377 : // Values of A10 and A11 should remain the same.
2378 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
2379 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
2380 :
2381 : // Clear and start over.
2382 2 : clearSheet(m_pDoc, 0);
2383 2 : m_pDoc->GetRangeName()->clear();
2384 :
2385 : // Set value to B2.
2386 2 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0);
2387 :
2388 : // Define B2 as 'MyCell'.
2389 2 : bInserted = m_pDoc->InsertNewRangeName("MyCell", ScAddress(0,0,0), "$Test.$B$2");
2390 2 : CPPUNIT_ASSERT(bInserted);
2391 :
2392 : // Set formula to B3 that references B2 via MyCell.
2393 2 : m_pDoc->SetString(ScAddress(1,2,0), "=MyCell*2");
2394 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
2395 :
2396 : // Move B2 to D2.
2397 2 : bMoved = rFunc.MoveBlock(ScRange(1,1,0,1,1,0), ScAddress(3,1,0), true, true, false, true);
2398 2 : CPPUNIT_ASSERT(bMoved);
2399 :
2400 : // Value in B3 should remain unchanged.
2401 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
2402 :
2403 4 : m_pDoc->DeleteTab(0);
2404 2 : }
2405 :
2406 2 : void Test::testFormulaRefUpdateNameExpandRef()
2407 : {
2408 2 : setExpandRefs(true);
2409 :
2410 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2411 :
2412 2 : m_pDoc->InsertTab(0, "Test");
2413 :
2414 2 : bool bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$A$1:$A$3");
2415 2 : CPPUNIT_ASSERT(bInserted);
2416 :
2417 : // Set values to A1:A3.
2418 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
2419 2 : m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
2420 2 : m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
2421 :
2422 2 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM(MyRange)");
2423 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,5,0)));
2424 :
2425 : // Insert a new row at row 4, which should expand the named range to A1:A4.
2426 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2427 4 : ScMarkData aMark;
2428 2 : aMark.SelectOneTable(0);
2429 2 : rFunc.InsertCells(ScRange(0,3,0,MAXCOL,3,0), &aMark, INS_INSROWS, false, true, false);
2430 2 : ScRangeData* pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2431 2 : CPPUNIT_ASSERT(pName);
2432 4 : OUString aSymbol;
2433 2 : pName->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2434 2 : CPPUNIT_ASSERT_EQUAL(OUString("$A$1:$A$4"), aSymbol);
2435 :
2436 : // Make sure the listening area has been expanded as well. Note the
2437 : // formula cell has been pushed downward by one cell.
2438 2 : m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
2439 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
2440 :
2441 : // Clear the document and start over.
2442 2 : m_pDoc->GetRangeName()->clear();
2443 2 : clearSheet(m_pDoc, 0);
2444 :
2445 : // Set values to B4:B6.
2446 2 : m_pDoc->SetValue(ScAddress(1,3,0), 1.0);
2447 2 : m_pDoc->SetValue(ScAddress(1,4,0), 2.0);
2448 2 : m_pDoc->SetValue(ScAddress(1,5,0), 3.0);
2449 :
2450 2 : bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$B$4:$B$6");
2451 2 : CPPUNIT_ASSERT(bInserted);
2452 :
2453 : // Set formula to A1.
2454 2 : m_pDoc->SetString(ScAddress(0,0,0), "=SUM(MyRange)");
2455 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,0,0));
2456 :
2457 : // Insert rows over 3:5 which should expand the range by 3 rows.
2458 2 : rFunc.InsertCells(ScRange(0,2,0,MAXCOL,4,0), &aMark, INS_INSROWS, false, true, false);
2459 :
2460 2 : pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2461 2 : CPPUNIT_ASSERT(pName);
2462 :
2463 2 : pName->GetSymbol(aSymbol, m_pDoc->GetGrammar());
2464 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$4:$B$9"), aSymbol);
2465 :
2466 : // Clear the document and start over.
2467 2 : m_pDoc->GetRangeName()->clear();
2468 2 : clearSheet(m_pDoc, 0);
2469 :
2470 : // Set values to A1:A3.
2471 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
2472 2 : m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
2473 2 : m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
2474 :
2475 : // Name A1:A3 'MyData'.
2476 2 : bInserted = m_pDoc->InsertNewRangeName("MyData", ScAddress(0,0,0), "$A$1:$A$3");
2477 2 : CPPUNIT_ASSERT(bInserted);
2478 :
2479 : // Set formulas to C1:C2 and E1.
2480 2 : m_pDoc->SetString(ScAddress(2,0,0), "=SUM(MyData)");
2481 2 : m_pDoc->SetString(ScAddress(2,1,0), "=SUM(MyData)");
2482 2 : m_pDoc->SetString(ScAddress(4,0,0), "=SUM(MyData)");
2483 :
2484 : // C1:C2 should be shared.
2485 2 : const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
2486 2 : CPPUNIT_ASSERT(pFC);
2487 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
2488 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2489 :
2490 : // E1 should not be shared.
2491 2 : pFC = m_pDoc->GetFormulaCell(ScAddress(4,0,0));
2492 2 : CPPUNIT_ASSERT(pFC);
2493 2 : CPPUNIT_ASSERT(!pFC->IsShared());
2494 :
2495 : // Check the results.
2496 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(2,0,0)));
2497 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2498 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(4,0,0)));
2499 :
2500 : // Insert a new row at row 3. This should expand MyData to A1:A4.
2501 2 : rFunc.InsertCells(ScRange(0,2,0,MAXCOL,2,0), &aMark, INS_INSROWS, false, true, false);
2502 :
2503 : // Set new value to A3.
2504 2 : m_pDoc->SetValue(ScAddress(0,2,0), 4.0);
2505 :
2506 : // Check the results again.
2507 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
2508 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2509 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(4,0,0)));
2510 :
2511 4 : m_pDoc->DeleteTab(0);
2512 2 : }
2513 :
2514 2 : void Test::testFormulaRefUpdateNameDeleteRow()
2515 : {
2516 2 : m_pDoc->InsertTab(0, "Test");
2517 :
2518 : // Insert a new name 'MyRange' to reference B2:B4.
2519 2 : bool bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$B$2:$B$4");
2520 2 : CPPUNIT_ASSERT(bInserted);
2521 :
2522 2 : const ScRangeData* pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2523 2 : CPPUNIT_ASSERT(pName);
2524 :
2525 2 : sc::TokenStringContext aCxt(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);
2526 2 : const ScTokenArray* pCode = pName->GetCode();
2527 4 : OUString aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2528 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$4"), aExpr);
2529 :
2530 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2531 :
2532 : // Delete row 3.
2533 4 : ScMarkData aMark;
2534 2 : aMark.SelectOneTable(0);
2535 2 : rFunc.DeleteCells(ScRange(0,2,0,MAXCOL,2,0), &aMark, DEL_CELLSUP, true, true);
2536 :
2537 : // The reference in the name should get updated to B2:B3.
2538 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2539 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$3"), aExpr);
2540 :
2541 : // Delete row 3 again.
2542 2 : rFunc.DeleteCells(ScRange(0,2,0,MAXCOL,2,0), &aMark, DEL_CELLSUP, true, true);
2543 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2544 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$2"), aExpr);
2545 :
2546 : // Undo and check.
2547 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
2548 2 : CPPUNIT_ASSERT(pUndoMgr);
2549 :
2550 2 : pUndoMgr->Undo();
2551 :
2552 2 : pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2553 2 : CPPUNIT_ASSERT(pName);
2554 2 : pCode = pName->GetCode();
2555 :
2556 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2557 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$3"), aExpr);
2558 :
2559 : // Undo again and check.
2560 2 : pUndoMgr->Undo();
2561 :
2562 2 : pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2563 2 : CPPUNIT_ASSERT(pName);
2564 2 : pCode = pName->GetCode();
2565 :
2566 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2567 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$4"), aExpr);
2568 :
2569 : // Delete row 2-3.
2570 2 : rFunc.DeleteCells(ScRange(0,1,0,MAXCOL,2,0), &aMark, DEL_CELLSUP, true, true);
2571 :
2572 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2573 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$2"), aExpr);
2574 :
2575 : // Undo and check.
2576 2 : pUndoMgr->Undo();
2577 :
2578 2 : pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
2579 2 : CPPUNIT_ASSERT(pName);
2580 2 : pCode = pName->GetCode();
2581 :
2582 2 : aExpr = pCode->CreateString(aCxt, ScAddress(0,0,0));
2583 2 : CPPUNIT_ASSERT_EQUAL(OUString("$B$2:$B$4"), aExpr);
2584 :
2585 4 : m_pDoc->DeleteTab(0);
2586 2 : }
2587 :
2588 2 : void Test::testFormulaRefUpdateValidity()
2589 : {
2590 : struct {
2591 :
2592 10 : bool checkList( std::vector<ScTypedStrData>& rList )
2593 : {
2594 10 : double aExpected[] = { 1.0, 2.0, 3.0 }; // must be sorted.
2595 10 : size_t nCheckSize = SAL_N_ELEMENTS(aExpected);
2596 :
2597 10 : if (rList.size() != nCheckSize)
2598 : {
2599 0 : cerr << "List size is not what is expected." << endl;
2600 0 : return false;
2601 : }
2602 :
2603 10 : std::sort(rList.begin(), rList.end(), ScTypedStrData::LessCaseSensitive());
2604 :
2605 40 : for (size_t i = 0; i < nCheckSize; ++i)
2606 : {
2607 30 : if (aExpected[i] != rList[i].GetValue())
2608 : {
2609 0 : cerr << "Incorrect value at position " << i
2610 0 : << ": expected=" << aExpected[i] << ", actual=" << rList[i].GetValue() << endl;
2611 0 : return false;
2612 : }
2613 : }
2614 :
2615 10 : return true;
2616 : }
2617 :
2618 : } aCheck;
2619 :
2620 2 : setExpandRefs(false);
2621 2 : setCalcAsShown(m_pDoc, true);
2622 :
2623 2 : m_pDoc->InsertTab(0, "Formula");
2624 :
2625 : // Set values in C2:C4.
2626 2 : m_pDoc->SetValue(ScAddress(2,1,0), 1.0);
2627 2 : m_pDoc->SetValue(ScAddress(2,2,0), 2.0);
2628 2 : m_pDoc->SetValue(ScAddress(2,3,0), 3.0);
2629 :
2630 : // Set validity in A2.
2631 : ScValidationData aData(
2632 : SC_VALID_LIST, SC_COND_EQUAL, "C2:C4", "", m_pDoc, ScAddress(0,1,0), "", "",
2633 2 : m_pDoc->GetGrammar(), m_pDoc->GetGrammar());
2634 :
2635 2 : sal_uLong nIndex = m_pDoc->AddValidationEntry(aData);
2636 4 : SfxUInt32Item aItem(ATTR_VALIDDATA, nIndex);
2637 :
2638 : ScPatternAttr aNewAttrs(
2639 4 : new SfxItemSet(*m_pDoc->GetPool(), ATTR_PATTERN_START, ATTR_PATTERN_END));
2640 2 : aNewAttrs.GetItemSet().Put(aItem);
2641 :
2642 2 : m_pDoc->ApplyPattern(0, 1, 0, aNewAttrs);
2643 :
2644 2 : const ScValidationData* pData = m_pDoc->GetValidationEntry(nIndex);
2645 2 : CPPUNIT_ASSERT(pData);
2646 :
2647 : // Make sure the list is correct.
2648 4 : std::vector<ScTypedStrData> aList;
2649 2 : pData->FillSelectionList(aList, ScAddress(0,1,0));
2650 2 : bool bGood = aCheck.checkList(aList);
2651 2 : CPPUNIT_ASSERT_MESSAGE("Initial list is incorrect.", bGood);
2652 :
2653 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2654 4 : ScMarkData aMark;
2655 2 : aMark.SelectOneTable(0);
2656 :
2657 : // Insert a new column at Column B, to move the list from C2:C4 to D2:D4.
2658 2 : bool bInserted = rFunc.InsertCells(ScRange(1,0,0,1,MAXROW,0), &aMark, INS_INSCOLS, true, true, false);
2659 2 : CPPUNIT_ASSERT_MESSAGE("Column insertion failed.", bInserted);
2660 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(3,1,0)));
2661 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,2,0)));
2662 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(3,3,0)));
2663 :
2664 : // Check the list values again.
2665 2 : aList.clear();
2666 2 : pData->FillSelectionList(aList, ScAddress(0,1,0));
2667 2 : bGood = aCheck.checkList(aList);
2668 2 : CPPUNIT_ASSERT_MESSAGE("List content is incorrect after column insertion.", bGood);
2669 :
2670 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
2671 2 : CPPUNIT_ASSERT(pUndoMgr);
2672 :
2673 : // Undo and check the list content again. The list moves back to C2:C4 after the undo.
2674 2 : pUndoMgr->Undo();
2675 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2676 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
2677 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
2678 :
2679 2 : aList.clear();
2680 2 : pData->FillSelectionList(aList, ScAddress(0,1,0));
2681 2 : bGood = aCheck.checkList(aList);
2682 2 : CPPUNIT_ASSERT_MESSAGE("List content is incorrect after undo of column insertion.", bGood);
2683 :
2684 : // Move C2:C4 to E5:E7.
2685 2 : bool bMoved = rFunc.MoveBlock(ScRange(2,1,0,2,3,0), ScAddress(4,4,0), false, true, false, true);
2686 2 : CPPUNIT_ASSERT(bMoved);
2687 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(4,4,0)));
2688 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(4,5,0)));
2689 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(4,6,0)));
2690 :
2691 : // Check the list again after the move.
2692 2 : aList.clear();
2693 2 : pData->FillSelectionList(aList, ScAddress(0,1,0));
2694 2 : bGood = aCheck.checkList(aList);
2695 2 : CPPUNIT_ASSERT_MESSAGE("List content is incorrect after moving C2:C4 to E5:E7.", bGood);
2696 :
2697 : // Undo the move and check. The list should be back to C2:C4.
2698 2 : pUndoMgr->Undo();
2699 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2700 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
2701 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
2702 :
2703 2 : aList.clear();
2704 2 : pData->FillSelectionList(aList, ScAddress(0,1,0));
2705 2 : bGood = aCheck.checkList(aList);
2706 2 : CPPUNIT_ASSERT_MESSAGE("List content is incorrect after undo of the move.", bGood);
2707 :
2708 4 : m_pDoc->DeleteTab(0);
2709 2 : }
2710 :
2711 2 : void Test::testMultipleOperations()
2712 : {
2713 2 : m_pDoc->InsertTab(0, "MultiOp");
2714 :
2715 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2716 :
2717 : // Insert the reference formula at top row.
2718 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
2719 2 : m_pDoc->SetString(ScAddress(1,0,0), "=A1*10");
2720 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,0,0)));
2721 :
2722 : // Insert variable inputs in A3:A5.
2723 2 : m_pDoc->SetValue(ScAddress(0,2,0), 2);
2724 2 : m_pDoc->SetValue(ScAddress(0,3,0), 3);
2725 2 : m_pDoc->SetValue(ScAddress(0,4,0), 4);
2726 :
2727 : // Set multiple operations range.
2728 2 : ScTabOpParam aParam;
2729 2 : aParam.aRefFormulaCell = ScRefAddress(1,0,0,false,false,false);
2730 2 : aParam.aRefFormulaEnd = aParam.aRefFormulaCell;
2731 2 : aParam.aRefColCell = ScRefAddress(0,0,0,false,false,false);
2732 4 : ScMarkData aMark;
2733 2 : aMark.SetMarkArea(ScRange(0,2,0,1,4,0)); // Select A3:B5.
2734 2 : m_pDoc->InsertTableOp(aParam, 0, 2, 1, 4, aMark);
2735 2 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(1,2,0));
2736 2 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(1,3,0));
2737 2 : CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(1,4,0));
2738 :
2739 : // Clear A3:B5.
2740 2 : clearRange(m_pDoc, ScRange(0,2,0,1,4,0));
2741 :
2742 : // This time, use indirect reference formula cell.
2743 2 : m_pDoc->SetString(ScAddress(2,0,0), "=B1"); // C1 simply references B1.
2744 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
2745 :
2746 : // Insert variable inputs in A3:A5.
2747 2 : m_pDoc->SetValue(ScAddress(0,2,0), 3);
2748 2 : m_pDoc->SetValue(ScAddress(0,3,0), 4);
2749 2 : m_pDoc->SetValue(ScAddress(0,4,0), 5);
2750 :
2751 : // Set multiple operations range again, but this time, we'll use C1 as the reference formula.
2752 2 : aParam.aRefFormulaCell.Set(2,0,0,false,false,false);
2753 2 : aParam.aRefFormulaEnd = aParam.aRefFormulaCell;
2754 2 : m_pDoc->InsertTableOp(aParam, 0, 2, 1, 4, aMark);
2755 2 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(1,2,0));
2756 2 : CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(1,3,0));
2757 2 : CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(1,4,0));
2758 :
2759 4 : m_pDoc->DeleteTab(0);
2760 2 : }
2761 :
2762 2 : void Test::testFuncCOLUMN()
2763 : {
2764 2 : m_pDoc->InsertTab(0, "Formula");
2765 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2766 :
2767 2 : m_pDoc->SetString(ScAddress(5,10,0), "=COLUMN()");
2768 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2769 :
2770 2 : m_pDoc->SetString(ScAddress(0,1,0), "=F11");
2771 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2772 :
2773 : // Move the formula cell with COLUMN() function to change its value.
2774 2 : m_pDoc->InsertCol(ScRange(5,0,0,5,MAXROW,0));
2775 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(6,10,0)));
2776 :
2777 : // The cell that references the moved cell should update its value as well.
2778 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2779 :
2780 : // Move the column in the other direction.
2781 2 : m_pDoc->DeleteCol(ScRange(5,0,0,5,MAXROW,0));
2782 :
2783 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2784 :
2785 : // The cell that references the moved cell should update its value as well.
2786 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2787 :
2788 2 : m_pDoc->DeleteTab(0);
2789 2 : }
2790 :
2791 2 : void Test::testFuncCOUNT()
2792 : {
2793 2 : m_pDoc->InsertTab(0, "Formula");
2794 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2795 :
2796 2 : m_pDoc->SetValue(ScAddress(0,0,0), 2); // A1
2797 2 : m_pDoc->SetValue(ScAddress(0,1,0), 4); // A2
2798 2 : m_pDoc->SetValue(ScAddress(0,2,0), 6); // A3
2799 :
2800 2 : ScAddress aPos(1,0,0);
2801 2 : m_pDoc->SetString(aPos, "=COUNT(A1:A3)");
2802 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(aPos));
2803 :
2804 2 : aPos.IncRow();
2805 2 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2)");
2806 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(aPos));
2807 :
2808 2 : aPos.IncRow();
2809 2 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2;4)");
2810 2 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(aPos));
2811 :
2812 2 : aPos.IncRow();
2813 2 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2;4;6)");
2814 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(aPos));
2815 :
2816 2 : m_pDoc->DeleteTab(0);
2817 2 : }
2818 :
2819 2 : void Test::testFuncCOUNTBLANK()
2820 : {
2821 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2822 2 : m_pDoc->InsertTab(0, "Formula");
2823 :
2824 : const char* aData[][4] = {
2825 : { "1", 0, "=B1", "=\"\"" },
2826 : { "2", 0, "=B2", "=\"\"" },
2827 : { "A", 0, "=B3", "=\"\"" },
2828 : { "B", 0, "=B4", "=D3" },
2829 : { 0, 0, "=B5", "=D4" },
2830 : { "=COUNTBLANK(A1:A5)", "=COUNTBLANK(B1:B5)", "=COUNTBLANK(C1:C5)", "=COUNTBLANK(D1:D5)" }
2831 2 : };
2832 :
2833 2 : ScAddress aPos(0,0,0);
2834 2 : ScRange aRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2835 2 : CPPUNIT_ASSERT(aRange.aStart == aPos);
2836 :
2837 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
2838 2 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,5,0)));
2839 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(2,5,0)));
2840 2 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(3,5,0)));
2841 :
2842 : // Test single cell reference cases.
2843 :
2844 2 : clearSheet(m_pDoc, 0);
2845 :
2846 : const char* aData2[][2] = {
2847 : { "1", "=COUNTBLANK(A1)" },
2848 : { "A", "=COUNTBLANK(A2)" },
2849 : { 0, "=COUNTBLANK(A3)" },
2850 : { "=\"\"", "=COUNTBLANK(A4)" },
2851 : { "=A4" , "=COUNTBLANK(A5)" },
2852 2 : };
2853 :
2854 2 : aRange = insertRangeData(m_pDoc, aPos, aData2, SAL_N_ELEMENTS(aData2));
2855 2 : CPPUNIT_ASSERT(aRange.aStart == aPos);
2856 :
2857 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1,0,0)));
2858 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1,1,0)));
2859 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,2,0)));
2860 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,3,0)));
2861 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,4,0)));
2862 :
2863 2 : m_pDoc->DeleteTab(0);
2864 2 : }
2865 :
2866 2 : void Test::testFuncROW()
2867 : {
2868 2 : m_pDoc->InsertTab(0, "Formula");
2869 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2870 :
2871 2 : m_pDoc->SetString(ScAddress(5,10,0), "=ROW()");
2872 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2873 :
2874 2 : m_pDoc->SetString(ScAddress(0,1,0), "=F11");
2875 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2876 :
2877 : // Insert 2 new rows at row 4.
2878 2 : m_pDoc->InsertRow(ScRange(0,3,0,MAXCOL,4,0));
2879 2 : CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc->GetValue(ScAddress(5,12,0)));
2880 :
2881 : // The cell that references the moved cell should update its value as well.
2882 2 : CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2883 :
2884 : // Delete 2 rows to move it back.
2885 2 : m_pDoc->DeleteRow(ScRange(0,3,0,MAXCOL,4,0));
2886 :
2887 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2888 :
2889 : // The cell that references the moved cell should update its value as well.
2890 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2891 :
2892 : // Clear sheet and start over.
2893 2 : clearSheet(m_pDoc, 0);
2894 :
2895 2 : m_pDoc->SetString(ScAddress(0,1,0), "=ROW(A5)");
2896 2 : m_pDoc->SetString(ScAddress(1,1,0), "=ROW(B5)");
2897 2 : m_pDoc->SetString(ScAddress(1,2,0), "=ROW(B6)");
2898 2 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2899 2 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,1,0)));
2900 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,2,0)));
2901 :
2902 : // B2:B3 should be shared.
2903 2 : const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
2904 2 : CPPUNIT_ASSERT(pFC);
2905 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2906 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2907 :
2908 : // Insert a new row at row 4.
2909 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
2910 4 : ScMarkData aMark;
2911 2 : aMark.SelectOneTable(0);
2912 2 : rFunc.InsertCells(ScRange(0,3,0,MAXCOL,3,0), &aMark, INS_INSROWS, false, true, false);
2913 2 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "ROW(A6)"))
2914 0 : CPPUNIT_FAIL("Wrong formula!");
2915 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "ROW(B6)"))
2916 0 : CPPUNIT_FAIL("Wrong formula!");
2917 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "ROW(B7)"))
2918 0 : CPPUNIT_FAIL("Wrong formula!");
2919 :
2920 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2921 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,1,0)));
2922 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(1,2,0)));
2923 :
2924 4 : m_pDoc->DeleteTab(0);
2925 2 : }
2926 :
2927 2 : void Test::testFuncSUM()
2928 : {
2929 2 : OUString aTabName("foo");
2930 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2931 2 : m_pDoc->InsertTab (0, aTabName));
2932 :
2933 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2934 :
2935 : // Single argument case.
2936 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
2937 2 : m_pDoc->SetValue(ScAddress(0,1,0), 1);
2938 2 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM(A1:A2)");
2939 2 : m_pDoc->CalcAll();
2940 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
2941 :
2942 : // Multiple argument case.
2943 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
2944 2 : m_pDoc->SetValue(ScAddress(0,1,0), 22);
2945 2 : m_pDoc->SetValue(ScAddress(0,2,0), 4);
2946 2 : m_pDoc->SetValue(ScAddress(0,3,0), 5);
2947 2 : m_pDoc->SetValue(ScAddress(0,4,0), 6);
2948 :
2949 2 : m_pDoc->SetValue(ScAddress(1,0,0), 3);
2950 2 : m_pDoc->SetValue(ScAddress(1,1,0), 4);
2951 2 : m_pDoc->SetValue(ScAddress(1,2,0), 5);
2952 2 : m_pDoc->SetValue(ScAddress(1,3,0), 6);
2953 2 : m_pDoc->SetValue(ScAddress(1,4,0), 7);
2954 :
2955 2 : m_pDoc->SetString(ScAddress(3,0,0), "=SUM(A1:A2;B1:B2)");
2956 2 : m_pDoc->SetString(ScAddress(3,1,0), "=SUM(A2:A3;B2:B3)");
2957 2 : m_pDoc->SetString(ScAddress(3,2,0), "=SUM(A3:A4;B3:B4)");
2958 2 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(ScAddress(3,0,0)));
2959 2 : CPPUNIT_ASSERT_EQUAL(35.0, m_pDoc->GetValue(ScAddress(3,1,0)));
2960 2 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(3,2,0)));
2961 :
2962 : // Clear and start over.
2963 2 : clearRange(m_pDoc, ScRange(0,0,0,3,MAXROW,0));
2964 :
2965 : // SUM needs to take the first error in case the range contains an error.
2966 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
2967 2 : m_pDoc->SetValue(ScAddress(0,1,0), 10.0);
2968 2 : m_pDoc->SetValue(ScAddress(0,2,0), 100.0);
2969 2 : m_pDoc->SetString(ScAddress(0,3,0), "=SUM(A1:A3)");
2970 2 : CPPUNIT_ASSERT_EQUAL(111.0, m_pDoc->GetValue(ScAddress(0,3,0)));
2971 :
2972 : // Set #DIV/0! error to A3. A4 should also inherit this error.
2973 2 : m_pDoc->SetString(ScAddress(0,2,0), "=1/0");
2974 2 : sal_uInt16 nErr = m_pDoc->GetErrCode(ScAddress(0,2,0));
2975 4 : CPPUNIT_ASSERT_MESSAGE("Cell should have a division by zero error.",
2976 2 : nErr == errDivisionByZero);
2977 2 : nErr = m_pDoc->GetErrCode(ScAddress(0,3,0));
2978 4 : CPPUNIT_ASSERT_MESSAGE("SUM should have also inherited a div-by-zero error.",
2979 2 : nErr == errDivisionByZero);
2980 :
2981 : // Set #NA! to A2. A4 should now inherit this error.
2982 2 : m_pDoc->SetString(ScAddress(0,1,0), "=NA()");
2983 2 : nErr = m_pDoc->GetErrCode(ScAddress(0,1,0));
2984 2 : CPPUNIT_ASSERT_MESSAGE("A2 should be an error.", nErr);
2985 4 : CPPUNIT_ASSERT_MESSAGE("A4 should have inherited the same error as A2.",
2986 2 : nErr == m_pDoc->GetErrCode(ScAddress(0,3,0)));
2987 :
2988 4 : m_pDoc->DeleteTab(0);
2989 2 : }
2990 :
2991 2 : void Test::testFuncPRODUCT()
2992 : {
2993 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto recalc.
2994 :
2995 4 : OUString aTabName("foo");
2996 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2997 2 : m_pDoc->InsertTab (0, aTabName));
2998 :
2999 2 : double val = 1;
3000 : double result;
3001 2 : m_pDoc->SetValue(0, 0, 0, val);
3002 2 : val = 2;
3003 2 : m_pDoc->SetValue(0, 1, 0, val);
3004 2 : val = 3;
3005 2 : m_pDoc->SetValue(0, 2, 0, val);
3006 2 : m_pDoc->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
3007 2 : m_pDoc->GetValue(0, 3, 0, result);
3008 2 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
3009 :
3010 2 : m_pDoc->SetString(0, 4, 0, OUString("=PRODUCT({2;3;4})"));
3011 2 : m_pDoc->GetValue(0, 4, 0, result);
3012 2 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 24.0);
3013 :
3014 4 : m_pDoc->DeleteTab(0);
3015 2 : }
3016 :
3017 2 : void Test::testFuncSUMPRODUCT()
3018 : {
3019 2 : m_pDoc->InsertTab(0, "Test");
3020 :
3021 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto recalc.
3022 :
3023 2 : ScAddress aPos(0,0,0);
3024 2 : m_pDoc->SetString(aPos, "=SUMPRODUCT(B1:B3;C1:C3)");
3025 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(aPos));
3026 2 : m_pDoc->SetValue(ScAddress(2,0,0), 1.0); // C1
3027 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(aPos));
3028 2 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0); // B1
3029 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(aPos));
3030 2 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0); // B2
3031 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(aPos));
3032 2 : m_pDoc->SetValue(ScAddress(2,1,0), 3.0); // C2
3033 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
3034 2 : m_pDoc->SetValue(ScAddress(2,2,0), -2.0); // C3
3035 2 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
3036 2 : m_pDoc->SetValue(ScAddress(1,2,0), 5.0); // B3
3037 2 : CPPUNIT_ASSERT_EQUAL(-3.0, m_pDoc->GetValue(aPos));
3038 :
3039 : // Force an error in C2 and test ForcedArray matrix error propagation.
3040 2 : m_pDoc->SetString( 2, 1, 0, "=1/0");
3041 2 : sal_uInt16 nError = m_pDoc->GetErrCode(aPos);
3042 2 : CPPUNIT_ASSERT_MESSAGE("Formula result should be a propagated error", nError);
3043 :
3044 2 : m_pDoc->DeleteTab(0);
3045 2 : }
3046 :
3047 2 : void Test::testFuncMIN()
3048 : {
3049 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto recalc.
3050 2 : m_pDoc->InsertTab(0, "Formula");
3051 :
3052 : // A1:A2
3053 2 : m_pDoc->SetString(ScAddress(0,0,0), "a");
3054 2 : m_pDoc->SetString(ScAddress(0,1,0), "b");
3055 :
3056 : // B1:B2
3057 2 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0);
3058 2 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0);
3059 :
3060 : // Matrix in C1:C2.
3061 4 : ScMarkData aMark;
3062 2 : aMark.SelectOneTable(0);
3063 2 : m_pDoc->InsertMatrixFormula(2, 0, 2, 1, aMark, "=MIN(IF(A1:A2=\"c\";B1:B2))");
3064 :
3065 : // Formula cell in C1:C2 should be a 1x2 matrix array.
3066 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
3067 2 : CPPUNIT_ASSERT(pFC);
3068 2 : CPPUNIT_ASSERT_MESSAGE("This formula should be an array.", pFC->GetMatrixFlag() == MM_FORMULA);
3069 :
3070 : SCCOL nCols;
3071 : SCROW nRows;
3072 2 : pFC->GetMatColsRows(nCols, nRows);
3073 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCols);
3074 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), nRows);
3075 :
3076 2 : CPPUNIT_ASSERT_MESSAGE("Formula in C1 is invalid.", m_pDoc->GetErrCode(ScAddress(2,0,0)) == 0);
3077 2 : CPPUNIT_ASSERT_MESSAGE("Formula in C2 is invalid.", m_pDoc->GetErrCode(ScAddress(2,1,0)) == 0);
3078 :
3079 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(2,0,0)));
3080 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(2,1,0)));
3081 :
3082 : // Inline array input (A4).
3083 2 : m_pDoc->SetString(ScAddress(0,3,0), "=MIN({-2;4;3})");
3084 2 : CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
3085 :
3086 : // Add more values to B3:B4.
3087 2 : m_pDoc->SetValue(ScAddress(1,2,0), 20.0);
3088 2 : m_pDoc->SetValue(ScAddress(1,3,0), -20.0);
3089 :
3090 : // Get the MIN of B1:B4.
3091 2 : m_pDoc->SetString(ScAddress(2,4,0), "=MIN(B1:B4)");
3092 2 : CPPUNIT_ASSERT_EQUAL(-20.0, m_pDoc->GetValue(ScAddress(2,4,0)));
3093 :
3094 4 : m_pDoc->DeleteTab(0);
3095 2 : }
3096 :
3097 2 : void Test::testFuncN()
3098 : {
3099 2 : OUString aTabName("foo");
3100 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3101 2 : m_pDoc->InsertTab (0, aTabName));
3102 :
3103 : double result;
3104 :
3105 : // Clear the area first.
3106 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 1, 20, 0));
3107 :
3108 : // Put values to reference.
3109 2 : double val = 0;
3110 2 : m_pDoc->SetValue(0, 0, 0, val);
3111 2 : m_pDoc->SetString(0, 2, 0, OUString("Text"));
3112 2 : val = 1;
3113 2 : m_pDoc->SetValue(0, 3, 0, val);
3114 2 : val = -1;
3115 2 : m_pDoc->SetValue(0, 4, 0, val);
3116 2 : val = 12.3;
3117 2 : m_pDoc->SetValue(0, 5, 0, val);
3118 2 : m_pDoc->SetString(0, 6, 0, OUString("'12.3"));
3119 :
3120 : // Cell references
3121 2 : m_pDoc->SetString(1, 0, 0, OUString("=N(A1)"));
3122 2 : m_pDoc->SetString(1, 1, 0, OUString("=N(A2)"));
3123 2 : m_pDoc->SetString(1, 2, 0, OUString("=N(A3)"));
3124 2 : m_pDoc->SetString(1, 3, 0, OUString("=N(A4)"));
3125 2 : m_pDoc->SetString(1, 4, 0, OUString("=N(A5)"));
3126 2 : m_pDoc->SetString(1, 5, 0, OUString("=N(A6)"));
3127 2 : m_pDoc->SetString(1, 6, 0, OUString("=N(A9)"));
3128 :
3129 : // In-line values
3130 2 : m_pDoc->SetString(1, 7, 0, OUString("=N(0)"));
3131 2 : m_pDoc->SetString(1, 8, 0, OUString("=N(1)"));
3132 2 : m_pDoc->SetString(1, 9, 0, OUString("=N(-1)"));
3133 2 : m_pDoc->SetString(1, 10, 0, OUString("=N(123)"));
3134 2 : m_pDoc->SetString(1, 11, 0, OUString("=N(\"\")"));
3135 2 : m_pDoc->SetString(1, 12, 0, OUString("=N(\"12\")"));
3136 2 : m_pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
3137 :
3138 : // Range references
3139 2 : m_pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
3140 2 : m_pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
3141 2 : m_pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
3142 2 : m_pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
3143 :
3144 : // Calculate and check the results.
3145 2 : m_pDoc->CalcAll();
3146 : double checks1[] = {
3147 : 0, 0, 0, 1, -1, 12.3, 0, // cell reference
3148 : 0, 1, -1, 123, 0, 0, 0 // in-line values
3149 2 : };
3150 30 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
3151 : {
3152 28 : m_pDoc->GetValue(1, i, 0, result);
3153 28 : bool bGood = result == checks1[i];
3154 28 : if (!bGood)
3155 : {
3156 0 : cerr << "row " << (i+1) << ": expected=" << checks1[i] << " actual=" << result << endl;
3157 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
3158 : }
3159 : }
3160 : double checks2[] = {
3161 : 0, 1, -1, 12.3 // range references
3162 2 : };
3163 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
3164 : {
3165 8 : m_pDoc->GetValue(1, i+2, 0, result);
3166 8 : bool bGood = result == checks2[i];
3167 8 : if (!bGood)
3168 : {
3169 0 : cerr << "row " << (i+2+1) << ": expected=" << checks2[i] << " actual=" << result << endl;
3170 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
3171 : }
3172 : }
3173 :
3174 2 : m_pDoc->DeleteTab(0);
3175 2 : }
3176 :
3177 2 : void Test::testFuncCOUNTIF()
3178 : {
3179 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3180 :
3181 : // COUNTIF (test case adopted from OOo i#36381)
3182 :
3183 4 : OUString aTabName("foo");
3184 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3185 2 : m_pDoc->InsertTab (0, aTabName));
3186 :
3187 : // Empty A1:A39 first.
3188 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
3189 :
3190 : // Raw data (rows 1 through 9)
3191 : const char* aData[] = {
3192 : "1999",
3193 : "2000",
3194 : "0",
3195 : "0",
3196 : "0",
3197 : "2002",
3198 : "2001",
3199 : "X",
3200 : "2002"
3201 2 : };
3202 :
3203 2 : SCROW nRows = SAL_N_ELEMENTS(aData);
3204 20 : for (SCROW i = 0; i < nRows; ++i)
3205 18 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
3206 :
3207 2 : printRange(m_pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
3208 :
3209 : // formulas and results
3210 : struct {
3211 : const char* pFormula; double fResult;
3212 : } aChecks[] = {
3213 : { "=COUNTIF(A1:A12;1999)", 1 },
3214 : { "=COUNTIF(A1:A12;2002)", 2 },
3215 : { "=COUNTIF(A1:A12;1998)", 0 },
3216 : { "=COUNTIF(A1:A12;\">=1999\")", 5 },
3217 : { "=COUNTIF(A1:A12;\">1999\")", 4 },
3218 : { "=COUNTIF(A1:A12;\"<2001\")", 5 },
3219 : { "=COUNTIF(A1:A12;\">0\")", 5 },
3220 : { "=COUNTIF(A1:A12;\">=0\")", 8 },
3221 : { "=COUNTIF(A1:A12;0)", 3 },
3222 : { "=COUNTIF(A1:A12;\"X\")", 1 },
3223 : { "=COUNTIF(A1:A12;)", 3 }
3224 2 : };
3225 :
3226 2 : nRows = SAL_N_ELEMENTS(aChecks);
3227 24 : for (SCROW i = 0; i < nRows; ++i)
3228 : {
3229 22 : SCROW nRow = 20 + i;
3230 22 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
3231 : }
3232 :
3233 24 : for (SCROW i = 0; i < nRows; ++i)
3234 : {
3235 : double result;
3236 22 : SCROW nRow = 20 + i;
3237 22 : m_pDoc->GetValue(0, nRow, 0, result);
3238 22 : bool bGood = result == aChecks[i].fResult;
3239 22 : if (!bGood)
3240 : {
3241 0 : cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
3242 0 : << " expected=" << aChecks[i].fResult << " actual=" << result << endl;
3243 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
3244 : }
3245 : }
3246 :
3247 : // Don't count empty strings when searching for a number.
3248 :
3249 : // Clear A1:A2.
3250 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 1, 0));
3251 :
3252 2 : m_pDoc->SetString(0, 0, 0, OUString("=\"\""));
3253 2 : m_pDoc->SetString(0, 1, 0, OUString("=COUNTIF(A1;1)"));
3254 :
3255 2 : double result = m_pDoc->GetValue(0, 1, 0);
3256 2 : CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
3257 :
3258 : // Another test case adopted from fdo#77039.
3259 2 : clearSheet(m_pDoc, 0);
3260 :
3261 : // Set formula cells with blank results in A1:A4.
3262 10 : for (SCROW i = 0; i <=3; ++i)
3263 8 : m_pDoc->SetString(ScAddress(0,i,0), "=\"\"");
3264 :
3265 : // Insert formula into A5 to count all cells with empty strings.
3266 2 : m_pDoc->SetString(ScAddress(0,4,0), "=COUNTIF(A1:A4;\"\"");
3267 :
3268 : // We should correctly count with empty string key.
3269 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,4,0)));
3270 :
3271 4 : m_pDoc->DeleteTab(0);
3272 2 : }
3273 :
3274 2 : void Test::testFuncIF()
3275 : {
3276 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3277 :
3278 2 : m_pDoc->InsertTab(0, "Formula");
3279 :
3280 2 : m_pDoc->SetString(ScAddress(0,0,0), "=IF(B1=2;\"two\";\"not two\")");
3281 2 : CPPUNIT_ASSERT_EQUAL(OUString("not two"), m_pDoc->GetString(ScAddress(0,0,0)));
3282 2 : m_pDoc->SetValue(ScAddress(1,0,0), 2.0);
3283 2 : CPPUNIT_ASSERT_EQUAL(OUString("two"), m_pDoc->GetString(ScAddress(0,0,0)));
3284 2 : m_pDoc->SetValue(ScAddress(1,0,0), 3.0);
3285 2 : CPPUNIT_ASSERT_EQUAL(OUString("not two"), m_pDoc->GetString(ScAddress(0,0,0)));
3286 :
3287 2 : m_pDoc->DeleteTab(0);
3288 2 : }
3289 :
3290 2 : void Test::testFuncCHOOSE()
3291 : {
3292 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3293 :
3294 2 : m_pDoc->InsertTab(0, "Formula");
3295 :
3296 2 : m_pDoc->SetString(ScAddress(0,0,0), "=CHOOSE(B1;\"one\";\"two\";\"three\")");
3297 2 : sal_uInt16 nError = m_pDoc->GetErrCode(ScAddress(0,0,0));
3298 2 : CPPUNIT_ASSERT_MESSAGE("Formula result should be an error since B1 is still empty.", nError);
3299 2 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0);
3300 2 : CPPUNIT_ASSERT_EQUAL(OUString("one"), m_pDoc->GetString(ScAddress(0,0,0)));
3301 2 : m_pDoc->SetValue(ScAddress(1,0,0), 2.0);
3302 2 : CPPUNIT_ASSERT_EQUAL(OUString("two"), m_pDoc->GetString(ScAddress(0,0,0)));
3303 2 : m_pDoc->SetValue(ScAddress(1,0,0), 3.0);
3304 2 : CPPUNIT_ASSERT_EQUAL(OUString("three"), m_pDoc->GetString(ScAddress(0,0,0)));
3305 2 : m_pDoc->SetValue(ScAddress(1,0,0), 4.0);
3306 2 : nError = m_pDoc->GetErrCode(ScAddress(0,0,0));
3307 2 : CPPUNIT_ASSERT_MESSAGE("Formula result should be an error due to out-of-bound input..", nError);
3308 :
3309 2 : m_pDoc->DeleteTab(0);
3310 2 : }
3311 :
3312 2 : void Test::testFuncIFERROR()
3313 : {
3314 : // IFERROR/IFNA (fdo#56124)
3315 :
3316 2 : OUString aTabName("foo");
3317 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3318 2 : m_pDoc->InsertTab (0, aTabName));
3319 :
3320 : // Empty A1:A39 first.
3321 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
3322 :
3323 : // Raw data (rows 1 through 12)
3324 : const char* aData[] = {
3325 : "1",
3326 : "e",
3327 : "=SQRT(4)",
3328 : "=SQRT(-2)",
3329 : "=A4",
3330 : "=1/0",
3331 : "=NA()",
3332 : "bar",
3333 : "4",
3334 : "gee",
3335 : "=1/0",
3336 : "23"
3337 2 : };
3338 :
3339 2 : SCROW nRows = SAL_N_ELEMENTS(aData);
3340 26 : for (SCROW i = 0; i < nRows; ++i)
3341 24 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
3342 :
3343 2 : printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows-1, 0), "data range for IFERROR/IFNA");
3344 :
3345 : // formulas and results
3346 : struct {
3347 : const char* pFormula; const char* pResult;
3348 : } aChecks[] = {
3349 : { "=IFERROR(A1;9)", "1" },
3350 : { "=IFERROR(A2;9)", "e" },
3351 : { "=IFERROR(A3;9)", "2" },
3352 : { "=IFERROR(A4;-7)", "-7" },
3353 : { "=IFERROR(A5;-7)", "-7" },
3354 : { "=IFERROR(A6;-7)", "-7" },
3355 : { "=IFERROR(A7;-7)", "-7" },
3356 : { "=IFNA(A6;9)", "#DIV/0!" },
3357 : { "=IFNA(A7;-7)", "-7" },
3358 : { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
3359 : { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
3360 : { "{=IFERROR(3*A11:A12;1998)}[0]", "1998" }, // um.. this is not the correct way to insert a
3361 : { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place holder, see below
3362 2 : };
3363 :
3364 2 : nRows = SAL_N_ELEMENTS(aChecks);
3365 24 : for (SCROW i = 0; i < nRows-2; ++i)
3366 : {
3367 22 : SCROW nRow = 20 + i;
3368 22 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
3369 : }
3370 :
3371 : // Create a matrix range in last two rows of the range above, actual data
3372 : // of the placeholders.
3373 4 : ScMarkData aMark;
3374 2 : aMark.SelectOneTable(0);
3375 2 : m_pDoc->InsertMatrixFormula(0, 20 + nRows-2, 0, 20 + nRows-1, aMark, "=IFERROR(3*A11:A12;1998)", NULL);
3376 :
3377 2 : m_pDoc->CalcAll();
3378 :
3379 28 : for (SCROW i = 0; i < nRows; ++i)
3380 : {
3381 26 : SCROW nRow = 20 + i;
3382 26 : OUString aResult = m_pDoc->GetString(0, nRow, 0);
3383 52 : CPPUNIT_ASSERT_EQUAL_MESSAGE(
3384 26 : aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
3385 26 : }
3386 :
3387 4 : m_pDoc->DeleteTab(0);
3388 2 : }
3389 :
3390 2 : void Test::testFuncSHEET()
3391 : {
3392 2 : OUString aTabName1("test1");
3393 4 : OUString aTabName2("test2");
3394 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3395 2 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName1));
3396 :
3397 2 : m_pDoc->SetString(0, 0, 0, OUString("=SHEETS()"));
3398 2 : m_pDoc->CalcFormulaTree(false, false);
3399 : double original;
3400 2 : m_pDoc->GetValue(0, 0, 0, original);
3401 :
3402 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() should equal the number of sheets, but doesn't.",
3403 2 : static_cast<SCTAB>(original) == m_pDoc->GetTableCount());
3404 :
3405 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3406 2 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName2));
3407 :
3408 : double modified;
3409 2 : m_pDoc->GetValue(0, 0, 0, modified);
3410 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
3411 2 : modified - original == 1.0);
3412 :
3413 2 : SCTAB nTabCount = m_pDoc->GetTableCount();
3414 2 : m_pDoc->DeleteTab(--nTabCount);
3415 :
3416 2 : m_pDoc->GetValue(0, 0, 0, modified);
3417 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet removal.",
3418 2 : modified - original == 0.0);
3419 :
3420 4 : m_pDoc->DeleteTab(--nTabCount);
3421 2 : }
3422 :
3423 2 : void Test::testFuncNOW()
3424 : {
3425 2 : OUString aTabName("foo");
3426 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3427 2 : m_pDoc->InsertTab (0, aTabName));
3428 :
3429 2 : double val = 1;
3430 2 : m_pDoc->SetValue(0, 0, 0, val);
3431 2 : m_pDoc->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
3432 : double now1;
3433 2 : m_pDoc->GetValue(0, 1, 0, now1);
3434 2 : CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
3435 :
3436 2 : val = 0;
3437 2 : m_pDoc->SetValue(0, 0, 0, val);
3438 2 : m_pDoc->CalcFormulaTree(false, false);
3439 : double zero;
3440 2 : m_pDoc->GetValue(0, 1, 0, zero);
3441 2 : CPPUNIT_ASSERT_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", zero == 0.0);
3442 :
3443 2 : val = 1;
3444 2 : m_pDoc->SetValue(0, 0, 0, val);
3445 2 : m_pDoc->CalcFormulaTree(false, false);
3446 : double now2;
3447 2 : m_pDoc->GetValue(0, 1, 0, now2);
3448 2 : CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
3449 :
3450 2 : m_pDoc->DeleteTab(0);
3451 2 : }
3452 :
3453 2 : void Test::testFuncNUMBERVALUE()
3454 : {
3455 : // NUMBERVALUE fdo#57180
3456 :
3457 2 : OUString aTabName("foo");
3458 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3459 2 : m_pDoc->InsertTab (0, aTabName));
3460 :
3461 : // Empty A1:A39 first.
3462 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
3463 :
3464 : // Raw data (rows 1 through 6)
3465 : const char* aData[] = {
3466 : "1ag9a9b9",
3467 : "1ag34 5g g6 78b9%%",
3468 : "1 234d56E-2",
3469 : "d4",
3470 : "54.4",
3471 : "1a2b3e1%"
3472 2 : };
3473 :
3474 2 : SCROW nRows = SAL_N_ELEMENTS(aData);
3475 14 : for (SCROW i = 0; i < nRows; ++i)
3476 12 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
3477 :
3478 2 : printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows - 1, 0), "data range for NUMBERVALUE");
3479 :
3480 : // formulas and results
3481 : struct {
3482 : const char* pFormula; const char* pResult;
3483 : } aChecks[] = {
3484 : { "=NUMBERVALUE(A1;\"b\";\"ag\")", "199.9" },
3485 : { "=NUMBERVALUE(A2;\"b\";\"ag\")", "134.56789" },
3486 : { "=NUMBERVALUE(A2;\"b\";\"g\")", "#VALUE!" },
3487 : { "=NUMBERVALUE(A3;\"d\")", "12.3456" },
3488 : { "=NUMBERVALUE(A4;\"d\";\"foo\")", "0.4" },
3489 : { "=NUMBERVALUE(A4;)", "Err:502" },
3490 : { "=NUMBERVALUE(A5;)", "Err:502" },
3491 : { "=NUMBERVALUE(A6;\"b\";\"a\")", "1.23" }
3492 2 : };
3493 :
3494 2 : nRows = SAL_N_ELEMENTS(aChecks);
3495 18 : for (SCROW i = 0; i < nRows; ++i)
3496 : {
3497 16 : SCROW nRow = 20 + i;
3498 16 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
3499 : }
3500 2 : m_pDoc->CalcAll();
3501 :
3502 18 : for (SCROW i = 0; i < nRows; ++i)
3503 : {
3504 16 : SCROW nRow = 20 + i;
3505 16 : OUString aResult = m_pDoc->GetString(0, nRow, 0);
3506 32 : CPPUNIT_ASSERT_EQUAL_MESSAGE(
3507 16 : aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
3508 16 : }
3509 :
3510 2 : m_pDoc->DeleteTab(0);
3511 2 : }
3512 :
3513 2 : void Test::testFuncLEN()
3514 : {
3515 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
3516 :
3517 2 : m_pDoc->InsertTab(0, "Formula");
3518 :
3519 : // Leave A1:A3 empty, and insert an array of LEN in B1:B3 that references
3520 : // these empty cells.
3521 :
3522 4 : ScMarkData aMark;
3523 2 : aMark.SelectOneTable(0);
3524 2 : m_pDoc->InsertMatrixFormula(1, 0, 1, 2, aMark, "=LEN(A1:A3)", NULL);
3525 :
3526 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
3527 2 : CPPUNIT_ASSERT(pFC);
3528 4 : CPPUNIT_ASSERT_MESSAGE("This formulashould be a matrix origin.",
3529 2 : pFC->GetMatrixFlag() == MM_FORMULA);
3530 :
3531 : // This should be a 1x3 matrix.
3532 2 : SCCOL nCols = -1;
3533 2 : SCROW nRows = -1;
3534 2 : pFC->GetMatColsRows(nCols, nRows);
3535 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCols);
3536 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), nRows);
3537 :
3538 : // LEN value should be 0 for an empty cell.
3539 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1,0,0)));
3540 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1,1,0)));
3541 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(ScAddress(1,2,0)));
3542 :
3543 4 : m_pDoc->DeleteTab(0);
3544 2 : }
3545 :
3546 2 : void Test::testFuncLOOKUP()
3547 : {
3548 2 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
3549 :
3550 2 : m_pDoc->InsertTab(0, "Test");
3551 :
3552 : // Raw data
3553 : const char* aData[][2] = {
3554 : { "=CONCATENATE(\"A\")", "1" },
3555 : { "=CONCATENATE(\"B\")", "2" },
3556 : { "=CONCATENATE(\"C\")", "3" },
3557 : { 0, 0 } // terminator
3558 2 : };
3559 :
3560 : // Insert raw data into A1:B3.
3561 8 : for (SCROW i = 0; aData[i][0]; ++i)
3562 : {
3563 6 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
3564 6 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
3565 : }
3566 :
3567 : const char* aData2[][2] = {
3568 : { "A", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
3569 : { "B", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
3570 : { "C", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
3571 : { 0, 0 } // terminator
3572 2 : };
3573 :
3574 : // Insert check formulas into A5:B7.
3575 8 : for (SCROW i = 0; aData2[i][0]; ++i)
3576 : {
3577 6 : m_pDoc->SetString(0, i+4, 0, OUString::createFromAscii(aData2[i][0]));
3578 6 : m_pDoc->SetString(1, i+4, 0, OUString::createFromAscii(aData2[i][1]));
3579 : }
3580 :
3581 2 : printRange(m_pDoc, ScRange(0,4,0,1,6,0), "Data range for LOOKUP.");
3582 :
3583 : // Values for B5:B7 should be 1, 2, and 3.
3584 2 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,4,0)) == 0);
3585 2 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,5,0)) == 0);
3586 2 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,6,0)) == 0);
3587 :
3588 2 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,4,0)));
3589 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,5,0)));
3590 2 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,6,0)));
3591 :
3592 2 : m_pDoc->DeleteTab(0);
3593 2 : }
3594 :
3595 2 : void Test::testFuncVLOOKUP()
3596 : {
3597 : // VLOOKUP
3598 :
3599 2 : OUString aTabName("foo");
3600 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3601 2 : m_pDoc->InsertTab (0, aTabName));
3602 :
3603 : // Clear A1:F40.
3604 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 5, 39, 0));
3605 :
3606 : // Raw data
3607 : const char* aData[][2] = {
3608 : { "Key", "Val" },
3609 : { "10", "3" },
3610 : { "20", "4" },
3611 : { "30", "5" },
3612 : { "40", "6" },
3613 : { "50", "7" },
3614 : { "60", "8" },
3615 : { "70", "9" },
3616 : { "B", "10" },
3617 : { "B", "11" },
3618 : { "C", "12" },
3619 : { "D", "13" },
3620 : { "E", "14" },
3621 : { "F", "15" },
3622 : { 0, 0 } // terminator
3623 2 : };
3624 :
3625 : // Insert raw data into A1:B14.
3626 30 : for (SCROW i = 0; aData[i][0]; ++i)
3627 : {
3628 28 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
3629 28 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
3630 : }
3631 :
3632 2 : printRange(m_pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
3633 :
3634 : // Formula data
3635 : struct {
3636 : const char* pLookup; const char* pFormula; const char* pRes;
3637 : } aChecks[] = {
3638 : { "Lookup", "Formula", 0 },
3639 : { "12", "=VLOOKUP(D2;A2:B14;2;1)", "3" },
3640 : { "29", "=VLOOKUP(D3;A2:B14;2;1)", "4" },
3641 : { "31", "=VLOOKUP(D4;A2:B14;2;1)", "5" },
3642 : { "45", "=VLOOKUP(D5;A2:B14;2;1)", "6" },
3643 : { "56", "=VLOOKUP(D6;A2:B14;2;1)", "7" },
3644 : { "65", "=VLOOKUP(D7;A2:B14;2;1)", "8" },
3645 : { "78", "=VLOOKUP(D8;A2:B14;2;1)", "9" },
3646 : { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", "#N/A" },
3647 : { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", "11" },
3648 : { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", "12" },
3649 : { "David", "=VLOOKUP(D12;A2:B14;2;1)", "13" },
3650 : { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", "14" },
3651 : { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", "15" },
3652 : { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", "15" },
3653 : { "100", "=VLOOKUP(D16;A2:B14;2;1)", "9" },
3654 : { "1000", "=VLOOKUP(D17;A2:B14;2;1)", "9" },
3655 : { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", "15" }
3656 2 : };
3657 :
3658 : // Insert formula data into D1:E18.
3659 38 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3660 : {
3661 36 : m_pDoc->SetString(3, i, 0, OUString::createFromAscii(aChecks[i].pLookup));
3662 36 : m_pDoc->SetString(4, i, 0, OUString::createFromAscii(aChecks[i].pFormula));
3663 : }
3664 2 : m_pDoc->CalcAll();
3665 2 : printRange(m_pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
3666 :
3667 : // Verify results.
3668 38 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3669 : {
3670 36 : if (i == 0)
3671 : // Skip the header row.
3672 2 : continue;
3673 :
3674 34 : OUString aRes = m_pDoc->GetString(4, i, 0);
3675 34 : bool bGood = aRes.equalsAscii(aChecks[i].pRes);
3676 34 : if (!bGood)
3677 : {
3678 0 : cerr << "row " << (i+1) << ": lookup value='" << aChecks[i].pLookup
3679 0 : << "' expected='" << aChecks[i].pRes << "' actual='" << aRes << "'" << endl;
3680 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
3681 : }
3682 34 : }
3683 :
3684 : // Clear the sheet and start over.
3685 2 : clearSheet(m_pDoc, 0);
3686 :
3687 : // Lookup on sorted data intersparsed with empty cells.
3688 :
3689 : // A1:B8 is the search range.
3690 2 : m_pDoc->SetValue(ScAddress(0,2,0), 1.0);
3691 2 : m_pDoc->SetValue(ScAddress(0,4,0), 2.0);
3692 2 : m_pDoc->SetValue(ScAddress(0,7,0), 4.0);
3693 2 : m_pDoc->SetString(ScAddress(1,2,0), "One");
3694 2 : m_pDoc->SetString(ScAddress(1,4,0), "Two");
3695 2 : m_pDoc->SetString(ScAddress(1,7,0), "Four");
3696 :
3697 : // D1:D5 contain match values.
3698 2 : m_pDoc->SetValue(ScAddress(3,0,0), 1.0);
3699 2 : m_pDoc->SetValue(ScAddress(3,1,0), 2.0);
3700 2 : m_pDoc->SetValue(ScAddress(3,2,0), 3.0);
3701 2 : m_pDoc->SetValue(ScAddress(3,3,0), 4.0);
3702 2 : m_pDoc->SetValue(ScAddress(3,4,0), 5.0);
3703 :
3704 : // E1:E5 contain formulas.
3705 2 : m_pDoc->SetString(ScAddress(4,0,0), "=VLOOKUP(D1;$A$1:$B$8;2)");
3706 2 : m_pDoc->SetString(ScAddress(4,1,0), "=VLOOKUP(D2;$A$1:$B$8;2)");
3707 2 : m_pDoc->SetString(ScAddress(4,2,0), "=VLOOKUP(D3;$A$1:$B$8;2)");
3708 2 : m_pDoc->SetString(ScAddress(4,3,0), "=VLOOKUP(D4;$A$1:$B$8;2)");
3709 2 : m_pDoc->SetString(ScAddress(4,4,0), "=VLOOKUP(D5;$A$1:$B$8;2)");
3710 2 : m_pDoc->CalcAll();
3711 :
3712 : // Check the formula results in E1:E5.
3713 2 : CPPUNIT_ASSERT_EQUAL(OUString("One"), m_pDoc->GetString(ScAddress(4,0,0)));
3714 2 : CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4,1,0)));
3715 2 : CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4,2,0)));
3716 2 : CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4,3,0)));
3717 2 : CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4,4,0)));
3718 :
3719 : // Start over again.
3720 2 : clearSheet(m_pDoc, 0);
3721 :
3722 : // Set A,B,....,G to A1:A7.
3723 2 : m_pDoc->SetString(ScAddress(0,0,0), "A");
3724 2 : m_pDoc->SetString(ScAddress(0,1,0), "B");
3725 2 : m_pDoc->SetString(ScAddress(0,2,0), "C");
3726 2 : m_pDoc->SetString(ScAddress(0,3,0), "D");
3727 2 : m_pDoc->SetString(ScAddress(0,4,0), "E");
3728 2 : m_pDoc->SetString(ScAddress(0,5,0), "F");
3729 2 : m_pDoc->SetString(ScAddress(0,6,0), "G");
3730 :
3731 : // Set the formula in C1.
3732 2 : m_pDoc->SetString(ScAddress(2,0,0), "=VLOOKUP(\"C\";A1:A16;1)");
3733 2 : CPPUNIT_ASSERT_EQUAL(OUString("C"), m_pDoc->GetString(ScAddress(2,0,0)));
3734 :
3735 2 : m_pDoc->DeleteTab(0);
3736 2 : }
3737 :
3738 : struct NumStrCheck {
3739 : double fVal;
3740 : const char* pRes;
3741 : };
3742 :
3743 : struct StrStrCheck {
3744 : const char* pVal;
3745 : const char* pRes;
3746 : };
3747 :
3748 : template<size_t _DataSize, size_t _FormulaSize, int _Type>
3749 4 : void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
3750 : {
3751 4 : size_t nDataSize = _DataSize;
3752 52 : for (size_t i = 0; i < nDataSize; ++i)
3753 48 : pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
3754 :
3755 66 : for (size_t i = 0; i < _FormulaSize; ++i)
3756 : {
3757 62 : pDoc->SetString(1, i, 0, OUString::createFromAscii(aChecks[i].pVal));
3758 :
3759 62 : OUStringBuffer aBuf;
3760 62 : aBuf.appendAscii("=MATCH(B");
3761 62 : aBuf.append(static_cast<sal_Int32>(i+1));
3762 62 : aBuf.appendAscii(";A1:A");
3763 62 : aBuf.append(static_cast<sal_Int32>(nDataSize));
3764 62 : aBuf.appendAscii(";");
3765 62 : aBuf.append(static_cast<sal_Int32>(_Type));
3766 62 : aBuf.appendAscii(")");
3767 124 : OUString aFormula = aBuf.makeStringAndClear();
3768 62 : pDoc->SetString(2, i, 0, aFormula);
3769 : }
3770 :
3771 4 : pDoc->CalcAll();
3772 4 : Test::printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
3773 :
3774 : // verify the results.
3775 66 : for (size_t i = 0; i < _FormulaSize; ++i)
3776 : {
3777 62 : OUString aStr = pDoc->GetString(2, i, 0);
3778 62 : if (!aStr.equalsAscii(aChecks[i].pRes))
3779 : {
3780 0 : cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
3781 0 : " criterion='" << aChecks[i].pVal << "'" << endl;
3782 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
3783 : }
3784 : }
3785 4 : }
3786 :
3787 : template<size_t _DataSize, size_t _FormulaSize, int _Type>
3788 4 : void runTestHorizontalMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
3789 : {
3790 4 : size_t nDataSize = _DataSize;
3791 52 : for (size_t i = 0; i < nDataSize; ++i)
3792 48 : pDoc->SetString(i, 0, 0, OUString::createFromAscii(aData[i]));
3793 :
3794 66 : for (size_t i = 0; i < _FormulaSize; ++i)
3795 : {
3796 62 : pDoc->SetString(i, 1, 0, OUString::createFromAscii(aChecks[i].pVal));
3797 :
3798 : // Assume we don't have more than 26 data columns..
3799 62 : OUStringBuffer aBuf;
3800 62 : aBuf.appendAscii("=MATCH(");
3801 62 : aBuf.append(static_cast<sal_Unicode>('A'+i));
3802 62 : aBuf.appendAscii("2;A1:");
3803 62 : aBuf.append(static_cast<sal_Unicode>('A'+nDataSize));
3804 62 : aBuf.appendAscii("1;");
3805 62 : aBuf.append(static_cast<sal_Int32>(_Type));
3806 62 : aBuf.appendAscii(")");
3807 124 : OUString aFormula = aBuf.makeStringAndClear();
3808 62 : pDoc->SetString(i, 2, 0, aFormula);
3809 : }
3810 :
3811 4 : pDoc->CalcAll();
3812 4 : Test::printRange(pDoc, ScRange(0, 0, 0, _FormulaSize-1, 2, 0), "MATCH");
3813 :
3814 : // verify the results.
3815 66 : for (size_t i = 0; i < _FormulaSize; ++i)
3816 : {
3817 62 : OUString aStr = pDoc->GetString(i, 2, 0);
3818 62 : if (!aStr.equalsAscii(aChecks[i].pRes))
3819 : {
3820 0 : cerr << "column " << char('A'+i) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
3821 0 : " criterion='" << aChecks[i].pVal << "'" << endl;
3822 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for horizontal MATCH", false);
3823 : }
3824 : }
3825 4 : }
3826 :
3827 2 : void Test::testFuncMATCH()
3828 : {
3829 2 : OUString aTabName("foo");
3830 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3831 2 : m_pDoc->InsertTab (0, aTabName));
3832 :
3833 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 40, 0));
3834 : {
3835 : // Ascending in-exact match
3836 :
3837 : // data range (A1:A9)
3838 : const char* aData[] = {
3839 : "1",
3840 : "2",
3841 : "3",
3842 : "4",
3843 : "5",
3844 : "6",
3845 : "7",
3846 : "8",
3847 : "9",
3848 : "B",
3849 : "B",
3850 : "C",
3851 2 : };
3852 :
3853 : // formula (B1:C12)
3854 : StrStrCheck aChecks[] = {
3855 : { "0.8", "#N/A" },
3856 : { "1.2", "1" },
3857 : { "2.3", "2" },
3858 : { "3.9", "3" },
3859 : { "4.1", "4" },
3860 : { "5.99", "5" },
3861 : { "6.1", "6" },
3862 : { "7.2", "7" },
3863 : { "8.569", "8" },
3864 : { "9.59", "9" },
3865 : { "10", "9" },
3866 : { "100", "9" },
3867 : { "Andy", "#N/A" },
3868 : { "Bruce", "11" },
3869 : { "Charlie", "12" }
3870 2 : };
3871 :
3872 2 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(m_pDoc, aData, aChecks);
3873 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 4, 40, 0));
3874 2 : runTestHorizontalMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(m_pDoc, aData, aChecks);
3875 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
3876 : }
3877 :
3878 : {
3879 : // Descending in-exact match
3880 :
3881 : // data range (A1:A9)
3882 : const char* aData[] = {
3883 : "D",
3884 : "C",
3885 : "B",
3886 : "9",
3887 : "8",
3888 : "7",
3889 : "6",
3890 : "5",
3891 : "4",
3892 : "3",
3893 : "2",
3894 : "1"
3895 2 : };
3896 :
3897 : // formula (B1:C12)
3898 : StrStrCheck aChecks[] = {
3899 : { "10", "#N/A" },
3900 : { "8.9", "4" },
3901 : { "7.8", "5" },
3902 : { "6.7", "6" },
3903 : { "5.5", "7" },
3904 : { "4.6", "8" },
3905 : { "3.3", "9" },
3906 : { "2.2", "10" },
3907 : { "1.1", "11" },
3908 : { "0.8", "12" },
3909 : { "0", "12" },
3910 : { "-2", "12" },
3911 : { "Andy", "3" },
3912 : { "Bruce", "2" },
3913 : { "Charlie", "1" },
3914 : { "David", "#N/A" }
3915 2 : };
3916 :
3917 2 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(m_pDoc, aData, aChecks);
3918 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 4, 40, 0));
3919 2 : runTestHorizontalMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(m_pDoc, aData, aChecks);
3920 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
3921 : }
3922 :
3923 : {
3924 : // search range contains leading and trailing empty cell ranges.
3925 :
3926 2 : clearRange(m_pDoc, ScRange(0,0,0,2,100,0));
3927 :
3928 : // A5:A8 contains sorted values.
3929 2 : m_pDoc->SetValue(ScAddress(0,4,0), 1.0);
3930 2 : m_pDoc->SetValue(ScAddress(0,5,0), 2.0);
3931 2 : m_pDoc->SetValue(ScAddress(0,6,0), 3.0);
3932 2 : m_pDoc->SetValue(ScAddress(0,7,0), 4.0);
3933 :
3934 : // Find value 2 which is in A6.
3935 2 : m_pDoc->SetString(ScAddress(1,0,0), "=MATCH(2;A1:A20)");
3936 2 : m_pDoc->CalcAll();
3937 :
3938 2 : CPPUNIT_ASSERT_EQUAL(OUString("6"), m_pDoc->GetString(ScAddress(1,0,0)));
3939 : }
3940 :
3941 2 : m_pDoc->DeleteTab(0);
3942 2 : }
3943 :
3944 2 : void Test::testFuncCELL()
3945 : {
3946 2 : OUString aTabName("foo");
3947 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3948 2 : m_pDoc->InsertTab (0, aTabName));
3949 :
3950 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
3951 :
3952 : {
3953 2 : const char* pContent = "Some random text";
3954 2 : m_pDoc->SetString(2, 9, 0, OUString::createFromAscii(pContent)); // Set this value to C10.
3955 2 : double val = 1.2;
3956 2 : m_pDoc->SetValue(2, 0, 0, val); // Set numeric value to C1;
3957 :
3958 : // We don't test: FILENAME, FORMAT, WIDTH, PROTECT, PREFIX
3959 : StrStrCheck aChecks[] = {
3960 : { "=CELL(\"COL\";C10)", "3" },
3961 : { "=CELL(\"ROW\";C10)", "10" },
3962 : { "=CELL(\"SHEET\";C10)", "1" },
3963 : { "=CELL(\"ADDRESS\";C10)", "$C$10" },
3964 : { "=CELL(\"CONTENTS\";C10)", pContent },
3965 : { "=CELL(\"COLOR\";C10)", "0" },
3966 : { "=CELL(\"TYPE\";C9)", "b" },
3967 : { "=CELL(\"TYPE\";C10)", "l" },
3968 : { "=CELL(\"TYPE\";C1)", "v" },
3969 : { "=CELL(\"PARENTHESES\";C10)", "0" }
3970 2 : };
3971 :
3972 22 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3973 20 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aChecks[i].pVal));
3974 2 : m_pDoc->CalcAll();
3975 :
3976 22 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3977 : {
3978 20 : OUString aVal = m_pDoc->GetString(0, i, 0);
3979 20 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
3980 20 : }
3981 : }
3982 :
3983 2 : m_pDoc->DeleteTab(0);
3984 2 : }
3985 :
3986 : /** See also test case document fdo#44456 sheet cpearson */
3987 2 : void Test::testFuncDATEDIF()
3988 : {
3989 2 : OUString aTabName("foo");
3990 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3991 2 : m_pDoc->InsertTab (0, aTabName));
3992 :
3993 : const char* aData[][5] = {
3994 : { "2007-01-01", "2007-01-10", "d", "9", "=DATEDIF(A1;B1;C1)" } ,
3995 : { "2007-01-01", "2007-01-31", "m", "0", "=DATEDIF(A2;B2;C2)" } ,
3996 : { "2007-01-01", "2007-02-01", "m", "1", "=DATEDIF(A3;B3;C3)" } ,
3997 : { "2007-01-01", "2007-02-28", "m", "1", "=DATEDIF(A4;B4;C4)" } ,
3998 : { "2007-01-01", "2007-12-31", "d", "364", "=DATEDIF(A5;B5;C5)" } ,
3999 : { "2007-01-01", "2007-01-31", "y", "0", "=DATEDIF(A6;B6;C6)" } ,
4000 : { "2007-01-01", "2008-07-01", "d", "547", "=DATEDIF(A7;B7;C7)" } ,
4001 : { "2007-01-01", "2008-07-01", "m", "18", "=DATEDIF(A8;B8;C8)" } ,
4002 : { "2007-01-01", "2008-07-01", "ym", "6", "=DATEDIF(A9;B9;C9)" } ,
4003 : { "2007-01-01", "2008-07-01", "yd", "182", "=DATEDIF(A10;B10;C10)" } ,
4004 : { "2008-01-01", "2009-07-01", "yd", "181", "=DATEDIF(A11;B11;C11)" } ,
4005 : { "2007-01-01", "2007-01-31", "md", "30", "=DATEDIF(A12;B12;C12)" } ,
4006 : { "2007-02-01", "2009-03-01", "md", "0", "=DATEDIF(A13;B13;C13)" } ,
4007 : { "2008-02-01", "2009-03-01", "md", "0", "=DATEDIF(A14;B14;C14)" } ,
4008 : { "2007-01-02", "2007-01-01", "md", "Err:502", "=DATEDIF(A15;B15;C15)" } // fail date1 > date2
4009 2 : };
4010 :
4011 2 : clearRange( m_pDoc, ScRange(0, 0, 0, 4, SAL_N_ELEMENTS(aData), 0));
4012 2 : ScAddress aPos(0,0,0);
4013 2 : ScRange aDataRange = insertRangeData( m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
4014 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
4015 :
4016 2 : m_pDoc->CalcAll();
4017 :
4018 32 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData); ++i)
4019 : {
4020 30 : OUString aVal = m_pDoc->GetString( 4, i, 0);
4021 : //std::cout << "row "<< i << ": " << OUStringToOString( aVal, RTL_TEXTENCODING_UTF8).getStr() << ", expected " << aData[i][3] << std::endl;
4022 30 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal.equalsAscii( aData[i][3]));
4023 30 : }
4024 :
4025 2 : m_pDoc->DeleteTab(0);
4026 2 : }
4027 :
4028 2 : void Test::testFuncINDIRECT()
4029 : {
4030 2 : OUString aTabName("foo");
4031 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
4032 2 : m_pDoc->InsertTab (0, aTabName));
4033 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
4034 :
4035 2 : bool bGood = m_pDoc->GetName(0, aTabName);
4036 2 : CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
4037 :
4038 4 : OUString aTest = "Test", aRefErr = "#REF!";
4039 2 : m_pDoc->SetString(0, 10, 0, aTest);
4040 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", m_pDoc->GetString(0,10,0) == aTest);
4041 :
4042 4 : OUString aPrefix = "=INDIRECT(\"";
4043 :
4044 4 : OUString aFormula = aPrefix + aTabName + ".A11\")"; // Calc A1
4045 2 : m_pDoc->SetString(0, 0, 0, aFormula);
4046 2 : aFormula = aPrefix + aTabName + "!A11\")"; // Excel A1
4047 2 : m_pDoc->SetString(0, 1, 0, aFormula);
4048 2 : aFormula = aPrefix + aTabName + "!R11C1\")"; // Excel R1C1
4049 2 : m_pDoc->SetString(0, 2, 0, aFormula);
4050 2 : aFormula = aPrefix + aTabName + "!R11C1\";0)"; // Excel R1C1 (forced)
4051 2 : m_pDoc->SetString(0, 3, 0, aFormula);
4052 :
4053 2 : m_pDoc->CalcAll();
4054 : {
4055 : // Default is to use the current formula syntax, which is Calc A1.
4056 : const OUString* aChecks[] = {
4057 : &aTest, &aRefErr, &aRefErr, &aTest
4058 2 : };
4059 :
4060 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4061 : {
4062 8 : OUString aVal = m_pDoc->GetString(0, i, 0);
4063 8 : CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
4064 8 : }
4065 : }
4066 :
4067 4 : ScCalcConfig aConfig;
4068 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
4069 2 : m_pDoc->SetCalcConfig(aConfig);
4070 2 : m_pDoc->CalcAll();
4071 : {
4072 : // Explicit Calc A1 syntax
4073 : const OUString* aChecks[] = {
4074 : &aTest, &aRefErr, &aRefErr, &aTest
4075 2 : };
4076 :
4077 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4078 : {
4079 8 : OUString aVal = m_pDoc->GetString(0, i, 0);
4080 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
4081 8 : }
4082 : }
4083 :
4084 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
4085 2 : m_pDoc->SetCalcConfig(aConfig);
4086 2 : m_pDoc->CalcAll();
4087 : {
4088 : // Excel A1 syntax
4089 : const OUString* aChecks[] = {
4090 : &aRefErr, &aTest, &aRefErr, &aTest
4091 2 : };
4092 :
4093 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4094 : {
4095 8 : OUString aVal = m_pDoc->GetString(0, i, 0);
4096 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
4097 8 : }
4098 : }
4099 :
4100 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
4101 2 : m_pDoc->SetCalcConfig(aConfig);
4102 2 : m_pDoc->CalcAll();
4103 : {
4104 : // Excel R1C1 syntax
4105 : const OUString* aChecks[] = {
4106 : &aRefErr, &aRefErr, &aTest, &aTest
4107 2 : };
4108 :
4109 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4110 : {
4111 8 : OUString aVal = m_pDoc->GetString(0, i, 0);
4112 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
4113 8 : }
4114 : }
4115 :
4116 4 : m_pDoc->DeleteTab(0);
4117 2 : }
4118 :
4119 2 : void Test::testFormulaDepTracking()
4120 : {
4121 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
4122 :
4123 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
4124 :
4125 : // B2 listens on D2.
4126 2 : m_pDoc->SetString(1, 1, 0, "=D2");
4127 2 : double val = -999.0; // dummy initial value
4128 2 : m_pDoc->GetValue(1, 1, 0, val);
4129 2 : CPPUNIT_ASSERT_MESSAGE("Referencing an empty cell should yield zero.", val == 0.0);
4130 :
4131 : // Changing the value of D2 should trigger recalculation of B2.
4132 2 : m_pDoc->SetValue(3, 1, 0, 1.1);
4133 2 : m_pDoc->GetValue(1, 1, 0, val);
4134 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 1.1);
4135 :
4136 : // And again.
4137 2 : m_pDoc->SetValue(3, 1, 0, 2.2);
4138 2 : m_pDoc->GetValue(1, 1, 0, val);
4139 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 2.2);
4140 :
4141 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
4142 :
4143 : // Now, let's test the range dependency tracking.
4144 :
4145 : // B2 listens on D2:E6.
4146 2 : m_pDoc->SetString(1, 1, 0, "=SUM(D2:E6)");
4147 2 : m_pDoc->GetValue(1, 1, 0, val);
4148 2 : CPPUNIT_ASSERT_MESSAGE("Summing an empty range should yield zero.", val == 0.0);
4149 :
4150 : // Set value to E3. This should trigger recalc on B2.
4151 2 : m_pDoc->SetValue(4, 2, 0, 2.4);
4152 2 : m_pDoc->GetValue(1, 1, 0, val);
4153 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", val == 2.4);
4154 :
4155 : // Set value to D5 to trigger recalc again. Note that this causes an
4156 : // addition of 1.2 + 2.4 which is subject to binary floating point
4157 : // rounding error. We need to use approxEqual to assess its value.
4158 :
4159 2 : m_pDoc->SetValue(3, 4, 0, 1.2);
4160 2 : m_pDoc->GetValue(1, 1, 0, val);
4161 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 3.6));
4162 :
4163 : // Change the value of D2 (boundary case).
4164 2 : m_pDoc->SetValue(3, 1, 0, 1.0);
4165 2 : m_pDoc->GetValue(1, 1, 0, val);
4166 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 4.6));
4167 :
4168 : // Change the value of E6 (another boundary case).
4169 2 : m_pDoc->SetValue(4, 5, 0, 2.0);
4170 2 : m_pDoc->GetValue(1, 1, 0, val);
4171 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 6.6));
4172 :
4173 : // Change the value of D6 (another boundary case).
4174 2 : m_pDoc->SetValue(3, 5, 0, 3.0);
4175 2 : m_pDoc->GetValue(1, 1, 0, val);
4176 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 9.6));
4177 :
4178 : // Change the value of E2 (another boundary case).
4179 2 : m_pDoc->SetValue(4, 1, 0, 0.4);
4180 2 : m_pDoc->GetValue(1, 1, 0, val);
4181 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 10.0));
4182 :
4183 : // Change the existing non-empty value cell (E2).
4184 2 : m_pDoc->SetValue(4, 1, 0, 2.4);
4185 2 : m_pDoc->GetValue(1, 1, 0, val);
4186 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 12.0));
4187 :
4188 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
4189 :
4190 : // Now, column-based dependency tracking. We now switch to the R1C1
4191 : // syntax which is easier to use for repeated relative references.
4192 :
4193 4 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
4194 :
4195 2 : val = 0.0;
4196 20 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
4197 : {
4198 : // Static value in column 1.
4199 18 : m_pDoc->SetValue(0, nRow, 0, ++val);
4200 :
4201 : // Formula in column 2 that references cell to the left.
4202 18 : m_pDoc->SetString(1, nRow, 0, "=RC[-1]");
4203 :
4204 : // Formula in column 3 that references cell to the left.
4205 18 : m_pDoc->SetString(2, nRow, 0, "=RC[-1]*2");
4206 : }
4207 :
4208 : // Check formula values.
4209 2 : val = 0.0;
4210 20 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
4211 : {
4212 18 : ++val;
4213 18 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, nRow, 0) == val);
4214 18 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, nRow, 0) == val*2.0);
4215 : }
4216 :
4217 : // Intentionally insert a formula in column 1. This will break column 1's
4218 : // uniformity of consisting only of static value cells.
4219 2 : m_pDoc->SetString(0, 4, 0, "=R2C3");
4220 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(0, 4, 0) == 2.0);
4221 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, 4, 0) == 2.0);
4222 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, 4, 0) == 4.0);
4223 :
4224 4 : m_pDoc->DeleteTab(0);
4225 2 : }
4226 :
4227 2 : void Test::testFormulaDepTracking2()
4228 : {
4229 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
4230 :
4231 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
4232 :
4233 2 : double val = 2.0;
4234 2 : m_pDoc->SetValue(0, 0, 0, val);
4235 2 : val = 4.0;
4236 2 : m_pDoc->SetValue(1, 0, 0, val);
4237 2 : val = 5.0;
4238 2 : m_pDoc->SetValue(0, 1, 0, val);
4239 2 : m_pDoc->SetString(2, 0, 0, "=A1/B1");
4240 2 : m_pDoc->SetString(1, 1, 0, "=B1*C1");
4241 :
4242 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1, 1, 0)); // B2 should equal 2.
4243 :
4244 2 : clearRange(m_pDoc, ScAddress(2, 0, 0)); // Delete C1.
4245 :
4246 2 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(1, 1, 0)); // B2 should now equal 0.
4247 :
4248 2 : m_pDoc->DeleteTab(0);
4249 2 : }
4250 :
4251 2 : void Test::testFormulaDepTrackingDeleteRow()
4252 : {
4253 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
4254 :
4255 2 : m_pDoc->InsertTab(0, "Test");
4256 :
4257 : // Values in A1:A3.
4258 2 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
4259 2 : m_pDoc->SetValue(ScAddress(0,1,0), 3.0);
4260 2 : m_pDoc->SetValue(ScAddress(0,2,0), 5.0);
4261 :
4262 : // SUM(A1:A3) in A5.
4263 2 : m_pDoc->SetString(ScAddress(0,4,0), "=SUM(A1:A3)");
4264 :
4265 : // A6 to reference A5.
4266 2 : m_pDoc->SetString(ScAddress(0,5,0), "=A5*10");
4267 2 : const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,5,0));
4268 2 : CPPUNIT_ASSERT(pFC);
4269 :
4270 : // A4 should have a broadcaster with A5 listening to it.
4271 2 : SvtBroadcaster* pBC = m_pDoc->GetBroadcaster(ScAddress(0,4,0));
4272 2 : CPPUNIT_ASSERT(pBC);
4273 2 : SvtBroadcaster::ListenersType* pListeners = &pBC->GetAllListeners();
4274 2 : CPPUNIT_ASSERT_MESSAGE("A5 should have one listener.", pListeners->size() == 1);
4275 2 : SvtListener* pListener = pListeners->at(0);
4276 2 : CPPUNIT_ASSERT_MESSAGE("A6 should be listening to A5.", pListener == pFC);
4277 :
4278 : // Check initial values.
4279 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
4280 2 : CPPUNIT_ASSERT_EQUAL(90.0, m_pDoc->GetValue(ScAddress(0,5,0)));
4281 :
4282 : // Delete row 2.
4283 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
4284 4 : ScMarkData aMark;
4285 2 : aMark.SelectOneTable(0);
4286 2 : rFunc.DeleteCells(ScRange(0,1,0,MAXCOL,1,0), &aMark, DEL_CELLSUP, true, true);
4287 :
4288 2 : pBC = m_pDoc->GetBroadcaster(ScAddress(0,3,0));
4289 2 : CPPUNIT_ASSERT_MESSAGE("Broadcaster at A5 should have shifted to A4.", pBC);
4290 2 : pListeners = &pBC->GetAllListeners();
4291 2 : CPPUNIT_ASSERT_MESSAGE("A3 should have one listener.", pListeners->size() == 1);
4292 2 : pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
4293 2 : CPPUNIT_ASSERT(pFC);
4294 2 : pListener = pListeners->at(0);
4295 2 : CPPUNIT_ASSERT_MESSAGE("A5 should be listening to A4.", pFC == pListener);
4296 :
4297 : // Check values after row deletion.
4298 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,3,0)));
4299 2 : CPPUNIT_ASSERT_EQUAL(60.0, m_pDoc->GetValue(ScAddress(0,4,0)));
4300 :
4301 4 : m_pDoc->DeleteTab(0);
4302 2 : }
4303 :
4304 2 : void Test::testFormulaDepTrackingDeleteCol()
4305 : {
4306 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
4307 :
4308 2 : m_pDoc->InsertTab(0, "Formula");
4309 :
4310 : const char* aData[][3] = {
4311 : { "2", "=A1", "=B1" }, // not grouped
4312 : { 0, 0, 0 }, // empty row to separate the formula groups.
4313 : { "3", "=A3", "=B3" }, // grouped
4314 : { "4", "=A4", "=B4" }, // grouped
4315 2 : };
4316 :
4317 2 : ScAddress aPos(0,0,0);
4318 2 : ScRange aRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
4319 2 : CPPUNIT_ASSERT(aRange.aStart == aPos);
4320 :
4321 : // Check the initial values.
4322 8 : for (SCCOL i = 0; i <= 2; ++i)
4323 : {
4324 6 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(i,0,0)));
4325 6 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(i,2,0)));
4326 6 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(i,3,0)));
4327 : }
4328 :
4329 : // Make sure B3:B4 and C3:C4 are grouped.
4330 2 : const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
4331 2 : CPPUNIT_ASSERT(pFC);
4332 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedTopRow());
4333 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
4334 :
4335 2 : pFC = m_pDoc->GetFormulaCell(ScAddress(2,2,0));
4336 2 : CPPUNIT_ASSERT(pFC);
4337 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedTopRow());
4338 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
4339 :
4340 : // Delete column A. A1, B1, A3:A4 and B3:B4 should all show #REF!.
4341 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
4342 4 : ScMarkData aMark;
4343 2 : aMark.SelectOneTable(0);
4344 2 : rFunc.DeleteCells(ScRange(0,0,0,0,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
4345 :
4346 : {
4347 : // Expected output table content. 0 = empty cell
4348 : const char* aOutputCheck[][2] = {
4349 : { "#REF!", "#REF!" },
4350 : { 0, 0 },
4351 : { "#REF!", "#REF!" },
4352 : { "#REF!", "#REF!" },
4353 2 : };
4354 :
4355 2 : ScRange aCheckRange(0,0,0,1,3,0);
4356 2 : bool bSuccess = checkOutput<2>(m_pDoc, aCheckRange, aOutputCheck, "Check after deleting column A");
4357 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
4358 : }
4359 :
4360 : // Undo and check the result.
4361 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
4362 2 : CPPUNIT_ASSERT(pUndoMgr);
4363 2 : pUndoMgr->Undo();
4364 :
4365 : {
4366 : // Expected output table content. 0 = empty cell
4367 : const char* aOutputCheck[][3] = {
4368 : { "2", "2", "2" },
4369 : { 0, 0, 0 },
4370 : { "3", "3", "3" },
4371 : { "4", "4", "4" },
4372 2 : };
4373 :
4374 2 : ScRange aCheckRange(0,0,0,2,3,0);
4375 2 : bool bSuccess = checkOutput<3>(m_pDoc, aCheckRange, aOutputCheck, "Check after undo");
4376 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
4377 : }
4378 :
4379 : // Redo and check.
4380 2 : pUndoMgr->Redo();
4381 : {
4382 : // Expected output table content. 0 = empty cell
4383 : const char* aOutputCheck[][2] = {
4384 : { "#REF!", "#REF!" },
4385 : { 0, 0 },
4386 : { "#REF!", "#REF!" },
4387 : { "#REF!", "#REF!" },
4388 2 : };
4389 :
4390 2 : ScRange aCheckRange(0,0,0,1,3,0);
4391 2 : bool bSuccess = checkOutput<2>(m_pDoc, aCheckRange, aOutputCheck, "Check after redo");
4392 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
4393 : }
4394 :
4395 : // Undo and change the values in column A.
4396 2 : pUndoMgr->Undo();
4397 2 : m_pDoc->SetValue(ScAddress(0,0,0), 22.0);
4398 2 : m_pDoc->SetValue(ScAddress(0,2,0), 23.0);
4399 2 : m_pDoc->SetValue(ScAddress(0,3,0), 24.0);
4400 :
4401 : {
4402 : // Expected output table content. 0 = empty cell
4403 : const char* aOutputCheck[][3] = {
4404 : { "22", "22", "22" },
4405 : { 0, 0, 0 },
4406 : { "23", "23", "23" },
4407 : { "24", "24", "24" },
4408 2 : };
4409 :
4410 2 : ScRange aCheckRange(0,0,0,2,3,0);
4411 2 : bool bSuccess = checkOutput<3>(m_pDoc, aCheckRange, aOutputCheck, "Check after undo & value change in column A");
4412 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
4413 : }
4414 :
4415 4 : m_pDoc->DeleteTab(0);
4416 2 : }
4417 :
4418 2 : void Test::testFormulaMatrixResultUpdate()
4419 : {
4420 2 : m_pDoc->InsertTab(0, "Test");
4421 :
4422 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
4423 :
4424 : // Set a numeric value to A1.
4425 2 : m_pDoc->SetValue(ScAddress(0,0,0), 11.0);
4426 :
4427 4 : ScMarkData aMark;
4428 2 : aMark.SelectOneTable(0);
4429 2 : m_pDoc->InsertMatrixFormula(1, 0, 1, 0, aMark, "=A1", NULL);
4430 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(1,0,0)));
4431 2 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
4432 2 : CPPUNIT_ASSERT_MESSAGE("Failed to get formula cell.", pFC);
4433 2 : pFC->SetChanged(false); // Clear this flag to simulate displaying of formula cell value on screen.
4434 :
4435 2 : m_pDoc->SetString(ScAddress(0,0,0), "ABC");
4436 2 : CPPUNIT_ASSERT_EQUAL(OUString("ABC"), m_pDoc->GetString(ScAddress(1,0,0)));
4437 2 : pFC->SetChanged(false);
4438 :
4439 : // Put a new value into A1. The formula should update.
4440 2 : m_pDoc->SetValue(ScAddress(0,0,0), 13.0);
4441 2 : CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc->GetValue(ScAddress(1,0,0)));
4442 :
4443 4 : m_pDoc->DeleteTab(0);
4444 2 : }
4445 :
4446 2 : void Test::testExternalRef()
4447 : {
4448 2 : ScDocShellRef xExtDocSh = new ScDocShell;
4449 4 : OUString aExtDocName("file:///extdata.fake");
4450 4 : OUString aExtSh1Name("Data1");
4451 4 : OUString aExtSh2Name("Data2");
4452 4 : OUString aExtSh3Name("Data3");
4453 2 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
4454 2 : xExtDocSh->DoInitNew(pMed);
4455 4 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
4456 2 : findLoadedDocShellByName(aExtDocName) != NULL);
4457 :
4458 : // Populate the external source document.
4459 2 : ScDocument& rExtDoc = xExtDocSh->GetDocument();
4460 2 : rExtDoc.InsertTab(0, aExtSh1Name);
4461 2 : rExtDoc.InsertTab(1, aExtSh2Name);
4462 2 : rExtDoc.InsertTab(2, aExtSh3Name);
4463 :
4464 4 : OUString name("Name");
4465 4 : OUString value("Value");
4466 4 : OUString andy("Andy");
4467 4 : OUString bruce("Bruce");
4468 4 : OUString charlie("Charlie");
4469 4 : OUString david("David");
4470 4 : OUString edward("Edward");
4471 4 : OUString frank("Frank");
4472 4 : OUString george("George");
4473 4 : OUString henry("Henry");
4474 :
4475 : // Sheet 1
4476 2 : rExtDoc.SetString(0, 0, 0, name);
4477 2 : rExtDoc.SetString(0, 1, 0, andy);
4478 2 : rExtDoc.SetString(0, 2, 0, bruce);
4479 2 : rExtDoc.SetString(0, 3, 0, charlie);
4480 2 : rExtDoc.SetString(0, 4, 0, david);
4481 2 : rExtDoc.SetString(1, 0, 0, value);
4482 2 : double val = 10;
4483 2 : rExtDoc.SetValue(1, 1, 0, val);
4484 2 : val = 11;
4485 2 : rExtDoc.SetValue(1, 2, 0, val);
4486 2 : val = 12;
4487 2 : rExtDoc.SetValue(1, 3, 0, val);
4488 2 : val = 13;
4489 2 : rExtDoc.SetValue(1, 4, 0, val);
4490 :
4491 : // Sheet 2 remains empty.
4492 :
4493 : // Sheet 3
4494 2 : rExtDoc.SetString(0, 0, 2, name);
4495 2 : rExtDoc.SetString(0, 1, 2, edward);
4496 2 : rExtDoc.SetString(0, 2, 2, frank);
4497 2 : rExtDoc.SetString(0, 3, 2, george);
4498 2 : rExtDoc.SetString(0, 4, 2, henry);
4499 2 : rExtDoc.SetString(1, 0, 2, value);
4500 2 : val = 99;
4501 2 : rExtDoc.SetValue(1, 1, 2, val);
4502 2 : val = 98;
4503 2 : rExtDoc.SetValue(1, 2, 2, val);
4504 2 : val = 97;
4505 2 : rExtDoc.SetValue(1, 3, 2, val);
4506 2 : val = 96;
4507 2 : rExtDoc.SetValue(1, 4, 2, val);
4508 :
4509 : // Test external refernces on the main document while the external
4510 : // document is still in memory.
4511 2 : m_pDoc->InsertTab(0, OUString("Test Sheet"));
4512 2 : m_pDoc->SetString(0, 0, 0, OUString("='file:///extdata.fake'#Data1.A1"));
4513 4 : OUString test = m_pDoc->GetString(0, 0, 0);
4514 2 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(name));
4515 :
4516 : // After the initial access to the external document, the external ref
4517 : // manager should create sheet cache entries for *all* sheets from that
4518 : // document. Note that the doc may have more than 3 sheets but ensure
4519 : // that the first 3 are what we expect.
4520 2 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
4521 2 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
4522 4 : vector<OUString> aTabNames;
4523 2 : pRefMgr->getAllCachedTableNames(nFileId, aTabNames);
4524 2 : CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
4525 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[0].equals(aExtSh1Name));
4526 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[1].equals(aExtSh2Name));
4527 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[2].equals(aExtSh3Name));
4528 :
4529 2 : m_pDoc->SetString(1, 0, 0, OUString("='file:///extdata.fake'#Data1.B1"));
4530 2 : test = m_pDoc->GetString(1, 0, 0);
4531 2 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(value));
4532 :
4533 2 : m_pDoc->SetString(0, 1, 0, OUString("='file:///extdata.fake'#Data1.A2"));
4534 2 : m_pDoc->SetString(0, 2, 0, OUString("='file:///extdata.fake'#Data1.A3"));
4535 2 : m_pDoc->SetString(0, 3, 0, OUString("='file:///extdata.fake'#Data1.A4"));
4536 2 : m_pDoc->SetString(0, 4, 0, OUString("='file:///extdata.fake'#Data1.A5"));
4537 2 : m_pDoc->SetString(0, 5, 0, OUString("='file:///extdata.fake'#Data1.A6"));
4538 :
4539 : {
4540 : // Referencing an empty cell should display '0'.
4541 2 : const char* pChecks[] = { "Andy", "Bruce", "Charlie", "David", "0" };
4542 12 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
4543 : {
4544 10 : test = m_pDoc->GetString(0, static_cast<SCROW>(i+1), 0);
4545 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
4546 : }
4547 : }
4548 2 : m_pDoc->SetString(1, 1, 0, OUString("='file:///extdata.fake'#Data1.B2"));
4549 2 : m_pDoc->SetString(1, 2, 0, OUString("='file:///extdata.fake'#Data1.B3"));
4550 2 : m_pDoc->SetString(1, 3, 0, OUString("='file:///extdata.fake'#Data1.B4"));
4551 2 : m_pDoc->SetString(1, 4, 0, OUString("='file:///extdata.fake'#Data1.B5"));
4552 2 : m_pDoc->SetString(1, 5, 0, OUString("='file:///extdata.fake'#Data1.B6"));
4553 : {
4554 2 : double pChecks[] = { 10, 11, 12, 13, 0 };
4555 12 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
4556 : {
4557 10 : m_pDoc->GetValue(1, static_cast<SCROW>(i+1), 0, val);
4558 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", val == pChecks[i]);
4559 : }
4560 : }
4561 :
4562 2 : m_pDoc->SetString(2, 0, 0, OUString("='file:///extdata.fake'#Data3.A1"));
4563 2 : m_pDoc->SetString(2, 1, 0, OUString("='file:///extdata.fake'#Data3.A2"));
4564 2 : m_pDoc->SetString(2, 2, 0, OUString("='file:///extdata.fake'#Data3.A3"));
4565 2 : m_pDoc->SetString(2, 3, 0, OUString("='file:///extdata.fake'#Data3.A4"));
4566 : {
4567 2 : const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
4568 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
4569 : {
4570 8 : test = m_pDoc->GetString(2, static_cast<SCROW>(i), 0);
4571 8 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
4572 : }
4573 : }
4574 :
4575 2 : m_pDoc->SetString(3, 0, 0, OUString("='file:///extdata.fake'#Data3.B1"));
4576 2 : m_pDoc->SetString(3, 1, 0, OUString("='file:///extdata.fake'#Data3.B2"));
4577 2 : m_pDoc->SetString(3, 2, 0, OUString("='file:///extdata.fake'#Data3.B3"));
4578 2 : m_pDoc->SetString(3, 3, 0, OUString("='file:///extdata.fake'#Data3.B4"));
4579 : {
4580 2 : const char* pChecks[] = { "Value", "99", "98", "97" };
4581 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
4582 : {
4583 8 : test = m_pDoc->GetString(3, static_cast<SCROW>(i), 0);
4584 8 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
4585 : }
4586 : }
4587 :
4588 : // At this point, all accessed cell data from the external document should
4589 : // have been cached.
4590 : ScExternalRefCache::TableTypeRef pCacheTab = pRefMgr->getCacheTable(
4591 4 : nFileId, aExtSh1Name, false);
4592 2 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab.get() != NULL);
4593 2 : ScRange aCachedRange = getCachedRange(pCacheTab);
4594 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
4595 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
4596 2 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 4);
4597 :
4598 : // Sheet2 is not referenced at all; the cache table shouldn't even exist.
4599 2 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh2Name, false);
4600 2 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 2 should *not* exist.", pCacheTab.get() == NULL);
4601 :
4602 : // Sheet3's row 5 is not referenced; it should not be cached.
4603 2 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh3Name, false);
4604 2 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab.get() != NULL);
4605 2 : aCachedRange = getCachedRange(pCacheTab);
4606 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
4607 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
4608 2 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 3);
4609 :
4610 : // Unload the external document shell.
4611 2 : xExtDocSh->DoClose();
4612 4 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
4613 2 : findLoadedDocShellByName(aExtDocName) == NULL);
4614 :
4615 4 : m_pDoc->DeleteTab(0);
4616 2 : }
4617 :
4618 2 : void testExtRefFuncT(ScDocument* pDoc, ScDocument& rExtDoc)
4619 : {
4620 2 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
4621 2 : Test::clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
4622 :
4623 2 : rExtDoc.SetString(0, 0, 0, OUString("'1.2"));
4624 2 : rExtDoc.SetString(0, 1, 0, OUString("Foo"));
4625 2 : rExtDoc.SetValue(0, 2, 0, 12.3);
4626 2 : pDoc->SetString(0, 0, 0, OUString("=T('file:///extdata.fake'#Data.A1)"));
4627 2 : pDoc->SetString(0, 1, 0, OUString("=T('file:///extdata.fake'#Data.A2)"));
4628 2 : pDoc->SetString(0, 2, 0, OUString("=T('file:///extdata.fake'#Data.A3)"));
4629 2 : pDoc->CalcAll();
4630 :
4631 2 : OUString aRes = pDoc->GetString(0, 0, 0);
4632 2 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "1.2" );
4633 2 : aRes = pDoc->GetString(0, 1, 0);
4634 2 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "Foo" );
4635 2 : aRes = pDoc->GetString(0, 2, 0);
4636 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
4637 2 : }
4638 :
4639 2 : void testExtRefFuncOFFSET(ScDocument* pDoc, ScDocument& rExtDoc)
4640 : {
4641 2 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
4642 2 : Test::clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
4643 :
4644 2 : sc::AutoCalcSwitch aACSwitch(*pDoc, true);
4645 :
4646 : // External document has sheet named 'Data', and the internal doc has sheet named 'Test'.
4647 2 : rExtDoc.SetValue(ScAddress(0,1,0), 1.2); // Set 1.2 to A2.
4648 2 : pDoc->SetString(ScAddress(0,0,0), "=OFFSET('file:///extdata.fake'#Data.$A$1;1;0;1;1)");
4649 2 : CPPUNIT_ASSERT_EQUAL(1.2, pDoc->GetValue(ScAddress(0,0,0)));
4650 2 : }
4651 :
4652 2 : void testExtRefFuncVLOOKUP(ScDocument* pDoc, ScDocument& rExtDoc)
4653 : {
4654 2 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
4655 2 : Test::clearRange(&rExtDoc, ScRange(0, 0, 0, 1, 9, 0));
4656 :
4657 : // Populate the external document.
4658 2 : rExtDoc.SetString(ScAddress(0,0,0), "A1");
4659 2 : rExtDoc.SetString(ScAddress(0,1,0), "A2");
4660 2 : rExtDoc.SetString(ScAddress(0,2,0), "A3");
4661 2 : rExtDoc.SetString(ScAddress(0,3,0), "A4");
4662 2 : rExtDoc.SetString(ScAddress(0,4,0), "A5");
4663 :
4664 2 : rExtDoc.SetString(ScAddress(1,0,0), "B1");
4665 2 : rExtDoc.SetString(ScAddress(1,1,0), "B2");
4666 2 : rExtDoc.SetString(ScAddress(1,2,0), "B3");
4667 2 : rExtDoc.SetString(ScAddress(1,3,0), "B4");
4668 2 : rExtDoc.SetString(ScAddress(1,4,0), "B5");
4669 :
4670 : // Put formula in the source document.
4671 :
4672 2 : pDoc->SetString(ScAddress(0,0,0), "A2");
4673 :
4674 : // Sort order TRUE
4675 2 : pDoc->SetString(ScAddress(1,0,0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;1)");
4676 2 : CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1,0,0)));
4677 :
4678 : // Sort order FALSE. It should return the same result.
4679 2 : pDoc->SetString(ScAddress(1,0,0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;0)");
4680 2 : CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1,0,0)));
4681 2 : }
4682 :
4683 2 : void Test::testExternalRefFunctions()
4684 : {
4685 2 : ScDocShellRef xExtDocSh = new ScDocShell;
4686 4 : OUString aExtDocName("file:///extdata.fake");
4687 2 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
4688 2 : xExtDocSh->DoInitNew(pMed);
4689 4 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
4690 2 : findLoadedDocShellByName(aExtDocName) != NULL);
4691 :
4692 2 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
4693 2 : CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
4694 2 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
4695 2 : const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
4696 4 : CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.",
4697 2 : pFileName && pFileName->equals(aExtDocName));
4698 :
4699 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
4700 :
4701 : // Populate the external source document.
4702 2 : ScDocument& rExtDoc = xExtDocSh->GetDocument();
4703 2 : rExtDoc.InsertTab(0, OUString("Data"));
4704 2 : double val = 1;
4705 2 : rExtDoc.SetValue(0, 0, 0, val);
4706 : // leave cell B1 empty.
4707 2 : val = 2;
4708 2 : rExtDoc.SetValue(0, 1, 0, val);
4709 2 : rExtDoc.SetValue(1, 1, 0, val);
4710 2 : val = 3;
4711 2 : rExtDoc.SetValue(0, 2, 0, val);
4712 2 : rExtDoc.SetValue(1, 2, 0, val);
4713 2 : val = 4;
4714 2 : rExtDoc.SetValue(0, 3, 0, val);
4715 2 : rExtDoc.SetValue(1, 3, 0, val);
4716 :
4717 2 : m_pDoc->InsertTab(0, OUString("Test"));
4718 :
4719 : struct {
4720 : const char* pFormula; double fResult;
4721 : } aChecks[] = {
4722 : { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
4723 : { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
4724 : { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
4725 : { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
4726 : { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
4727 : { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 }
4728 2 : };
4729 :
4730 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4731 : {
4732 12 : m_pDoc->SetString(0, 0, 0, OUString::createFromAscii(aChecks[i].pFormula));
4733 12 : m_pDoc->GetValue(0, 0, 0, val);
4734 12 : CPPUNIT_ASSERT_MESSAGE("unexpected result involving external ranges.", val == aChecks[i].fResult);
4735 : }
4736 :
4737 2 : pRefMgr->clearCache(nFileId);
4738 2 : testExtRefFuncT(m_pDoc, rExtDoc);
4739 2 : testExtRefFuncOFFSET(m_pDoc, rExtDoc);
4740 2 : testExtRefFuncVLOOKUP(m_pDoc, rExtDoc);
4741 :
4742 : // Unload the external document shell.
4743 2 : xExtDocSh->DoClose();
4744 4 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
4745 2 : findLoadedDocShellByName(aExtDocName) == NULL);
4746 :
4747 4 : m_pDoc->DeleteTab(0);
4748 8 : }
4749 :
4750 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
