File indexing completed on 2024-05-12 16:35:56
0001 /* This file is part of the KDE project 0002 Copyright 2007 Ariya Hidayat <ariya@kde.org> 0003 Copyright 2006 Stefan Nikolaus <stefan.nikolaus@kdemail.net> 0004 Copyright 2005 Tomas Mecir <mecirt@gmail.com> 0005 0006 This library is free software; you can redistribute it and/or 0007 modify it under the terms of the GNU Library General Public 0008 License as published by the Free Software Foundation; only 0009 version 2 of the License. 0010 0011 This library is distributed in the hope that it will be useful, 0012 but WITHOUT ANY WARRANTY; without even the implied warranty of 0013 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0014 Library General Public License for more details. 0015 0016 You should have received a copy of the GNU Library General Public License 0017 along with this library; see the file COPYING.LIB. If not, write to 0018 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0019 Boston, MA 02110-1301, USA. 0020 */ 0021 0022 #include "TestOpenFormula.h" 0023 0024 #include <klocale.h> 0025 0026 #include <QTest> 0027 0028 #include <Formula.h> 0029 #include <FunctionModuleRegistry.h> 0030 #include <Region.h> 0031 #include <Util.h> 0032 #include <Value.h> 0033 #include <odf/SheetsOdf.h> 0034 0035 using namespace Calligra::Sheets; 0036 0037 // because we may need to promote expected value from integer to float 0038 #define CHECK_EVAL(x,y) { Value z(y); QCOMPARE(evaluate(x,z),(z)); } 0039 0040 Value TestOpenFormula::evaluate(const QString& formula, Value& ex) 0041 { 0042 Formula f; 0043 QString expr = formula; 0044 if (expr[0] != '=') 0045 expr.prepend('='); 0046 f.setExpression(expr); 0047 Value result = f.eval(); 0048 0049 if (result.isFloat() && ex.isInteger()) 0050 ex = Value(ex.asFloat()); 0051 if (result.isInteger() && ex.isFloat()) 0052 result = Value(result.asFloat()); 0053 0054 return result; 0055 } 0056 0057 namespace QTest 0058 { 0059 template<> 0060 char *toString(const Value& value) 0061 { 0062 QString message; 0063 QTextStream ts(&message, QIODevice::WriteOnly); 0064 ts << value; 0065 return qstrdup(message.toLatin1()); 0066 } 0067 } 0068 0069 #define CHECK_CONVERT(x,y) \ 0070 { QCOMPARE(convertToOpenFormula(x),QString(y)); \ 0071 QCOMPARE(convertFromOpenFormula(y),QString(x)); } 0072 0073 QString TestOpenFormula::convertToOpenFormula(const QString& expr) 0074 { 0075 KLocale locale("en_US"); 0076 locale.setDecimalSymbol(","); 0077 locale.setThousandsSeparator(" "); 0078 0079 QString formula = Odf::encodeFormula(expr, &locale); 0080 return formula; 0081 } 0082 0083 QString TestOpenFormula::convertFromOpenFormula(const QString& expr) 0084 { 0085 KLocale locale("en_US"); 0086 locale.setDecimalSymbol(","); 0087 locale.setThousandsSeparator(" "); 0088 0089 QString formula = Odf::decodeFormula(expr, &locale); 0090 return formula; 0091 } 0092 0093 void TestOpenFormula::initTestCase() 0094 { 0095 FunctionModuleRegistry::instance()->loadFunctionModules(); 0096 } 0097 0098 void TestOpenFormula::testEvaluation() 0099 { 0100 // tests from the OpenFormula testing suite: 0101 // note that these get auto-generated using generate-openformula-tests 0102 CHECK_EVAL("=(1/3)*3=1", Value(true)); // row 51 0103 CHECK_EVAL("=(\"4\" & \"5\")+2", Value(47)); // row 57 0104 CHECK_EVAL("=2+(\"4\" & \"5\")", Value(47)); // row 58 0105 CHECK_EVAL("=1+2", Value(3)); // row 63 0106 CHECK_EVAL("=3-1", Value(2)); // row 65 0107 CHECK_EVAL("=5--2", Value(7)); // row 67 0108 CHECK_EVAL("=3*4", Value(12)); // row 68 0109 CHECK_EVAL("=2+3*4", Value(14)); // row 70 0110 CHECK_EVAL("=6/3", Value(2)); // row 71 0111 CHECK_EVAL("=5/2", Value(2.5)); // row 72 0112 CHECK_EVAL("=ISERROR(1/0)", Value(true)); // row 73 0113 CHECK_EVAL("=2^3", Value(8)); // row 74 0114 CHECK_EVAL("=9^0.5", Value(3)); // row 75 0115 CHECK_EVAL("=(-5)^3", Value(-125)); // row 76 0116 CHECK_EVAL("=4^-1", Value(0.25)); // row 77 0117 CHECK_EVAL("=5^0", Value(1)); // row 78 0118 CHECK_EVAL("=0^5", Value(0)); // row 79 0119 CHECK_EVAL("=2+3*4^2", Value(50)); // row 80 0120 CHECK_EVAL("=-2^2", Value(4)); // row 81 0121 CHECK_EVAL("=1=1", Value(true)); // row 82 0122 CHECK_EVAL("=1=0", Value(false)); // row 84 0123 CHECK_EVAL("=3=3.0001", Value(false)); // row 85 0124 // Not passed for line 86. 0125 CHECK_EVAL("=\"Hi\"=\"Bye\"", Value(false)); // row 87 0126 CHECK_EVAL("=FALSE()=FALSE()", Value(true)); // row 88 0127 CHECK_EVAL("=TRUE()=FALSE()", Value(false)); // row 89 0128 CHECK_EVAL("=\"5\"=5", Value(false)); // row 90 0129 CHECK_EVAL("=TRUE()=1", Value(false)); // row 91 0130 // Not passed for line 92. 0131 // Not passed for line 93. 0132 CHECK_EVAL("=1<>1", Value(false)); // row 94 0133 CHECK_EVAL("=1<>2", Value(true)); // row 95 0134 CHECK_EVAL("=1<>\"1\"", Value(true)); // row 96 0135 // Not passed for line 97. 0136 CHECK_EVAL("=5<6", Value(true)); // row 98 0137 CHECK_EVAL("=5<=6", Value(true)); // row 99 0138 CHECK_EVAL("=5>6", Value(false)); // row 100 0139 CHECK_EVAL("=5>=6", Value(false)); // row 101 0140 CHECK_EVAL("=\"A\"<\"B\"", Value(true)); // row 102 0141 // Not passed for line 103. 0142 CHECK_EVAL("=\"AA\">\"A\"", Value(true)); // row 104 0143 CHECK_EVAL("=\"Hi \" & \"there\"", Value("Hi there")); // row 107 0144 CHECK_EVAL("=\"H\" & \"\"", Value("H")); // row 108 0145 // Not passed for line 109. 0146 CHECK_EVAL("=50%", Value(0.5)); // row 111 0147 CHECK_EVAL("=20+50%", Value(20.5)); // row 112 0148 CHECK_EVAL("=+5", Value(5)); // row 113 0149 CHECK_EVAL("=+\"Hello\"", Value("Hello")); // row 114 0150 CHECK_EVAL("=-\"7\"", Value(-7)); // row 116 0151 /* 0152 These are currently disabled, due to being locale specific. 0153 CHECK_EVAL("=DATE(2005;1;3)=DATEVALUE(\"2005-01-03\")", Value(true)); // row 118 0154 CHECK_EVAL("=DATE(2017.5; 1; 2)=DATEVALUE(\"2017-01-02\")", Value(true)); // row 119 0155 CHECK_EVAL("=DATE(2006; 2.5; 3)=DATEVALUE(\"2006-02-03\")", Value(true)); // row 120 0156 CHECK_EVAL("=DATE(2006; 1; 3.5)=DATEVALUE(\"2006-01-03\")", Value(true)); // row 121 0157 CHECK_EVAL("=DATE(2006; 13; 3)=DATEVALUE(\"2007-01-03\")", Value(true)); // row 122 0158 CHECK_EVAL("=DATE(2006; 1; 32)=DATEVALUE(\"2006-02-01\")", Value(true)); // row 123 0159 CHECK_EVAL("=DATE(2006; 25; 34)=DATEVALUE(\"2008-02-03\")", Value(true)); // row 124 0160 CHECK_EVAL("=DATE(2006;-1; 1)=DATEVALUE(\"2005-11-01\")", Value(true)); // row 125 0161 // Not passed for line 126. 0162 // Not passed for line 127. 0163 CHECK_EVAL("=DATE(2004;2;29)=DATEVALUE(\"2004-02-29\")", Value(true)); // row 128 0164 CHECK_EVAL("=DATE(2003;2;29)=DATEVALUE(\"2003-03-01\")", Value(true)); // row 129 0165 CHECK_EVAL("=DATE(1904; 1; 1)=DATEVALUE(\"1904-01-01\")", Value(true)); // row 130 0166 CHECK_EVAL("=DATEVALUE(\"2004-12-25\")=DATE(2004;12;25)", Value(true)); // row 131 0167 CHECK_EVAL("=DAY(\"2006-05-21\")", Value(21)); // row 132 0168 CHECK_EVAL("=DAY(\"5/21/2006\")", Value(21)); // row 133 0169 CHECK_EVAL("=DAY(\"05-21-2006\")", Value(21)); // row 134 0170 CHECK_EVAL("=DAY(\"5/21/06\")", Value(21)); // row 135 0171 CHECK_EVAL("=DAY(\"5-21-06\")", Value(21)); // row 136 0172 */ 0173 0174 } 0175 0176 void TestOpenFormula::testFormulaConversion() 0177 { 0178 // cell references 0179 CHECK_CONVERT("=A1", "=[.A1]"); 0180 CHECK_CONVERT("=A1:A4", "=[.A1:.A4]"); 0181 CHECK_CONVERT("=A$1:$A4", "=[.A$1:.$A4]"); 0182 CHECK_CONVERT("=Sheet2!A1", "=[Sheet2.A1]"); 0183 CHECK_CONVERT("='Sheet 2'!A1", "=['Sheet 2'.A1]"); 0184 CHECK_CONVERT("=Sheet2!A1:B4", "=[Sheet2.A1:Sheet2.B4]"); 0185 CHECK_CONVERT("='Sheet 2'!A1:B4", "=['Sheet 2'.A1:'Sheet 2'.B4]"); 0186 0187 // equality 0188 CHECK_CONVERT("=A1==A2", "=[.A1]=[.A2]"); 0189 0190 // strings 0191 CHECK_CONVERT("=\"2,2\"+2,1+\"2,0\"", "=\"2,2\"+2.1+\"2,0\""); 0192 0193 // decimal separator ',' 0194 CHECK_CONVERT("=,12", "=.12"); 0195 CHECK_CONVERT("=12,12", "=12.12"); 0196 CHECK_CONVERT("=368*7*(0,1738+0,1784)*(0,1738+0,1784)", "=368*7*(0.1738+0.1784)*(0.1738+0.1784)"); 0197 0198 // function names 0199 CHECK_CONVERT("=sum(A1;A2;A3;A4;A5)", "=sum([.A1];[.A2];[.A3];[.A4];[.A5])"); 0200 } 0201 0202 void TestOpenFormula::testReferenceLoading() 0203 { 0204 QCOMPARE(Odf::loadRegion(".A1"), QString("A1")); 0205 QCOMPARE(Odf::loadRegion(".A1:.A4"), QString("A1:A4")); 0206 QCOMPARE(Odf::loadRegion(".A$1:.$A4"), QString("A$1:$A4")); 0207 QCOMPARE(Odf::loadRegion("Sheet2.A1"), QString("Sheet2!A1")); 0208 QCOMPARE(Odf::loadRegion("'Sheet 2'.A1"), QString("'Sheet 2'!A1")); 0209 QCOMPARE(Odf::loadRegion("Sheet2.A1:Sheet2.B4"), QString("Sheet2!A1:B4")); 0210 QCOMPARE(Odf::loadRegion("'Sheet 2'.A1:'Sheet 2'.B4"), QString("'Sheet 2'!A1:B4")); 0211 QCOMPARE(Odf::loadRegion("$Sheet2.A1:$Sheet2.B4"), QString("Sheet2!A1:B4")); 0212 QCOMPARE(Odf::loadRegion("$'Sheet 2'.A1:$'Sheet 2'.B4"), QString("'Sheet 2'!A1:B4")); 0213 } 0214 0215 void TestOpenFormula::testReferenceSaving() 0216 { 0217 QCOMPARE(Odf::saveRegion("A1"), QString(".A1")); 0218 QCOMPARE(Odf::saveRegion("A1:A4"), QString(".A1:.A4")); 0219 QCOMPARE(Odf::saveRegion("A$1:$A4"), QString(".A$1:.$A4")); 0220 QCOMPARE(Odf::saveRegion("Sheet2!A1"), QString("Sheet2.A1")); 0221 QCOMPARE(Odf::saveRegion("'Sheet 2'!A1"), QString("'Sheet 2'.A1")); 0222 QCOMPARE(Odf::saveRegion("Sheet2!A1:B4"), QString("Sheet2.A1:Sheet2.B4")); 0223 QCOMPARE(Odf::saveRegion("'Sheet 2'!A1:B4"), QString("'Sheet 2'.A1:'Sheet 2'.B4")); 0224 } 0225 0226 QTEST_MAIN(TestOpenFormula)