File indexing completed on 2024-05-12 16:35:55
0001 /* This file is part of the KDE project 0002 Copyright 2004,2007 Ariya Hidayat <ariya@kde.org> 0003 0004 This library is free software; you can redistribute it and/or 0005 modify it under the terms of the GNU Library General Public 0006 License as published by the Free Software Foundation; only 0007 version 2 of the License. 0008 0009 This library is distributed in the hope that it will be useful, 0010 but WITHOUT ANY WARRANTY; without even the implied warranty of 0011 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0012 Library General Public License for more details. 0013 0014 You should have received a copy of the GNU Library General Public License 0015 along with this library; see the file COPYING.LIB. If not, write to 0016 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0017 Boston, MA 02110-1301, USA. 0018 */ 0019 #include "TestFormula.h" 0020 0021 #include "TestKspreadCommon.h" 0022 0023 using namespace Calligra::Sheets; 0024 0025 static char encodeTokenType(const Token& token) 0026 { 0027 char result = '?'; 0028 switch (token.type()) { 0029 case Token::Boolean: result = 'b'; break; 0030 case Token::Integer: result = 'i'; break; 0031 case Token::Float: result = 'f'; break; 0032 case Token::Operator: result = 'o'; break; 0033 case Token::Cell: result = 'c'; break; 0034 case Token::Range: result = 'r'; break; 0035 case Token::Identifier: result = 'x'; break; 0036 case Token::String: result = 's'; break; 0037 default: break; 0038 } 0039 return result; 0040 } 0041 0042 #if 0 // not used? 0043 static QString describeTokenCodes(const QString& tokenCodes) 0044 { 0045 QString result; 0046 0047 if (tokenCodes.isEmpty()) 0048 result = "(invalid)"; 0049 else 0050 for (int i = 0; i < tokenCodes.length(); i++) { 0051 switch (tokenCodes[i].unicode()) { 0052 case 'b': result.append("Boolean"); break; 0053 case 'i': result.append("integer"); break; 0054 case 'f': result.append("float"); break; 0055 case 'o': result.append("operator"); break; 0056 case 'c': result.append("cell"); break; 0057 case 'r': result.append("range"); break; 0058 case 'x': result.append("identifier"); break; 0059 default: result.append("unknown"); break; 0060 } 0061 if (i < tokenCodes.length() - 1) result.append(", "); 0062 } 0063 0064 return result.prepend("{").append("}"); 0065 } 0066 #endif 0067 0068 #define CHECK_TOKENIZE(x,y) QCOMPARE(tokenizeFormula(x), QString(y)) 0069 0070 static QString tokenizeFormula(const QString& formula) 0071 { 0072 Formula f; 0073 QString expr = formula; 0074 expr.prepend('='); 0075 f.setExpression(expr); 0076 Tokens tokens = f.tokens(); 0077 0078 QString resultCodes; 0079 if (tokens.valid()) 0080 for (int i = 0; i < tokens.count(); i++) 0081 resultCodes.append(encodeTokenType(tokens[i])); 0082 0083 return resultCodes; 0084 } 0085 0086 0087 // because we may need to promote expected value from integer to float 0088 #define CHECK_EVAL(x,y) { Value z(y); QCOMPARE(evaluate(x,z),(z)); } 0089 0090 Value TestFormula::evaluate(const QString& formula, Value& ex) 0091 { 0092 Formula f; 0093 QString expr = formula; 0094 if (expr[0] != '=') 0095 expr.prepend('='); 0096 f.setExpression(expr); 0097 Value result = f.eval(); 0098 0099 if (result.isFloat() && ex.isInteger()) 0100 ex = Value(ex.asFloat()); 0101 if (result.isInteger() && ex.isFloat()) 0102 result = Value(result.asFloat()); 0103 0104 return result; 0105 } 0106 0107 void TestFormula::initTestCase() 0108 { 0109 FunctionModuleRegistry::instance()->loadFunctionModules(); 0110 } 0111 0112 void TestFormula::testTokenizer() 0113 { 0114 // simple, single-token formulas 0115 CHECK_TOKENIZE("True", "x"); 0116 CHECK_TOKENIZE("False", "x"); 0117 CHECK_TOKENIZE("36", "i"); 0118 CHECK_TOKENIZE("0", "i"); 0119 CHECK_TOKENIZE("3.14159", "f"); 0120 CHECK_TOKENIZE(".25", "f"); 0121 CHECK_TOKENIZE("1e-9", "f"); 0122 CHECK_TOKENIZE("2e3", "f"); 0123 CHECK_TOKENIZE(".3333e0", "f"); 0124 0125 // cell/range/identifier 0126 CHECK_TOKENIZE("A1", "c"); 0127 CHECK_TOKENIZE("Sheet1!A1", "c"); 0128 CHECK_TOKENIZE("'Sheet1'!A1", "c"); 0129 CHECK_TOKENIZE("'Sheet One'!A1", "c"); 0130 CHECK_TOKENIZE("2006!A1", "c"); 0131 CHECK_TOKENIZE("2006bak!A1", "c"); 0132 CHECK_TOKENIZE("2006bak2!A1", "c"); 0133 CHECK_TOKENIZE("'2006bak2'!A1", "c"); 0134 CHECK_TOKENIZE("A1:B100", "r"); 0135 CHECK_TOKENIZE("Sheet1!A1:B100", "r"); 0136 CHECK_TOKENIZE("'Sheet One'!A1:B100", "r"); 0137 CHECK_TOKENIZE("SIN", "x"); 0138 0139 // log2 and log10 are cell references and function identifiers 0140 CHECK_TOKENIZE("LOG2", "c"); 0141 CHECK_TOKENIZE("LOG10:11", "r"); 0142 CHECK_TOKENIZE("LOG2(2)", "xoio"); 0143 CHECK_TOKENIZE("LOG10(10)", "xoio"); 0144 0145 // operators 0146 CHECK_TOKENIZE("+", "o"); 0147 CHECK_TOKENIZE("-", "o"); 0148 CHECK_TOKENIZE("*", "o"); 0149 CHECK_TOKENIZE("/", "o"); 0150 CHECK_TOKENIZE("+", "o"); 0151 CHECK_TOKENIZE("^", "o"); 0152 CHECK_TOKENIZE("(", "o"); 0153 CHECK_TOKENIZE(")", "o"); 0154 CHECK_TOKENIZE(",", "o"); 0155 CHECK_TOKENIZE(";", "o"); 0156 CHECK_TOKENIZE("=", "o"); 0157 CHECK_TOKENIZE("<", "o"); 0158 CHECK_TOKENIZE(">", "o"); 0159 CHECK_TOKENIZE("<=", "o"); 0160 CHECK_TOKENIZE(">=", "o"); 0161 CHECK_TOKENIZE("%", "o"); 0162 0163 // commonly used formulas 0164 CHECK_TOKENIZE("A1+A2", "coc"); 0165 CHECK_TOKENIZE("2.5*B1", "foc"); 0166 CHECK_TOKENIZE("SUM(A1:Z10)", "xoro"); 0167 CHECK_TOKENIZE("MAX(Sheet1!Sales)", "xoro"); 0168 CHECK_TOKENIZE("-ABS(A1)", "oxoco"); 0169 0170 // should be correctly parsed though they are nonsense (can't be evaluated) 0171 CHECK_TOKENIZE("0E0.5", "ff"); 0172 CHECK_TOKENIZE("B3 D4:D5 Sheet1!K1", "crc"); 0173 CHECK_TOKENIZE("SIN A1", "xc"); 0174 CHECK_TOKENIZE("SIN A1:A20", "xr"); 0175 0176 // invalid formulas, can't be parsed correctly 0177 CHECK_TOKENIZE("+1.23E", QString()); 0178 0179 // incomplete formulas 0180 CHECK_TOKENIZE("COMPARE(\"", "xo"); 0181 CHECK_TOKENIZE("SHEETS(Sheet2!", ""); 0182 0183 // empty parameter 0184 CHECK_TOKENIZE("IF(A1;A2;)", "xococoo"); 0185 CHECK_TOKENIZE("OFFSET(Sheet2'!B7;0;0)", ""); 0186 0187 // " escape 0188 CHECK_TOKENIZE("CONCATENATE(\"\"\"\")", "xoso"); 0189 // function cascade 0190 CHECK_TOKENIZE("SUM(ABS(-1);ABS(-1))", "xoxooiooxooioo"); 0191 } 0192 0193 void TestFormula::testConstant() 0194 { 0195 // simple constants 0196 CHECK_EVAL("0", Value(0)); 0197 CHECK_EVAL("1", Value(1)); 0198 CHECK_EVAL("-1", Value(-1)); 0199 CHECK_EVAL("3.14e7", Value(3.14e7)); 0200 CHECK_EVAL("3.14e-7", Value(3.14e-7)); 0201 0202 // String constants (from Odf 1.2 spec) 0203 CHECK_EVAL("\"Hi\"", Value("Hi")); 0204 CHECK_EVAL("\"Hi\"\"t\"", Value("Hi\"t")); 0205 CHECK_EVAL("\"\\n\"", Value("\\n")); 0206 0207 // Constant errors 0208 CHECK_EVAL("#N/A", Value::errorNA()); 0209 CHECK_EVAL("#DIV/0!", Value::errorDIV0()); 0210 CHECK_EVAL("#NAME?", Value::errorNAME()); 0211 CHECK_EVAL("#NULL!", Value::errorNULL()); 0212 CHECK_EVAL("#NUM!", Value::errorNUM()); 0213 CHECK_EVAL("#REF!", Value::errorREF()); 0214 CHECK_EVAL("#VALUE!", Value::errorVALUE()); 0215 0216 } 0217 0218 void TestFormula::testWhitespace() 0219 { 0220 CHECK_EVAL("=ROUND( 10.1 ; 0 )", Value(10)); 0221 CHECK_EVAL("= ROUND(10.1;0)", Value(10)); 0222 CHECK_EVAL(" =ROUND(10.1;0)", Value::errorPARSE()); 0223 CHECK_EVAL("= ( ROUND( 9.8 ; 0 ) + ROUND( 9.8 ; 0 ) ) ", Value(20)); 0224 CHECK_EVAL("=(ROUND(9.8;0) ROUND(9.8;0))", Value::errorPARSE()); 0225 } 0226 0227 void TestFormula::testInvalid() 0228 { 0229 // Basic operations always throw errors if one of the values 0230 // is invalid. This is the difference to SUM and co. 0231 CHECK_EVAL("a+0", Value::errorVALUE()); 0232 CHECK_EVAL("0-z", Value::errorVALUE()); 0233 CHECK_EVAL("a*b", Value::errorVALUE()); 0234 CHECK_EVAL("u/2", Value::errorVALUE()); 0235 } 0236 0237 void TestFormula::testUnary() 0238 { 0239 // unary minus 0240 CHECK_EVAL("-1", Value(-1)); 0241 CHECK_EVAL("--1", Value(1)); 0242 CHECK_EVAL("---1", Value(-1)); 0243 CHECK_EVAL("----1", Value(1)); 0244 CHECK_EVAL("-----1", Value(-1)); 0245 CHECK_EVAL("5-1", Value(4)); 0246 CHECK_EVAL("5--1", Value(6)); 0247 CHECK_EVAL("5---1", Value(4)); 0248 CHECK_EVAL("5----1", Value(6)); 0249 CHECK_EVAL("5-----1", Value(4)); 0250 CHECK_EVAL("5-----1*2.5", Value(2.5)); 0251 CHECK_EVAL("5------1*2.5", Value(7.5)); 0252 CHECK_EVAL("-SIN(0)", Value(0)); 0253 CHECK_EVAL("1.1-SIN(0)", Value(1.1)); 0254 CHECK_EVAL("1.2--SIN(0)", Value(1.2)); 0255 CHECK_EVAL("1.3---SIN(0)", Value(1.3)); 0256 CHECK_EVAL("-COS(0)", Value(-1)); 0257 CHECK_EVAL("1.1-COS(0)", Value(0.1)); 0258 CHECK_EVAL("1.2--COS(0)", Value(2.2)); 0259 CHECK_EVAL("1.3---COS(0)", Value(0.3)); 0260 } 0261 0262 void TestFormula::testBinary() 0263 { 0264 // simple binary operation 0265 CHECK_EVAL("0+0", Value(0)); 0266 CHECK_EVAL("1+1", Value(2)); 0267 0268 // power operator is left associative 0269 CHECK_EVAL("2^3", Value(8)); 0270 CHECK_EVAL("2^3^2", Value(64)); 0271 0272 // lead to division by zero 0273 CHECK_EVAL("0/0", Value::errorDIV0()); 0274 CHECK_EVAL("1/0", Value::errorDIV0()); 0275 CHECK_EVAL("-4/0", Value::errorDIV0()); 0276 CHECK_EVAL("(2*3)/(6-2*3)", Value::errorDIV0()); 0277 CHECK_EVAL("1e3+7/0", Value::errorDIV0()); 0278 CHECK_EVAL("2^(99/0)", Value::errorDIV0()); 0279 0280 } 0281 0282 void TestFormula::testOperators() 0283 { 0284 // no parentheses, checking operator precendences 0285 CHECK_EVAL("14+3*77", Value(245)); 0286 CHECK_EVAL("14-3*77", Value(-217)); 0287 CHECK_EVAL("26*4+81", Value(185)); 0288 CHECK_EVAL("26*4-81", Value(23)); 0289 CHECK_EVAL("30-45/3", Value(15)); 0290 CHECK_EVAL("45+45/3", Value(60)); 0291 CHECK_EVAL("4+3*2-1", Value(9)); 0292 } 0293 0294 void TestFormula::testComparison() 0295 { 0296 // compare numbers 0297 CHECK_EVAL("6>5", Value(true)); 0298 CHECK_EVAL("6<5", Value(false)); 0299 CHECK_EVAL("2=2", Value(true)); 0300 CHECK_EVAL("2=22", Value(false)); 0301 CHECK_EVAL("=3=3.0001", Value(false)); 0302 // compare booleans 0303 CHECK_EVAL("=TRUE()=FALSE()", Value(false)); 0304 CHECK_EVAL("=TRUE()=TRUE()", Value(true)); 0305 CHECK_EVAL("=FALSE()=FALSE()", Value(true)); 0306 // compare strings 0307 CHECK_EVAL("=\"Hi\"=\"Bye\"", Value(false)); 0308 CHECK_EVAL("=\"5\"=5", Value(false)); 0309 CHECK_EVAL("=\"Hi\"=\"HI\"", Value(false)); 0310 CHECK_EVAL("b>a", Value(true)); 0311 CHECK_EVAL("b<aa", Value(false)); 0312 CHECK_EVAL("c<d", Value(true)); 0313 CHECK_EVAL("cc>d", Value(false)); 0314 // compare dates 0315 CHECK_EVAL("=DATE(2001;12;12)>DATE(2001;12;11)", Value(true)); 0316 CHECK_EVAL("=DATE(2001;12;12)<DATE(2001;12;11)", Value(false)); 0317 CHECK_EVAL("=DATE(1999;01;01)=DATE(1999;01;01)", Value(true)); 0318 CHECK_EVAL("=DATE(1998;01;01)=DATE(1999;01;01)", Value(false)); 0319 // errors cannot be compared 0320 CHECK_EVAL("=NA()=NA()", Value::errorNA()); 0321 CHECK_EVAL("=NA()>NA()", Value::errorNA()); 0322 CHECK_EVAL("#DIV/0!>0", Value::errorDIV0()); 0323 CHECK_EVAL("5<#VALUE!", Value::errorVALUE()); 0324 CHECK_EVAL("#DIV/0!=#DIV/0!", Value::errorDIV0()); 0325 } 0326 0327 void TestFormula::testString() 0328 { 0329 // string expansion ... 0330 CHECK_EVAL("\"2\"+5", Value(7)); 0331 CHECK_EVAL("2+\"5\"", Value(7)); 0332 CHECK_EVAL("\"2\"+\"5\"", Value(7)); 0333 } 0334 0335 void TestFormula::testFunction() 0336 { 0337 // function with no arguments 0338 CHECK_EVAL("TRUE()", Value(true)); 0339 0340 //the built-in sine function 0341 CHECK_EVAL("SIN(0)", Value(0)); 0342 CHECK_EVAL("2+sin(\"2\"-\"2\")", Value(2)); 0343 CHECK_EVAL("\"1\"+sin(\"0\")", Value(1)); 0344 0345 // function cascades 0346 CHECK_EVAL("SUM(ABS( 1);ABS( 1))", Value(2)); 0347 CHECK_EVAL("SUM(ABS( 1);ABS(-1))", Value(2)); 0348 CHECK_EVAL("SUM(ABS(-1);ABS( 1))", Value(2)); 0349 CHECK_EVAL("SUM(ABS(-1);ABS(-1))", Value(2)); 0350 CHECK_EVAL("SUM(SUM(-2;-2;-2);SUM(-2;-2;-2;-2);SUM(-2;-2;-2;-2;-2))", Value(-24)); 0351 } 0352 0353 void TestFormula::testInlineArrays() 0354 { 0355 #ifdef CALLIGRA_SHEETS_INLINE_ARRAYS 0356 // inline arrays 0357 CHECK_TOKENIZE("{1;2|3;4}", "oioioioio"); 0358 0359 Value array(Value::Array); 0360 array.setElement(0, 0, Value((int)1)); 0361 array.setElement(1, 0, Value((int)2)); 0362 array.setElement(0, 1, Value((int)3)); 0363 array.setElement(1, 1, Value((int)4)); 0364 CHECK_EVAL("={1;2|3;4}", array); 0365 0366 array.setElement(1, 0, Value(0.0)); 0367 CHECK_EVAL("={1;SIN(0)|3;4}", array); // "dynamic" 0368 CHECK_EVAL("=SUM({1;2|3;4})", Value(10)); 0369 #endif 0370 } 0371 0372 QTEST_MAIN(TestFormula)