File indexing completed on 2025-01-19 13:27:33 

0001 /*
0002  * This file is part of Office 2007 Filters for Calligra
0003  *
0004  * Copyright (C) 2010 Sebastian Sauer <sebsauer@kdab.com>
0005  * Copyright (C) 2009-2010 Nokia Corporation and/or its subsidiary(-ies).
0006  * Copyright (C) 2010 Marijn Kruisselbrink <mkruisselbrink@kde.org>
0007  *
0008  * Contact: Suresh Chande suresh.chande@nokia.com
0009  *
0010  * This library is free software; you can redistribute it and/or
0011  * modify it under the terms of the GNU Lesser General Public License
0012  * version 2.1 as published by the Free Software Foundation.
0013  *
0014  * This library is distributed in the hope that it will be useful, but
0015  * WITHOUT ANY WARRANTY; without even the implied warranty of
0016  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0017  * Lesser General Public License for more details.
0018  *
0019  * You should have received a copy of the GNU Lesser General Public
0020  * License along with this library; if not, write to the Free Software
0021  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
0022  * 02110-1301 USA
0023  *
0024  */
0025 #include "TestFormulaParser.h"
0026 
0027 #include <QTest>
0028 
0029 #include "FormulaParser.h"
0030 #include "XlsxXmlWorksheetReader_p.h"
0031 #include <sheets/Util.h>
0032 
0033 void TestFormulaParser::testConvertFormula_data()
0034 {
0035     QTest::addColumn<QString>("xlsx");
0036     QTest::addColumn<QString>("odf");
0037 
0038     QTest::newRow("simple")
0039         << "A1"
0040         << "=A1";
0041     QTest::newRow("argument delimiter")
0042         << "IF(A1=A2,1,2)"
0043         << "=IF(A1=A2;1;2)";
0044     QTest::newRow("string")
0045         << "LEFT(\" Some   ~text \",3)"
0046         << "=LEFT(\" Some   ~text \";3)";
0047     QTest::newRow("condition")
0048         << "IF(A15<$B$6*$B$4,A15+1,\"\")"
0049         << "=IF(A15<$B$6*$B$4;A15+1;\"\")";
0050     QTest::newRow("union operator")
0051         << "AREAS((A1:A3,B3:C5))"
0052         << "=AREAS((A1:A3~B3:C5))";
0053     QTest::newRow("nested function calls")
0054         << "IF(OR(C12=\"\",D12=\"\"),\"\",IF(C12=D12,\"Pass\",\"Fail\"))"
0055         << "=IF(OR(C12=\"\";D12=\"\");\"\";IF(C12=D12;\"Pass\";\"Fail\"))";
0056     QTest::newRow("intersection operator")
0057         << "AREAS((A1:C5 B2:B3))"
0058         << "=AREAS((A1:C5!B2:B3))";
0059     QTest::newRow("whitespace in normal arguments")
0060         << "IF(A1=A2, 2, \" IF(1,2) \")"
0061         << "=IF(A1=A2; 2; \" IF(1,2) \")";
0062     QTest::newRow("multiple whitespace in normal arguments")
0063         << "IF(A1=A2 ,   2  , \" IF(1,2) \")"
0064         << "=IF(A1=A2 ;   2  ; \" IF(1,2) \")";
0065     QTest::newRow("mixing union and intersection")
0066         << "AREAS((A1:C5 B2:B3,C2:C3))"
0067         << "=AREAS((A1:C5!B2:B3~C2:C3))";
0068     QTest::newRow("absolute positions")
0069         << "AREAS(($A$1:$A$3,$B3:C$5))"
0070         << "=AREAS(($A$1:$A$3~$B3:C$5))";
0071     QTest::newRow("whole column")
0072         << "IF(A=B,A:B,2)"
0073         << "=IF(A=B;A$1:B$65536;2)";
0074     QTest::newRow("Sheetname")
0075         << "=IF('Sheet 1'!A1,''Sheet '1''!A2,'''Sheet 1'''!A3"
0076         << "=IF('Sheet 1'!A1;'Sheet ''1'!A2;'Sheet 1'!A3";
0077     QTest::newRow("intersection operator without extra parenthesis")
0078         << "AREAS(B2:D4 B2)"
0079         << "=AREAS(B2:D4!B2)";
0080     QTest::newRow("intersection operator without extra parenthesis, extra whitespace")
0081         << "AREAS(B2:D4    B2)"
0082         << "=AREAS(B2:D4!   B2)";
0083 }
0084 
0085 void TestFormulaParser::testConvertFormula()
0086 {
0087     QFETCH(QString, xlsx);
0088     QFETCH(QString, odf);
0089 
0090     QCOMPARE(Calligra::Sheets::MSOOXML::convertFormula(xlsx), odf);
0091 }
0092 
0093 void TestFormulaParser::testSharedFormulaReferences()
0094 {
0095     Sheet s1("Sheet1");
0096     Cell* c1 = s1.cell(2, 5, true);
0097     c1->formula = new FormulaImpl("=D6-E7");
0098     Cell* c2 = s1.cell(12, 43, true);
0099     QCOMPARE(MSOOXML::convertFormulaReference(c1, c2), QString("=N44-O45"));
0100 
0101     static_cast<FormulaImpl*>(c1->formula)->m_formula = "=SUM(D6-E7)";
0102     QCOMPARE(MSOOXML::convertFormulaReference(c1, c2), QString("=SUM(N44-O45)"));
0103 
0104     static_cast<FormulaImpl*>(c1->formula)->m_formula = "=D6";
0105     QCOMPARE(MSOOXML::convertFormulaReference(c1, c2), QString("=N44"));
0106 
0107     static_cast<FormulaImpl*>(c1->formula)->m_formula = "=SUM(D6)";
0108     QCOMPARE(MSOOXML::convertFormulaReference(c1, c2), QString("=SUM(N44)"));
0109 
0110     static_cast<FormulaImpl*>(c1->formula)->m_formula = "=F8(H12)";
0111     QCOMPARE(MSOOXML::convertFormulaReference(c1, c2), QString("=F8(R50)"));
0112 }
0113 
0114 QTEST_GUILESS_MAIN(TestFormulaParser)