File indexing completed on 2024-05-12 16:35:56
0001 /* This file is part of the KDE project 0002 Copyright 2007 Brad Hards <bradh@frogmouth.net> 0003 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com> 0004 0005 This library is free software; you can redistribute it and/or 0006 modify it under the terms of the GNU Library General Public 0007 License as published by the Free Software Foundation; only 0008 version 2 of the License. 0009 0010 This library is distributed in the hope that it will be useful, 0011 but WITHOUT ANY WARRANTY; without even the implied warranty of 0012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0013 Library General Public License for more details. 0014 0015 You should have received a copy of the GNU Library General Public License 0016 along with this library; see the file COPYING.LIB. If not, write to 0017 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0018 Boston, MA 02110-1301, USA. 0019 */ 0020 0021 #include "TestInformationFunctions.h" 0022 0023 #include <CellStorage.h> 0024 #include <Formula.h> 0025 #include <Map.h> 0026 #include <Sheet.h> 0027 #include <CalculationSettings.h> 0028 0029 #include "TestKspreadCommon.h" 0030 0031 #include <KLocale> 0032 0033 // because we may need to promote expected value from integer to float 0034 #define CHECK_EVAL(x,y) { Value z(y); QCOMPARE(evaluate(x,z),(z)); } 0035 0036 Value TestInformationFunctions::evaluate(const QString& formula, Value& ex, const Cell &cell) 0037 { 0038 Sheet* sheet = m_map->sheet(0); 0039 Formula f(sheet, cell); 0040 QString expr = formula; 0041 if (expr[0] != '=') 0042 expr.prepend('='); 0043 f.setExpression(expr); 0044 Value result = f.eval(); 0045 0046 if (result.isFloat() && ex.isInteger()) 0047 ex = Value(ex.asFloat()); 0048 if (result.isInteger() && ex.isFloat()) 0049 result = Value(result.asFloat()); 0050 0051 return result; 0052 } 0053 0054 void TestInformationFunctions::initTestCase() 0055 { 0056 FunctionModuleRegistry::instance()->loadFunctionModules(); 0057 m_map = new Map(0 /* no Doc */); 0058 // some tests are sensitive to locale, so use C for all tests 0059 *(m_map->calculationSettings()->locale()) = KLocale("C", "C"); 0060 0061 m_map->addNewSheet(); 0062 Sheet* sheet = m_map->sheet(0); 0063 sheet->setSheetName("Sheet1"); 0064 CellStorage* storage = sheet->cellStorage(); 0065 0066 // 0067 // Test case data set 0068 // 0069 0070 0071 // A19:A31 0072 storage->setValue(1,19, Value( 1 ) ); 0073 storage->setValue(1,20, Value( 2 ) ); 0074 storage->setValue(1,21, Value( 4 ) ); 0075 storage->setValue(1,22, Value( 8 ) ); 0076 storage->setValue(1,23, Value( 16 ) ); 0077 storage->setValue(1,24, Value( 32 ) ); 0078 storage->setValue(1,25, Value( 64 ) ); 0079 storage->setValue(1,26, Value( 128 ) ); 0080 storage->setValue(1,27, Value( 256 ) ); 0081 storage->setValue(1,28, Value( 512 ) ); 0082 storage->setValue(1,29, Value( 1024 ) ); 0083 storage->setValue(1,30, Value( 2048 ) ); 0084 storage->setValue(1,31, Value( 4096 ) ); 0085 0086 // B1:B2 0087 Formula formula(sheet); 0088 formula.setExpression("=SUM(A19:A31)"); 0089 storage->setFormula(2,1, formula); 0090 storage->setFormula(2,2, Formula::empty()); 0091 0092 // B3:B17 0093 storage->setValue(2, 3, Value("7")); 0094 storage->setValue(2, 4, Value(2)); 0095 storage->setValue(2, 5, Value(3)); 0096 storage->setValue(2, 6, Value(true)); 0097 storage->setValue(2, 7, Value("Hello")); 0098 // B8 leave empty 0099 storage->setValue(2, 9, Value::errorDIV0()); 0100 storage->setValue(2, 10, Value(0)); 0101 // storage->setValue(2,11, Value( 3 ) ); 0102 // storage->setValue(2,12, Value( 4 ) ); 0103 // storage->setValue(2,13, Value( "2005-0131T01:00:00" )); 0104 // storage->setValue(2,14, Value( 1 ) ); 0105 // storage->setValue(2,15, Value( 2 ) ); 0106 // storage->setValue(2,16, Value( 3 ) ); 0107 // storage->setValue(2,17, Value( 4 ) ); 0108 // 0109 // 0110 // // C4:C7 0111 storage->setValue(3, 4, Value(4)); 0112 storage->setValue(3, 5, Value(5)); 0113 // storage->setValue(3, 6, Value( 7 ) ); 0114 storage->setValue(3, 7, Value("2005-01-31")); 0115 0116 // C11:C17 0117 storage->setValue(3,11, Value( 5 ) ); 0118 storage->setValue(3,12, Value( 6 ) ); 0119 storage->setValue(3,13, Value( 8 ) ); 0120 storage->setValue(3,14, Value( 4 ) ); 0121 storage->setValue(3,15, Value( 3 ) ); 0122 storage->setValue(3,16, Value( 2 ) ); 0123 storage->setValue(3,17, Value( 1 ) ); 0124 0125 // // C19:C31 0126 // storage->setValue(3,19, Value( 0 ) ); 0127 // storage->setValue(3,20, Value( 5 ) ); 0128 // storage->setValue(3,21, Value( 2 ) ); 0129 // storage->setValue(3,22, Value( 5 ) ); 0130 // storage->setValue(3,23, Value( 3 ) ); 0131 // storage->setValue(3,24, Value( 4 ) ); 0132 // storage->setValue(3,25, Value( 4 ) ); 0133 // storage->setValue(3,26, Value( 0 ) ); 0134 // storage->setValue(3,27, Value( 8 ) ); 0135 // storage->setValue(3,28, Value( 1 ) ); 0136 // storage->setValue(3,29, Value( 9 ) ); 0137 // storage->setValue(3,30, Value( 6 ) ); 0138 // storage->setValue(3,31, Value( 2 ) ); 0139 // // C51:C57 0140 // storage->setValue(3,51, Value( 7 ) ); 0141 // storage->setValue(3,52, Value( 9 ) ); 0142 // storage->setValue(3,53, Value( 11 ) ); 0143 // storage->setValue(3,54, Value( 12 ) ); 0144 // storage->setValue(3,55, Value( 15 ) ); 0145 // storage->setValue(3,56, Value( 17 ) ); 0146 // storage->setValue(3,57, Value( 19 ) ); 0147 // 0148 // // D51:D57 0149 // storage->setValue(4,51, Value( 100 ) ); 0150 // storage->setValue(4,52, Value( 105 ) ); 0151 // storage->setValue(4,53, Value( 104 ) ); 0152 // storage->setValue(4,54, Value( 108 ) ); 0153 // storage->setValue(4,55, Value( 111 ) ); 0154 // storage->setValue(4,56, Value( 120 ) ); 0155 // storage->setValue(4,57, Value( 133 ) ); 0156 // 0157 // 0158 // // F51:F60 0159 // storage->setValue(6,51, Value( 3 ) ); 0160 // storage->setValue(6,52, Value( 4 ) ); 0161 // storage->setValue(6,53, Value( 5 ) ); 0162 // storage->setValue(6,54, Value( 2 ) ); 0163 // storage->setValue(6,55, Value( 3 ) ); 0164 // storage->setValue(6,56, Value( 4 ) ); 0165 // storage->setValue(6,57, Value( 5 ) ); 0166 // storage->setValue(6,58, Value( 6 ) ); 0167 // storage->setValue(6,59, Value( 4 ) ); 0168 // storage->setValue(6,60, Value( 7 ) ); 0169 // 0170 // 0171 // // G51:G60 0172 // storage->setValue(7,51, Value( 23 ) ); 0173 // storage->setValue(7,52, Value( 24 ) ); 0174 // storage->setValue(7,53, Value( 25 ) ); 0175 // storage->setValue(7,54, Value( 22 ) ); 0176 // storage->setValue(7,55, Value( 23 ) ); 0177 // storage->setValue(7,56, Value( 24 ) ); 0178 // storage->setValue(7,57, Value( 25 ) ); 0179 // storage->setValue(7,58, Value( 26 ) ); 0180 // storage->setValue(7,59, Value( 24 ) ); 0181 // storage->setValue(7,60, Value( 27 ) ); 0182 0183 // A1000:G1000 0184 storage->setValue(1, 1000, Value("abc")); 0185 storage->setValue(2, 1000, Value("def")); 0186 storage->setValue(3, 1000, Value("efoob")); 0187 storage->setValue(4, 1000, Value("flka")); 0188 storage->setValue(5, 1000, Value("kde")); 0189 storage->setValue(6, 1000, Value("kde")); 0190 storage->setValue(7, 1000, Value("xxx")); 0191 0192 // Z19:Z23 0193 storage->setValue(26,19, Value( 16 ) ); 0194 storage->setValue(26,20, Value( 8 ) ); 0195 storage->setValue(26,21, Value( 4 ) ); 0196 storage->setValue(26,22, Value( 2 ) ); 0197 storage->setValue(26,23, Value( 1 ) ); 0198 0199 // Add the second sheet 0200 m_map->addNewSheet(); 0201 sheet = m_map->sheet(1); 0202 sheet->setSheetName("Sheet2"); 0203 storage = sheet->cellStorage(); 0204 0205 // B1:B2 0206 Formula formula2(sheet); 0207 formula2.setExpression("=SUM(Sheet1!A19:Sheet1!A31)"); 0208 storage->setFormula(2,1, formula2); 0209 storage->setFormula(2,2, Formula::empty()); 0210 0211 // Add the third sheet 0212 m_map->addNewSheet(); 0213 sheet = m_map->sheet(2); 0214 sheet->setSheetName("Sheet3"); 0215 storage = sheet->cellStorage(); 0216 0217 // A1:A2 0218 storage->setValue(1,1, Value( 1.1 ) ); 0219 storage->setValue(1,2, Value( 2.2 ) ); 0220 } 0221 0222 // 0223 // unittests 0224 // 0225 0226 void TestInformationFunctions::testAREAS() 0227 { 0228 CHECK_EVAL("AREAS(B3)", Value(1)); // A reference to a single cell has one area 0229 CHECK_EVAL("AREAS(B3:C4)", Value(1)); // A reference to a single range has one area 0230 // concatenation is not supported yet 0231 // CHECK_EVAL( "AREAS(B3:C4~D5:D6)", Value( 2 ) ); // Cell concatenation creates multiple areas 0232 // CHECK_EVAL( "AREAS(B3:C4~B3)", Value( 2 ) ); // Cell concatenation counts, even if the cells are duplicated 0233 } 0234 0235 void TestInformationFunctions::testCELL() 0236 { 0237 CHECK_EVAL( "CELL(\"COL\";C7)", Value( 3 ) ); // Column C is column number 3. 0238 CHECK_EVAL( "CELL(\"COL\";Sheet2!C7)", Value( 3 ) ); 0239 0240 CHECK_EVAL( "CELL(\"ROW\";C7)", Value( 7 ) ); // Row 7 is row number 7. 0241 CHECK_EVAL( "CELL(\"ROW\";Sheet2!C7)", Value( 7 ) ); 0242 0243 CHECK_EVAL( "CELL(\"Sheet\";C7)", Value( 1 ) ); 0244 CHECK_EVAL( "CELL(\"Sheet\";Sheet2!C7)", Value( 2 ) ); 0245 CHECK_EVAL( "CELL(\"Sheet\";Sheet3!C7)", Value( 3 ) ); 0246 0247 CHECK_EVAL( "CELL(\"ADDRESS\";B7)", Value( "$B$7" ) ); 0248 CHECK_EVAL( "CELL(\"ADDRESS\";Sheet2!B7)", Value( "'Sheet2'!$B$7" ) ); 0249 0250 Value v1( "$B$7" ); 0251 Value r1 = evaluate("CELL(\"ADDRESS\")", v1, Cell(m_map->sheet(0), 2, 7)); 0252 QCOMPARE(r1, v1); 0253 0254 Value v2( "$B$7" ); 0255 Value r2 = evaluate("CELL(\"ADDRESS\")", v2, Cell(m_map->sheet(1), 2, 7)); 0256 QCOMPARE(r2, v2); 0257 0258 //CHECK_EVAL( "CELL(\"ADDRESS\";'x:\\sample.ods'#Sheet3!B7)", Value( "'file:///x:/sample.ods'#$Sheet3.$B$7" ) ); 0259 0260 m_map->calculationSettings()->setFileName("/home/sample.ods"); 0261 CHECK_EVAL( "CELL(\"FILENAME\")", Value( "/home/sample.ods" ) ); 0262 CHECK_EVAL( "CELL(\"FILENAME\";B7)", Value( "/home/sample.ods" ) ); 0263 } 0264 0265 void TestInformationFunctions::testCOLUMN() 0266 { 0267 CHECK_EVAL("COLUMN(B7)", Value(2)); // Column "B" is column number 2. 0268 // CHECK_EVAL( "COLUMN()", Value( 5 ) ); // Column of current cell is default, here formula in column E. 0269 0270 Value res(Value::Array); 0271 res.setElement(0, 0, Value(2)); 0272 res.setElement(1, 0, Value(3)); 0273 res.setElement(2, 0, Value(4)); 0274 CHECK_EVAL("COLUMN(B2:D2)", res); // Array with column numbers. 0275 } 0276 0277 void TestInformationFunctions::testCOLUMNS() 0278 { 0279 CHECK_EVAL("COLUMNS(C1)", Value(1)); // Single cell range contains one column. 0280 CHECK_EVAL("COLUMNS(C1:C4)", Value(1)); // Range with only one column. 0281 CHECK_EVAL("COLUMNS(A4:D100)", Value(4)); // Number of columns in range. 0282 } 0283 0284 void TestInformationFunctions::testCOUNT() 0285 { 0286 CHECK_EVAL("COUNT(1;2;3)", Value(3)); // Simple count. 0287 CHECK_EVAL("COUNT(B4:B5)", Value(2)); // Two numbers in the range. 0288 CHECK_EVAL("COUNT(B4:B5;B4:B5)", Value(4)); // Duplicates are not removed. 0289 CHECK_EVAL("COUNT(B4:B9)", Value(2)); // Errors in referenced cells or ranges are ignored. 0290 CHECK_EVAL("COUNT(B4:B8;1/0)", Value(2)); // Errors in direct parameters are still ignored.. 0291 CHECK_EVAL("COUNT(B3:B5)", Value(2)); // Conversion to NumberSequence ignores strings (in B3). 0292 } 0293 0294 void TestInformationFunctions::testCOUNTA() 0295 { 0296 CHECK_EVAL("COUNTA(\"1\";2;TRUE())", Value(3)); // Simple count of 3 constant values. 0297 CHECK_EVAL("COUNTA(B3:B5)", Value(3)); // Three non-empty cells in the range. 0298 CHECK_EVAL("COUNTA(B3:B5;B3:B5)", Value(6)); // Duplicates are not removed. 0299 CHECK_EVAL("COUNTA(B3:B9)", Value(6)); // Where B9 is "=1/0", i.e. an error, 0300 // counts the error as non-empty; errors contained 0301 // in a reference do not propagate the error into the result. 0302 CHECK_EVAL("COUNTA(\"1\";2;SUM(B3:B9))", Value(3)); // Errors in an evaluated formula do not propagate; they are just counted. 0303 CHECK_EVAL("COUNTA(\"1\";2;B3:B9)", Value(8)); // Errors in the range do not propagate either 0304 } 0305 0306 void TestInformationFunctions::testCOUNTBLANK() 0307 { 0308 CHECK_EVAL("COUNTBLANK(B3:B10)", Value(1)); // Only B8 is blank. Zero ('0') in B10 is not considered blank. 0309 } 0310 0311 void TestInformationFunctions::testCOUNTIF() 0312 { 0313 CHECK_EVAL("COUNTIF(B4:B5;\">2.5\")", Value(1)); // B4 is 2 and B5 is 3, so there is one cell in the range with a value greater than 2.5. 0314 CHECK_EVAL("COUNTIF(B3:B5;B4)", Value(1)); // Test if a cell equals the value in [.B4]. 0315 CHECK_EVAL("COUNTIF(\"\";B4)", Value::errorNA()); // Constant values are not allowed for the range. 0316 CHECK_EVAL("COUNTIF(B3:B10;\"7\")", Value(1)); // [.B3] is the string "7". 0317 CHECK_EVAL("COUNTIF(B3:B10;1+1)", Value(1)); // The criteria can be an expression. 0318 } 0319 0320 void TestInformationFunctions::testERRORTYPE() 0321 { 0322 CHECK_EVAL("ERRORTYPE(0)", Value::errorVALUE()); // Non-errors produce an error. 0323 CHECK_EVAL("ERRORTYPE(NA())", Value(7)); // By convention, the ERROR.TYPE of NA() is 7. 0324 CHECK_EVAL("ERRORTYPE(1/0)", Value(2)); 0325 } 0326 0327 void TestInformationFunctions::testFORMULA() 0328 { 0329 CHECK_EVAL( "FORMULA(B1)", Value( "=SUM(A19:A31)" ) ); // B1 contains a simple SUM formula 0330 CHECK_EVAL( "FORMULA(B2)", Value::errorNA() ); // Empty formula means no formula 0331 CHECK_EVAL( "FORMULA(B3)", Value::errorNA() ); // Cell constants are not formulas 0332 0333 CHECK_EVAL( "LEN(FORMULA(B1))>0", Value( true ) ); // B7 is a formula, so this is fine and will produce a text value 0334 } 0335 0336 void TestInformationFunctions::testINFO() 0337 { 0338 CHECK_EVAL("INFO(\"recalc\")", Value("Automatic")); // 0339 CHECK_EVAL("ISTEXT(INFO(\"system\"))", Value(true)); // The details of "system" vary by system, but it is always a text value 0340 CHECK_EVAL("ISTEXT(INFO(\"directory\"))", Value(true)); // Test to see that every required category is supported 0341 // CHECK_EVAL( "ISNUMBER(INFO(\"memavail\"))", Value( true ) ); // not implemented 0342 // CHECK_EVAL( "ISNUMBER(INFO(\"memused\"))", Value( true ) ); // not implemented 0343 CHECK_EVAL("ISNUMBER(INFO(\"numfile\"))", Value(true)); // 0344 CHECK_EVAL("ISTEXT(INFO(\"osversion\"))", Value(true)); // 0345 // CHECK_EVAL( "ISTEXT(INFO(\"origin\"))", Value( true ) ); // not implemented 0346 CHECK_EVAL("ISTEXT(INFO(\"recalc\"))", Value(true)); // 0347 CHECK_EVAL("ISTEXT(INFO(\"release\"))", Value(true)); // 0348 // CHECK_EVAL( "ISNUMBER(INFO(\"totmem\"))", Value( true ) ); // not implemented 0349 0350 // TODO should ISTEXT return errorVALUE() if args is errorVALUE? false seems to be more logical 0351 // CHECK_EVAL( "ISTEXT(INFO(\"completely-unknown-category\"))", Value::errorVALUE() ); // Error if the category is unknown 0352 } 0353 0354 void TestInformationFunctions::testISBLANK() 0355 { 0356 CHECK_EVAL("ISBLANK(1)", Value(false)); // Numbers return false. 0357 CHECK_EVAL("ISBLANK(\"\")", Value(false)); // Text, even empty string, returns false. 0358 CHECK_EVAL("ISBLANK(B8)", Value(true)); // Blank cell is true. 0359 CHECK_EVAL("ISBLANK(B7)", Value(false)); // Non-blank cell is false. 0360 } 0361 0362 void TestInformationFunctions::testISERR() 0363 { 0364 CHECK_EVAL("ISERR(1/0)", Value(true)); // Error values other than NA() return true. 0365 CHECK_EVAL("ISERR(NA())", Value(false)); // NA() does NOT return True. 0366 CHECK_EVAL("ISERR(\"#N/A\")", Value(false)); // Text is not an error. 0367 CHECK_EVAL("ISERR(1)", Value(false)); // Numbers are not an error. 0368 } 0369 0370 void TestInformationFunctions::testISERROR() 0371 { 0372 CHECK_EVAL("ISERROR(1/0)", Value(true)); // Error values return true. 0373 CHECK_EVAL("ISERROR(NA())", Value(true)); // Even NA(). 0374 CHECK_EVAL("ISERROR(\"#N/A\")", Value(false)); // Text is not an error. 0375 CHECK_EVAL("ISERROR(1)", Value(false)); // Numbers are not an error. 0376 CHECK_EVAL("ISERROR(CHOOSE(0; \"Apple\"; \"Orange\";" 0377 " \"Grape\"; \"Perry\"))", Value(true)); // If CHOOSE given 0378 // out-of-range value, ISERROR needs to capture it. 0379 } 0380 0381 void TestInformationFunctions::testISEVEN() 0382 { 0383 CHECK_EVAL("ISEVEN( 2)", Value(true)); // 2 is even, because (2 modulo 2) = 0 0384 CHECK_EVAL("ISEVEN( 6)", Value(true)); // 6 is even, because (6 modulo 2) = 0 0385 CHECK_EVAL("ISEVEN( 2.1)", Value(true)); // 0386 CHECK_EVAL("ISEVEN( 2.5)", Value(true)); // 0387 CHECK_EVAL("ISEVEN( 2.9)", Value(true)); // TRUNC(2.9)=2, and 2 is even. 0388 CHECK_EVAL("ISEVEN( 3)", Value(false)); // 3 is not even. 0389 CHECK_EVAL("ISEVEN( 3.9)", Value(false)); // TRUNC(3.9)=3, and 3 is not even. 0390 CHECK_EVAL("ISEVEN(-2)", Value(true)); // 0391 CHECK_EVAL("ISEVEN(-2.1)", Value(true)); // 0392 CHECK_EVAL("ISEVEN(-2.5)", Value(true)); // 0393 CHECK_EVAL("ISEVEN(-2.9)", Value(true)); // TRUNC(-2.9)=-2, and -2 is even. 0394 CHECK_EVAL("ISEVEN(-3)", Value(false)); // 0395 CHECK_EVAL("ISEVEN(NA())", Value::errorNA()); // 0396 CHECK_EVAL("ISEVEN( 0)", Value(true)); // 0397 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETISEVEN(2.5)", Value(true)); // alternate function name 0398 } 0399 0400 void TestInformationFunctions::testISFORMULA() 0401 { 0402 CHECK_EVAL( "ISFORMULA(B1)", Value( true ) ); // B1 contains a simple SUM formula 0403 CHECK_EVAL( "ISFORMULA(B2)", Value( false ) ); // Empty formula means no formula 0404 CHECK_EVAL( "ISFORMULA(B3)", Value( false ) ); // Cell constants are not formulas 0405 } 0406 0407 void TestInformationFunctions::testISLOGICAL() 0408 { 0409 CHECK_EVAL("ISLOGICAL(TRUE())", Value(true)); // Logical values return true. 0410 CHECK_EVAL("ISLOGICAL(FALSE())", Value(true)); // Logical values return true. 0411 CHECK_EVAL("ISLOGICAL(\"TRUE\")", Value(false)); // Text values are not logicals, 0412 // even if they can be converted. 0413 } 0414 0415 void TestInformationFunctions::testISNONTEXT() 0416 { 0417 CHECK_EVAL("ISNONTEXT(1)", Value(true)); // Numbers are not text 0418 CHECK_EVAL("ISNONTEXT(TRUE())", Value(true)); // Logical values are not text. 0419 CHECK_EVAL("ISNONTEXT(\"1\")", Value(false)); // TexText values are text, even 0420 // if they can be converted into a number. 0421 CHECK_EVAL("ISNONTEXT(B7)", Value(false)); // B7 is a cell with text 0422 CHECK_EVAL("ISNONTEXT(B9)", Value(true)); // B9 is an error, thus not text 0423 CHECK_EVAL("ISNONTEXT(B8)", Value(true)); // B8 is a blank cell, so this will return TRUE 0424 } 0425 0426 void TestInformationFunctions::testISNA() 0427 { 0428 CHECK_EVAL("ISNA(1/0)", Value(false)); // Error values other than NA() return False - the error does not propagate. 0429 CHECK_EVAL("ISNA(NA())", Value(true)); // By definition 0430 // CHECK_EVAL( "ISNA(#N/A)", Value( true ) ); // By definition 0431 CHECK_EVAL("ISNA(\"#N/A\")", Value(false)); // Text is not NA 0432 CHECK_EVAL("ISNA(1)", Value(false)); // Numbers are not NA 0433 } 0434 0435 void TestInformationFunctions::testISNUMBER() 0436 { 0437 CHECK_EVAL("ISNUMBER(1)", Value(true)); // Numbers are numbers 0438 CHECK_EVAL("ISNUMBER(\"1\")", Value(false)); // Text values are not numbers, 0439 // even if they can be converted into a number. 0440 } 0441 0442 void TestInformationFunctions::testISODD() 0443 { 0444 CHECK_EVAL("ISODD(3)", Value(true)); // 3 is odd, because (3 modulo 2) = 0 0445 CHECK_EVAL("ISODD(5)", Value(true)); // 5 is odd, because (5 modulo 2) = 0 0446 CHECK_EVAL("ISODD(3.1)", Value(true)); // TRUNC(3.1)=3, and 3 is odd 0447 CHECK_EVAL("ISODD(3.5)", Value(true)); // 0448 CHECK_EVAL("ISODD(3.9)", Value(true)); // TRUNC(3.9)=3, and 3 is odd. 0449 CHECK_EVAL("ISODD(4)", Value(false)); // 4 is not even. 0450 CHECK_EVAL("ISODD(4.9)", Value(false)); // TRUNC(4.9)=4, and 3 is not even. 0451 CHECK_EVAL("ISODD(-3)", Value(true)); // 0452 CHECK_EVAL("ISODD(-3.1)", Value(true)); // 0453 CHECK_EVAL("ISODD(-3.5)", Value(true)); // 0454 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETISODD(3.1)", Value(true)); // alternate function name 0455 } 0456 0457 void TestInformationFunctions::testISTEXT() 0458 { 0459 CHECK_EVAL("ISTEXT(1)", Value(false)); // Numbers are not text 0460 CHECK_EVAL("ISTEXT(\"1\")", Value(true)); // Text values are text, 0461 // even if they can be converted into a number. 0462 } 0463 0464 void TestInformationFunctions::testISREF() 0465 { 0466 CHECK_EVAL("ISREF(B3)", Value(true)); // 0467 CHECK_EVAL("ISREF(B3:C4)", Value(true)); // The range operator produces references 0468 CHECK_EVAL("ISREF(1)", Value(false)); // Numbers are not references 0469 CHECK_EVAL("ISREF(\"A1\")", Value(false)); // Text is not a reference, even if it looks a little like one 0470 CHECK_EVAL("ISREF(NA())", Value::errorNA()); // Errors propagate through this function 0471 } 0472 0473 void TestInformationFunctions::testN() 0474 { 0475 CHECK_EVAL("N(6)", Value(6)); // N does not change numbers. 0476 CHECK_EVAL("N(TRUE())", Value(1)); // Does convert logicals. 0477 CHECK_EVAL("N(FALSE())", Value(0)); // Does convert logicals. 0478 } 0479 0480 void TestInformationFunctions::testNA() 0481 { 0482 CHECK_EVAL("ISERROR(NA())", Value(true)); // NA is an error. 0483 CHECK_EVAL("ISNA(NA())", Value(true)); // Obviously, if this doesn't work, NA() or ISNA() is broken. 0484 CHECK_EVAL("ISNA(5+NA())", Value(true)); // NA propagates through various functions 0485 // and operators, just like any other error type. 0486 } 0487 0488 // TODO row not working here 0489 void TestInformationFunctions::testROW() 0490 { 0491 CHECK_EVAL("ROW(B7)", Value(7)); // The second value of a cell reference is the row number. 0492 // CHECK_EVAL( "ROW()", Value( 5 ) ); // Row of current cell is default, here formula in row 5. 0493 0494 Value res(Value::Array); 0495 res.setElement(0, 0, Value(2)); 0496 res.setElement(0, 1, Value(3)); 0497 res.setElement(0, 2, Value(4)); 0498 0499 CHECK_EVAL("ROW(B2:B4)", res); // Array with row numbers. 0500 } 0501 0502 void TestInformationFunctions::testROWS() 0503 { 0504 CHECK_EVAL("ROWS(C1)", Value(1)); // Single cell range contains one row. 0505 CHECK_EVAL("ROWS(C1:C4)", Value(4)); // Range with four rows. 0506 CHECK_EVAL("ROWS(A4:D100)", Value(97)); // Number of rows in range. 0507 } 0508 0509 void TestInformationFunctions::testSHEET() 0510 { 0511 CHECK_EVAL("SHEET(B7)", Value(1)); 0512 CHECK_EVAL("SHEET(Sheet2!B7)", Value(2)); 0513 CHECK_EVAL("SHEET(Sheet3!B7)", Value(3)); 0514 CHECK_EVAL("SHEET()", Value(1)); 0515 } 0516 0517 void TestInformationFunctions::testSHEETS() 0518 { 0519 CHECK_EVAL( "SHEETS(B7)", Value( 1 ) ); // If given, the sheet number of the reference is used. 0520 CHECK_EVAL( "SHEETS(Sheet1!B7:C9)", Value( 1 ) ); 0521 CHECK_EVAL( "SHEETS(Sheet1!A7:Sheet1!C9)", Value( 1 ) ); 0522 0523 //TODO this should not fail! :-( 0524 //CHECK_EVAL( "SHEETS(Sheet1!B7:Sheet2!C9)", Value( 2 ) ); 0525 //CHECK_EVAL( "SHEETS(Sheet1!B7:Sheet3!C9)", Value( 2 ) ); 0526 //CHECK_EVAL( "SHEETS(Sheet1!A7:Sheet3!C9)", Value( 3 ) ); 0527 0528 CHECK_EVAL( "SHEETS()", Value( 3 ) ); // Count all sheets 0529 } 0530 0531 void TestInformationFunctions::testTYPE() 0532 { 0533 // Value's Type | Type return 0534 // --------------+------------- 0535 // Number | 1 0536 // Text | 2 0537 // Logical | 4 0538 // Error | 16 0539 // Array | 64 0540 0541 CHECK_EVAL("TYPE(1+2)", Value(1)); // Number has TYPE code of 1 0542 CHECK_EVAL("TYPE(\"Hi\"&\"there\")", Value(2)); // Text has TYPE 2 0543 CHECK_EVAL("TYPE(NA())", Value(16)); // Errors have TYPE 16. 0544 } 0545 0546 void TestInformationFunctions::testVALUE() 0547 { 0548 CHECK_EVAL("VALUE(\"6\")", Value(6)); 0549 CHECK_EVAL("VALUE(\"1E5\")", Value(100000)); 0550 CHECK_EVAL("VALUE(\"200%\")", Value(2)); 0551 CHECK_EVAL("VALUE(\"1.5\")", Value(1.5)); 0552 // Check fractions 0553 CHECK_EVAL("VALUE(\"7 1/4\")", Value(7.25)); 0554 CHECK_EVAL("VALUE(\"0 1/2\")", Value(0.5)); 0555 CHECK_EVAL("VALUE(\"0 7/2\")", Value(3.5)); 0556 CHECK_EVAL("VALUE(\"-7 1/5\")", Value(-7.2)); 0557 CHECK_EVAL("VALUE(\"-7 10/50\")", Value(-7.2)); 0558 CHECK_EVAL("VALUE(\"-7 10/500\")", Value(-7.02)); 0559 CHECK_EVAL("VALUE(\"-7 4/2\")", Value(-9)); 0560 CHECK_EVAL("VALUE(\"-7 40/20\")", Value(-9)); 0561 // Check times 0562 CHECK_EVAL("VALUE(\"00:00\")", Value(0)); 0563 CHECK_EVAL("VALUE(\"00:00:00\")", Value(0)); 0564 CHECK_EVAL("VALUE(\"02:00\")-2/24", Value(0)); 0565 CHECK_EVAL("VALUE(\"02:00:00\")-2/24", Value(0)); 0566 CHECK_EVAL("VALUE(\"02:00:00.0\")-2/24", Value(0)); 0567 CHECK_EVAL("VALUE(\"02:00:00.00\")-2/24", Value(0)); 0568 CHECK_EVAL("VALUE(\"02:00:00.000\")-2/24", Value(0)); 0569 CHECK_EVAL("VALUE(\"2:03:05\") -2/24-3/(24*60) -5/(24*60*60)", Value(0)); 0570 CHECK_EVAL("VALUE(\"2:03\")-(2/24)-(3/(24*60))", Value(0)); 0571 // check dates - local dependent 0572 // CHECK_EVAL( "VALUE(\"5/21/06\")=DATE(2006;5;21)", Value( true ) ); 0573 // CHECK_EVAL( "VALUE(\"1/2/2005\")=DATE(2005;1;2)", Value( true ) ); 0574 } 0575 0576 void TestInformationFunctions::testMATCH() 0577 { 0578 // invalid matchType 0579 CHECK_EVAL("MATCH(1;A19:A31;\"foo\")", Value::errorVALUE()); 0580 0581 // matchType == 0, exact match 0582 CHECK_EVAL("MATCH(5;C11:C17;0)", Value(1)); 0583 CHECK_EVAL("MATCH(8;C11:C17;0)", Value(3)); 0584 CHECK_EVAL("MATCH(1;C11:C17;0)", Value(7)); 0585 CHECK_EVAL("MATCH(13;C11:C17;0)", Value::errorNA()); 0586 CHECK_EVAL("MATCH(5;C11:C11;0)", Value(1)); 0587 CHECK_EVAL("MATCH(5;C11;0)", Value(1)); 0588 CHECK_EVAL("MATCH(5;C11:D13;0)", Value::errorNA()); // not sure if this is the best error 0589 CHECK_EVAL("MATCH(\"Hello\";B3:B10;0)", Value(5)); 0590 CHECK_EVAL("MATCH(\"hello\";B3:B10;0)", Value(5)); // match is always case insensitive 0591 CHECK_EVAL("MATCH(\"kde\";A1000:G1000;0)", Value(5)); 0592 0593 // matchType == 1 or omitted, largest value less than or equal to search value in sorted range 0594 CHECK_EVAL("MATCH(0;A19:A31;1)", Value::errorNA()); 0595 CHECK_EVAL("MATCH(1;A19:A31;1)", Value(1)); 0596 CHECK_EVAL("MATCH(16;A19:A31;1)", Value(5)); 0597 CHECK_EVAL("MATCH(40;A19:A31;1)", Value(6)); 0598 CHECK_EVAL("MATCH(4096;A19:A31;1)", Value(13)); 0599 CHECK_EVAL("MATCH(5000;A19:A31;1)", Value(13)); 0600 CHECK_EVAL("MATCH(\"aaa\";A1000:G1000)", Value::errorNA()); 0601 CHECK_EVAL("MATCH(\"abc\";A1000:G1000)", Value(1)); 0602 CHECK_EVAL("MATCH(\"efoob\";A1000:G1000)", Value(3)); 0603 CHECK_EVAL("MATCH(\"epub\";A1000:G1000)", Value(3)); 0604 CHECK_EVAL("MATCH(\"kde\";A1000:G1000)", Value(6)); 0605 CHECK_EVAL("MATCH(\"xxx\";A1000:G1000)", Value(7)); 0606 CHECK_EVAL("MATCH(\"zzz\";A1000:G1000)", Value(7)); 0607 CHECK_EVAL("MATCH(13;C11:D13;1)", Value::errorNA()); // not sure if this is the best error 0608 0609 // matchType == -1, smallest value greater than or equal to search value, in descending range 0610 CHECK_EVAL("MATCH(0;Z19:Z23;-1)", Value(5)); 0611 CHECK_EVAL("MATCH(1;Z19:Z23;-1)", Value(5)); 0612 CHECK_EVAL("MATCH(4;Z19:Z23;-1)", Value(3)); 0613 CHECK_EVAL("MATCH(5;Z19:Z23;-1)", Value(2)); 0614 CHECK_EVAL("MATCH(16;Z19:Z23;-1)", Value(1)); 0615 CHECK_EVAL("MATCH(33;Z19:Z23;-1)", Value::errorNA()); 0616 CHECK_EVAL("MATCH(13;C11:D13;-1)", Value::errorNA()); // not sure if this is the best error 0617 } 0618 0619 // 0620 // cleanup test 0621 // 0622 0623 void TestInformationFunctions::cleanupTestCase() 0624 { 0625 delete m_map; 0626 } 0627 0628 QTEST_MAIN(TestInformationFunctions)