File indexing completed on 2024-04-14 03:46:33

0001 /*
0002     File                 : SpreadsheetFormulaTest.cpp
0003     Project              : LabPlot
0004     Description          : Tests for formula in spreadsheets
0005     --------------------------------------------------------------------
0006     SPDX-FileCopyrightText: 2022 Stefan Gerlach <stefan.gerlach@uni.kn>
0007 
0008     SPDX-License-Identifier: GPL-2.0-or-later
0009 */
0010 
0011 #include "SpreadsheetFormulaTest.h"
0012 #include "backend/lib/macros.h"
0013 #include "backend/spreadsheet/Spreadsheet.h"
0014 #include "commonfrontend/spreadsheet/SpreadsheetView.h"
0015 
0016 #include <QClipboard>
0017 
0018 #define INIT_SPREADSHEET                                                                                                                                       \
0019     Spreadsheet sheet(QStringLiteral("test 2 cols"), false);                                                                                                   \
0020     const int cols = 2;                                                                                                                                        \
0021     const int rows = 100;                                                                                                                                      \
0022                                                                                                                                                                \
0023     sheet.setColumnCount(cols);                                                                                                                                \
0024     sheet.setRowCount(rows);                                                                                                                                   \
0025                                                                                                                                                                \
0026     SpreadsheetView view(&sheet, false);                                                                                                                       \
0027     view.selectColumn(0);                                                                                                                                      \
0028     view.fillWithRowNumbers();                                                                                                                                 \
0029                                                                                                                                                                \
0030     QStringList variableNames;                                                                                                                                 \
0031     variableNames << QLatin1String("x");                                                                                                                       \
0032     QVector<Column*> variableColumns;                                                                                                                          \
0033     variableColumns << sheet.column(0);                                                                                                                        \
0034     sheet.column(1)->setFormulaVariableColumn(sheet.column(0));
0035 
0036 #define INIT_SPREADSHEET2                                                                                                                                      \
0037     Spreadsheet sheet(QStringLiteral("test 3 cols"), false);                                                                                                   \
0038     const int cols = 3;                                                                                                                                        \
0039     const int rows = 100;                                                                                                                                      \
0040                                                                                                                                                                \
0041     sheet.setColumnCount(cols);                                                                                                                                \
0042     sheet.setRowCount(rows);                                                                                                                                   \
0043                                                                                                                                                                \
0044     SpreadsheetView view(&sheet, false);                                                                                                                       \
0045     view.selectColumn(0);                                                                                                                                      \
0046     view.fillWithRowNumbers();                                                                                                                                 \
0047     for (int i = 0; i < rows; i++)                                                                                                                             \
0048         sheet.column(1)->setValueAt(i, 1.);                                                                                                                    \
0049                                                                                                                                                                \
0050     QStringList variableNames;                                                                                                                                 \
0051     variableNames << QStringLiteral("x") << QStringLiteral("y");                                                                                               \
0052     QVector<Column*> variableColumns;                                                                                                                          \
0053     variableColumns << sheet.column(0) << sheet.column(1);                                                                                                     \
0054     sheet.column(2)->setFormulaVariableColumn(sheet.column(0));                                                                                                \
0055     sheet.column(2)->setFormulaVariableColumn(sheet.column(1));
0056 
0057 //**********************************************************
0058 //********** Check different formulas **********************
0059 //**********************************************************
0060 /*!
0061    formula "1"
0062 */
0063 void SpreadsheetFormulaTest::formula1() {
0064     INIT_SPREADSHEET
0065 
0066     sheet.column(1)->setFormula(QLatin1String("1"), variableNames, variableColumns, true);
0067     sheet.column(1)->updateFormula();
0068 
0069     // spreadsheet size
0070     QCOMPARE(sheet.columnCount(), cols);
0071     QCOMPARE(sheet.rowCount(), rows);
0072 
0073     // column modes
0074     QCOMPARE(sheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer);
0075     QCOMPARE(sheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0076 
0077     // values
0078     for (int i = 0; i < rows; i++) {
0079         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0080         QCOMPARE(sheet.column(1)->valueAt(i), 1);
0081     }
0082 }
0083 /*!
0084    formula "x"
0085 */
0086 void SpreadsheetFormulaTest::formulax() {
0087     INIT_SPREADSHEET
0088 
0089     sheet.column(1)->setFormula(QLatin1String("x"), variableNames, variableColumns, true);
0090     sheet.column(1)->updateFormula();
0091 
0092     // values
0093     for (int i = 0; i < rows; i++) {
0094         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0095         QCOMPARE(sheet.column(1)->valueAt(i), i + 1);
0096     }
0097 }
0098 /*!
0099    formula "x + 1"
0100 */
0101 void SpreadsheetFormulaTest::formulaxp1() {
0102     INIT_SPREADSHEET
0103 
0104     sheet.column(1)->setFormula(QLatin1String("x+1"), variableNames, variableColumns, true);
0105     sheet.column(1)->updateFormula();
0106 
0107     // values
0108     for (int i = 0; i < rows; i++) {
0109         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0110         QCOMPARE(sheet.column(1)->valueAt(i), i + 2);
0111     }
0112 }
0113 //**********************************************************
0114 //********** Check different cell() formulas ***************
0115 //**********************************************************
0116 /*!
0117    formula "cell(1, x)"
0118 */
0119 void SpreadsheetFormulaTest::formulaCell1() {
0120     INIT_SPREADSHEET
0121 
0122     sheet.column(1)->setFormula(QLatin1String("cell(1; x)"), variableNames, variableColumns, true);
0123     sheet.column(1)->updateFormula();
0124 
0125     // values
0126     for (int i = 0; i < rows; i++) {
0127         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0128         QCOMPARE(sheet.column(1)->valueAt(i), 1);
0129     }
0130 }
0131 /*!
0132    formula "cell(i, x)"
0133 */
0134 void SpreadsheetFormulaTest::formulaCelli() {
0135     INIT_SPREADSHEET
0136 
0137     sheet.column(1)->setFormula(QLatin1String("cell(i; x)"), variableNames, variableColumns, true);
0138     sheet.column(1)->updateFormula();
0139 
0140     // values
0141     for (int i = 0; i < rows; i++) {
0142         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0143         QCOMPARE(sheet.column(1)->valueAt(i), i + 1);
0144     }
0145 }
0146 /*!
0147    formula "cell(i + 1, x)"
0148 */
0149 void SpreadsheetFormulaTest::formulaCellip1() {
0150     INIT_SPREADSHEET
0151 
0152     sheet.column(1)->setFormula(QLatin1String("cell(i + 1; x)"), variableNames, variableColumns, true);
0153     sheet.column(1)->updateFormula();
0154 
0155     // values
0156     for (int i = 0; i < rows; i++) {
0157         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0158         if (i < rows - 1)
0159             QCOMPARE(sheet.column(1)->valueAt(i), i + 2);
0160         else
0161             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0162     }
0163 }
0164 /*!
0165    formula "cell(i - 1, x)"
0166 */
0167 void SpreadsheetFormulaTest::formulaCellim1() {
0168     INIT_SPREADSHEET
0169 
0170     sheet.column(1)->setFormula(QLatin1String("cell(i - 1; x)"), variableNames, variableColumns, true);
0171     sheet.column(1)->updateFormula();
0172 
0173     // values
0174     for (int i = 0; i < rows; i++) {
0175         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0176         if (i > 0)
0177             QCOMPARE(sheet.column(1)->valueAt(i), i);
0178         else
0179             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0180     }
0181 }
0182 /*!
0183    formula "cell(2*i, x)"
0184 */
0185 void SpreadsheetFormulaTest::formulaCell2i() {
0186     INIT_SPREADSHEET
0187 
0188     sheet.column(1)->setFormula(QLatin1String("cell(2*i; x)"), variableNames, variableColumns, true);
0189     sheet.column(1)->updateFormula();
0190 
0191     // values
0192     for (int i = 0; i < rows; i++) {
0193         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0194         if (i < rows / 2)
0195             QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1));
0196         else
0197             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0198     }
0199 }
0200 /*!
0201    formula "cell(i+1, x) - cell(i-1, x)"
0202 */
0203 void SpreadsheetFormulaTest::formulaCellip1im1() {
0204     INIT_SPREADSHEET
0205 
0206     sheet.column(1)->setFormula(QLatin1String("cell(i+1; x) - cell(i-1; x)"), variableNames, variableColumns, true);
0207     sheet.column(1)->updateFormula();
0208 
0209     // values
0210     for (int i = 0; i < rows; i++) {
0211         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0212         if (i > 0 && i < rows - 1)
0213             QCOMPARE(sheet.column(1)->valueAt(i), 2);
0214         else
0215             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0216     }
0217 }
0218 /*!
0219    formula "sqrt(cell(i+1, x))"
0220 */
0221 void SpreadsheetFormulaTest::formulaCellsqrtip1() {
0222     INIT_SPREADSHEET
0223 
0224     sheet.column(1)->setFormula(QLatin1String("sqrt(cell(i+1; x))"), variableNames, variableColumns, true);
0225     sheet.column(1)->updateFormula();
0226 
0227     // values
0228     for (int i = 0; i < rows; i++) {
0229         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0230         if (i < rows - 1)
0231             QCOMPARE(sheet.column(1)->valueAt(i), std::sqrt(i + 2));
0232         else
0233             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0234     }
0235 }
0236 
0237 /*!
0238    formula "cell(1, 2*x)"
0239 */
0240 void SpreadsheetFormulaTest::formulaCell1_2x() {
0241     INIT_SPREADSHEET
0242 
0243     sheet.column(1)->setFormula(QLatin1String("2*cell(1; x)"), variableNames, variableColumns, true);
0244     sheet.column(1)->updateFormula();
0245 
0246     // values
0247     for (int i = 0; i < rows; i++) {
0248         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0249         QCOMPARE(sheet.column(1)->valueAt(i), 2);
0250     }
0251 }
0252 /*!
0253    formula "cell(i, 2*x)"
0254 */
0255 void SpreadsheetFormulaTest::formulaCelli_2x() {
0256     INIT_SPREADSHEET
0257 
0258     sheet.column(1)->setFormula(QLatin1String("cell(i; x) * 2"), variableNames, variableColumns, true);
0259     sheet.column(1)->updateFormula();
0260 
0261     // values
0262     for (int i = 0; i < rows; i++) {
0263         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0264         QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1));
0265     }
0266 }
0267 /*!
0268    formula "cell(1, x+x)"
0269 */
0270 void SpreadsheetFormulaTest::formulaCelli_xpx() {
0271     INIT_SPREADSHEET
0272 
0273     sheet.column(1)->setFormula(QLatin1String("cell(i; x) * 2"), variableNames, variableColumns, true);
0274     sheet.column(1)->updateFormula();
0275 
0276     // values
0277     for (int i = 0; i < rows; i++) {
0278         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0279         QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1));
0280     }
0281 }
0282 /*!
0283    formula "cell(i, x + 2*x)"
0284 */
0285 void SpreadsheetFormulaTest::formulaCelli_xp2x() {
0286     INIT_SPREADSHEET
0287 
0288     sheet.column(1)->setFormula(QLatin1String("3 * cell(i; x)"), variableNames, variableColumns, true);
0289     sheet.column(1)->updateFormula();
0290 
0291     // values
0292     for (int i = 0; i < rows; i++) {
0293         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0294         QCOMPARE(sheet.column(1)->valueAt(i), 3 * (i + 1));
0295     }
0296 }
0297 /*!
0298    formula "cell(i, sqrt(x))"
0299 */
0300 void SpreadsheetFormulaTest::formulaCelli_sqrtx() {
0301     INIT_SPREADSHEET
0302 
0303     sheet.column(1)->setFormula(QLatin1String("sqrt(cell(i; x))"), variableNames, variableColumns, true);
0304     sheet.column(1)->updateFormula();
0305 
0306     // values
0307     for (int i = 0; i < rows; i++) {
0308         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0309         QCOMPARE(sheet.column(1)->valueAt(i), std::sqrt(i + 1));
0310     }
0311 }
0312 /*!
0313    formula "cell(i, x+y)"
0314 */
0315 void SpreadsheetFormulaTest::formulaCelli_xpy() {
0316     INIT_SPREADSHEET2
0317 
0318     sheet.column(2)->setFormula(QLatin1String("cell(i; x) + cell(i; y)"), variableNames, variableColumns, true);
0319     sheet.column(2)->updateFormula();
0320 
0321     // values
0322     for (int i = 0; i < rows; i++) {
0323         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0324         QCOMPARE(sheet.column(1)->valueAt(i), 1);
0325         QCOMPARE(sheet.column(2)->valueAt(i), i + 2);
0326     }
0327 }
0328 
0329 /*!
0330    formula "cell(2*i, x+y)"
0331 */
0332 void SpreadsheetFormulaTest::formulaCell2i_xpy() {
0333     INIT_SPREADSHEET2
0334 
0335     sheet.column(2)->setFormula(QLatin1String("cell(2*i; x) + cell(2*i; y)"), variableNames, variableColumns, true);
0336     sheet.column(2)->updateFormula();
0337 
0338     // values
0339     for (int i = 0; i < rows; i++) {
0340         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0341         QCOMPARE(sheet.column(1)->valueAt(i), 1);
0342         if (i < rows / 2)
0343             QCOMPARE(sheet.column(2)->valueAt(i), sheet.column(0)->valueAt(2 * i + 1) + sheet.column(1)->valueAt(2 * i + 1));
0344         else
0345             QCOMPARE(sheet.column(2)->valueAt(i), NAN);
0346     }
0347 }
0348 /*!
0349    formula "cell(i, 2*x) + cell (i, 2*y)"
0350 */
0351 void SpreadsheetFormulaTest::formulaCelli_2xpCelli_2y() {
0352     INIT_SPREADSHEET2
0353 
0354     sheet.column(2)->setFormula(QLatin1String("2*cell(i; x) + cell(i; y) * 2"), variableNames, variableColumns, true);
0355     sheet.column(2)->updateFormula();
0356 
0357     // values
0358     for (int i = 0; i < rows; i++) {
0359         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0360         QCOMPARE(sheet.column(1)->valueAt(i), 1);
0361         QCOMPARE(sheet.column(2)->valueAt(i), 2 * sheet.column(0)->valueAt(i) + 2 * sheet.column(1)->valueAt(i));
0362     }
0363 }
0364 
0365 ///////////////////////// check group separator problem /////////////////////
0366 
0367 void SpreadsheetFormulaTest::formulaLocale() {
0368     Spreadsheet sheet(QStringLiteral("test"), false);
0369     const int cols = 2;
0370     const int rows = 3;
0371     const QVector<double> xData{13000, 14000, 15000};
0372 
0373     sheet.setColumnCount(cols);
0374     sheet.setRowCount(rows);
0375     auto* col0{sheet.column(0)};
0376     col0->replaceValues(0, xData);
0377 
0378     SpreadsheetView view(&sheet, false);
0379     view.selectColumn(0);
0380 
0381     QStringList variableNames;
0382     variableNames << QLatin1String("x");
0383     QVector<Column*> variableColumns;
0384     variableColumns << sheet.column(0);
0385     sheet.column(1)->setFormulaVariableColumn(sheet.column(0));
0386 
0387     sheet.column(1)->setFormula(QLatin1String("mean(x)"), variableNames, variableColumns, true);
0388     sheet.column(1)->updateFormula();
0389 
0390     // values
0391     for (int i = 0; i < rows; i++)
0392         QCOMPARE(sheet.column(1)->valueAt(i), sheet.column(0)->valueAt(1));
0393 }
0394 
0395 ///////////////////////// more methods /////////////////////
0396 
0397 /*!
0398    formula "ma(x)"
0399 */
0400 void SpreadsheetFormulaTest::formulama() {
0401     INIT_SPREADSHEET
0402 
0403     sheet.column(1)->setFormula(QLatin1String("ma(x)"), variableNames, variableColumns, true);
0404     sheet.column(1)->updateFormula();
0405 
0406     // values
0407     for (int i = 0; i < rows; i++) {
0408         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0409         if (i > 0)
0410             QCOMPARE(sheet.column(1)->valueAt(i), i + .5);
0411         else
0412             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0413     }
0414 }
0415 /*!
0416    formula "mr(x)"
0417 */
0418 void SpreadsheetFormulaTest::formulamr() {
0419     INIT_SPREADSHEET
0420 
0421     sheet.column(1)->setFormula(QLatin1String("mr(x)"), variableNames, variableColumns, true);
0422     sheet.column(1)->updateFormula();
0423 
0424     // values
0425     for (int i = 0; i < rows; i++) {
0426         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0427         if (i > 0)
0428             QCOMPARE(sheet.column(1)->valueAt(i), 1);
0429         else
0430             QCOMPARE(sheet.column(1)->valueAt(i), NAN);
0431     }
0432 }
0433 /*!
0434    formula "sma(n, x)"
0435 */
0436 void SpreadsheetFormulaTest::formulasma() {
0437     INIT_SPREADSHEET
0438 
0439     sheet.column(1)->setFormula(QLatin1String("sma(4; x)"), variableNames, variableColumns, true);
0440     sheet.column(1)->updateFormula();
0441 
0442     // values
0443     const int N = 4;
0444     for (int i = 0; i < rows; i++) {
0445         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0446         double value = 0.;
0447         for (int index = std::max(0, i - N + 1); index <= i; index++)
0448             value += sheet.column(0)->valueAt(index);
0449         QCOMPARE(sheet.column(1)->valueAt(i), value / N);
0450     }
0451 }
0452 /*!
0453    formula "smr(n, x)"
0454 */
0455 void SpreadsheetFormulaTest::formulasmr() {
0456     INIT_SPREADSHEET
0457 
0458     sheet.column(1)->setFormula(QLatin1String("smr(4; x)"), variableNames, variableColumns, true);
0459     sheet.column(1)->updateFormula();
0460 
0461     // values
0462     QCOMPARE(sheet.column(1)->valueAt(0), 0);
0463     QCOMPARE(sheet.column(1)->valueAt(1), 1);
0464     QCOMPARE(sheet.column(1)->valueAt(2), 2);
0465     for (int i = 3; i < rows; i++) {
0466         QCOMPARE(sheet.column(0)->valueAt(i), i + 1);
0467         QCOMPARE(sheet.column(1)->valueAt(i), 3);
0468     }
0469 }
0470 
0471 // ##############################################################################
0472 // ######### check updates of columns defined via a formula on changes ##########
0473 // ##############################################################################
0474 void SpreadsheetFormulaTest::formulaUpdateAfterCellChange() {
0475     INIT_SPREADSHEET
0476 
0477     auto* col1 = sheet.column(0);
0478     auto* col2 = sheet.column(1);
0479     col2->setFormula(QLatin1String("x"), variableNames, variableColumns, true);
0480     col2->updateFormula();
0481 
0482     // values
0483     for (int i = 0; i < rows; i++) {
0484         QCOMPARE(col1->valueAt(i), i + 1);
0485         QCOMPARE(col2->valueAt(i), i + 1);
0486     }
0487 
0488     // modify the first cell in the first column and check the updated values
0489     col1->setIntegerAt(0, 5);
0490 
0491     // check the first row
0492     QCOMPARE(col1->valueAt(0), 5);
0493     QCOMPARE(col2->valueAt(0), 5);
0494 
0495     // check the remaining rows
0496     for (int i = 1; i < rows; i++) {
0497         QCOMPARE(col1->valueAt(i), i + 1);
0498         QCOMPARE(col2->valueAt(i), i + 1);
0499     }
0500 }
0501 
0502 void SpreadsheetFormulaTest::formulaUpdateAfterPaste() {
0503     INIT_SPREADSHEET
0504 
0505     auto* col1 = sheet.column(0);
0506     auto* col2 = sheet.column(1);
0507     col2->setFormula(QLatin1String("x"), variableNames, variableColumns, true);
0508     col2->updateFormula();
0509 
0510     // values
0511     for (int i = 0; i < rows; i++) {
0512         QCOMPARE(col1->valueAt(i), i + 1);
0513         QCOMPARE(col2->valueAt(i), i + 1);
0514     }
0515 
0516     // paste three values into the first column and check the updated values
0517     const QString str = QStringLiteral("10\n20\n30");
0518     QApplication::clipboard()->setText(str);
0519     SpreadsheetView viewToPaste(&sheet, false);
0520     viewToPaste.pasteIntoSelection();
0521 
0522     // check the first three rows
0523     QCOMPARE(col1->valueAt(0), 10);
0524     QCOMPARE(col2->valueAt(0), 10);
0525     QCOMPARE(col1->valueAt(1), 20);
0526     QCOMPARE(col2->valueAt(1), 20);
0527     QCOMPARE(col1->valueAt(2), 30);
0528     QCOMPARE(col2->valueAt(2), 30);
0529 
0530     // check the remaining rows
0531     for (int i = 3; i < rows; i++) {
0532         QCOMPARE(col1->valueAt(i), i + 1);
0533         QCOMPARE(col2->valueAt(i), i + 1);
0534     }
0535 }
0536 
0537 QTEST_MAIN(SpreadsheetFormulaTest)