File indexing completed on 2024-04-28 07:36:22
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)