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 "inputopt.hxx"
21 : #include "scmod.hxx"
22 : #include "docsh.hxx"
23 : #include "docfunc.hxx"
24 : #include "paramisc.hxx"
25 : #include "tokenstringcontext.hxx"
26 : #include "dbdata.hxx"
27 :
28 : #include "formula/vectortoken.hxx"
29 :
30 : #include <boost/scoped_ptr.hpp>
31 :
32 : using namespace formula;
33 :
34 : namespace {
35 :
36 2 : ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
37 : {
38 2 : ScRange aRange;
39 :
40 2 : vector<SCROW> aRows;
41 2 : pCacheTab->getAllRows(aRows);
42 2 : vector<SCROW>::const_iterator itrRow = aRows.begin(), itrRowEnd = aRows.end();
43 2 : bool bFirst = true;
44 11 : for (; itrRow != itrRowEnd; ++itrRow)
45 : {
46 9 : SCROW nRow = *itrRow;
47 9 : vector<SCCOL> aCols;
48 9 : pCacheTab->getAllCols(nRow, aCols);
49 9 : vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end();
50 27 : for (; itrCol != itrColEnd; ++itrCol)
51 : {
52 18 : SCCOL nCol = *itrCol;
53 18 : if (bFirst)
54 : {
55 2 : aRange.aStart = ScAddress(nCol, nRow, 0);
56 2 : aRange.aEnd = aRange.aStart;
57 2 : bFirst = false;
58 : }
59 : else
60 : {
61 16 : if (nCol < aRange.aStart.Col())
62 0 : aRange.aStart.SetCol(nCol);
63 16 : else if (aRange.aEnd.Col() < nCol)
64 2 : aRange.aEnd.SetCol(nCol);
65 :
66 16 : if (nRow < aRange.aStart.Row())
67 0 : aRange.aStart.SetRow(nRow);
68 16 : else if (aRange.aEnd.Row() < nRow)
69 7 : aRange.aEnd.SetRow(nRow);
70 : }
71 : }
72 9 : }
73 2 : return aRange;
74 : }
75 :
76 : }
77 :
78 1 : void Test::testFormulaCreateStringFromTokens()
79 : {
80 : // Insert sheets.
81 1 : OUString aTabName1("Test");
82 2 : OUString aTabName2("Kevin's Data");
83 2 : OUString aTabName3("Past Data");
84 2 : OUString aTabName4("2013");
85 1 : m_pDoc->InsertTab(0, aTabName1);
86 1 : m_pDoc->InsertTab(1, aTabName2);
87 1 : m_pDoc->InsertTab(2, aTabName3);
88 1 : m_pDoc->InsertTab(3, aTabName4);
89 :
90 : // Insert named ranges.
91 : struct {
92 : bool bGlobal;
93 : const char* pName;
94 : const char* pExpr;
95 : } aNames[] = {
96 : { true, "x", "Test.H1" },
97 : { true, "y", "Test.H2" },
98 : { true, "z", "Test.H3" },
99 :
100 : { false, "sheetx", "Test.J1" }
101 1 : };
102 :
103 1 : ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
104 1 : ScRangeName* pSheetNames = m_pDoc->GetRangeName(0);
105 1 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
106 1 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain sheet-local named expression object.", pSheetNames);
107 :
108 5 : for (size_t i = 0, n = SAL_N_ELEMENTS(aNames); i < n; ++i)
109 : {
110 : ScRangeData* pName = new ScRangeData(
111 : m_pDoc, OUString::createFromAscii(aNames[i].pName), OUString::createFromAscii(aNames[i].pExpr),
112 4 : ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
113 :
114 4 : if (aNames[i].bGlobal)
115 : {
116 3 : bool bInserted = pGlobalNames->insert(pName);
117 3 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
118 : }
119 : else
120 : {
121 1 : bool bInserted = pSheetNames->insert(pName);
122 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
123 : }
124 : }
125 :
126 : // Insert DB ranges.
127 : struct {
128 : const char* pName;
129 : SCTAB nTab;
130 : SCCOL nCol1;
131 : SCROW nRow1;
132 : SCCOL nCol2;
133 : SCROW nRow2;
134 : } aDBs[] = {
135 : { "Table1", 0, 0, 0, 10, 10 },
136 : { "Table2", 1, 0, 0, 10, 10 },
137 : { "Table3", 2, 0, 0, 10, 10 }
138 1 : };
139 :
140 1 : ScDBCollection* pDBs = m_pDoc->GetDBCollection();
141 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
142 :
143 4 : for (size_t i = 0, n = SAL_N_ELEMENTS(aDBs); i < n; ++i)
144 : {
145 : ScDBData* pData = new ScDBData(
146 : OUString::createFromAscii(
147 3 : aDBs[i].pName), aDBs[i].nTab, aDBs[i].nCol1, aDBs[i].nRow1, aDBs[i].nCol2,aDBs[i].nRow2);
148 3 : bool bInserted = pDBs->getNamedDBs().insert(pData);
149 6 : CPPUNIT_ASSERT_MESSAGE(
150 : OString(
151 : "Failed to insert \"" + OString(aDBs[i].pName) + "\"").getStr(),
152 3 : bInserted);
153 : }
154 :
155 : const char* aTests[] = {
156 : "1+2",
157 : "SUM(A1:A10;B1:B10;C5;D6)",
158 : "IF(Test.B10<>10;\"Good\";\"Bad\")",
159 : "AVERAGE('2013'.B10:C20)",
160 : "'Kevin''s Data'.B10",
161 : "'Past Data'.B1+'2013'.B2*(1+'Kevin''s Data'.C10)",
162 : "x+y*z", // named ranges
163 : "SUM(sheetx;x;y;z)", // sheet local and global named ranges mixed
164 : "MAX(Table1)+MIN(Table2)*SUM(Table3)", // database ranges
165 : "{1;TRUE;3|FALSE;5;\"Text\"|;;}", // inline matrix
166 : "SUM('file:///path/to/fake.file'#$Sheet.A1:B10)",
167 1 : };
168 :
169 2 : boost::scoped_ptr<ScTokenArray> pArray;
170 :
171 2 : sc::TokenStringContext aCxt(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);
172 :
173 : // Artificially add external refererence data after the context object is
174 : // initialized.
175 1 : aCxt.maExternalFileNames.push_back("file:///path/to/fake.file");
176 2 : std::vector<OUString> aExtTabNames;
177 1 : aExtTabNames.push_back("Sheet");
178 : aCxt.maExternalCachedTabNames.insert(
179 1 : sc::TokenStringContext::IndexNamesMapType::value_type(0, aExtTabNames));
180 :
181 1 : ScAddress aPos(0,0,0);
182 :
183 12 : for (size_t i = 0, n = SAL_N_ELEMENTS(aTests); i < n; ++i)
184 : {
185 : #if 0
186 : OUString aFormula = OUString::createFromAscii(aTests[i]);
187 : #endif
188 11 : ScCompiler aComp(m_pDoc, aPos);
189 11 : aComp.SetGrammar(FormulaGrammar::GRAM_ENGLISH);
190 : #if 0 // TODO: This call to CompileString() causes the cppunittester to somehow fail on Windows.
191 : pArray.reset(aComp.CompileString(aFormula));
192 : CPPUNIT_ASSERT_MESSAGE("Failed to compile formula string.", pArray.get());
193 :
194 : OUString aCheck = pArray->CreateString(aCxt, aPos);
195 : CPPUNIT_ASSERT_EQUAL(aFormula, aCheck);
196 : #endif
197 11 : }
198 :
199 1 : m_pDoc->DeleteTab(3);
200 1 : m_pDoc->DeleteTab(2);
201 1 : m_pDoc->DeleteTab(1);
202 2 : m_pDoc->DeleteTab(0);
203 1 : }
204 :
205 : namespace {
206 :
207 18 : bool isEmpty( const formula::VectorRefArray& rArray, size_t nPos )
208 : {
209 18 : if (rArray.mpStringArray)
210 : {
211 7 : if (rArray.mpStringArray[nPos])
212 0 : return false;
213 : }
214 :
215 18 : if (rArray.mpNumericArray)
216 17 : return rtl::math::isNan(rArray.mpNumericArray[nPos]);
217 : else
218 1 : return true;
219 : }
220 :
221 35 : bool equals( const formula::VectorRefArray& rArray, size_t nPos, double fVal )
222 : {
223 35 : if (rArray.mpStringArray && rArray.mpStringArray[nPos])
224 : // This is a string cell.
225 0 : return false;
226 :
227 35 : if (rArray.mpNumericArray && rArray.mpNumericArray[nPos] == fVal)
228 35 : return true;
229 :
230 0 : return false;
231 : }
232 :
233 16 : bool equals( const formula::VectorRefArray& rArray, size_t nPos, const OUString& rVal )
234 : {
235 16 : if (!rArray.mpStringArray)
236 0 : return false;
237 :
238 16 : bool bEquals = OUString(rArray.mpStringArray[nPos]).equalsIgnoreAsciiCase(rVal);
239 16 : if (!bEquals)
240 : {
241 0 : cerr << "Expected: " << rVal.toAsciiUpperCase() << " (upcased)" << endl;
242 0 : cerr << "Actual: " << OUString(rArray.mpStringArray[nPos]) << " (upcased)" << endl;
243 : }
244 16 : return bEquals;
245 : }
246 :
247 : }
248 :
249 1 : void Test::testFormulaParseReference()
250 : {
251 2 : OUString aTab1("90's Music"), aTab2("90's and 70's"), aTab3("All Others"), aTab4("NoQuote");
252 1 : m_pDoc->InsertTab(0, "Dummy"); // just to shift the sheet indices...
253 1 : m_pDoc->InsertTab(1, aTab1); // name with a single quote.
254 1 : m_pDoc->InsertTab(2, aTab2); // name with 2 single quotes.
255 1 : m_pDoc->InsertTab(3, aTab3); // name without single quotes.
256 1 : m_pDoc->InsertTab(4, aTab4); // name that doesn't require to be quoted.
257 :
258 2 : OUString aTabName;
259 1 : m_pDoc->GetName(1, aTabName);
260 1 : CPPUNIT_ASSERT_EQUAL(aTab1, aTabName);
261 1 : m_pDoc->GetName(2, aTabName);
262 1 : CPPUNIT_ASSERT_EQUAL(aTab2, aTabName);
263 1 : m_pDoc->GetName(3, aTabName);
264 1 : CPPUNIT_ASSERT_EQUAL(aTab3, aTabName);
265 1 : m_pDoc->GetName(4, aTabName);
266 1 : CPPUNIT_ASSERT_EQUAL(aTab4, aTabName);
267 :
268 : // Make sure the formula input and output match.
269 : {
270 : const char* aChecks[] = {
271 : "'90''s Music'.B12",
272 : "'90''s and 70''s'.$AB$100",
273 : "'All Others'.Z$100",
274 : "NoQuote.$C111"
275 1 : };
276 :
277 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
278 : {
279 : // Use the 'Dummy' sheet for this.
280 4 : OUString aInput("=");
281 4 : aInput += OUString::createFromAscii(aChecks[i]);
282 4 : m_pDoc->SetString(ScAddress(0,0,0), aInput);
283 4 : if (!checkFormula(*m_pDoc, ScAddress(0,0,0), aChecks[i]))
284 0 : CPPUNIT_FAIL("Wrong formula");
285 4 : }
286 : }
287 :
288 1 : ScAddress aPos;
289 2 : ScAddress::ExternalInfo aExtInfo;
290 1 : sal_uInt16 nRes = aPos.Parse("'90''s Music'.D10", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
291 1 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
292 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(1), aPos.Tab());
293 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), aPos.Col());
294 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), aPos.Row());
295 1 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
296 :
297 1 : nRes = aPos.Parse("'90''s and 70''s'.C100", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
298 1 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
299 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(2), aPos.Tab());
300 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), aPos.Col());
301 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(99), aPos.Row());
302 1 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
303 :
304 1 : nRes = aPos.Parse("'All Others'.B3", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
305 1 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
306 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(3), aPos.Tab());
307 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aPos.Col());
308 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), aPos.Row());
309 1 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
310 :
311 1 : nRes = aPos.Parse("NoQuote.E13", m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
312 1 : CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & SCA_VALID) != 0);
313 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(4), aPos.Tab());
314 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(4), aPos.Col());
315 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), aPos.Row());
316 1 : CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);
317 :
318 1 : m_pDoc->DeleteTab(4);
319 1 : m_pDoc->DeleteTab(3);
320 1 : m_pDoc->DeleteTab(2);
321 1 : m_pDoc->DeleteTab(1);
322 2 : m_pDoc->DeleteTab(0);
323 1 : }
324 :
325 1 : void Test::testFetchVectorRefArray()
326 : {
327 1 : m_pDoc->InsertTab(0, "Test");
328 :
329 : // All numeric cells in Column A.
330 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
331 1 : m_pDoc->SetValue(ScAddress(0,1,0), 2);
332 1 : m_pDoc->SetValue(ScAddress(0,2,0), 3);
333 1 : m_pDoc->SetValue(ScAddress(0,3,0), 4);
334 :
335 1 : formula::VectorRefArray aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 4);
336 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
337 1 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
338 1 : CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
339 1 : CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
340 1 : CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
341 1 : CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);
342 :
343 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 5);
344 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
345 1 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
346 1 : CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
347 1 : CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
348 1 : CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
349 1 : CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);
350 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
351 :
352 : // All string cells in Column B. Note that the fetched string arrays are
353 : // only to be compared case-insensitively. Right now, we use upper cased
354 : // strings to achieve case-insensitive-ness, but that may change. So,
355 : // don't count on that.
356 1 : m_pDoc->SetString(ScAddress(1,0,0), "Andy");
357 1 : m_pDoc->SetString(ScAddress(1,1,0), "Bruce");
358 1 : m_pDoc->SetString(ScAddress(1,2,0), "Charlie");
359 1 : m_pDoc->SetString(ScAddress(1,3,0), "David");
360 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 5);
361 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
362 1 : CPPUNIT_ASSERT_MESSAGE("Array is expected to be string cells only.", !aArray.mpNumericArray);
363 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "Andy"));
364 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "Bruce"));
365 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "Charlie"));
366 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "David"));
367 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
368 :
369 : // Mixture of numeric, string, and empty cells in Column C.
370 1 : m_pDoc->SetString(ScAddress(2,0,0), "Header");
371 1 : m_pDoc->SetValue(ScAddress(2,1,0), 11);
372 1 : m_pDoc->SetValue(ScAddress(2,2,0), 12);
373 1 : m_pDoc->SetValue(ScAddress(2,3,0), 13);
374 1 : m_pDoc->SetString(ScAddress(2,5,0), "=SUM(C2:C4)");
375 1 : m_pDoc->CalcAll();
376 :
377 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(2,0,0), 7);
378 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
379 1 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
380 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "Header"));
381 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 11));
382 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 12));
383 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 13));
384 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
385 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 36));
386 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));
387 :
388 : // Mixed type again in Column D, but it starts with a numeric cell.
389 1 : m_pDoc->SetValue(ScAddress(3,0,0), 10);
390 1 : m_pDoc->SetString(ScAddress(3,1,0), "Below 10");
391 : // Leave 2 empty cells.
392 1 : m_pDoc->SetValue(ScAddress(3,4,0), 11);
393 1 : m_pDoc->SetString(ScAddress(3,5,0), "=12");
394 1 : m_pDoc->SetString(ScAddress(3,6,0), "=13");
395 1 : m_pDoc->SetString(ScAddress(3,7,0), "=CONCATENATE(\"A\";\"B\";\"C\")");
396 1 : m_pDoc->CalcAll();
397 :
398 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(3,0,0), 8);
399 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
400 1 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
401 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 10));
402 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "Below 10"));
403 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 2));
404 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));
405 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 11));
406 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 12));
407 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 13));
408 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 7, "ABC"));
409 :
410 : // Column E consists of formula cells whose results are all numeric.
411 8 : for (SCROW i = 0; i <= 6; ++i)
412 7 : m_pDoc->SetString(ScAddress(4,i,0), "=ROW()");
413 1 : m_pDoc->CalcAll();
414 :
415 : // Leave row 7 empty.
416 1 : m_pDoc->SetString(ScAddress(4,8,0), "Andy");
417 1 : m_pDoc->SetValue(ScAddress(4,9,0), 123);
418 :
419 : // This array fits within a single formula block.
420 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 5);
421 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
422 1 : CPPUNIT_ASSERT_MESSAGE("Array should be purely numeric.", aArray.mpNumericArray && !aArray.mpStringArray);
423 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
424 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
425 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
426 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
427 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));
428 :
429 : // This array spans over multiple blocks.
430 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 11);
431 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
432 1 : CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray && aArray.mpStringArray);
433 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
434 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
435 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
436 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
437 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));
438 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 6));
439 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 7));
440 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
441 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 8, "Andy"));
442 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 9, 123));
443 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 10));
444 :
445 : // Hit the cache but at a different start row.
446 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,2,0), 3);
447 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
448 1 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
449 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 3));
450 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 4));
451 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 5));
452 :
453 : // Column F begins with empty rows at the top.
454 1 : m_pDoc->SetValue(ScAddress(5,2,0), 1.1);
455 1 : m_pDoc->SetValue(ScAddress(5,3,0), 1.2);
456 1 : m_pDoc->SetString(ScAddress(5,4,0), "=2*8");
457 1 : m_pDoc->CalcAll();
458 :
459 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 4);
460 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
461 1 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
462 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1.1));
463 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 1.2));
464 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 16));
465 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));
466 :
467 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 3);
468 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
469 1 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
470 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
471 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
472 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));
473 :
474 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 10);
475 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
476 1 : CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
477 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
478 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
479 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));
480 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 1.2));
481 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 16));
482 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 5));
483 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));
484 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
485 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 8));
486 1 : CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 9));
487 :
488 : // Get the array for F3:F4. This array should only consist of numeric array.
489 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 3);
490 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
491 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
492 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
493 :
494 : // Column G consists only of strings.
495 1 : m_pDoc->SetString(ScAddress(6,0,0), "Title");
496 1 : m_pDoc->SetString(ScAddress(6,1,0), "foo");
497 1 : m_pDoc->SetString(ScAddress(6,2,0), "bar");
498 1 : m_pDoc->SetString(ScAddress(6,3,0), "foo");
499 1 : m_pDoc->SetString(ScAddress(6,4,0), "baz");
500 1 : m_pDoc->SetString(ScAddress(6,5,0), "quack");
501 1 : m_pDoc->SetString(ScAddress(6,6,0), "beep");
502 1 : m_pDoc->SetString(ScAddress(6,7,0), "kerker");
503 :
504 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,1,0), 4); // G2:G5
505 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
506 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
507 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
508 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "foo"));
509 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "bar"));
510 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "foo"));
511 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "baz"));
512 :
513 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,2,0), 4); // G3:G6
514 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
515 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
516 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
517 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, "bar"));
518 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, "foo"));
519 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, "baz"));
520 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, "quack"));
521 :
522 : // Column H starts with formula cells.
523 11 : for (SCROW i = 0; i < 10; ++i)
524 10 : m_pDoc->SetString(ScAddress(7,i,0), "=ROW()");
525 :
526 1 : m_pDoc->CalcAll();
527 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,3,0), 3); // H4:H6
528 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
529 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
530 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
531 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 4.0));
532 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, 5.0));
533 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, 6.0));
534 :
535 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,4,0), 10); // H5:H15
536 1 : CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
537 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
538 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
539 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 5.0));
540 :
541 : // Clear everything and start over.
542 1 : clearRange(m_pDoc, ScRange(0,0,0,MAXCOL,MAXROW,0));
543 1 : m_pDoc->ClearFormulaContext();
544 :
545 : // Totally empty range in a totally empty column (Column A).
546 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 3); // A1:A3
547 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
548 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
549 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
550 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
551 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
552 :
553 : // Totally empty range in a non-empty column (Column B).
554 1 : m_pDoc->SetString(ScAddress(1,10,0), "Some text"); // B11
555 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 3); // B1:B3
556 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
557 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
558 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
559 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
560 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
561 :
562 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,12,0), 3); // B13:B15
563 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
564 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
565 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
566 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
567 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
568 :
569 : // These values come from a cache because of the call above.
570 1 : aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,1,0), 3); // B2:B4
571 1 : CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
572 1 : CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
573 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[0]));
574 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[1]));
575 1 : CPPUNIT_ASSERT(rtl::math::isNan(aArray.mpNumericArray[2]));
576 :
577 1 : m_pDoc->DeleteTab(0);
578 1 : }
579 :
580 1 : void Test::testFormulaHashAndTag()
581 : {
582 1 : m_pDoc->InsertTab(0, "Test");
583 :
584 1 : ScAddress aPos1(0,0,0), aPos2(1,0,0);
585 :
586 : // Test formula hashing.
587 :
588 : struct {
589 : const char* pFormula1; const char* pFormula2; bool bEqual;
590 : } aHashTests[] = {
591 : { "=1", "=2", false }, // different constants
592 : { "=SUM(1;2;3;4;5)", "=AVERAGE(1;2;3;4;5)", false }, // different functions
593 : { "=C2*3", "=D2*3", true }, // relative references
594 : { "=C2*3", "=D2*4", false }, // different constants
595 : { "=C2*4", "=D2*4", true }, // relative references
596 : { "=3*4*5", "=3*4*\"foo\"", false }, // numeric vs string constants
597 : { "=$C3/2", "=$C3/2", true }, // absolute column references
598 : { "=C$3/2", "=D$3/2", true }, // absolute row references
599 : { "=$E$30/2", "=$E$30/2", true }, // absolute references
600 : { "=X20", "=$X$20", false }, // absolute vs relative
601 : { "=X20", "=X$20", false }, // absolute vs relative
602 : { "=X20", "=$X20", false }, // absolute vs relative
603 : { "=X$20", "=$X20", false }, // column absolute vs row absolute
604 : // similar enough for merging ...
605 : { "=A1", "=B1", true },
606 : { "=$A$1", "=$B$1", true },
607 : { "=A1", "=C2", true },
608 : { "=SUM(A1)", "=SUM(B1)", true },
609 : { "=A1+3", "=B1+3", true },
610 : { "=A1+7", "=B1+42", false },
611 1 : };
612 :
613 20 : for (size_t i = 0; i < SAL_N_ELEMENTS(aHashTests); ++i)
614 : {
615 19 : m_pDoc->SetString(aPos1, OUString::createFromAscii(aHashTests[i].pFormula1));
616 19 : m_pDoc->SetString(aPos2, OUString::createFromAscii(aHashTests[i].pFormula2));
617 19 : size_t nHashVal1 = m_pDoc->GetFormulaHash(aPos1);
618 19 : size_t nHashVal2 = m_pDoc->GetFormulaHash(aPos2);
619 :
620 19 : std::ostringstream os;
621 19 : os << "(expr1:" << aHashTests[i].pFormula1 << "; expr2:" << aHashTests[i].pFormula2 << ")";
622 19 : if (aHashTests[i].bEqual)
623 : {
624 10 : os << " Error: these hashes should be equal." << endl;
625 10 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), nHashVal1 == nHashVal2);
626 : }
627 : else
628 : {
629 9 : os << " Error: these hashes should differ." << endl;
630 9 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), nHashVal1 != nHashVal2);
631 : }
632 :
633 19 : aPos1.IncRow();
634 19 : aPos2.IncRow();
635 19 : }
636 :
637 : // Go back to row 1.
638 1 : aPos1.SetRow(0);
639 1 : aPos2.SetRow(0);
640 :
641 : // Test formula vectorization state.
642 :
643 : struct {
644 : const char* pFormula; ScFormulaVectorState eState;
645 : } aVectorTests[] = {
646 : { "=SUM(1;2;3;4;5)", FormulaVectorEnabled },
647 : { "=NOW()", FormulaVectorDisabled },
648 : { "=AVERAGE(X1:Y200)", FormulaVectorCheckReference },
649 : { "=MAX(X1:Y200;10;20)", FormulaVectorCheckReference },
650 : { "=MIN(10;11;22)", FormulaVectorEnabled },
651 : { "=H4", FormulaVectorCheckReference },
652 1 : };
653 :
654 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aVectorTests); ++i)
655 : {
656 6 : m_pDoc->SetString(aPos1, OUString::createFromAscii(aVectorTests[i].pFormula));
657 6 : ScFormulaVectorState eState = m_pDoc->GetFormulaVectorState(aPos1);
658 :
659 6 : if (eState != aVectorTests[i].eState)
660 : {
661 0 : std::ostringstream os;
662 0 : os << "Unexpected vectorization state: expr:" << aVectorTests[i].pFormula;
663 0 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), false);
664 : }
665 6 : aPos1.IncRow();
666 : }
667 :
668 1 : m_pDoc->DeleteTab(0);
669 1 : }
670 :
671 1 : void Test::testFormulaTokenEquality()
672 : {
673 : struct FormulaTokenEqualityTest
674 : {
675 : const char* mpFormula1;
676 : const char* mpFormula2;
677 : bool mbEqual;
678 : };
679 :
680 : FormulaTokenEqualityTest aTests[] = {
681 : { "R1C2", "R1C2", true },
682 : { "R1C2", "R1C3", false },
683 : { "R1C2", "R2C2", false },
684 : { "RC2", "RC[1]", false },
685 : { "R1C2:R10C2", "R1C2:R10C2", true },
686 : { "R1C2:R10C2", "R1C2:R11C2", false },
687 : { "1", "2", false },
688 : { "RC[1]+1.2", "RC[1]+1.2", true },
689 : { "RC[1]*0.2", "RC[1]*0.5", false },
690 : { "\"Test1\"", "\"Test2\"", false },
691 : { "\"Test\"", "\"Test\"", true },
692 : { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test1\")", true },
693 : { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test2\")", false },
694 1 : };
695 :
696 1 : formula::FormulaGrammar::Grammar eGram = formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1;
697 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
698 : {
699 13 : ScFormulaCell aCell1(m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula1), eGram);
700 26 : ScFormulaCell aCell2(m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula2), eGram);
701 :
702 13 : ScFormulaCell::CompareState eComp = aCell1.CompareByTokenArray(aCell2);
703 13 : if (aTests[i].mbEqual)
704 : {
705 5 : if (eComp == ScFormulaCell::NotEqual)
706 : {
707 0 : std::ostringstream os;
708 0 : os << "These two formulas should be evaluated equal: '"
709 0 : << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
710 0 : CPPUNIT_FAIL(os.str().c_str());
711 : }
712 : }
713 : else
714 : {
715 8 : if (eComp != ScFormulaCell::NotEqual)
716 : {
717 0 : std::ostringstream os;
718 0 : os << "These two formulas should be evaluated non-equal: '"
719 0 : << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
720 0 : CPPUNIT_FAIL(os.str().c_str());
721 : }
722 : }
723 13 : }
724 1 : }
725 :
726 1 : void Test::testFormulaRefData()
727 : {
728 1 : ScAddress aAddr(4,5,3), aPos(2,2,2);
729 : ScSingleRefData aRef;
730 1 : aRef.InitAddress(aAddr);
731 1 : CPPUNIT_ASSERT_MESSAGE("Wrong ref data state.", !aRef.IsRowRel() && !aRef.IsColRel() && !aRef.IsTabRel());
732 1 : ASSERT_EQUAL_TYPE(SCCOL, 4, aRef.Col());
733 1 : ASSERT_EQUAL_TYPE(SCROW, 5, aRef.Row());
734 1 : ASSERT_EQUAL_TYPE(SCTAB, 3, aRef.Tab());
735 :
736 1 : aRef.SetRowRel(true);
737 1 : aRef.SetColRel(true);
738 1 : aRef.SetTabRel(true);
739 1 : aRef.SetAddress(aAddr, aPos);
740 1 : ASSERT_EQUAL_TYPE(SCCOL, 2, aRef.Col());
741 1 : ASSERT_EQUAL_TYPE(SCROW, 3, aRef.Row());
742 1 : ASSERT_EQUAL_TYPE(SCTAB, 1, aRef.Tab());
743 :
744 : // Test extension of range reference.
745 :
746 : ScComplexRefData aDoubleRef;
747 1 : aDoubleRef.InitRange(ScRange(2,2,0,4,4,0));
748 :
749 1 : aRef.InitAddress(ScAddress(6,5,0));
750 :
751 1 : aDoubleRef.Extend(aRef, ScAddress());
752 1 : ScRange aTest = aDoubleRef.toAbs(ScAddress());
753 1 : CPPUNIT_ASSERT_MESSAGE("Wrong start position of extended range.", aTest.aStart == ScAddress(2,2,0));
754 1 : CPPUNIT_ASSERT_MESSAGE("Wrong end position of extended range.", aTest.aEnd == ScAddress(6,5,0));
755 :
756 : ScComplexRefData aDoubleRef2;
757 1 : aDoubleRef2.InitRangeRel(ScRange(1,2,0,8,6,0), ScAddress(5,5,0));
758 1 : aDoubleRef.Extend(aDoubleRef2, ScAddress(5,5,0));
759 1 : aTest = aDoubleRef.toAbs(ScAddress(5,5,0));
760 :
761 1 : CPPUNIT_ASSERT_MESSAGE("Wrong start position of extended range.", aTest.aStart == ScAddress(1,2,0));
762 1 : CPPUNIT_ASSERT_MESSAGE("Wrong end position of extended range.", aTest.aEnd == ScAddress(8,6,0));
763 1 : }
764 :
765 1 : void Test::testFormulaCompiler()
766 : {
767 : struct {
768 : const char* pInput; FormulaGrammar::Grammar eInputGram;
769 : const char* pOutput; FormulaGrammar::Grammar eOutputGram;
770 : } aTests[] = {
771 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "[.B1]-[.$C2]+[.D$3]-[.$E$4]", FormulaGrammar::GRAM_ODFF },
772 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE },
773 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE_XL_A1 },
774 : { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "RC[1]-R[1]C3+R3C[3]-R4C5", FormulaGrammar::GRAM_NATIVE_XL_R1C1 },
775 1 : };
776 :
777 5 : for (size_t i = 0, n = SAL_N_ELEMENTS(aTests); i < n; ++i)
778 : {
779 4 : boost::scoped_ptr<ScTokenArray> pArray;
780 : {
781 4 : ScCompiler aComp(m_pDoc, ScAddress());
782 4 : aComp.SetGrammar(aTests[i].eInputGram);
783 4 : pArray.reset(aComp.CompileString(OUString::createFromAscii(aTests[i].pInput)));
784 4 : CPPUNIT_ASSERT_MESSAGE("Token array shouldn't be NULL!", pArray.get());
785 : }
786 :
787 8 : OUString aFormula = toString(*m_pDoc, ScAddress(), *pArray, aTests[i].eOutputGram);
788 4 : CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aTests[i].pOutput), aFormula);
789 4 : }
790 1 : }
791 :
792 1 : void Test::testFormulaRefUpdate()
793 : {
794 1 : m_pDoc->InsertTab(0, "Formula");
795 :
796 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
797 :
798 1 : m_pDoc->SetValue(ScAddress(0,0,0), 2.0); // A1
799 1 : m_pDoc->SetString(ScAddress(2,2,0), "=A1"); // C3
800 1 : m_pDoc->SetString(ScAddress(2,3,0), "=$A$1"); // C4
801 :
802 1 : ScAddress aPos(2,2,0);
803 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
804 0 : CPPUNIT_FAIL("Wrong formula in C3.");
805 :
806 1 : aPos = ScAddress(2,3,0);
807 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
808 0 : CPPUNIT_FAIL("Wrong formula in C4.");
809 :
810 : // Delete row 2 to push formula cells up (to C2:C3).
811 1 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,1,0));
812 :
813 1 : aPos = ScAddress(2,1,0);
814 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
815 0 : CPPUNIT_FAIL("Wrong formula in C2.");
816 :
817 1 : aPos = ScAddress(2,2,0);
818 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
819 0 : CPPUNIT_FAIL("Wrong formula in C3.");
820 :
821 : // Insert one row at row 2 to move them back.
822 1 : m_pDoc->InsertRow(ScRange(0,1,0,MAXCOL,1,0));
823 :
824 1 : aPos = ScAddress(2,2,0);
825 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
826 0 : CPPUNIT_FAIL("Wrong formula in C3.");
827 :
828 1 : aPos = ScAddress(2,3,0);
829 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
830 0 : CPPUNIT_FAIL("Wrong formula in C4.");
831 :
832 : // Insert 2 rows at row 1 to shift all of A1 and C3:C4 down.
833 1 : m_pDoc->InsertRow(ScRange(0,0,0,MAXCOL,1,0));
834 :
835 1 : aPos = ScAddress(2,4,0);
836 1 : if (!checkFormula(*m_pDoc, aPos, "A3"))
837 0 : CPPUNIT_FAIL("Wrong formula in C5.");
838 :
839 1 : aPos = ScAddress(2,5,0);
840 1 : if (!checkFormula(*m_pDoc, aPos, "$A$3"))
841 0 : CPPUNIT_FAIL("Wrong formula in C6.");
842 :
843 : // Delete 2 rows at row 1 to shift them back.
844 1 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,1,0));
845 :
846 1 : aPos = ScAddress(2,2,0);
847 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
848 0 : CPPUNIT_FAIL("Wrong formula in C3.");
849 :
850 1 : aPos = ScAddress(2,3,0);
851 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
852 0 : CPPUNIT_FAIL("Wrong formula in C4.");
853 :
854 : // Insert 3 columns at column B. to shift C3:C4 to F3:F4.
855 1 : m_pDoc->InsertCol(ScRange(1,0,0,3,MAXROW,0));
856 :
857 1 : aPos = ScAddress(5,2,0);
858 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
859 0 : CPPUNIT_FAIL("Wrong formula in F3.");
860 :
861 1 : aPos = ScAddress(5,3,0);
862 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
863 0 : CPPUNIT_FAIL("Wrong formula in F4.");
864 :
865 : // Delete columns B:D to shift them back.
866 1 : m_pDoc->DeleteCol(ScRange(1,0,0,3,MAXROW,0));
867 :
868 1 : aPos = ScAddress(2,2,0);
869 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
870 0 : CPPUNIT_FAIL("Wrong formula in C3.");
871 :
872 1 : aPos = ScAddress(2,3,0);
873 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
874 0 : CPPUNIT_FAIL("Wrong formula in C4.");
875 :
876 : // Insert cells over A1:A3 to only shift A1 down to A4.
877 1 : m_pDoc->InsertRow(ScRange(0,0,0,0,2,0));
878 :
879 1 : aPos = ScAddress(2,2,0);
880 1 : if (!checkFormula(*m_pDoc, aPos, "A4"))
881 0 : CPPUNIT_FAIL("Wrong formula in C3.");
882 :
883 1 : aPos = ScAddress(2,3,0);
884 1 : if (!checkFormula(*m_pDoc, aPos, "$A$4"))
885 0 : CPPUNIT_FAIL("Wrong formula in C4.");
886 :
887 : // .. and back.
888 1 : m_pDoc->DeleteRow(ScRange(0,0,0,0,2,0));
889 :
890 1 : aPos = ScAddress(2,2,0);
891 1 : if (!checkFormula(*m_pDoc, aPos, "A1"))
892 0 : CPPUNIT_FAIL("Wrong formula in C3.");
893 :
894 1 : aPos = ScAddress(2,3,0);
895 1 : if (!checkFormula(*m_pDoc, aPos, "$A$1"))
896 0 : CPPUNIT_FAIL("Wrong formula in C4.");
897 :
898 : // Delete row 1 which will delete the value cell (A1).
899 1 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,0,0));
900 :
901 1 : aPos = ScAddress(2,1,0);
902 1 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
903 1 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
904 1 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
905 1 : aPos = ScAddress(2,2,0);
906 1 : pFC = m_pDoc->GetFormulaCell(aPos);
907 1 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
908 1 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
909 :
910 : // Clear all and start over.
911 1 : clearRange(m_pDoc, ScRange(0,0,0,10,10,0));
912 :
913 :
914 : // Test range updates
915 :
916 :
917 : // Fill B2:C3 with values.
918 1 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
919 1 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
920 1 : m_pDoc->SetValue(ScAddress(2,1,0), 3);
921 1 : m_pDoc->SetValue(ScAddress(2,2,0), 4);
922 :
923 1 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM(B2:C3)");
924 1 : m_pDoc->SetString(ScAddress(0,6,0), "=SUM($B$2:$C$3)");
925 :
926 1 : aPos = ScAddress(0,5,0);
927 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
928 0 : CPPUNIT_FAIL("Wrong formula in A6.");
929 :
930 1 : aPos = ScAddress(0,6,0);
931 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
932 0 : CPPUNIT_FAIL("Wrong formula in A7.");
933 :
934 : // Insert a row at row 1.
935 1 : m_pDoc->InsertRow(ScRange(0,0,0,MAXCOL,0,0));
936 :
937 1 : aPos = ScAddress(0,6,0);
938 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B3:C4)"))
939 0 : CPPUNIT_FAIL("Wrong formula in A7.");
940 :
941 1 : aPos = ScAddress(0,7,0);
942 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$3:$C$4)"))
943 0 : CPPUNIT_FAIL("Wrong formula in A8.");
944 :
945 : // ... and back.
946 1 : m_pDoc->DeleteRow(ScRange(0,0,0,MAXCOL,0,0));
947 :
948 1 : aPos = ScAddress(0,5,0);
949 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
950 0 : CPPUNIT_FAIL("Wrong formula in A6.");
951 :
952 1 : aPos = ScAddress(0,6,0);
953 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
954 0 : CPPUNIT_FAIL("Wrong formula in A7.");
955 :
956 : // Insert columns B:C to shift only the value range.
957 1 : m_pDoc->InsertCol(ScRange(1,0,0,2,MAXROW,0));
958 :
959 1 : aPos = ScAddress(0,5,0);
960 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(D2:E3)"))
961 0 : CPPUNIT_FAIL("Wrong formula in A6.");
962 :
963 1 : aPos = ScAddress(0,6,0);
964 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($D$2:$E$3)"))
965 0 : CPPUNIT_FAIL("Wrong formula in A7.");
966 :
967 : // ... and back.
968 1 : m_pDoc->DeleteCol(ScRange(1,0,0,2,MAXROW,0));
969 :
970 1 : aPos = ScAddress(0,5,0);
971 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
972 0 : CPPUNIT_FAIL("Wrong formula in A6.");
973 :
974 1 : aPos = ScAddress(0,6,0);
975 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
976 0 : CPPUNIT_FAIL("Wrong formula in A7.");
977 :
978 : // Insert rows 5:6 to shift the formula cells only.
979 1 : m_pDoc->InsertRow(ScRange(0,4,0,MAXCOL,5,0));
980 :
981 1 : aPos = ScAddress(0,7,0);
982 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
983 0 : CPPUNIT_FAIL("Wrong formula in A8.");
984 :
985 1 : aPos = ScAddress(0,8,0);
986 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
987 0 : CPPUNIT_FAIL("Wrong formula in A9.");
988 :
989 : // ... and back.
990 1 : m_pDoc->DeleteRow(ScRange(0,4,0,MAXCOL,5,0));
991 :
992 1 : aPos = ScAddress(0,5,0);
993 1 : if (!checkFormula(*m_pDoc, aPos, "SUM(B2:C3)"))
994 0 : CPPUNIT_FAIL("Wrong formula in A6.");
995 :
996 1 : aPos = ScAddress(0,6,0);
997 1 : if (!checkFormula(*m_pDoc, aPos, "SUM($B$2:$C$3)"))
998 0 : CPPUNIT_FAIL("Wrong formula in A7.");
999 :
1000 : // Check the values of the formula cells in A6:A7.
1001 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1002 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1003 :
1004 : // Insert cells over B1:B2 to partially shift value range.
1005 1 : m_pDoc->InsertRow(ScRange(1,0,0,1,1,0));
1006 :
1007 : // Check the values of the formula cells in A6:A7 again.
1008 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1009 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1010 :
1011 : // ... and shift them back.
1012 1 : m_pDoc->DeleteRow(ScRange(1,0,0,1,1,0));
1013 :
1014 : // The formula cell results should be back too.
1015 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1016 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1017 :
1018 : // Delete rows 2:3 to completely remove the referenced range.
1019 1 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,2,0));
1020 :
1021 : // Both A4 and A5 should show #REF! errors.
1022 1 : pFC = m_pDoc->GetFormulaCell(ScAddress(0,3,0));
1023 1 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1024 1 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
1025 :
1026 1 : pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
1027 1 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1028 1 : CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoRef, pFC->GetErrCode());
1029 :
1030 1 : m_pDoc->DeleteTab(0);
1031 1 : }
1032 :
1033 1 : void Test::testFormulaRefUpdateRange()
1034 : {
1035 1 : m_pDoc->InsertTab(0, "Formula");
1036 :
1037 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1038 :
1039 1 : ScModule* pMod = SC_MOD();
1040 2 : ScInputOptions aOpt = pMod->GetInputOptions();
1041 1 : aOpt.SetExpandRefs(false);
1042 1 : pMod->SetInputOptions(aOpt);
1043 :
1044 : // Set values to B2:C5.
1045 1 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
1046 1 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
1047 1 : m_pDoc->SetValue(ScAddress(1,3,0), 3);
1048 1 : m_pDoc->SetValue(ScAddress(1,4,0), 4);
1049 1 : m_pDoc->SetValue(ScAddress(2,1,0), 5);
1050 1 : m_pDoc->SetValue(ScAddress(2,2,0), 6);
1051 1 : m_pDoc->SetValue(ScAddress(2,3,0), 7);
1052 1 : m_pDoc->SetValue(ScAddress(2,4,0), 8);
1053 :
1054 : // Set formula cells to A7 and A8.
1055 1 : m_pDoc->SetString(ScAddress(0,6,0), "=SUM(B2:C5)");
1056 1 : m_pDoc->SetString(ScAddress(0,7,0), "=SUM($B$2:$C$5)");
1057 :
1058 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM(B2:C5)"))
1059 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1060 :
1061 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM($B$2:$C$5)"))
1062 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1063 :
1064 1 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1065 1 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1066 :
1067 : // Delete row 3. This should shrink the range references by one row.
1068 1 : m_pDoc->DeleteRow(ScRange(0,2,0,MAXCOL,2,0));
1069 :
1070 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM(B2:C4)"))
1071 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1072 :
1073 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM($B$2:$C$4)"))
1074 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1075 :
1076 1 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1077 1 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1078 :
1079 : // Delete row 4 - bottom of range
1080 1 : m_pDoc->DeleteRow(ScRange(0,3,0,MAXCOL,3,0));
1081 :
1082 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(B2:C3)"))
1083 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1084 :
1085 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($B$2:$C$3)"))
1086 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1087 :
1088 1 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1089 1 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1090 :
1091 : // Delete row 2 - top of range
1092 1 : m_pDoc->DeleteRow(ScRange(0,1,0,MAXCOL,1,0));
1093 :
1094 1 : if (!checkFormula(*m_pDoc, ScAddress(0,3,0), "SUM(B2:C2)"))
1095 0 : CPPUNIT_FAIL("Wrong formula in A4.");
1096 :
1097 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM($B$2:$C$2)"))
1098 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1099 :
1100 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1101 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1102 :
1103 : // Clear the range and start over.
1104 1 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1105 :
1106 : // Fill C2:F3 with values.
1107 1 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
1108 1 : m_pDoc->SetValue(ScAddress(3,1,0), 2);
1109 1 : m_pDoc->SetValue(ScAddress(4,1,0), 3);
1110 1 : m_pDoc->SetValue(ScAddress(5,1,0), 4);
1111 1 : m_pDoc->SetValue(ScAddress(2,2,0), 5);
1112 1 : m_pDoc->SetValue(ScAddress(3,2,0), 6);
1113 1 : m_pDoc->SetValue(ScAddress(4,2,0), 7);
1114 1 : m_pDoc->SetValue(ScAddress(5,2,0), 8);
1115 :
1116 : // Set formulas to A2 and A3.
1117 1 : m_pDoc->SetString(ScAddress(0,1,0), "=SUM(C2:F3)");
1118 1 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM($C$2:$F$3)");
1119 :
1120 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:F3)"))
1121 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1122 :
1123 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$F$3)"))
1124 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1125 :
1126 1 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1127 1 : CPPUNIT_ASSERT_EQUAL(36.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1128 :
1129 : // Delete column D.
1130 1 : m_pDoc->DeleteCol(ScRange(3,0,0,3,MAXROW,0));
1131 :
1132 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:E3)"))
1133 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1134 :
1135 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$E$3)"))
1136 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1137 :
1138 1 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1139 1 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1140 :
1141 : // Delete column E - the right edge of reference range.
1142 1 : m_pDoc->DeleteCol(ScRange(4,0,0,4,MAXROW,0));
1143 :
1144 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:D3)"))
1145 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1146 :
1147 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$D$3)"))
1148 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1149 :
1150 1 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1151 1 : CPPUNIT_ASSERT_EQUAL(16.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1152 :
1153 : // Delete column C - the left edge of reference range.
1154 1 : m_pDoc->DeleteCol(ScRange(2,0,0,2,MAXROW,0));
1155 :
1156 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C2:C3)"))
1157 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1158 :
1159 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$2:$C$3)"))
1160 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1161 :
1162 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1163 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1164 :
1165 : // Clear the range and start over.
1166 1 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1167 :
1168 : // Disable expansion of range reference on insertion in adjacent areas.
1169 1 : m_pDoc->SetExpandRefs(false);
1170 :
1171 : // Fill C2:D3 with values.
1172 1 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
1173 1 : m_pDoc->SetValue(ScAddress(3,1,0), 2);
1174 1 : m_pDoc->SetValue(ScAddress(2,2,0), 3);
1175 1 : m_pDoc->SetValue(ScAddress(3,2,0), 4);
1176 :
1177 : // Set formulas at A5 and A6.
1178 1 : m_pDoc->SetString(ScAddress(0,4,0), "=SUM(C2:D3)");
1179 1 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM($C$2:$D$3)");
1180 :
1181 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:D3)"))
1182 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1183 :
1184 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$D$3)"))
1185 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1186 :
1187 : // Insert a column at column C. This should simply shift the reference without expansion.
1188 1 : m_pDoc->InsertCol(ScRange(2,0,0,2,MAXROW,0));
1189 :
1190 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(D2:E3)"))
1191 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1192 :
1193 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($D$2:$E$3)"))
1194 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1195 :
1196 : // Shift it back.
1197 1 : m_pDoc->DeleteCol(ScRange(2,0,0,2,MAXROW,0));
1198 :
1199 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:D3)"))
1200 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1201 :
1202 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$D$3)"))
1203 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1204 :
1205 : // Insert at column D. This should expand the reference by one column length.
1206 1 : m_pDoc->InsertCol(ScRange(3,0,0,3,MAXROW,0));
1207 :
1208 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:E3)"))
1209 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1210 :
1211 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$E$3)"))
1212 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1213 :
1214 : // Insert at column F. No expansion should occur since the edge expansion is turned off.
1215 1 : m_pDoc->InsertCol(ScRange(5,0,0,5,MAXROW,0));
1216 :
1217 1 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "SUM(C2:E3)"))
1218 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1219 :
1220 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM($C$2:$E$3)"))
1221 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1222 :
1223 : // Insert at row 2. No expansion should occur with edge expansion turned off.
1224 1 : m_pDoc->InsertRow(ScRange(0,1,0,MAXCOL,1,0));
1225 :
1226 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "SUM(C3:E4)"))
1227 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1228 :
1229 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM($C$3:$E$4)"))
1230 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1231 :
1232 : // Insert at row 4 to expand the reference range.
1233 1 : m_pDoc->InsertRow(ScRange(0,3,0,MAXCOL,3,0));
1234 :
1235 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "SUM(C3:E5)"))
1236 0 : CPPUNIT_FAIL("Wrong formula in A7.");
1237 :
1238 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM($C$3:$E$5)"))
1239 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1240 :
1241 : // Insert at row 6. No expansion with edge expansion turned off.
1242 1 : m_pDoc->InsertRow(ScRange(0,5,0,MAXCOL,5,0));
1243 :
1244 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "SUM(C3:E5)"))
1245 0 : CPPUNIT_FAIL("Wrong formula in A8.");
1246 :
1247 1 : if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "SUM($C$3:$E$5)"))
1248 0 : CPPUNIT_FAIL("Wrong formula in A9.");
1249 :
1250 : // Clear the range and start over.
1251 1 : clearRange(m_pDoc, ScRange(0,0,0,20,20,0));
1252 :
1253 : // Turn edge expansion on.
1254 1 : aOpt.SetExpandRefs(true);
1255 1 : pMod->SetInputOptions(aOpt);
1256 :
1257 : // Fill C6:D7 with values.
1258 1 : m_pDoc->SetValue(ScAddress(2,5,0), 1);
1259 1 : m_pDoc->SetValue(ScAddress(2,6,0), 2);
1260 1 : m_pDoc->SetValue(ScAddress(3,5,0), 3);
1261 1 : m_pDoc->SetValue(ScAddress(3,6,0), 4);
1262 :
1263 : // Set formulas at A2 and A3.
1264 1 : m_pDoc->SetString(ScAddress(0,1,0), "=SUM(C6:D7)");
1265 1 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM($C$6:$D$7)");
1266 :
1267 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:D7)"))
1268 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1269 :
1270 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$D$7)"))
1271 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1272 :
1273 : // Insert at column E. This should expand the reference range by one column.
1274 1 : m_pDoc->InsertCol(ScRange(4,0,0,4,MAXROW,0));
1275 :
1276 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:E7)"))
1277 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1278 :
1279 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$E$7)"))
1280 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1281 :
1282 : // Insert at column C to edge-expand the reference range.
1283 1 : m_pDoc->InsertCol(ScRange(2,0,0,2,MAXROW,0));
1284 :
1285 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F7)"))
1286 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1287 :
1288 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$7)"))
1289 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1290 :
1291 : // Insert at row 8 to edge-expand.
1292 1 : m_pDoc->InsertRow(ScRange(0,7,0,MAXCOL,7,0));
1293 :
1294 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F8)"))
1295 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1296 :
1297 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$8)"))
1298 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1299 :
1300 : // Insert at row 6 to edge-expand.
1301 1 : m_pDoc->InsertRow(ScRange(0,5,0,MAXCOL,5,0));
1302 :
1303 1 : if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "SUM(C6:F9)"))
1304 0 : CPPUNIT_FAIL("Wrong formula in A2.");
1305 :
1306 1 : if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$9)"))
1307 0 : CPPUNIT_FAIL("Wrong formula in A3.");
1308 :
1309 2 : m_pDoc->DeleteTab(0);
1310 1 : }
1311 :
1312 1 : void Test::testFormulaRefUpdateSheets()
1313 : {
1314 1 : m_pDoc->InsertTab(0, "Sheet1");
1315 1 : m_pDoc->InsertTab(1, "Sheet2");
1316 :
1317 1 : OUString aName;
1318 1 : m_pDoc->GetName(0, aName);
1319 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1320 1 : m_pDoc->GetName(1, aName);
1321 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1322 :
1323 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1324 :
1325 : // Set values to B2:C3 on sheet Sheet1.
1326 1 : m_pDoc->SetValue(ScAddress(1,1,0), 1);
1327 1 : m_pDoc->SetValue(ScAddress(1,2,0), 2);
1328 1 : m_pDoc->SetValue(ScAddress(2,1,0), 3);
1329 1 : m_pDoc->SetValue(ScAddress(2,2,0), 4);
1330 :
1331 : // Set formulas to B2 and B3 on sheet Sheet2.
1332 1 : m_pDoc->SetString(ScAddress(1,1,1), "=SUM(Sheet1.B2:C3)");
1333 1 : m_pDoc->SetString(ScAddress(1,2,1), "=SUM($Sheet1.$B$2:$C$3)");
1334 :
1335 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1336 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1337 :
1338 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1339 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1340 :
1341 : // Swap the sheets.
1342 1 : m_pDoc->MoveTab(0, 1);
1343 1 : m_pDoc->GetName(0, aName);
1344 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1345 1 : m_pDoc->GetName(1, aName);
1346 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1347 :
1348 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(Sheet1.B2:C3)"))
1349 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1350 :
1351 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($Sheet1.$B$2:$C$3)"))
1352 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1353 :
1354 : // Swap back.
1355 1 : m_pDoc->MoveTab(0, 1);
1356 1 : m_pDoc->GetName(0, aName);
1357 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1358 1 : m_pDoc->GetName(1, aName);
1359 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1360 :
1361 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1362 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1363 :
1364 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1365 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1366 :
1367 : // Insert a new sheet between the two.
1368 1 : m_pDoc->InsertTab(1, "Temp");
1369 :
1370 1 : m_pDoc->GetName(1, aName);
1371 1 : CPPUNIT_ASSERT_EQUAL(OUString("Temp"), aName);
1372 1 : m_pDoc->GetName(2, aName);
1373 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1374 :
1375 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1376 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1377 :
1378 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1379 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1380 :
1381 : // Move the last sheet (Sheet2) to the first position.
1382 1 : m_pDoc->MoveTab(2, 0);
1383 :
1384 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(Sheet1.B2:C3)"))
1385 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1386 :
1387 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($Sheet1.$B$2:$C$3)"))
1388 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1389 :
1390 : // Move back.
1391 1 : m_pDoc->MoveTab(0, 2);
1392 :
1393 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1394 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1395 :
1396 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1397 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1398 :
1399 : // Move the "Temp" sheet to the last position.
1400 1 : m_pDoc->MoveTab(1, 2);
1401 :
1402 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1403 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1404 :
1405 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1406 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1407 :
1408 : // Move back.
1409 1 : m_pDoc->MoveTab(2, 1);
1410 :
1411 : // Delete the temporary sheet.
1412 1 : m_pDoc->DeleteTab(1);
1413 :
1414 1 : m_pDoc->GetName(1, aName);
1415 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1416 :
1417 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1418 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1419 :
1420 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1421 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1422 :
1423 : // Insert a new sheet before the first one.
1424 1 : m_pDoc->InsertTab(0, "Temp");
1425 :
1426 1 : m_pDoc->GetName(1, aName);
1427 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aName);
1428 1 : m_pDoc->GetName(2, aName);
1429 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1430 :
1431 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,2), "SUM(Sheet1.B2:C3)"))
1432 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1433 :
1434 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,2), "SUM($Sheet1.$B$2:$C$3)"))
1435 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1436 :
1437 : // Delete the temporary sheet.
1438 1 : m_pDoc->DeleteTab(0);
1439 :
1440 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1441 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1442 :
1443 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1444 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1445 :
1446 : // Append a bunch of sheets.
1447 1 : m_pDoc->InsertTab(2, "Temp1");
1448 1 : m_pDoc->InsertTab(3, "Temp2");
1449 1 : m_pDoc->InsertTab(4, "Temp3");
1450 :
1451 : // Move these tabs around. This shouldn't affects the first 2 sheets.
1452 1 : m_pDoc->MoveTab(2, 4);
1453 1 : m_pDoc->MoveTab(3, 2);
1454 :
1455 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,1), "SUM(Sheet1.B2:C3)"))
1456 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1457 :
1458 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,1), "SUM($Sheet1.$B$2:$C$3)"))
1459 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1460 :
1461 : // Delete the temp sheets.
1462 1 : m_pDoc->DeleteTab(4);
1463 1 : m_pDoc->DeleteTab(3);
1464 1 : m_pDoc->DeleteTab(2);
1465 :
1466 : // Delete Sheet1.
1467 1 : m_pDoc->DeleteTab(0);
1468 1 : m_pDoc->GetName(0, aName);
1469 1 : CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aName);
1470 :
1471 1 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(#REF!.B2:C3)"))
1472 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B2.");
1473 :
1474 1 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($#REF!.$B$2:$C$3)"))
1475 0 : CPPUNIT_FAIL("Wrong formula in Sheet2.B3.");
1476 :
1477 2 : m_pDoc->DeleteTab(0);
1478 1 : }
1479 :
1480 1 : void Test::testFormulaRefUpdateMove()
1481 : {
1482 1 : m_pDoc->InsertTab(0, "Sheet1");
1483 :
1484 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1485 :
1486 : // Set value to B4:B6.
1487 1 : m_pDoc->SetValue(ScAddress(1,3,0), 1);
1488 1 : m_pDoc->SetValue(ScAddress(1,4,0), 2);
1489 1 : m_pDoc->SetValue(ScAddress(1,5,0), 3);
1490 :
1491 : // Set formulas to A9:A12 that references B4:B6.
1492 1 : m_pDoc->SetString(ScAddress(0,8,0), "=SUM(B4:B6)");
1493 1 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM($B$4:$B$6)");
1494 1 : m_pDoc->SetString(ScAddress(0,10,0), "=B5");
1495 1 : m_pDoc->SetString(ScAddress(0,11,0), "=$B$6");
1496 :
1497 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,8,0));
1498 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,9,0));
1499 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(0,10,0));
1500 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(0,11,0));
1501 :
1502 : // Move B4:B6 to D4 (two columsn to the right).
1503 1 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1504 1 : bool bMoved = rFunc.MoveBlock(ScRange(1,3,0,1,5,0), ScAddress(3,3,0), true, false, false, false);
1505 1 : CPPUNIT_ASSERT_MESSAGE("Failed to move B4:B6.", bMoved);
1506 :
1507 : // The results of the formula cells that reference the moved range should remain the same.
1508 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,8,0));
1509 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,9,0));
1510 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(0,10,0));
1511 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(0,11,0));
1512 :
1513 1 : if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "SUM(D4:D6)"))
1514 0 : CPPUNIT_FAIL("Wrong formula.");
1515 :
1516 1 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM($D$4:$D$6)"))
1517 0 : CPPUNIT_FAIL("Wrong formula.");
1518 :
1519 1 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "D5"))
1520 0 : CPPUNIT_FAIL("Wrong formula.");
1521 :
1522 1 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "$D$6"))
1523 0 : CPPUNIT_FAIL("Wrong formula.");
1524 :
1525 : // Move A9:A12 to B10:B13.
1526 1 : bMoved = rFunc.MoveBlock(ScRange(0,8,0,0,11,0), ScAddress(1,9,0), true, false, false, false);
1527 1 : CPPUNIT_ASSERT_MESSAGE("Failed to move A9:A12 to B10:B13", bMoved);
1528 :
1529 : // The results of these formula cells should still stay the same.
1530 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(1,9,0));
1531 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(1,10,0));
1532 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1,11,0));
1533 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,12,0));
1534 :
1535 : // Displayed formulas should stay the same since the referenced range hasn't moved.
1536 1 : if (!checkFormula(*m_pDoc, ScAddress(1,9,0), "SUM(D4:D6)"))
1537 0 : CPPUNIT_FAIL("Wrong formula.");
1538 :
1539 1 : if (!checkFormula(*m_pDoc, ScAddress(1,10,0), "SUM($D$4:$D$6)"))
1540 0 : CPPUNIT_FAIL("Wrong formula.");
1541 :
1542 1 : if (!checkFormula(*m_pDoc, ScAddress(1,11,0), "D5"))
1543 0 : CPPUNIT_FAIL("Wrong formula.");
1544 :
1545 1 : if (!checkFormula(*m_pDoc, ScAddress(1,12,0), "$D$6"))
1546 0 : CPPUNIT_FAIL("Wrong formula.");
1547 :
1548 : // The value cells are in D4:D6. Move D4:D5 to the right but leave D6
1549 : // where it is.
1550 1 : bMoved = rFunc.MoveBlock(ScRange(3,3,0,3,4,0), ScAddress(4,3,0), true, false, false, false);
1551 1 : CPPUNIT_ASSERT_MESSAGE("Failed to move D4:D5 to E4:E5", bMoved);
1552 :
1553 : // Only the values of B10 and B11 should be updated.
1554 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,9,0));
1555 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,10,0));
1556 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1,11,0));
1557 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(1,12,0));
1558 :
1559 1 : if (!checkFormula(*m_pDoc, ScAddress(1,9,0), "SUM(D4:D6)"))
1560 0 : CPPUNIT_FAIL("Wrong formula.");
1561 :
1562 1 : if (!checkFormula(*m_pDoc, ScAddress(1,10,0), "SUM($D$4:$D$6)"))
1563 0 : CPPUNIT_FAIL("Wrong formula.");
1564 :
1565 1 : if (!checkFormula(*m_pDoc, ScAddress(1,11,0), "E5"))
1566 0 : CPPUNIT_FAIL("Wrong formula.");
1567 :
1568 1 : if (!checkFormula(*m_pDoc, ScAddress(1,12,0), "$D$6"))
1569 0 : CPPUNIT_FAIL("Wrong formula.");
1570 :
1571 1 : m_pDoc->DeleteTab(0);
1572 1 : }
1573 :
1574 1 : void Test::testFormulaRefUpdateMoveUndo()
1575 : {
1576 1 : m_pDoc->InsertTab(0, "Test");
1577 :
1578 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1579 :
1580 : // Set values in A1:A4.
1581 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1582 1 : m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1583 1 : m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1584 1 : m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
1585 :
1586 : // Set formulas with single cell references in A6:A8.
1587 1 : m_pDoc->SetString(ScAddress(0,5,0), "=A1");
1588 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1589 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A1"))
1590 0 : CPPUNIT_FAIL("Wrong formula.");
1591 :
1592 1 : m_pDoc->SetString(ScAddress(0,6,0), "=A1+A2+A3");
1593 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1594 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "A1+A2+A3"))
1595 0 : CPPUNIT_FAIL("Wrong formula.");
1596 :
1597 1 : m_pDoc->SetString(ScAddress(0,7,0), "=A1+A3+A4");
1598 1 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1599 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "A1+A3+A4"))
1600 0 : CPPUNIT_FAIL("Wrong formula.");
1601 :
1602 : // Set formulas with range references in A10:A12.
1603 1 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM(A1:A2)");
1604 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1605 1 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(A1:A2)"))
1606 0 : CPPUNIT_FAIL("Wrong formula.");
1607 :
1608 1 : m_pDoc->SetString(ScAddress(0,10,0), "=SUM(A1:A3)");
1609 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1610 1 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(A1:A3)"))
1611 0 : CPPUNIT_FAIL("Wrong formula.");
1612 :
1613 1 : m_pDoc->SetString(ScAddress(0,11,0), "=SUM(A1:A4)");
1614 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1615 1 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1616 0 : CPPUNIT_FAIL("Wrong formula.");
1617 :
1618 : // Move A1:A3 to C1:C3. Note that A4 remains.
1619 1 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1620 1 : bool bMoved = rFunc.MoveBlock(ScRange(0,0,0,0,2,0), ScAddress(2,0,0), true, true, false, true);
1621 1 : CPPUNIT_ASSERT(bMoved);
1622 :
1623 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1624 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "C1"))
1625 0 : CPPUNIT_FAIL("Wrong formula.");
1626 :
1627 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1628 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "C1+C2+C3"))
1629 0 : CPPUNIT_FAIL("Wrong formula.");
1630 :
1631 1 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1632 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "C1+C3+A4"))
1633 0 : CPPUNIT_FAIL("Wrong formula.");
1634 :
1635 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1636 1 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(C1:C2)"))
1637 0 : CPPUNIT_FAIL("Wrong formula.");
1638 :
1639 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1640 1 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(C1:C3)"))
1641 0 : CPPUNIT_FAIL("Wrong formula.");
1642 :
1643 1 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1644 1 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1645 0 : CPPUNIT_FAIL("Wrong formula.");
1646 :
1647 : // Undo the move.
1648 1 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1649 1 : CPPUNIT_ASSERT(pUndoMgr);
1650 1 : pUndoMgr->Undo();
1651 :
1652 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1653 1 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A1"))
1654 0 : CPPUNIT_FAIL("Wrong formula.");
1655 :
1656 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1657 1 : if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "A1+A2+A3"))
1658 0 : CPPUNIT_FAIL("Wrong formula.");
1659 :
1660 1 : CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1661 1 : if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "A1+A3+A4"))
1662 0 : CPPUNIT_FAIL("Wrong formula.");
1663 :
1664 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1665 1 : if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(A1:A2)"))
1666 0 : CPPUNIT_FAIL("Wrong formula.");
1667 :
1668 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1669 1 : if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM(A1:A3)"))
1670 0 : CPPUNIT_FAIL("Wrong formula.");
1671 :
1672 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1673 1 : if (!checkFormula(*m_pDoc, ScAddress(0,11,0), "SUM(A1:A4)"))
1674 0 : CPPUNIT_FAIL("Wrong formula.");
1675 :
1676 : // Make sure the broadcasters are still valid by changing the value of A1.
1677 1 : m_pDoc->SetValue(ScAddress(0,0,0), 20);
1678 :
1679 1 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1680 1 : CPPUNIT_ASSERT_EQUAL(25.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1681 1 : CPPUNIT_ASSERT_EQUAL(27.0, m_pDoc->GetValue(ScAddress(0,7,0)));
1682 :
1683 1 : CPPUNIT_ASSERT_EQUAL(22.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1684 1 : CPPUNIT_ASSERT_EQUAL(25.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1685 1 : CPPUNIT_ASSERT_EQUAL(29.0, m_pDoc->GetValue(ScAddress(0,11,0)));
1686 :
1687 1 : m_pDoc->DeleteTab(0);
1688 1 : }
1689 :
1690 1 : void Test::testFormulaRefUpdateNamedExpression()
1691 : {
1692 1 : m_pDoc->InsertTab(0, "Formula");
1693 :
1694 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1695 :
1696 : // Fill C2:C5 with values.
1697 1 : m_pDoc->SetValue(ScAddress(2,1,0), 1);
1698 1 : m_pDoc->SetValue(ScAddress(2,2,0), 2);
1699 1 : m_pDoc->SetValue(ScAddress(2,3,0), 3);
1700 1 : m_pDoc->SetValue(ScAddress(2,4,0), 4);
1701 :
1702 : // Add a named expression that references the immediate left cell.
1703 1 : ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
1704 1 : CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
1705 : ScRangeData* pName = new ScRangeData(
1706 1 : m_pDoc, "ToLeft", "RC[-1]", ScAddress(2,1,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1);
1707 :
1708 1 : bool bInserted = pGlobalNames->insert(pName);
1709 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
1710 :
1711 : // Insert formulas in D2:D5 using the named expression.
1712 1 : m_pDoc->SetString(ScAddress(3,1,0), "=ToLeft");
1713 1 : m_pDoc->SetString(ScAddress(3,2,0), "=ToLeft");
1714 1 : m_pDoc->SetString(ScAddress(3,3,0), "=ToLeft");
1715 1 : m_pDoc->SetString(ScAddress(3,4,0), "=ToLeft");
1716 :
1717 : // Make sure the results are correct.
1718 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,1,0));
1719 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,2,0));
1720 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,3,0));
1721 1 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(3,4,0));
1722 :
1723 : // Push cells in column C down by one cell.
1724 1 : m_pDoc->InsertRow(ScRange(2,0,0,2,0,0));
1725 :
1726 : // Make sure the results change accordingly.
1727 1 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(3,1,0));
1728 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,2,0));
1729 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,3,0));
1730 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,4,0));
1731 :
1732 : // Move cells back.
1733 1 : m_pDoc->DeleteRow(ScRange(2,0,0,2,0,0));
1734 :
1735 : // Make sure the results are back as well.
1736 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(3,1,0));
1737 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(3,2,0));
1738 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(3,3,0));
1739 1 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(3,4,0));
1740 :
1741 : // Fill B10:B12 with values.
1742 1 : m_pDoc->SetValue(ScAddress(1,9,0), 10);
1743 1 : m_pDoc->SetValue(ScAddress(1,10,0), 11);
1744 1 : m_pDoc->SetValue(ScAddress(1,11,0), 12);
1745 :
1746 : // Insert a new named expression that references these values as absolute range.
1747 : pName = new ScRangeData(
1748 1 : m_pDoc, "MyRange", "$B$10:$B$12", ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
1749 1 : bInserted = pGlobalNames->insert(pName);
1750 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
1751 :
1752 : // Set formula at C8 that references this named expression.
1753 1 : m_pDoc->SetString(ScAddress(2,7,0), "=SUM(MyRange)");
1754 1 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,0)));
1755 :
1756 : // Shift B10:B12 to right by 2 columns.
1757 1 : m_pDoc->InsertCol(ScRange(1,9,0,2,11,0));
1758 :
1759 : // This should shift the absolute range B10:B12 that MyRange references.
1760 1 : pName = pGlobalNames->findByUpperName("MYRANGE");
1761 1 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
1762 2 : OUString aExpr;
1763 1 : pName->GetSymbol(aExpr);
1764 1 : CPPUNIT_ASSERT_EQUAL(OUString("$D$10:$D$12"), aExpr);
1765 :
1766 : // This move shouldn't affect the value of C8.
1767 1 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,7,0));
1768 1 : CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1769 1 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,0)));
1770 :
1771 : // Update the value of D10 and make sure C8 gets updated.
1772 1 : m_pDoc->SetValue(ScAddress(3,9,0), 20);
1773 1 : CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(2,7,0)));
1774 :
1775 : // Insert a new sheet before the current.
1776 1 : m_pDoc->InsertTab(0, "New");
1777 2 : OUString aName;
1778 1 : m_pDoc->GetName(1, aName);
1779 1 : CPPUNIT_ASSERT_EQUAL(OUString("Formula"), aName);
1780 :
1781 1 : pName = pGlobalNames->findByUpperName("MYRANGE");
1782 1 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
1783 :
1784 1 : m_pDoc->SetValue(ScAddress(3,9,1), 10);
1785 1 : CPPUNIT_ASSERT_EQUAL(33.0, m_pDoc->GetValue(ScAddress(2,7,1)));
1786 :
1787 : // Delete the inserted sheet, which will shift the 'Formula' sheet to the left.
1788 1 : m_pDoc->DeleteTab(0);
1789 :
1790 1 : aName = OUString();
1791 1 : m_pDoc->GetName(0, aName);
1792 1 : CPPUNIT_ASSERT_EQUAL(OUString("Formula"), aName);
1793 :
1794 1 : pName = pGlobalNames->findByUpperName("MYRANGE");
1795 1 : CPPUNIT_ASSERT_MESSAGE("Failed to find named expression 'MyRange' in the global scope.", pName);
1796 :
1797 1 : m_pDoc->SetValue(ScAddress(3,9,0), 11);
1798 1 : CPPUNIT_ASSERT_EQUAL(34.0, m_pDoc->GetValue(ScAddress(2,7,0)));
1799 :
1800 : // Clear all and start over.
1801 1 : clearRange(m_pDoc, ScRange(0,0,0,100,100,0));
1802 1 : pGlobalNames->clear();
1803 :
1804 : pName = new ScRangeData(
1805 1 : m_pDoc, "MyRange", "$B$1:$C$6", ScAddress(0,0,0), RT_NAME, formula::FormulaGrammar::GRAM_NATIVE);
1806 1 : bInserted = pGlobalNames->insert(pName);
1807 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
1808 1 : pName->GetSymbol(aExpr);
1809 1 : CPPUNIT_ASSERT_EQUAL(OUString("$B$1:$C$6"), aExpr);
1810 :
1811 : // Insert range of cells to shift right. The range partially overlaps the named range.
1812 1 : m_pDoc->InsertCol(ScRange(2,4,0,3,8,0));
1813 :
1814 : // This should not alter the range.
1815 1 : pName->GetSymbol(aExpr);
1816 1 : CPPUNIT_ASSERT_EQUAL(OUString("$B$1:$C$6"), aExpr);
1817 :
1818 2 : m_pDoc->DeleteTab(0);
1819 1 : }
1820 :
1821 1 : void Test::testFormulaRefUpdateNamedExpressionMove()
1822 : {
1823 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1824 :
1825 1 : m_pDoc->InsertTab(0, "Test");
1826 :
1827 : // Set values to B2:B4.
1828 1 : m_pDoc->SetValue(ScAddress(1,1,0), 1.0);
1829 1 : m_pDoc->SetValue(ScAddress(1,2,0), 2.0);
1830 1 : m_pDoc->SetValue(ScAddress(1,3,0), 3.0);
1831 :
1832 : // Set named range for B2:B4.
1833 1 : bool bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$Test.$B$2:$B$4");
1834 1 : CPPUNIT_ASSERT(bInserted);
1835 :
1836 : // Set formula in A10.
1837 1 : m_pDoc->SetString(ScAddress(0,9,0), "=SUM(MyRange)");
1838 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1839 :
1840 1 : ScRangeData* pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
1841 1 : CPPUNIT_ASSERT(pData);
1842 2 : OUString aSymbol;
1843 1 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1844 1 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$B$2:$B$4"), aSymbol);
1845 :
1846 : // Move B2:B4 to D3.
1847 1 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1848 1 : bool bMoved = rFunc.MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(3,2,0), true, true, false, true);
1849 1 : CPPUNIT_ASSERT(bMoved);
1850 :
1851 : // The named range should have moved as well.
1852 1 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1853 1 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$D$3:$D$5"), aSymbol);
1854 :
1855 : // The value of A10 should remain unchanged.
1856 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1857 :
1858 1 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1859 1 : CPPUNIT_ASSERT(pUndoMgr);
1860 :
1861 : // Undo and check.
1862 1 : pUndoMgr->Undo();
1863 :
1864 1 : pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
1865 1 : CPPUNIT_ASSERT(pData);
1866 1 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1867 1 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$B$2:$B$4"), aSymbol);
1868 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1869 :
1870 : // Redo and check.
1871 1 : pUndoMgr->Redo();
1872 :
1873 1 : pData = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
1874 1 : CPPUNIT_ASSERT(pData);
1875 1 : pData->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1876 1 : CPPUNIT_ASSERT_EQUAL(OUString("$Test.$D$3:$D$5"), aSymbol);
1877 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1878 :
1879 : // Undo again to bring it back to the initial condition, and clear the undo buffer.
1880 1 : pUndoMgr->Undo();
1881 1 : pUndoMgr->Clear();
1882 :
1883 : // Add an identical formula to A11 and make a formula group over A10:A11.
1884 1 : m_pDoc->SetString(ScAddress(0,10,0), "=SUM(MyRange)");
1885 1 : ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,9,0));
1886 1 : CPPUNIT_ASSERT(pFC);
1887 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedTopRow());
1888 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
1889 :
1890 : // Move B2:B4 to D3 again.
1891 1 : bMoved = rFunc.MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(3,2,0), true, true, false, true);
1892 1 : CPPUNIT_ASSERT(bMoved);
1893 :
1894 : // Values of A10 and A11 should remain the same.
1895 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,9,0)));
1896 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,10,0)));
1897 :
1898 2 : m_pDoc->DeleteTab(0);
1899 1 : }
1900 :
1901 1 : void Test::testFormulaRefUpdateNamedExpressionExpandRef()
1902 : {
1903 1 : m_pDoc->InsertTab(0, "Test");
1904 1 : m_pDoc->SetExpandRefs(true); // turn on automatic range expansion.
1905 :
1906 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1907 :
1908 1 : bool bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$A$1:$A$3");
1909 1 : CPPUNIT_ASSERT(bInserted);
1910 :
1911 : // Set values to A1:A3.
1912 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1913 1 : m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1914 1 : m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1915 :
1916 1 : m_pDoc->SetString(ScAddress(0,5,0), "=SUM(MyRange)");
1917 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1918 :
1919 : // Insert a new row at row 4, which should expand the named range to A1:A4.
1920 1 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1921 2 : ScMarkData aMark;
1922 1 : aMark.SelectOneTable(0);
1923 1 : rFunc.InsertCells(ScRange(0,3,0,MAXCOL,3,0), &aMark, INS_INSROWS, false, true, false);
1924 1 : ScRangeData* pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
1925 1 : CPPUNIT_ASSERT(pName);
1926 2 : OUString aSymbol;
1927 1 : pName->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1928 1 : CPPUNIT_ASSERT_EQUAL(OUString("$A$1:$A$4"), aSymbol);
1929 :
1930 : // Make sure the listening area has been expanded as well. Note the
1931 : // formula cell has been pushed downward by one cell.
1932 1 : m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
1933 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(0,6,0)));
1934 :
1935 : // Clear the document and start over.
1936 1 : m_pDoc->GetRangeName()->clear();
1937 1 : clearSheet(m_pDoc, 0);
1938 :
1939 : // Set values to B4:B6.
1940 1 : m_pDoc->SetValue(ScAddress(1,3,0), 1.0);
1941 1 : m_pDoc->SetValue(ScAddress(1,4,0), 2.0);
1942 1 : m_pDoc->SetValue(ScAddress(1,5,0), 3.0);
1943 :
1944 1 : bInserted = m_pDoc->InsertNewRangeName("MyRange", ScAddress(0,0,0), "$B$4:$B$6");
1945 1 : CPPUNIT_ASSERT(bInserted);
1946 :
1947 : // Set formula to A1.
1948 1 : m_pDoc->SetString(ScAddress(0,0,0), "=SUM(MyRange)");
1949 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(0,0,0));
1950 :
1951 : // Insert rows over 3:5 which should expand the range by 3 rows.
1952 1 : rFunc.InsertCells(ScRange(0,2,0,MAXCOL,4,0), &aMark, INS_INSROWS, false, true, false);
1953 :
1954 1 : pName = m_pDoc->GetRangeName()->findByUpperName("MYRANGE");
1955 1 : CPPUNIT_ASSERT(pName);
1956 :
1957 1 : pName->GetSymbol(aSymbol, m_pDoc->GetGrammar());
1958 1 : CPPUNIT_ASSERT_EQUAL(OUString("$B$4:$B$9"), aSymbol);
1959 :
1960 2 : m_pDoc->DeleteTab(0);
1961 1 : }
1962 :
1963 1 : void Test::testMultipleOperations()
1964 : {
1965 1 : m_pDoc->InsertTab(0, "MultiOp");
1966 :
1967 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1968 :
1969 : // Insert the reference formula at top row.
1970 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
1971 1 : m_pDoc->SetString(ScAddress(1,0,0), "=A1*10");
1972 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1973 :
1974 : // Insert variable inputs in A3:A5.
1975 1 : m_pDoc->SetValue(ScAddress(0,2,0), 2);
1976 1 : m_pDoc->SetValue(ScAddress(0,3,0), 3);
1977 1 : m_pDoc->SetValue(ScAddress(0,4,0), 4);
1978 :
1979 : // Set multiple operations range.
1980 1 : ScTabOpParam aParam;
1981 1 : aParam.aRefFormulaCell = ScRefAddress(1,0,0,false,false,false);
1982 1 : aParam.aRefFormulaEnd = aParam.aRefFormulaCell;
1983 1 : aParam.aRefColCell = ScRefAddress(0,0,0,false,false,false);
1984 2 : ScMarkData aMark;
1985 1 : aMark.SetMarkArea(ScRange(0,2,0,1,4,0)); // Select A3:B5.
1986 1 : m_pDoc->InsertTableOp(aParam, 0, 2, 1, 4, aMark);
1987 1 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(1,2,0));
1988 1 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(1,3,0));
1989 1 : CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(1,4,0));
1990 :
1991 : // Clear A3:B5.
1992 1 : clearRange(m_pDoc, ScRange(0,2,0,1,4,0));
1993 :
1994 : // This time, use indirect reference formula cell.
1995 1 : m_pDoc->SetString(ScAddress(2,0,0), "=B1"); // C1 simply references B1.
1996 1 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1997 :
1998 : // Insert variable inputs in A3:A5.
1999 1 : m_pDoc->SetValue(ScAddress(0,2,0), 3);
2000 1 : m_pDoc->SetValue(ScAddress(0,3,0), 4);
2001 1 : m_pDoc->SetValue(ScAddress(0,4,0), 5);
2002 :
2003 : // Set multiple operations range again, but this time, we'll use C1 as the reference formula.
2004 1 : aParam.aRefFormulaCell.Set(2,0,0,false,false,false);
2005 1 : aParam.aRefFormulaEnd = aParam.aRefFormulaCell;
2006 1 : m_pDoc->InsertTableOp(aParam, 0, 2, 1, 4, aMark);
2007 1 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(1,2,0));
2008 1 : CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(1,3,0));
2009 1 : CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(1,4,0));
2010 :
2011 2 : m_pDoc->DeleteTab(0);
2012 1 : }
2013 :
2014 1 : void Test::testFuncCOLUMN()
2015 : {
2016 1 : m_pDoc->InsertTab(0, "Formula");
2017 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2018 :
2019 1 : m_pDoc->SetString(ScAddress(5,10,0), "=COLUMN()");
2020 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2021 :
2022 1 : m_pDoc->SetString(ScAddress(0,1,0), "=F11");
2023 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2024 :
2025 : // Move the formula cell with COLUMN() function to change its value.
2026 1 : m_pDoc->InsertCol(ScRange(5,0,0,5,MAXROW,0));
2027 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(6,10,0)));
2028 :
2029 : // The cell that references the moved cell should update its value as well.
2030 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2031 :
2032 : // Move the column in the other direction.
2033 1 : m_pDoc->DeleteCol(ScRange(5,0,0,5,MAXROW,0));
2034 :
2035 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2036 :
2037 : // The cell that references the moved cell should update its value as well.
2038 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2039 :
2040 1 : m_pDoc->DeleteTab(0);
2041 1 : }
2042 :
2043 1 : void Test::testFuncCOUNT()
2044 : {
2045 1 : m_pDoc->InsertTab(0, "Formula");
2046 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2047 :
2048 1 : m_pDoc->SetValue(ScAddress(0,0,0), 2); // A1
2049 1 : m_pDoc->SetValue(ScAddress(0,1,0), 4); // A2
2050 1 : m_pDoc->SetValue(ScAddress(0,2,0), 6); // A3
2051 :
2052 1 : ScAddress aPos(1,0,0);
2053 1 : m_pDoc->SetString(aPos, "=COUNT(A1:A3)");
2054 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(aPos));
2055 :
2056 1 : aPos.IncRow();
2057 1 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2)");
2058 1 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(aPos));
2059 :
2060 1 : aPos.IncRow();
2061 1 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2;4)");
2062 1 : CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(aPos));
2063 :
2064 1 : aPos.IncRow();
2065 1 : m_pDoc->SetString(aPos, "=COUNT(A1:A3;2;4;6)");
2066 1 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(aPos));
2067 :
2068 1 : m_pDoc->DeleteTab(0);
2069 1 : }
2070 :
2071 1 : void Test::testFuncROW()
2072 : {
2073 1 : m_pDoc->InsertTab(0, "Formula");
2074 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
2075 :
2076 1 : m_pDoc->SetString(ScAddress(5,10,0), "=ROW()");
2077 1 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2078 :
2079 1 : m_pDoc->SetString(ScAddress(0,1,0), "=F11");
2080 1 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2081 :
2082 : // Insert 2 new rows at row 4.
2083 1 : m_pDoc->InsertRow(ScRange(0,3,0,MAXCOL,4,0));
2084 1 : CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc->GetValue(ScAddress(5,12,0)));
2085 :
2086 : // The cell that references the moved cell should update its value as well.
2087 1 : CPPUNIT_ASSERT_EQUAL(13.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2088 :
2089 : // Delete 2 rows to move it back.
2090 1 : m_pDoc->DeleteRow(ScRange(0,3,0,MAXCOL,4,0));
2091 :
2092 1 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(5,10,0)));
2093 :
2094 : // The cell that references the moved cell should update its value as well.
2095 1 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,1,0)));
2096 :
2097 1 : m_pDoc->DeleteTab(0);
2098 1 : }
2099 :
2100 1 : void Test::testFuncSUM()
2101 : {
2102 1 : OUString aTabName("foo");
2103 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2104 1 : m_pDoc->InsertTab (0, aTabName));
2105 :
2106 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2107 :
2108 : // Single argument case.
2109 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
2110 1 : m_pDoc->SetValue(ScAddress(0,1,0), 1);
2111 1 : m_pDoc->SetString(ScAddress(0,2,0), "=SUM(A1:A2)");
2112 1 : m_pDoc->CalcAll();
2113 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
2114 :
2115 : // Multiple argument case.
2116 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1);
2117 1 : m_pDoc->SetValue(ScAddress(0,1,0), 22);
2118 1 : m_pDoc->SetValue(ScAddress(0,2,0), 4);
2119 1 : m_pDoc->SetValue(ScAddress(0,3,0), 5);
2120 1 : m_pDoc->SetValue(ScAddress(0,4,0), 6);
2121 :
2122 1 : m_pDoc->SetValue(ScAddress(1,0,0), 3);
2123 1 : m_pDoc->SetValue(ScAddress(1,1,0), 4);
2124 1 : m_pDoc->SetValue(ScAddress(1,2,0), 5);
2125 1 : m_pDoc->SetValue(ScAddress(1,3,0), 6);
2126 1 : m_pDoc->SetValue(ScAddress(1,4,0), 7);
2127 :
2128 1 : m_pDoc->SetString(ScAddress(3,0,0), "=SUM(A1:A2;B1:B2)");
2129 1 : m_pDoc->SetString(ScAddress(3,1,0), "=SUM(A2:A3;B2:B3)");
2130 1 : m_pDoc->SetString(ScAddress(3,2,0), "=SUM(A3:A4;B3:B4)");
2131 1 : CPPUNIT_ASSERT_EQUAL(30.0, m_pDoc->GetValue(ScAddress(3,0,0)));
2132 1 : CPPUNIT_ASSERT_EQUAL(35.0, m_pDoc->GetValue(ScAddress(3,1,0)));
2133 1 : CPPUNIT_ASSERT_EQUAL(20.0, m_pDoc->GetValue(ScAddress(3,2,0)));
2134 :
2135 : // Clear and start over.
2136 1 : clearRange(m_pDoc, ScRange(0,0,0,3,MAXROW,0));
2137 :
2138 : // SUM needs to take the first error in case the range contains an error.
2139 1 : m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
2140 1 : m_pDoc->SetValue(ScAddress(0,1,0), 10.0);
2141 1 : m_pDoc->SetValue(ScAddress(0,2,0), 100.0);
2142 1 : m_pDoc->SetString(ScAddress(0,3,0), "=SUM(A1:A3)");
2143 1 : CPPUNIT_ASSERT_EQUAL(111.0, m_pDoc->GetValue(ScAddress(0,3,0)));
2144 :
2145 : // Set #DIV/0! error to A3. A4 should also inherit this error.
2146 1 : m_pDoc->SetString(ScAddress(0,2,0), "=1/0");
2147 1 : sal_uInt16 nErr = m_pDoc->GetErrCode(ScAddress(0,2,0));
2148 2 : CPPUNIT_ASSERT_MESSAGE("Cell should have a division by zero error.",
2149 1 : nErr == errDivisionByZero);
2150 1 : nErr = m_pDoc->GetErrCode(ScAddress(0,3,0));
2151 2 : CPPUNIT_ASSERT_MESSAGE("SUM should have also inherited a div-by-zero error.",
2152 1 : nErr == errDivisionByZero);
2153 :
2154 : // Set #NA! to A2. A4 should now inherit this error.
2155 1 : m_pDoc->SetString(ScAddress(0,1,0), "=NA()");
2156 1 : nErr = m_pDoc->GetErrCode(ScAddress(0,1,0));
2157 1 : CPPUNIT_ASSERT_MESSAGE("A2 should be an error.", nErr);
2158 2 : CPPUNIT_ASSERT_MESSAGE("A4 should have inherited the same error as A2.",
2159 1 : nErr == m_pDoc->GetErrCode(ScAddress(0,3,0)));
2160 :
2161 2 : m_pDoc->DeleteTab(0);
2162 1 : }
2163 :
2164 1 : void Test::testFuncPRODUCT()
2165 : {
2166 1 : OUString aTabName("foo");
2167 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2168 1 : m_pDoc->InsertTab (0, aTabName));
2169 :
2170 1 : double val = 1;
2171 : double result;
2172 1 : m_pDoc->SetValue(0, 0, 0, val);
2173 1 : val = 2;
2174 1 : m_pDoc->SetValue(0, 1, 0, val);
2175 1 : val = 3;
2176 1 : m_pDoc->SetValue(0, 2, 0, val);
2177 1 : m_pDoc->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
2178 1 : m_pDoc->CalcAll();
2179 1 : m_pDoc->GetValue(0, 3, 0, result);
2180 1 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
2181 :
2182 1 : m_pDoc->SetString(0, 4, 0, OUString("=PRODUCT({1;2;3})"));
2183 1 : m_pDoc->CalcAll();
2184 1 : m_pDoc->GetValue(0, 4, 0, result);
2185 1 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 6.0);
2186 :
2187 1 : m_pDoc->DeleteTab(0);
2188 1 : }
2189 :
2190 1 : void Test::testFuncSUMPRODUCT()
2191 : {
2192 1 : m_pDoc->InsertTab(0, "Test");
2193 :
2194 1 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto recalc.
2195 :
2196 1 : ScAddress aPos(0,0,0);
2197 1 : m_pDoc->SetString(aPos, "=SUMPRODUCT(B1:B3;C1:C3)");
2198 1 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(aPos));
2199 1 : m_pDoc->SetValue(ScAddress(2,0,0), 1.0); // C1
2200 1 : CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(aPos));
2201 1 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0); // B1
2202 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(aPos));
2203 1 : m_pDoc->SetValue(ScAddress(1,1,0), 2.0); // B2
2204 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(aPos));
2205 1 : m_pDoc->SetValue(ScAddress(2,1,0), 3.0); // C2
2206 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
2207 1 : m_pDoc->SetValue(ScAddress(2,2,0), -2.0); // C3
2208 1 : CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(aPos));
2209 1 : m_pDoc->SetValue(ScAddress(1,2,0), 5.0); // B3
2210 1 : CPPUNIT_ASSERT_EQUAL(-3.0, m_pDoc->GetValue(aPos));
2211 :
2212 1 : m_pDoc->DeleteTab(0);
2213 1 : }
2214 :
2215 1 : void Test::testFuncN()
2216 : {
2217 1 : OUString aTabName("foo");
2218 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2219 1 : m_pDoc->InsertTab (0, aTabName));
2220 :
2221 : double result;
2222 :
2223 : // Clear the area first.
2224 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 1, 20, 0));
2225 :
2226 : // Put values to reference.
2227 1 : double val = 0;
2228 1 : m_pDoc->SetValue(0, 0, 0, val);
2229 1 : m_pDoc->SetString(0, 2, 0, OUString("Text"));
2230 1 : val = 1;
2231 1 : m_pDoc->SetValue(0, 3, 0, val);
2232 1 : val = -1;
2233 1 : m_pDoc->SetValue(0, 4, 0, val);
2234 1 : val = 12.3;
2235 1 : m_pDoc->SetValue(0, 5, 0, val);
2236 1 : m_pDoc->SetString(0, 6, 0, OUString("'12.3"));
2237 :
2238 : // Cell references
2239 1 : m_pDoc->SetString(1, 0, 0, OUString("=N(A1)"));
2240 1 : m_pDoc->SetString(1, 1, 0, OUString("=N(A2)"));
2241 1 : m_pDoc->SetString(1, 2, 0, OUString("=N(A3)"));
2242 1 : m_pDoc->SetString(1, 3, 0, OUString("=N(A4)"));
2243 1 : m_pDoc->SetString(1, 4, 0, OUString("=N(A5)"));
2244 1 : m_pDoc->SetString(1, 5, 0, OUString("=N(A6)"));
2245 1 : m_pDoc->SetString(1, 6, 0, OUString("=N(A9)"));
2246 :
2247 : // In-line values
2248 1 : m_pDoc->SetString(1, 7, 0, OUString("=N(0)"));
2249 1 : m_pDoc->SetString(1, 8, 0, OUString("=N(1)"));
2250 1 : m_pDoc->SetString(1, 9, 0, OUString("=N(-1)"));
2251 1 : m_pDoc->SetString(1, 10, 0, OUString("=N(123)"));
2252 1 : m_pDoc->SetString(1, 11, 0, OUString("=N(\"\")"));
2253 1 : m_pDoc->SetString(1, 12, 0, OUString("=N(\"12\")"));
2254 1 : m_pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
2255 :
2256 : // Range references
2257 1 : m_pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
2258 1 : m_pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
2259 1 : m_pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
2260 1 : m_pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
2261 :
2262 : // Calculate and check the results.
2263 1 : m_pDoc->CalcAll();
2264 : double checks1[] = {
2265 : 0, 0, 0, 1, -1, 12.3, 0, // cell reference
2266 : 0, 1, -1, 123, 0, 0, 0 // in-line values
2267 1 : };
2268 15 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
2269 : {
2270 14 : m_pDoc->GetValue(1, i, 0, result);
2271 14 : bool bGood = result == checks1[i];
2272 14 : if (!bGood)
2273 : {
2274 0 : cerr << "row " << (i+1) << ": expected=" << checks1[i] << " actual=" << result << endl;
2275 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
2276 : }
2277 : }
2278 : double checks2[] = {
2279 : 0, 1, -1, 12.3 // range references
2280 1 : };
2281 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
2282 : {
2283 4 : m_pDoc->GetValue(1, i+2, 0, result);
2284 4 : bool bGood = result == checks2[i];
2285 4 : if (!bGood)
2286 : {
2287 0 : cerr << "row " << (i+2+1) << ": expected=" << checks2[i] << " actual=" << result << endl;
2288 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
2289 : }
2290 : }
2291 :
2292 1 : m_pDoc->DeleteTab(0);
2293 1 : }
2294 :
2295 1 : void Test::testFuncCOUNTIF()
2296 : {
2297 : // COUNTIF (test case adopted from OOo i#36381)
2298 :
2299 1 : OUString aTabName("foo");
2300 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2301 1 : m_pDoc->InsertTab (0, aTabName));
2302 :
2303 : // Empty A1:A39 first.
2304 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
2305 :
2306 : // Raw data (rows 1 through 9)
2307 : const char* aData[] = {
2308 : "1999",
2309 : "2000",
2310 : "0",
2311 : "0",
2312 : "0",
2313 : "2002",
2314 : "2001",
2315 : "X",
2316 : "2002"
2317 1 : };
2318 :
2319 1 : SCROW nRows = SAL_N_ELEMENTS(aData);
2320 10 : for (SCROW i = 0; i < nRows; ++i)
2321 9 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
2322 :
2323 1 : printRange(m_pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
2324 :
2325 : // formulas and results
2326 : struct {
2327 : const char* pFormula; double fResult;
2328 : } aChecks[] = {
2329 : { "=COUNTIF(A1:A12;1999)", 1 },
2330 : { "=COUNTIF(A1:A12;2002)", 2 },
2331 : { "=COUNTIF(A1:A12;1998)", 0 },
2332 : { "=COUNTIF(A1:A12;\">=1999\")", 5 },
2333 : { "=COUNTIF(A1:A12;\">1999\")", 4 },
2334 : { "=COUNTIF(A1:A12;\"<2001\")", 5 },
2335 : { "=COUNTIF(A1:A12;\">0\")", 5 },
2336 : { "=COUNTIF(A1:A12;\">=0\")", 8 },
2337 : { "=COUNTIF(A1:A12;0)", 3 },
2338 : { "=COUNTIF(A1:A12;\"X\")", 1 },
2339 : { "=COUNTIF(A1:A12;)", 3 }
2340 1 : };
2341 :
2342 1 : nRows = SAL_N_ELEMENTS(aChecks);
2343 12 : for (SCROW i = 0; i < nRows; ++i)
2344 : {
2345 11 : SCROW nRow = 20 + i;
2346 11 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
2347 : }
2348 1 : m_pDoc->CalcAll();
2349 :
2350 12 : for (SCROW i = 0; i < nRows; ++i)
2351 : {
2352 : double result;
2353 11 : SCROW nRow = 20 + i;
2354 11 : m_pDoc->GetValue(0, nRow, 0, result);
2355 11 : bool bGood = result == aChecks[i].fResult;
2356 11 : if (!bGood)
2357 : {
2358 0 : cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
2359 0 : << " expected=" << aChecks[i].fResult << " actual=" << result << endl;
2360 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
2361 : }
2362 : }
2363 :
2364 : // Don't count empty strings when searching for a number.
2365 :
2366 : // Clear A1:A2.
2367 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 1, 0));
2368 :
2369 1 : m_pDoc->SetString(0, 0, 0, OUString("=\"\""));
2370 1 : m_pDoc->SetString(0, 1, 0, OUString("=COUNTIF(A1;1)"));
2371 1 : m_pDoc->CalcAll();
2372 :
2373 1 : double result = m_pDoc->GetValue(0, 1, 0);
2374 1 : CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
2375 :
2376 1 : m_pDoc->DeleteTab(0);
2377 1 : }
2378 :
2379 1 : void Test::testFuncIFERROR()
2380 : {
2381 : // IFERROR/IFNA (fdo#56124)
2382 :
2383 1 : OUString aTabName("foo");
2384 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2385 1 : m_pDoc->InsertTab (0, aTabName));
2386 :
2387 : // Empty A1:A39 first.
2388 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
2389 :
2390 : // Raw data (rows 1 through 12)
2391 : const char* aData[] = {
2392 : "1",
2393 : "e",
2394 : "=SQRT(4)",
2395 : "=SQRT(-2)",
2396 : "=A4",
2397 : "=1/0",
2398 : "=NA()",
2399 : "bar",
2400 : "4",
2401 : "gee",
2402 : "=1/0",
2403 : "23"
2404 1 : };
2405 :
2406 1 : SCROW nRows = SAL_N_ELEMENTS(aData);
2407 13 : for (SCROW i = 0; i < nRows; ++i)
2408 12 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
2409 :
2410 1 : printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows-1, 0), "data range for IFERROR/IFNA");
2411 :
2412 : // formulas and results
2413 : struct {
2414 : const char* pFormula; const char* pResult;
2415 : } aChecks[] = {
2416 : { "=IFERROR(A1;9)", "1" },
2417 : { "=IFERROR(A2;9)", "e" },
2418 : { "=IFERROR(A3;9)", "2" },
2419 : { "=IFERROR(A4;-7)", "-7" },
2420 : { "=IFERROR(A5;-7)", "-7" },
2421 : { "=IFERROR(A6;-7)", "-7" },
2422 : { "=IFERROR(A7;-7)", "-7" },
2423 : { "=IFNA(A6;9)", "#DIV/0!" },
2424 : { "=IFNA(A7;-7)", "-7" },
2425 : { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
2426 : { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
2427 : { "{=IFERROR(3*A11:A12;1998)}[0]", "1998" }, // um.. this is not the correct way to insert a
2428 : { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place holder, see below
2429 1 : };
2430 :
2431 1 : nRows = SAL_N_ELEMENTS(aChecks);
2432 12 : for (SCROW i = 0; i < nRows-2; ++i)
2433 : {
2434 11 : SCROW nRow = 20 + i;
2435 11 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
2436 : }
2437 :
2438 : // Create a matrix range in last two rows of the range above, actual data
2439 : // of the placeholders.
2440 2 : ScMarkData aMark;
2441 1 : aMark.SelectOneTable(0);
2442 1 : m_pDoc->InsertMatrixFormula(0, 20 + nRows-2, 0, 20 + nRows-1, aMark, "=IFERROR(3*A11:A12;1998)", NULL);
2443 :
2444 1 : m_pDoc->CalcAll();
2445 :
2446 14 : for (SCROW i = 0; i < nRows; ++i)
2447 : {
2448 13 : SCROW nRow = 20 + i;
2449 13 : OUString aResult = m_pDoc->GetString(0, nRow, 0);
2450 26 : CPPUNIT_ASSERT_EQUAL_MESSAGE(
2451 13 : aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
2452 13 : }
2453 :
2454 2 : m_pDoc->DeleteTab(0);
2455 1 : }
2456 :
2457 1 : void Test::testFuncSHEET()
2458 : {
2459 1 : OUString aTabName1("test1");
2460 2 : OUString aTabName2("test2");
2461 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2462 1 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName1));
2463 :
2464 1 : m_pDoc->SetString(0, 0, 0, OUString("=SHEETS()"));
2465 1 : m_pDoc->CalcFormulaTree(false, false);
2466 : double original;
2467 1 : m_pDoc->GetValue(0, 0, 0, original);
2468 :
2469 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() should equal the number of sheets, but doesn't.",
2470 1 : static_cast<SCTAB>(original) == m_pDoc->GetTableCount());
2471 :
2472 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2473 1 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName2));
2474 :
2475 : double modified;
2476 1 : m_pDoc->GetValue(0, 0, 0, modified);
2477 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
2478 1 : modified - original == 1.0);
2479 :
2480 1 : SCTAB nTabCount = m_pDoc->GetTableCount();
2481 1 : m_pDoc->DeleteTab(--nTabCount);
2482 :
2483 1 : m_pDoc->GetValue(0, 0, 0, modified);
2484 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet removal.",
2485 1 : modified - original == 0.0);
2486 :
2487 2 : m_pDoc->DeleteTab(--nTabCount);
2488 1 : }
2489 :
2490 1 : void Test::testFuncNOW()
2491 : {
2492 1 : OUString aTabName("foo");
2493 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2494 1 : m_pDoc->InsertTab (0, aTabName));
2495 :
2496 1 : double val = 1;
2497 1 : m_pDoc->SetValue(0, 0, 0, val);
2498 1 : m_pDoc->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
2499 : double now1;
2500 1 : m_pDoc->GetValue(0, 1, 0, now1);
2501 1 : CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
2502 :
2503 1 : val = 0;
2504 1 : m_pDoc->SetValue(0, 0, 0, val);
2505 1 : m_pDoc->CalcFormulaTree(false, false);
2506 : double zero;
2507 1 : m_pDoc->GetValue(0, 1, 0, zero);
2508 1 : CPPUNIT_ASSERT_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", zero == 0.0);
2509 :
2510 1 : val = 1;
2511 1 : m_pDoc->SetValue(0, 0, 0, val);
2512 1 : m_pDoc->CalcFormulaTree(false, false);
2513 : double now2;
2514 1 : m_pDoc->GetValue(0, 1, 0, now2);
2515 1 : CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
2516 :
2517 1 : m_pDoc->DeleteTab(0);
2518 1 : }
2519 :
2520 1 : void Test::testFuncNUMBERVALUE()
2521 : {
2522 : // NUMBERVALUE fdo#57180
2523 :
2524 1 : OUString aTabName("foo");
2525 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2526 1 : m_pDoc->InsertTab (0, aTabName));
2527 :
2528 : // Empty A1:A39 first.
2529 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
2530 :
2531 : // Raw data (rows 1 through 6)
2532 : const char* aData[] = {
2533 : "1ag9a9b9",
2534 : "1ag34 5g g6 78b9%%",
2535 : "1 234d56E-2",
2536 : "d4",
2537 : "54.4",
2538 : "1a2b3e1%"
2539 1 : };
2540 :
2541 1 : SCROW nRows = SAL_N_ELEMENTS(aData);
2542 7 : for (SCROW i = 0; i < nRows; ++i)
2543 6 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
2544 :
2545 1 : printRange(m_pDoc, ScRange(0, 0, 0, 0, nRows - 1, 0), "data range for NUMBERVALUE");
2546 :
2547 : // formulas and results
2548 : struct {
2549 : const char* pFormula; const char* pResult;
2550 : } aChecks[] = {
2551 : { "=NUMBERVALUE(A1;\"b\";\"ag\")", "199.9" },
2552 : { "=NUMBERVALUE(A2;\"b\";\"ag\")", "134.56789" },
2553 : { "=NUMBERVALUE(A2;\"b\";\"g\")", "#VALUE!" },
2554 : { "=NUMBERVALUE(A3;\"d\")", "12.3456" },
2555 : { "=NUMBERVALUE(A4;\"d\";\"foo\")", "0.4" },
2556 : { "=NUMBERVALUE(A4;)", "Err:502" },
2557 : { "=NUMBERVALUE(A5;)", "Err:502" },
2558 : { "=NUMBERVALUE(A6;\"b\";\"a\")", "1.23" }
2559 1 : };
2560 :
2561 1 : nRows = SAL_N_ELEMENTS(aChecks);
2562 9 : for (SCROW i = 0; i < nRows; ++i)
2563 : {
2564 8 : SCROW nRow = 20 + i;
2565 8 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aChecks[i].pFormula));
2566 : }
2567 1 : m_pDoc->CalcAll();
2568 :
2569 9 : for (SCROW i = 0; i < nRows; ++i)
2570 : {
2571 8 : SCROW nRow = 20 + i;
2572 8 : OUString aResult = m_pDoc->GetString(0, nRow, 0);
2573 16 : CPPUNIT_ASSERT_EQUAL_MESSAGE(
2574 8 : aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
2575 8 : }
2576 :
2577 1 : m_pDoc->DeleteTab(0);
2578 1 : }
2579 :
2580 1 : void Test::testFuncLOOKUP()
2581 : {
2582 1 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
2583 :
2584 1 : m_pDoc->InsertTab(0, "Test");
2585 :
2586 : // Raw data
2587 : const char* aData[][2] = {
2588 : { "=CONCATENATE(\"A\")", "1" },
2589 : { "=CONCATENATE(\"B\")", "2" },
2590 : { "=CONCATENATE(\"C\")", "3" },
2591 : { 0, 0 } // terminator
2592 1 : };
2593 :
2594 : // Insert raw data into A1:B3.
2595 4 : for (SCROW i = 0; aData[i][0]; ++i)
2596 : {
2597 3 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
2598 3 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
2599 : }
2600 :
2601 : const char* aData2[][2] = {
2602 : { "A", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
2603 : { "B", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
2604 : { "C", "=LOOKUP(RC[-1];R1C1:R3C1;R1C2:R3C2)" },
2605 : { 0, 0 } // terminator
2606 1 : };
2607 :
2608 : // Insert check formulas into A5:B7.
2609 4 : for (SCROW i = 0; aData2[i][0]; ++i)
2610 : {
2611 3 : m_pDoc->SetString(0, i+4, 0, OUString::createFromAscii(aData2[i][0]));
2612 3 : m_pDoc->SetString(1, i+4, 0, OUString::createFromAscii(aData2[i][1]));
2613 : }
2614 :
2615 1 : printRange(m_pDoc, ScRange(0,4,0,1,6,0), "Data range for LOOKUP.");
2616 :
2617 : // Values for B5:B7 should be 1, 2, and 3.
2618 1 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,4,0)) == 0);
2619 1 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,5,0)) == 0);
2620 1 : CPPUNIT_ASSERT_MESSAGE("This formula should not have an error code.", m_pDoc->GetErrCode(ScAddress(1,6,0)) == 0);
2621 :
2622 1 : CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,4,0)));
2623 1 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,5,0)));
2624 1 : CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,6,0)));
2625 :
2626 1 : m_pDoc->DeleteTab(0);
2627 1 : }
2628 :
2629 1 : void Test::testFuncVLOOKUP()
2630 : {
2631 : // VLOOKUP
2632 :
2633 1 : OUString aTabName("foo");
2634 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2635 1 : m_pDoc->InsertTab (0, aTabName));
2636 :
2637 : // Clear A1:F40.
2638 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 5, 39, 0));
2639 :
2640 : // Raw data
2641 : const char* aData[][2] = {
2642 : { "Key", "Val" },
2643 : { "10", "3" },
2644 : { "20", "4" },
2645 : { "30", "5" },
2646 : { "40", "6" },
2647 : { "50", "7" },
2648 : { "60", "8" },
2649 : { "70", "9" },
2650 : { "B", "10" },
2651 : { "B", "11" },
2652 : { "C", "12" },
2653 : { "D", "13" },
2654 : { "E", "14" },
2655 : { "F", "15" },
2656 : { 0, 0 } // terminator
2657 1 : };
2658 :
2659 : // Insert raw data into A1:B14.
2660 15 : for (SCROW i = 0; aData[i][0]; ++i)
2661 : {
2662 14 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
2663 14 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
2664 : }
2665 :
2666 1 : printRange(m_pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
2667 :
2668 : // Formula data
2669 : struct {
2670 : const char* pLookup; const char* pFormula; const char* pRes;
2671 : } aChecks[] = {
2672 : { "Lookup", "Formula", 0 },
2673 : { "12", "=VLOOKUP(D2;A2:B14;2;1)", "3" },
2674 : { "29", "=VLOOKUP(D3;A2:B14;2;1)", "4" },
2675 : { "31", "=VLOOKUP(D4;A2:B14;2;1)", "5" },
2676 : { "45", "=VLOOKUP(D5;A2:B14;2;1)", "6" },
2677 : { "56", "=VLOOKUP(D6;A2:B14;2;1)", "7" },
2678 : { "65", "=VLOOKUP(D7;A2:B14;2;1)", "8" },
2679 : { "78", "=VLOOKUP(D8;A2:B14;2;1)", "9" },
2680 : { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", "#N/A" },
2681 : { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", "11" },
2682 : { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", "12" },
2683 : { "David", "=VLOOKUP(D12;A2:B14;2;1)", "13" },
2684 : { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", "14" },
2685 : { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", "15" },
2686 : { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", "15" },
2687 : { "100", "=VLOOKUP(D16;A2:B14;2;1)", "9" },
2688 : { "1000", "=VLOOKUP(D17;A2:B14;2;1)", "9" },
2689 : { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", "15" }
2690 1 : };
2691 :
2692 : // Insert formula data into D1:E18.
2693 19 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2694 : {
2695 18 : m_pDoc->SetString(3, i, 0, OUString::createFromAscii(aChecks[i].pLookup));
2696 18 : m_pDoc->SetString(4, i, 0, OUString::createFromAscii(aChecks[i].pFormula));
2697 : }
2698 1 : m_pDoc->CalcAll();
2699 1 : printRange(m_pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
2700 :
2701 : // Verify results.
2702 19 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2703 : {
2704 18 : if (i == 0)
2705 : // Skip the header row.
2706 1 : continue;
2707 :
2708 17 : OUString aRes = m_pDoc->GetString(4, i, 0);
2709 17 : bool bGood = aRes.equalsAscii(aChecks[i].pRes);
2710 17 : if (!bGood)
2711 : {
2712 0 : cerr << "row " << (i+1) << ": lookup value='" << aChecks[i].pLookup
2713 0 : << "' expected='" << aChecks[i].pRes << "' actual='" << aRes << "'" << endl;
2714 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
2715 : }
2716 17 : }
2717 :
2718 : // Clear the sheet and start over.
2719 1 : clearSheet(m_pDoc, 0);
2720 :
2721 : // Lookup on sorted data intersparsed with empty cells.
2722 :
2723 : // A1:B8 is the search range.
2724 1 : m_pDoc->SetValue(ScAddress(0,2,0), 1.0);
2725 1 : m_pDoc->SetValue(ScAddress(0,4,0), 2.0);
2726 1 : m_pDoc->SetValue(ScAddress(0,7,0), 4.0);
2727 1 : m_pDoc->SetString(ScAddress(1,2,0), "One");
2728 1 : m_pDoc->SetString(ScAddress(1,4,0), "Two");
2729 1 : m_pDoc->SetString(ScAddress(1,7,0), "Four");
2730 :
2731 : // D1:D5 contain match values.
2732 1 : m_pDoc->SetValue(ScAddress(3,0,0), 1.0);
2733 1 : m_pDoc->SetValue(ScAddress(3,1,0), 2.0);
2734 1 : m_pDoc->SetValue(ScAddress(3,2,0), 3.0);
2735 1 : m_pDoc->SetValue(ScAddress(3,3,0), 4.0);
2736 1 : m_pDoc->SetValue(ScAddress(3,4,0), 5.0);
2737 :
2738 : // E1:E5 contain formulas.
2739 1 : m_pDoc->SetString(ScAddress(4,0,0), "=VLOOKUP(D1;$A$1:$B$8;2)");
2740 1 : m_pDoc->SetString(ScAddress(4,1,0), "=VLOOKUP(D2;$A$1:$B$8;2)");
2741 1 : m_pDoc->SetString(ScAddress(4,2,0), "=VLOOKUP(D3;$A$1:$B$8;2)");
2742 1 : m_pDoc->SetString(ScAddress(4,3,0), "=VLOOKUP(D4;$A$1:$B$8;2)");
2743 1 : m_pDoc->SetString(ScAddress(4,4,0), "=VLOOKUP(D5;$A$1:$B$8;2)");
2744 1 : m_pDoc->CalcAll();
2745 :
2746 : // Check the formula results in E1:E5.
2747 1 : CPPUNIT_ASSERT_EQUAL(OUString("One"), m_pDoc->GetString(ScAddress(4,0,0)));
2748 1 : CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4,1,0)));
2749 1 : CPPUNIT_ASSERT_EQUAL(OUString("Two"), m_pDoc->GetString(ScAddress(4,2,0)));
2750 1 : CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4,3,0)));
2751 1 : CPPUNIT_ASSERT_EQUAL(OUString("Four"), m_pDoc->GetString(ScAddress(4,4,0)));
2752 :
2753 : // Start over again.
2754 1 : clearSheet(m_pDoc, 0);
2755 :
2756 : // Set A,B,....,G to A1:A7.
2757 1 : m_pDoc->SetString(ScAddress(0,0,0), "A");
2758 1 : m_pDoc->SetString(ScAddress(0,1,0), "B");
2759 1 : m_pDoc->SetString(ScAddress(0,2,0), "C");
2760 1 : m_pDoc->SetString(ScAddress(0,3,0), "D");
2761 1 : m_pDoc->SetString(ScAddress(0,4,0), "E");
2762 1 : m_pDoc->SetString(ScAddress(0,5,0), "F");
2763 1 : m_pDoc->SetString(ScAddress(0,6,0), "G");
2764 :
2765 : // Set the formula in C1.
2766 1 : m_pDoc->SetString(ScAddress(2,0,0), "=VLOOKUP(\"C\";A1:A16;1)");
2767 1 : CPPUNIT_ASSERT_EQUAL(OUString("C"), m_pDoc->GetString(ScAddress(2,0,0)));
2768 :
2769 1 : m_pDoc->DeleteTab(0);
2770 1 : }
2771 :
2772 : struct NumStrCheck {
2773 : double fVal;
2774 : const char* pRes;
2775 : };
2776 :
2777 : struct StrStrCheck {
2778 : const char* pVal;
2779 : const char* pRes;
2780 : };
2781 :
2782 : template<size_t _DataSize, size_t _FormulaSize, int _Type>
2783 2 : void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
2784 : {
2785 2 : size_t nDataSize = _DataSize;
2786 26 : for (size_t i = 0; i < nDataSize; ++i)
2787 24 : pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i]));
2788 :
2789 33 : for (size_t i = 0; i < _FormulaSize; ++i)
2790 : {
2791 31 : pDoc->SetString(1, i, 0, OUString::createFromAscii(aChecks[i].pVal));
2792 :
2793 31 : OUStringBuffer aBuf;
2794 31 : aBuf.appendAscii("=MATCH(B");
2795 31 : aBuf.append(static_cast<sal_Int32>(i+1));
2796 31 : aBuf.appendAscii(";A1:A");
2797 31 : aBuf.append(static_cast<sal_Int32>(nDataSize));
2798 31 : aBuf.appendAscii(";");
2799 31 : aBuf.append(static_cast<sal_Int32>(_Type));
2800 31 : aBuf.appendAscii(")");
2801 62 : OUString aFormula = aBuf.makeStringAndClear();
2802 31 : pDoc->SetString(2, i, 0, aFormula);
2803 : }
2804 :
2805 2 : pDoc->CalcAll();
2806 2 : Test::printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
2807 :
2808 : // verify the results.
2809 33 : for (size_t i = 0; i < _FormulaSize; ++i)
2810 : {
2811 31 : OUString aStr = pDoc->GetString(2, i, 0);
2812 31 : if (!aStr.equalsAscii(aChecks[i].pRes))
2813 : {
2814 0 : cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
2815 0 : << " criterion='" << aChecks[i].pVal << "'" << endl;
2816 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
2817 : }
2818 : }
2819 2 : }
2820 :
2821 : template<size_t _DataSize, size_t _FormulaSize, int _Type>
2822 2 : void runTestHorizontalMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
2823 : {
2824 2 : size_t nDataSize = _DataSize;
2825 26 : for (size_t i = 0; i < nDataSize; ++i)
2826 24 : pDoc->SetString(i, 0, 0, OUString::createFromAscii(aData[i]));
2827 :
2828 33 : for (size_t i = 0; i < _FormulaSize; ++i)
2829 : {
2830 31 : pDoc->SetString(i, 1, 0, OUString::createFromAscii(aChecks[i].pVal));
2831 :
2832 : // Assume we don't have more than 26 data columns..
2833 31 : OUStringBuffer aBuf;
2834 31 : aBuf.appendAscii("=MATCH(");
2835 31 : aBuf.append(static_cast<sal_Unicode>('A'+i));
2836 31 : aBuf.appendAscii("2;A1:");
2837 31 : aBuf.append(static_cast<sal_Unicode>('A'+nDataSize));
2838 31 : aBuf.appendAscii("1;");
2839 31 : aBuf.append(static_cast<sal_Int32>(_Type));
2840 31 : aBuf.appendAscii(")");
2841 62 : OUString aFormula = aBuf.makeStringAndClear();
2842 31 : pDoc->SetString(i, 2, 0, aFormula);
2843 : }
2844 :
2845 2 : pDoc->CalcAll();
2846 2 : Test::printRange(pDoc, ScRange(0, 0, 0, _FormulaSize-1, 2, 0), "MATCH");
2847 :
2848 : // verify the results.
2849 33 : for (size_t i = 0; i < _FormulaSize; ++i)
2850 : {
2851 31 : OUString aStr = pDoc->GetString(i, 2, 0);
2852 31 : if (!aStr.equalsAscii(aChecks[i].pRes))
2853 : {
2854 0 : cerr << "column " << char('A'+i) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
2855 0 : << " criterion='" << aChecks[i].pVal << "'" << endl;
2856 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for horizontal MATCH", false);
2857 : }
2858 : }
2859 2 : }
2860 :
2861 1 : void Test::testFuncMATCH()
2862 : {
2863 1 : OUString aTabName("foo");
2864 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2865 1 : m_pDoc->InsertTab (0, aTabName));
2866 :
2867 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 40, 0));
2868 : {
2869 : // Ascending in-exact match
2870 :
2871 : // data range (A1:A9)
2872 : const char* aData[] = {
2873 : "1",
2874 : "2",
2875 : "3",
2876 : "4",
2877 : "5",
2878 : "6",
2879 : "7",
2880 : "8",
2881 : "9",
2882 : "B",
2883 : "B",
2884 : "C",
2885 1 : };
2886 :
2887 : // formula (B1:C12)
2888 : StrStrCheck aChecks[] = {
2889 : { "0.8", "#N/A" },
2890 : { "1.2", "1" },
2891 : { "2.3", "2" },
2892 : { "3.9", "3" },
2893 : { "4.1", "4" },
2894 : { "5.99", "5" },
2895 : { "6.1", "6" },
2896 : { "7.2", "7" },
2897 : { "8.569", "8" },
2898 : { "9.59", "9" },
2899 : { "10", "9" },
2900 : { "100", "9" },
2901 : { "Andy", "#N/A" },
2902 : { "Bruce", "11" },
2903 : { "Charlie", "12" }
2904 1 : };
2905 :
2906 1 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(m_pDoc, aData, aChecks);
2907 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 4, 40, 0));
2908 1 : runTestHorizontalMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(m_pDoc, aData, aChecks);
2909 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
2910 : }
2911 :
2912 : {
2913 : // Descending in-exact match
2914 :
2915 : // data range (A1:A9)
2916 : const char* aData[] = {
2917 : "D",
2918 : "C",
2919 : "B",
2920 : "9",
2921 : "8",
2922 : "7",
2923 : "6",
2924 : "5",
2925 : "4",
2926 : "3",
2927 : "2",
2928 : "1"
2929 1 : };
2930 :
2931 : // formula (B1:C12)
2932 : StrStrCheck aChecks[] = {
2933 : { "10", "#N/A" },
2934 : { "8.9", "4" },
2935 : { "7.8", "5" },
2936 : { "6.7", "6" },
2937 : { "5.5", "7" },
2938 : { "4.6", "8" },
2939 : { "3.3", "9" },
2940 : { "2.2", "10" },
2941 : { "1.1", "11" },
2942 : { "0.8", "12" },
2943 : { "0", "12" },
2944 : { "-2", "12" },
2945 : { "Andy", "3" },
2946 : { "Bruce", "2" },
2947 : { "Charlie", "1" },
2948 : { "David", "#N/A" }
2949 1 : };
2950 :
2951 1 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(m_pDoc, aData, aChecks);
2952 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 4, 40, 0));
2953 1 : runTestHorizontalMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(m_pDoc, aData, aChecks);
2954 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 40, 4, 0));
2955 : }
2956 :
2957 : {
2958 : // search range contains leading and trailing empty cell ranges.
2959 :
2960 1 : clearRange(m_pDoc, ScRange(0,0,0,2,100,0));
2961 :
2962 : // A5:A8 contains sorted values.
2963 1 : m_pDoc->SetValue(ScAddress(0,4,0), 1.0);
2964 1 : m_pDoc->SetValue(ScAddress(0,5,0), 2.0);
2965 1 : m_pDoc->SetValue(ScAddress(0,6,0), 3.0);
2966 1 : m_pDoc->SetValue(ScAddress(0,7,0), 4.0);
2967 :
2968 : // Find value 2 which is in A6.
2969 1 : m_pDoc->SetString(ScAddress(1,0,0), "=MATCH(2;A1:A20)");
2970 1 : m_pDoc->CalcAll();
2971 :
2972 1 : CPPUNIT_ASSERT_EQUAL(OUString("6"), m_pDoc->GetString(ScAddress(1,0,0)));
2973 : }
2974 :
2975 1 : m_pDoc->DeleteTab(0);
2976 1 : }
2977 :
2978 1 : void Test::testFuncCELL()
2979 : {
2980 1 : OUString aTabName("foo");
2981 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
2982 1 : m_pDoc->InsertTab (0, aTabName));
2983 :
2984 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
2985 :
2986 : {
2987 1 : const char* pContent = "Some random text";
2988 1 : m_pDoc->SetString(2, 9, 0, OUString::createFromAscii(pContent)); // Set this value to C10.
2989 1 : double val = 1.2;
2990 1 : m_pDoc->SetValue(2, 0, 0, val); // Set numeric value to C1;
2991 :
2992 : // We don't test: FILENAME, FORMAT, WIDTH, PROTECT, PREFIX
2993 : StrStrCheck aChecks[] = {
2994 : { "=CELL(\"COL\";C10)", "3" },
2995 : { "=CELL(\"ROW\";C10)", "10" },
2996 : { "=CELL(\"SHEET\";C10)", "1" },
2997 : { "=CELL(\"ADDRESS\";C10)", "$C$10" },
2998 : { "=CELL(\"CONTENTS\";C10)", pContent },
2999 : { "=CELL(\"COLOR\";C10)", "0" },
3000 : { "=CELL(\"TYPE\";C9)", "b" },
3001 : { "=CELL(\"TYPE\";C10)", "l" },
3002 : { "=CELL(\"TYPE\";C1)", "v" },
3003 : { "=CELL(\"PARENTHESES\";C10)", "0" }
3004 1 : };
3005 :
3006 11 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3007 10 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aChecks[i].pVal));
3008 1 : m_pDoc->CalcAll();
3009 :
3010 11 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3011 : {
3012 10 : OUString aVal = m_pDoc->GetString(0, i, 0);
3013 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
3014 10 : }
3015 : }
3016 :
3017 1 : m_pDoc->DeleteTab(0);
3018 1 : }
3019 :
3020 : /** See also test case document fdo#44456 sheet cpearson */
3021 1 : void Test::testFuncDATEDIF()
3022 : {
3023 1 : OUString aTabName("foo");
3024 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3025 1 : m_pDoc->InsertTab (0, aTabName));
3026 :
3027 : const char* aData[][5] = {
3028 : { "2007-01-01", "2007-01-10", "d", "9", "=DATEDIF(A1;B1;C1)" } ,
3029 : { "2007-01-01", "2007-01-31", "m", "0", "=DATEDIF(A2;B2;C2)" } ,
3030 : { "2007-01-01", "2007-02-01", "m", "1", "=DATEDIF(A3;B3;C3)" } ,
3031 : { "2007-01-01", "2007-02-28", "m", "1", "=DATEDIF(A4;B4;C4)" } ,
3032 : { "2007-01-01", "2007-12-31", "d", "364", "=DATEDIF(A5;B5;C5)" } ,
3033 : { "2007-01-01", "2007-01-31", "y", "0", "=DATEDIF(A6;B6;C6)" } ,
3034 : { "2007-01-01", "2008-07-01", "d", "547", "=DATEDIF(A7;B7;C7)" } ,
3035 : { "2007-01-01", "2008-07-01", "m", "18", "=DATEDIF(A8;B8;C8)" } ,
3036 : { "2007-01-01", "2008-07-01", "ym", "6", "=DATEDIF(A9;B9;C9)" } ,
3037 : { "2007-01-01", "2008-07-01", "yd", "182", "=DATEDIF(A10;B10;C10)" } ,
3038 : { "2008-01-01", "2009-07-01", "yd", "181", "=DATEDIF(A11;B11;C11)" } ,
3039 : { "2007-01-01", "2007-01-31", "md", "30", "=DATEDIF(A12;B12;C12)" } ,
3040 : { "2007-02-01", "2009-03-01", "md", "0", "=DATEDIF(A13;B13;C13)" } ,
3041 : { "2008-02-01", "2009-03-01", "md", "0", "=DATEDIF(A14;B14;C14)" } ,
3042 : { "2007-01-02", "2007-01-01", "md", "Err:502", "=DATEDIF(A15;B15;C15)" } // fail date1 > date2
3043 1 : };
3044 :
3045 1 : clearRange( m_pDoc, ScRange(0, 0, 0, 4, SAL_N_ELEMENTS(aData), 0));
3046 1 : ScAddress aPos(0,0,0);
3047 1 : ScRange aDataRange = insertRangeData( m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3048 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3049 :
3050 1 : m_pDoc->CalcAll();
3051 :
3052 16 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData); ++i)
3053 : {
3054 15 : OUString aVal = m_pDoc->GetString( 4, i, 0);
3055 : //std::cout << "row "<< i << ": " << OUStringToOString( aVal, RTL_TEXTENCODING_UTF8).getStr() << ", expected " << aData[i][3] << std::endl;
3056 15 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal.equalsAscii( aData[i][3]));
3057 15 : }
3058 :
3059 1 : m_pDoc->DeleteTab(0);
3060 1 : }
3061 :
3062 1 : void Test::testFuncINDIRECT()
3063 : {
3064 1 : OUString aTabName("foo");
3065 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
3066 1 : m_pDoc->InsertTab (0, aTabName));
3067 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
3068 :
3069 1 : bool bGood = m_pDoc->GetName(0, aTabName);
3070 1 : CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
3071 :
3072 2 : OUString aTest = "Test", aRefErr = "#REF!";
3073 1 : m_pDoc->SetString(0, 10, 0, aTest);
3074 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", m_pDoc->GetString(0,10,0) == aTest);
3075 :
3076 2 : OUString aPrefix = "=INDIRECT(\"";
3077 :
3078 2 : OUString aFormula = aPrefix + aTabName + ".A11\")"; // Calc A1
3079 1 : m_pDoc->SetString(0, 0, 0, aFormula);
3080 1 : aFormula = aPrefix + aTabName + "!A11\")"; // Excel A1
3081 1 : m_pDoc->SetString(0, 1, 0, aFormula);
3082 1 : aFormula = aPrefix + aTabName + "!R11C1\")"; // Excel R1C1
3083 1 : m_pDoc->SetString(0, 2, 0, aFormula);
3084 1 : aFormula = aPrefix + aTabName + "!R11C1\";0)"; // Excel R1C1 (forced)
3085 1 : m_pDoc->SetString(0, 3, 0, aFormula);
3086 :
3087 1 : m_pDoc->CalcAll();
3088 : {
3089 : // Default is to use the current formula syntax, which is Calc A1.
3090 : const OUString* aChecks[] = {
3091 : &aTest, &aRefErr, &aRefErr, &aTest
3092 1 : };
3093 :
3094 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3095 : {
3096 4 : OUString aVal = m_pDoc->GetString(0, i, 0);
3097 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value!", *aChecks[i], aVal);
3098 4 : }
3099 : }
3100 :
3101 2 : ScCalcConfig aConfig;
3102 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
3103 1 : m_pDoc->SetCalcConfig(aConfig);
3104 1 : m_pDoc->CalcAll();
3105 : {
3106 : // Explicit Calc A1 syntax
3107 : const OUString* aChecks[] = {
3108 : &aTest, &aRefErr, &aRefErr, &aTest
3109 1 : };
3110 :
3111 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3112 : {
3113 4 : OUString aVal = m_pDoc->GetString(0, i, 0);
3114 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
3115 4 : }
3116 : }
3117 :
3118 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
3119 1 : m_pDoc->SetCalcConfig(aConfig);
3120 1 : m_pDoc->CalcAll();
3121 : {
3122 : // Excel A1 syntax
3123 : const OUString* aChecks[] = {
3124 : &aRefErr, &aTest, &aRefErr, &aTest
3125 1 : };
3126 :
3127 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3128 : {
3129 4 : OUString aVal = m_pDoc->GetString(0, i, 0);
3130 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
3131 4 : }
3132 : }
3133 :
3134 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
3135 1 : m_pDoc->SetCalcConfig(aConfig);
3136 1 : m_pDoc->CalcAll();
3137 : {
3138 : // Excel R1C1 syntax
3139 : const OUString* aChecks[] = {
3140 : &aRefErr, &aRefErr, &aTest, &aTest
3141 1 : };
3142 :
3143 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3144 : {
3145 4 : OUString aVal = m_pDoc->GetString(0, i, 0);
3146 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
3147 4 : }
3148 : }
3149 :
3150 2 : m_pDoc->DeleteTab(0);
3151 1 : }
3152 :
3153 1 : void Test::testExternalRef()
3154 : {
3155 1 : ScDocShellRef xExtDocSh = new ScDocShell;
3156 2 : OUString aExtDocName("file:///extdata.fake");
3157 2 : OUString aExtSh1Name("Data1");
3158 2 : OUString aExtSh2Name("Data2");
3159 2 : OUString aExtSh3Name("Data3");
3160 1 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3161 1 : xExtDocSh->DoInitNew(pMed);
3162 2 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3163 1 : findLoadedDocShellByName(aExtDocName) != NULL);
3164 :
3165 : // Populate the external source document.
3166 1 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3167 1 : pExtDoc->InsertTab(0, aExtSh1Name);
3168 1 : pExtDoc->InsertTab(1, aExtSh2Name);
3169 1 : pExtDoc->InsertTab(2, aExtSh3Name);
3170 :
3171 2 : OUString name("Name");
3172 2 : OUString value("Value");
3173 2 : OUString andy("Andy");
3174 2 : OUString bruce("Bruce");
3175 2 : OUString charlie("Charlie");
3176 2 : OUString david("David");
3177 2 : OUString edward("Edward");
3178 2 : OUString frank("Frank");
3179 2 : OUString george("George");
3180 2 : OUString henry("Henry");
3181 :
3182 : // Sheet 1
3183 1 : pExtDoc->SetString(0, 0, 0, name);
3184 1 : pExtDoc->SetString(0, 1, 0, andy);
3185 1 : pExtDoc->SetString(0, 2, 0, bruce);
3186 1 : pExtDoc->SetString(0, 3, 0, charlie);
3187 1 : pExtDoc->SetString(0, 4, 0, david);
3188 1 : pExtDoc->SetString(1, 0, 0, value);
3189 1 : double val = 10;
3190 1 : pExtDoc->SetValue(1, 1, 0, val);
3191 1 : val = 11;
3192 1 : pExtDoc->SetValue(1, 2, 0, val);
3193 1 : val = 12;
3194 1 : pExtDoc->SetValue(1, 3, 0, val);
3195 1 : val = 13;
3196 1 : pExtDoc->SetValue(1, 4, 0, val);
3197 :
3198 : // Sheet 2 remains empty.
3199 :
3200 : // Sheet 3
3201 1 : pExtDoc->SetString(0, 0, 2, name);
3202 1 : pExtDoc->SetString(0, 1, 2, edward);
3203 1 : pExtDoc->SetString(0, 2, 2, frank);
3204 1 : pExtDoc->SetString(0, 3, 2, george);
3205 1 : pExtDoc->SetString(0, 4, 2, henry);
3206 1 : pExtDoc->SetString(1, 0, 2, value);
3207 1 : val = 99;
3208 1 : pExtDoc->SetValue(1, 1, 2, val);
3209 1 : val = 98;
3210 1 : pExtDoc->SetValue(1, 2, 2, val);
3211 1 : val = 97;
3212 1 : pExtDoc->SetValue(1, 3, 2, val);
3213 1 : val = 96;
3214 1 : pExtDoc->SetValue(1, 4, 2, val);
3215 :
3216 : // Test external refernces on the main document while the external
3217 : // document is still in memory.
3218 1 : m_pDoc->InsertTab(0, OUString("Test Sheet"));
3219 1 : m_pDoc->SetString(0, 0, 0, OUString("='file:///extdata.fake'#Data1.A1"));
3220 2 : OUString test = m_pDoc->GetString(0, 0, 0);
3221 1 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(name));
3222 :
3223 : // After the initial access to the external document, the external ref
3224 : // manager should create sheet cache entries for *all* sheets from that
3225 : // document. Note that the doc may have more than 3 sheets but ensure
3226 : // that the first 3 are what we expect.
3227 1 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3228 1 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3229 2 : vector<OUString> aTabNames;
3230 1 : pRefMgr->getAllCachedTableNames(nFileId, aTabNames);
3231 1 : CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
3232 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[0].equals(aExtSh1Name));
3233 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[1].equals(aExtSh2Name));
3234 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[2].equals(aExtSh3Name));
3235 :
3236 1 : m_pDoc->SetString(1, 0, 0, OUString("='file:///extdata.fake'#Data1.B1"));
3237 1 : test = m_pDoc->GetString(1, 0, 0);
3238 1 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(value));
3239 :
3240 1 : m_pDoc->SetString(0, 1, 0, OUString("='file:///extdata.fake'#Data1.A2"));
3241 1 : m_pDoc->SetString(0, 2, 0, OUString("='file:///extdata.fake'#Data1.A3"));
3242 1 : m_pDoc->SetString(0, 3, 0, OUString("='file:///extdata.fake'#Data1.A4"));
3243 1 : m_pDoc->SetString(0, 4, 0, OUString("='file:///extdata.fake'#Data1.A5"));
3244 1 : m_pDoc->SetString(0, 5, 0, OUString("='file:///extdata.fake'#Data1.A6"));
3245 :
3246 : {
3247 : // Referencing an empty cell should display '0'.
3248 1 : const char* pChecks[] = { "Andy", "Bruce", "Charlie", "David", "0" };
3249 6 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3250 : {
3251 5 : test = m_pDoc->GetString(0, static_cast<SCROW>(i+1), 0);
3252 5 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3253 : }
3254 : }
3255 1 : m_pDoc->SetString(1, 1, 0, OUString("='file:///extdata.fake'#Data1.B2"));
3256 1 : m_pDoc->SetString(1, 2, 0, OUString("='file:///extdata.fake'#Data1.B3"));
3257 1 : m_pDoc->SetString(1, 3, 0, OUString("='file:///extdata.fake'#Data1.B4"));
3258 1 : m_pDoc->SetString(1, 4, 0, OUString("='file:///extdata.fake'#Data1.B5"));
3259 1 : m_pDoc->SetString(1, 5, 0, OUString("='file:///extdata.fake'#Data1.B6"));
3260 : {
3261 1 : double pChecks[] = { 10, 11, 12, 13, 0 };
3262 6 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3263 : {
3264 5 : m_pDoc->GetValue(1, static_cast<SCROW>(i+1), 0, val);
3265 5 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", val == pChecks[i]);
3266 : }
3267 : }
3268 :
3269 1 : m_pDoc->SetString(2, 0, 0, OUString("='file:///extdata.fake'#Data3.A1"));
3270 1 : m_pDoc->SetString(2, 1, 0, OUString("='file:///extdata.fake'#Data3.A2"));
3271 1 : m_pDoc->SetString(2, 2, 0, OUString("='file:///extdata.fake'#Data3.A3"));
3272 1 : m_pDoc->SetString(2, 3, 0, OUString("='file:///extdata.fake'#Data3.A4"));
3273 : {
3274 1 : const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
3275 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3276 : {
3277 4 : test = m_pDoc->GetString(2, static_cast<SCROW>(i), 0);
3278 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3279 : }
3280 : }
3281 :
3282 1 : m_pDoc->SetString(3, 0, 0, OUString("='file:///extdata.fake'#Data3.B1"));
3283 1 : m_pDoc->SetString(3, 1, 0, OUString("='file:///extdata.fake'#Data3.B2"));
3284 1 : m_pDoc->SetString(3, 2, 0, OUString("='file:///extdata.fake'#Data3.B3"));
3285 1 : m_pDoc->SetString(3, 3, 0, OUString("='file:///extdata.fake'#Data3.B4"));
3286 : {
3287 1 : const char* pChecks[] = { "Value", "99", "98", "97" };
3288 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3289 : {
3290 4 : test = m_pDoc->GetString(3, static_cast<SCROW>(i), 0);
3291 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3292 : }
3293 : }
3294 :
3295 : // At this point, all accessed cell data from the external document should
3296 : // have been cached.
3297 : ScExternalRefCache::TableTypeRef pCacheTab = pRefMgr->getCacheTable(
3298 2 : nFileId, aExtSh1Name, false);
3299 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab.get() != NULL);
3300 1 : ScRange aCachedRange = getCachedRange(pCacheTab);
3301 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3302 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3303 1 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 4);
3304 :
3305 : // Sheet2 is not referenced at all; the cache table shouldn't even exist.
3306 1 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh2Name, false);
3307 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 2 should *not* exist.", pCacheTab.get() == NULL);
3308 :
3309 : // Sheet3's row 5 is not referenced; it should not be cached.
3310 1 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh3Name, false);
3311 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab.get() != NULL);
3312 1 : aCachedRange = getCachedRange(pCacheTab);
3313 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3314 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3315 1 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 3);
3316 :
3317 : // Unload the external document shell.
3318 1 : xExtDocSh->DoClose();
3319 2 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3320 1 : findLoadedDocShellByName(aExtDocName) == NULL);
3321 :
3322 2 : m_pDoc->DeleteTab(0);
3323 1 : }
3324 :
3325 1 : void testExtRefFuncT(ScDocument* pDoc, ScDocument* pExtDoc)
3326 : {
3327 1 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
3328 1 : Test::clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
3329 :
3330 1 : pExtDoc->SetString(0, 0, 0, OUString("'1.2"));
3331 1 : pExtDoc->SetString(0, 1, 0, OUString("Foo"));
3332 1 : pExtDoc->SetValue(0, 2, 0, 12.3);
3333 1 : pDoc->SetString(0, 0, 0, OUString("=T('file:///extdata.fake'#Data.A1)"));
3334 1 : pDoc->SetString(0, 1, 0, OUString("=T('file:///extdata.fake'#Data.A2)"));
3335 1 : pDoc->SetString(0, 2, 0, OUString("=T('file:///extdata.fake'#Data.A3)"));
3336 1 : pDoc->CalcAll();
3337 :
3338 1 : OUString aRes = pDoc->GetString(0, 0, 0);
3339 1 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "1.2" );
3340 1 : aRes = pDoc->GetString(0, 1, 0);
3341 1 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "Foo" );
3342 1 : aRes = pDoc->GetString(0, 2, 0);
3343 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
3344 1 : }
3345 :
3346 1 : void testExtRefFuncOFFSET(ScDocument* pDoc, ScDocument* pExtDoc)
3347 : {
3348 1 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
3349 1 : Test::clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
3350 :
3351 1 : sc::AutoCalcSwitch aACSwitch(*pDoc, true);
3352 :
3353 : // External document has sheet named 'Data', and the internal doc has sheet named 'Test'.
3354 1 : pExtDoc->SetValue(ScAddress(0,1,0), 1.2); // Set 1.2 to A2.
3355 1 : pDoc->SetString(ScAddress(0,0,0), "=OFFSET('file:///extdata.fake'#Data.$A$1;1;0;1;1)");
3356 1 : CPPUNIT_ASSERT_EQUAL(1.2, pDoc->GetValue(ScAddress(0,0,0)));
3357 1 : }
3358 :
3359 1 : void testExtRefFuncVLOOKUP(ScDocument* pDoc, ScDocument* pExtDoc)
3360 : {
3361 1 : Test::clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
3362 1 : Test::clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
3363 :
3364 : // Populate the external document.
3365 1 : pExtDoc->SetString(ScAddress(0,0,0), "A1");
3366 1 : pExtDoc->SetString(ScAddress(0,1,0), "A2");
3367 1 : pExtDoc->SetString(ScAddress(0,2,0), "A3");
3368 1 : pExtDoc->SetString(ScAddress(0,3,0), "A4");
3369 1 : pExtDoc->SetString(ScAddress(0,4,0), "A5");
3370 :
3371 1 : pExtDoc->SetString(ScAddress(1,0,0), "B1");
3372 1 : pExtDoc->SetString(ScAddress(1,1,0), "B2");
3373 1 : pExtDoc->SetString(ScAddress(1,2,0), "B3");
3374 1 : pExtDoc->SetString(ScAddress(1,3,0), "B4");
3375 1 : pExtDoc->SetString(ScAddress(1,4,0), "B5");
3376 :
3377 : // Put formula in the source document.
3378 :
3379 1 : pDoc->SetString(ScAddress(0,0,0), "A2");
3380 :
3381 : // Sort order TRUE
3382 1 : pDoc->SetString(ScAddress(1,0,0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;1)");
3383 1 : CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1,0,0)));
3384 :
3385 : // Sort order FALSE. It should return the same result.
3386 1 : pDoc->SetString(ScAddress(1,0,0), "=VLOOKUP(A1;'file:///extdata.fake'#Data.A1:B5;2;0)");
3387 1 : CPPUNIT_ASSERT_EQUAL(OUString("B2"), pDoc->GetString(ScAddress(1,0,0)));
3388 1 : }
3389 :
3390 1 : void Test::testExternalRefFunctions()
3391 : {
3392 1 : ScDocShellRef xExtDocSh = new ScDocShell;
3393 2 : OUString aExtDocName("file:///extdata.fake");
3394 1 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3395 1 : xExtDocSh->DoInitNew(pMed);
3396 2 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3397 1 : findLoadedDocShellByName(aExtDocName) != NULL);
3398 :
3399 1 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3400 1 : CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
3401 1 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3402 1 : const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
3403 2 : CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.",
3404 1 : pFileName && pFileName->equals(aExtDocName));
3405 :
3406 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
3407 :
3408 : // Populate the external source document.
3409 1 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3410 1 : pExtDoc->InsertTab(0, OUString("Data"));
3411 1 : double val = 1;
3412 1 : pExtDoc->SetValue(0, 0, 0, val);
3413 : // leave cell B1 empty.
3414 1 : val = 2;
3415 1 : pExtDoc->SetValue(0, 1, 0, val);
3416 1 : pExtDoc->SetValue(1, 1, 0, val);
3417 1 : val = 3;
3418 1 : pExtDoc->SetValue(0, 2, 0, val);
3419 1 : pExtDoc->SetValue(1, 2, 0, val);
3420 1 : val = 4;
3421 1 : pExtDoc->SetValue(0, 3, 0, val);
3422 1 : pExtDoc->SetValue(1, 3, 0, val);
3423 :
3424 1 : m_pDoc->InsertTab(0, OUString("Test"));
3425 :
3426 : struct {
3427 : const char* pFormula; double fResult;
3428 : } aChecks[] = {
3429 : { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
3430 : { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
3431 : { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
3432 : { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
3433 : { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
3434 : { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 }
3435 1 : };
3436 :
3437 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3438 : {
3439 6 : m_pDoc->SetString(0, 0, 0, OUString::createFromAscii(aChecks[i].pFormula));
3440 6 : m_pDoc->GetValue(0, 0, 0, val);
3441 6 : CPPUNIT_ASSERT_MESSAGE("unexpected result involving external ranges.", val == aChecks[i].fResult);
3442 : }
3443 :
3444 1 : pRefMgr->clearCache(nFileId);
3445 1 : testExtRefFuncT(m_pDoc, pExtDoc);
3446 1 : testExtRefFuncOFFSET(m_pDoc, pExtDoc);
3447 1 : testExtRefFuncVLOOKUP(m_pDoc, pExtDoc);
3448 :
3449 : // Unload the external document shell.
3450 1 : xExtDocSh->DoClose();
3451 2 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3452 1 : findLoadedDocShellByName(aExtDocName) == NULL);
3453 :
3454 2 : m_pDoc->DeleteTab(0);
3455 4 : }
3456 :
3457 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
