File indexing completed on 2024-05-12 16:35:59
0001 /* This file is part of the KDE project 0002 Copyright 2007 Brad Hards <bradh@frogmouth.net> 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 "TestTextFunctions.h" 0020 0021 #include "TestKspreadCommon.h" 0022 0023 void TestTextFunctions::initTestCase() 0024 { 0025 FunctionModuleRegistry::instance()->loadFunctionModules(); 0026 } 0027 0028 #define CHECK_EVAL(x,y) { Value z(y); QCOMPARE(evaluate(x,z),(z)); } 0029 // CHECK_EVAL has the side effect that the value 'y' is modified, e.g due to localization 0030 // Use CHECK_VALUE2 where this this is a problem 0031 #define CHECK_EVAL2(x,y,r) { Value z(y); QCOMPARE(evaluate(x,z),r); } 0032 0033 Value TestTextFunctions::evaluate(const QString& formula, Value& ex) 0034 { 0035 Formula f; 0036 QString expr = formula; 0037 if (expr[0] != '=') 0038 expr.prepend('='); 0039 f.setExpression(expr); 0040 Value result = f.eval(); 0041 0042 if (result.isFloat() && ex.isInteger()) 0043 ex = Value(ex.asFloat()); 0044 if (result.isInteger() && ex.isFloat()) 0045 result = Value(result.asFloat()); 0046 0047 return result; 0048 } 0049 0050 void TestTextFunctions::testASC() 0051 { 0052 // TODO reactivate after function is implemented 0053 // CHECK_EVAL( "ASC(\"ＡＢＣ\")", Value( "ABC" ) ); 0054 // CHECK_EVAL( "ASC(\"アイウ\")", Value( "ｧｨｩ" ) ); 0055 } 0056 0057 void TestTextFunctions::testCHAR() 0058 { 0059 CHECK_EVAL("CHAR(65)", Value("A")); 0060 CHECK_EVAL("CHAR(60)", Value("<")); 0061 CHECK_EVAL("CHAR(97)", Value("a")); 0062 CHECK_EVAL("CHAR(126)", Value("~")); 0063 CHECK_EVAL("CHAR(32)", Value(" ")); 0064 0065 // newline 0066 CHECK_EVAL("LEN(CHAR(10))", Value(1)); 0067 // number has to be >=0 0068 CHECK_EVAL("CHAR(-1)", Value::errorNUM()); 0069 } 0070 0071 void TestTextFunctions::testCLEAN() 0072 { 0073 CHECK_EVAL("CLEAN(\"Text\")", Value("Text")); 0074 CHECK_EVAL("CLEAN(CHAR(7)&\"Tex\"&CHAR(8)&\"t\"&CHAR(9))", Value("Text")); 0075 CHECK_EVAL("CLEAN(\"Hi there\")", Value("Hi there")); 0076 } 0077 0078 void TestTextFunctions::testCODE() 0079 { 0080 CHECK_EVAL("CODE(\"A\")", Value(65)); 0081 CHECK_EVAL("CODE(\"0\")>0", Value(true)); 0082 CHECK_EVAL("CODE(\"Text\")=CODE(\"T\")", Value(true)); 0083 } 0084 0085 void TestTextFunctions::testCONCATENATE() 0086 { 0087 CHECK_EVAL("CONCATENATE(\"Hi \"; \"there\")", Value("Hi there")); 0088 CHECK_EVAL("CONCATENATE(\"A\"; \"B\"; \"C\")", Value("ABC")); 0089 CHECK_EVAL("CONCATENATE(2;3)", Value("23")); 0090 CHECK_EVAL("CONCATENATE(23)", Value("23")); 0091 } 0092 0093 void TestTextFunctions::testEXACT() 0094 { 0095 CHECK_EVAL("EXACT(\"A\";\"A\")", Value(true)); 0096 CHECK_EVAL("EXACT(\"A\";\"a\")", Value(false)); 0097 CHECK_EVAL("EXACT(1;1)", Value(true)); 0098 CHECK_EVAL("EXACT((1/3)*3;1)", Value(true)); 0099 CHECK_EVAL("EXACT(TRUE();TRUE())", Value(true)); 0100 CHECK_EVAL("EXACT(\"1\";2)", Value(false)); 0101 CHECK_EVAL("EXACT(\"h\";1)", Value(false)); 0102 CHECK_EVAL("EXACT(\"1\";1)", Value(true)); 0103 CHECK_EVAL("EXACT(\" 1\";1)", Value(false)); 0104 } 0105 0106 void TestTextFunctions::testFIND() 0107 { 0108 CHECK_EVAL("FIND(\"b\";\"abcabc\")", Value(2)); 0109 CHECK_EVAL("FIND(\"b\";\"abcabcabc\"; 3)", Value(5)); 0110 CHECK_EVAL("FIND(\"b\";\"ABC\";1)", Value::errorVALUE()); 0111 CHECK_EVAL("FIND(\"b\";\"bbbb\")", Value(1)); 0112 CHECK_EVAL("FIND(\"b\";\"bbbb\";2)", Value(2)); 0113 CHECK_EVAL("FIND(\"b\";\"bbbb\";2.9)", Value(2)); 0114 CHECK_EVAL("FIND(\"b\";\"bbbb\";0)", Value::errorVALUE()); 0115 CHECK_EVAL("FIND(\"b\";\"bbbb\";0.9)", Value::errorVALUE()); 0116 } 0117 0118 void TestTextFunctions::testFIXED() 0119 { 0120 // localize the expected value 0121 QLocale loc = QLocale::system(); 0122 Value expected(loc.toString(12345.0, 'f', 3)); 0123 0124 CHECK_EVAL2("FIXED(12345;3)", Value("12,345.000"), expected); 0125 CHECK_EVAL("ISTEXT(FIXED(12345;3))", Value(true)); 0126 CHECK_EVAL2("FIXED(12345;3;FALSE())", Value("12,345.000"), expected); 0127 CHECK_EVAL2("FIXED(12345;3.95;FALSE())", Value("12,345.000"), expected); 0128 0129 expected = Value(loc.toString(12345.0, 'f', 4).remove(loc.groupSeparator())); 0130 CHECK_EVAL2("FIXED(12345;4;TRUE())", Value("12345.0000"), expected); 0131 expected = Value(loc.toString(123.45, 'f', 1)); 0132 CHECK_EVAL2("FIXED(123.45;1)", Value("123.5"), expected); 0133 CHECK_EVAL("FIXED(125.45; -1)", Value("130")); 0134 CHECK_EVAL("FIXED(125.45; -1.1)", Value("130")); 0135 CHECK_EVAL("FIXED(125.45; -1.9)", Value("130")); 0136 CHECK_EVAL("FIXED(125.45; -2)", Value("100")); 0137 CHECK_EVAL("FIXED(125.45; -2.87)", Value("100")); 0138 CHECK_EVAL("FIXED(125.45; -3)", Value("0")); 0139 CHECK_EVAL("FIXED(125.45; -4)", Value("0")); 0140 CHECK_EVAL("FIXED(125.45; -5)", Value("0")); 0141 } 0142 0143 void TestTextFunctions::testJIS() 0144 { 0145 // TODO reactivate after function is implemented 0146 // CHECK_EVAL( "JIS(\"ABC\")", Value( "ＡＢＣ") ); 0147 // CHECK_EVAL( "JIS(\"ｧｨｩ\")", Value( "アイウ" ) ); 0148 } 0149 0150 void TestTextFunctions::testLEFT() 0151 { 0152 CHECK_EVAL("LEFT(\"Hello\";2)", Value("He")); 0153 CHECK_EVAL("LEFT(\"Hello\")", Value("H")); 0154 CHECK_EVAL("LEFT(\"Hello\";20)", Value("Hello")); 0155 CHECK_EVAL("LEFT(\"Hello\";0)", Value("")); 0156 CHECK_EVAL("LEFT(\"\";4)", Value("")); 0157 CHECK_EVAL("LEFT(\"xxx\";-0.1)", Value::errorVALUE()); 0158 CHECK_EVAL("LEFT(\"Hello\";2^15-1)", Value("Hello")); 0159 CHECK_EVAL("LEFT(\"Hello\";2.9)", Value("He")); 0160 } 0161 0162 void TestTextFunctions::testLEN() 0163 { 0164 CHECK_EVAL("LEN(\"Hi there\")", Value(8)); 0165 CHECK_EVAL("LEN(\"\")", Value(0)); 0166 CHECK_EVAL("LEN(55)", Value(2)); 0167 } 0168 0169 void TestTextFunctions::testLOWER() 0170 { 0171 CHECK_EVAL("LOWER(\"HELLObc7\")", Value("hellobc7")); 0172 } 0173 0174 void TestTextFunctions::testMID() 0175 { 0176 CHECK_EVAL("MID(\"123456789\";5;3)", Value("567")); 0177 CHECK_EVAL("MID(\"123456789\";20;3)", Value("")); 0178 CHECK_EVAL("MID(\"123456789\";-1;0)", Value::errorVALUE()); 0179 CHECK_EVAL("MID(\"123456789\";1;0)", Value("")); 0180 CHECK_EVAL("MID(\"123456789\";2.9;1)", Value("2")); 0181 CHECK_EVAL("MID(\"123456789\";2;2.9)", Value("23")); 0182 CHECK_EVAL("MID(\"123456789\";5)", Value("56789")); 0183 } 0184 0185 void TestTextFunctions::testNUMBERVALUE() 0186 { 0187 CHECK_EVAL( "NUMBERVALUE(\"6\"; \".\")", Value( 6 ) ); // VALUE converts text to numbers (unlike N). 0188 CHECK_EVAL( "NUMBERVALUE(\"6,000.5\"; \".\")", Value( 6000.5 ) ); // Period works. 0189 CHECK_EVAL( "NUMBERVALUE(\"6.000,5\"; \",\")", Value( 6000.5 ) ); // Comma works 0190 CHECK_EVAL( "NUMBERVALUE(\"3!456!000*567\"; \"*\"; \"!\")", Value( 3456000.567 ) ); // Thousands separator works 0191 CHECK_EVAL( "NUMBERVALUE(\"+6,000.5\"; \".\")", Value( 6000.5 ) ); // Positive sign 0192 CHECK_EVAL( "NUMBERVALUE(\"-6,000.5\"; \".\")", Value( -6000.5 ) ); // Negative sign 0193 } 0194 0195 void TestTextFunctions::testPROPER() 0196 { 0197 CHECK_EVAL("PROPER(\"hello there\")", Value("Hello There")); 0198 CHECK_EVAL("PROPER(\"HELLO THERE\")", Value("Hello There")); 0199 CHECK_EVAL("PROPER(\"HELLO.THERE\")", Value("Hello.There")); 0200 } 0201 0202 void TestTextFunctions::testREPLACE() 0203 { 0204 CHECK_EVAL("REPLACE(\"123456789\";5;3;\"Q\")", Value("1234Q89")); 0205 CHECK_EVAL("REPLACE(\"123456789\";5;0;\"Q\")", Value("1234Q56789")); 0206 } 0207 0208 void TestTextFunctions::testREPT() 0209 { 0210 CHECK_EVAL("REPT(\"X\";3)", Value("XXX")); 0211 CHECK_EVAL("REPT(\"XY\";2)", Value("XYXY")); 0212 CHECK_EVAL("REPT(\"X\";2.9)", Value("XX")); 0213 CHECK_EVAL("REPT(\"XY\";2.9)", Value("XYXY")); 0214 CHECK_EVAL("REPT(\"X\";0)", Value("")); 0215 CHECK_EVAL("REPT(\"XYZ\";0)", Value("")); 0216 CHECK_EVAL("REPT(\"X\";-1)", Value::errorVALUE()); 0217 CHECK_EVAL("REPT(\"XYZ\";-0.1)", Value::errorVALUE()); 0218 } 0219 0220 void TestTextFunctions::testRIGHT() 0221 { 0222 CHECK_EVAL("RIGHT(\"Hello\";2)", Value("lo")); 0223 CHECK_EVAL("RIGHT(\"Hello\")", Value("o")); 0224 CHECK_EVAL("RIGHT(\"Hello\";20)", Value("Hello")); 0225 CHECK_EVAL("RIGHT(\"Hello\";0)", Value("")); 0226 CHECK_EVAL("RIGHT(\"\";4)", Value("")); 0227 CHECK_EVAL("RIGHT(\"xxx\";-1)", Value::errorVALUE()); 0228 CHECK_EVAL("RIGHT(\"xxx\";-0.1)", Value::errorVALUE()); 0229 CHECK_EVAL("RIGHT(\"Hello\";2^15-1)", Value("Hello")); 0230 CHECK_EVAL("RIGHT(\"Hello\";2.9)", Value("lo")); 0231 } 0232 0233 void TestTextFunctions::testSEARCH() 0234 { 0235 CHECK_EVAL("=SEARCH(\"b\";\"abcabc\")", Value(2)); 0236 CHECK_EVAL("=SEARCH(\"b\";\"abcabcabc\"; 3)", Value(5)); 0237 CHECK_EVAL("=SEARCH(\"b\";\"ABC\";1)", Value(2)); 0238 CHECK_EVAL("=SEARCH(\"c?a\";\"abcabcda\")", Value(6)); 0239 CHECK_EVAL("=SEARCH(\"e*o\";\"yes and no\")", Value(2)); 0240 CHECK_EVAL("=SEARCH(\"b*c\";\"abcabcabc\")", Value(2)); 0241 } 0242 0243 void TestTextFunctions::testSUBSTITUTE() 0244 { 0245 CHECK_EVAL("SUBSTITUTE(\"121212\";\"2\";\"ab\")", Value("1ab1ab1ab")); 0246 CHECK_EVAL("SUBSTITUTE(\"121212\";\"2\";\"ab\";2)", Value("121ab12")); 0247 CHECK_EVAL("SUBSTITUTE(\"Hello\";\"x\";\"ab\")", Value("Hello")); 0248 CHECK_EVAL("SUBSTITUTE(\"xyz\";\"\";\"ab\")", Value("xyz")); 0249 CHECK_EVAL("SUBSTITUTE(\"\";\"\";\"ab\")", Value("")); 0250 CHECK_EVAL("SUBSTITUTE(\"Hello\"; \"H\"; \"J\"; 0)", Value::errorVALUE()); 0251 CHECK_EVAL("SUBSTITUTE(\"Hello\"; \"H\"; \"J\"; 1)", Value("Jello")); 0252 CHECK_EVAL("SUBSTITUTE(\"fo\"\"o\";\"o\";\"a\")", Value("fa\"a")); 0253 } 0254 0255 void TestTextFunctions::testT() 0256 { 0257 CHECK_EVAL("T(\"Hi\")", Value("Hi")); 0258 CHECK_EVAL("T(5)", Value("")); 0259 } 0260 0261 void TestTextFunctions::testTRIM() 0262 { 0263 CHECK_EVAL("TRIM(\" Hi \")", Value("Hi")); 0264 CHECK_EVAL("LEN(TRIM(\"H\" & \" \" & \" \" & \"I\"))", Value(3)); 0265 } 0266 0267 void TestTextFunctions::testUNICHAR() 0268 { 0269 CHECK_EVAL("UNICHAR(65)", Value("A")); 0270 CHECK_EVAL("UNICHAR(8364)", Value(QChar(8364))); 0271 } 0272 0273 void TestTextFunctions::testUNICODE() 0274 { 0275 QChar euro(8364); 0276 0277 CHECK_EVAL("UNICODE(\"A\")", Value(65)); 0278 CHECK_EVAL("UNICODE(\"AB€C\")", Value(65)); 0279 CHECK_EVAL(QString("UNICODE(\"%1\")").arg(euro), Value(8364)); 0280 CHECK_EVAL(QString("UNICODE(\"%1F\")").arg(euro), Value(8364)); 0281 } 0282 0283 void TestTextFunctions::testUPPER() 0284 { 0285 CHECK_EVAL("UPPER(\"Habc7\")", Value("HABC7")); 0286 } 0287 0288 void TestTextFunctions::testROT13() 0289 { 0290 CHECK_EVAL("ROT13(\"KSpread\")", Value("XFcernq")); 0291 CHECK_EVAL("ROT13(\"XFcernq\")", Value("KSpread")); 0292 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETROT13(\"KSpread\")", Value("XFcernq")); 0293 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETROT13(\"XFcernq\")", Value("KSpread")); 0294 } 0295 0296 void TestTextFunctions::testBAHTTEXT() 0297 { 0298 Value r; 0299 r = evaluate("BAHTTEXT(23)", r); 0300 CHECK_EVAL("BAHTTEXT(23)", r); 0301 CHECK_EVAL("COM.MICROSOFT.BAHTTEXT(23)", r); 0302 } 0303 0304 void TestTextFunctions::testTEXT() 0305 { 0306 CHECK_EVAL("TEXT(TIME(13;10;43);\"hh:mm\")", Value("13:10")); 0307 } 0308 0309 QTEST_MAIN(TestTextFunctions)