Warning, file /education/labplot/tests/spreadsheet/SpreadsheetFormulaTest.cpp was not indexed or was modified since last indexation (in which case cross-reference links may be missing, inaccurate or erroneous).
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 #define INIT_SPREADSHEET \ 0017 Spreadsheet sheet(QStringLiteral("test 2 cols"), false); \ 0018 const int cols = 2; \ 0019 const int rows = 100; \ 0020 \ 0021 sheet.setColumnCount(cols); \ 0022 sheet.setRowCount(rows); \ 0023 \ 0024 SpreadsheetView view(&sheet, false); \ 0025 view.selectColumn(0); \ 0026 view.fillWithRowNumbers(); \ 0027 \ 0028 QStringList variableNames; \ 0029 variableNames << QLatin1String("x"); \ 0030 QVector<Column*> variableColumns; \ 0031 variableColumns << sheet.column(0); \ 0032 sheet.column(1)->setFormulaVariableColumn(sheet.column(0)); 0033 0034 #define INIT_SPREADSHEET2 \ 0035 Spreadsheet sheet(QStringLiteral("test 3 cols"), false); \ 0036 const int cols = 3; \ 0037 const int rows = 100; \ 0038 \ 0039 sheet.setColumnCount(cols); \ 0040 sheet.setRowCount(rows); \ 0041 \ 0042 SpreadsheetView view(&sheet, false); \ 0043 view.selectColumn(0); \ 0044 view.fillWithRowNumbers(); \ 0045 for (int i = 0; i < rows; i++) \ 0046 sheet.column(1)->setValueAt(i, 1.); \ 0047 \ 0048 QStringList variableNames; \ 0049 variableNames << QStringLiteral("x") << QStringLiteral("y"); \ 0050 QVector<Column*> variableColumns; \ 0051 variableColumns << sheet.column(0) << sheet.column(1); \ 0052 sheet.column(2)->setFormulaVariableColumn(sheet.column(0)); \ 0053 sheet.column(2)->setFormulaVariableColumn(sheet.column(1)); 0054 0055 //********************************************************** 0056 //********** Check different formulas ********************** 0057 //********************************************************** 0058 /*! 0059 formula "1" 0060 */ 0061 void SpreadsheetFormulaTest::formula1() { 0062 INIT_SPREADSHEET 0063 0064 sheet.column(1)->setFormula(QLatin1String("1"), variableNames, variableColumns, true); 0065 sheet.column(1)->updateFormula(); 0066 0067 // spreadsheet size 0068 QCOMPARE(sheet.columnCount(), cols); 0069 QCOMPARE(sheet.rowCount(), rows); 0070 0071 // column modes 0072 QCOMPARE(sheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer); 0073 QCOMPARE(sheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double); 0074 0075 // values 0076 for (int i = 0; i < rows; i++) { 0077 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0078 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0079 } 0080 } 0081 /*! 0082 formula "x" 0083 */ 0084 void SpreadsheetFormulaTest::formulax() { 0085 INIT_SPREADSHEET 0086 0087 sheet.column(1)->setFormula(QLatin1String("x"), variableNames, variableColumns, true); 0088 sheet.column(1)->updateFormula(); 0089 0090 // values 0091 for (int i = 0; i < rows; i++) { 0092 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0093 QCOMPARE(sheet.column(1)->valueAt(i), i + 1); 0094 } 0095 } 0096 /*! 0097 formula "x + 1" 0098 */ 0099 void SpreadsheetFormulaTest::formulaxp1() { 0100 INIT_SPREADSHEET 0101 0102 sheet.column(1)->setFormula(QLatin1String("x+1"), variableNames, variableColumns, true); 0103 sheet.column(1)->updateFormula(); 0104 0105 // values 0106 for (int i = 0; i < rows; i++) { 0107 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0108 QCOMPARE(sheet.column(1)->valueAt(i), i + 2); 0109 } 0110 } 0111 //********************************************************** 0112 //********** Check different cell() formulas *************** 0113 //********************************************************** 0114 /*! 0115 formula "cell(1, x)" 0116 */ 0117 void SpreadsheetFormulaTest::formulaCell1() { 0118 INIT_SPREADSHEET 0119 0120 sheet.column(1)->setFormula(QLatin1String("cell(1, x)"), variableNames, variableColumns, true); 0121 sheet.column(1)->updateFormula(); 0122 0123 // values 0124 for (int i = 0; i < rows; i++) { 0125 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0126 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0127 } 0128 } 0129 /*! 0130 formula "cell(i, x)" 0131 */ 0132 void SpreadsheetFormulaTest::formulaCelli() { 0133 INIT_SPREADSHEET 0134 0135 sheet.column(1)->setFormula(QLatin1String("cell(i, x)"), variableNames, variableColumns, true); 0136 sheet.column(1)->updateFormula(); 0137 0138 // values 0139 for (int i = 0; i < rows; i++) { 0140 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0141 QCOMPARE(sheet.column(1)->valueAt(i), i + 1); 0142 } 0143 } 0144 /*! 0145 formula "cell(i + 1, x)" 0146 */ 0147 void SpreadsheetFormulaTest::formulaCellip1() { 0148 INIT_SPREADSHEET 0149 0150 sheet.column(1)->setFormula(QLatin1String("cell(i + 1, x)"), variableNames, variableColumns, true); 0151 sheet.column(1)->updateFormula(); 0152 0153 // values 0154 for (int i = 0; i < rows; i++) { 0155 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0156 if (i < rows - 1) 0157 QCOMPARE(sheet.column(1)->valueAt(i), i + 2); 0158 else 0159 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0160 } 0161 } 0162 /*! 0163 formula "cell(i - 1, x)" 0164 */ 0165 void SpreadsheetFormulaTest::formulaCellim1() { 0166 INIT_SPREADSHEET 0167 0168 sheet.column(1)->setFormula(QLatin1String("cell(i - 1, x)"), variableNames, variableColumns, true); 0169 sheet.column(1)->updateFormula(); 0170 0171 // values 0172 for (int i = 0; i < rows; i++) { 0173 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0174 if (i > 0) 0175 QCOMPARE(sheet.column(1)->valueAt(i), i); 0176 else 0177 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0178 } 0179 } 0180 /*! 0181 formula "cell(2*i, x)" 0182 */ 0183 void SpreadsheetFormulaTest::formulaCell2i() { 0184 INIT_SPREADSHEET 0185 0186 sheet.column(1)->setFormula(QLatin1String("cell(2*i, x)"), variableNames, variableColumns, true); 0187 sheet.column(1)->updateFormula(); 0188 0189 // values 0190 for (int i = 0; i < rows; i++) { 0191 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0192 if (i < rows / 2) 0193 QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1)); 0194 else 0195 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0196 } 0197 } 0198 /*! 0199 formula "cell(i+1, x) - cell(i-1, x)" 0200 */ 0201 void SpreadsheetFormulaTest::formulaCellip1im1() { 0202 INIT_SPREADSHEET 0203 0204 sheet.column(1)->setFormula(QLatin1String("cell(i+1, x) - cell(i-1, x)"), variableNames, variableColumns, true); 0205 sheet.column(1)->updateFormula(); 0206 0207 // values 0208 for (int i = 0; i < rows; i++) { 0209 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0210 if (i > 0 && i < rows - 1) 0211 QCOMPARE(sheet.column(1)->valueAt(i), 2); 0212 else 0213 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0214 } 0215 } 0216 /*! 0217 formula "sqrt(cell(i+1, x))" 0218 */ 0219 void SpreadsheetFormulaTest::formulaCellsqrtip1() { 0220 INIT_SPREADSHEET 0221 0222 sheet.column(1)->setFormula(QLatin1String("sqrt(cell(i+1, x))"), variableNames, variableColumns, true); 0223 sheet.column(1)->updateFormula(); 0224 0225 // values 0226 for (int i = 0; i < rows; i++) { 0227 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0228 if (i < rows - 1) 0229 QCOMPARE(sheet.column(1)->valueAt(i), std::sqrt(i + 2)); 0230 else 0231 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0232 } 0233 } 0234 0235 /*! 0236 formula "cell(1, 2*x)" 0237 */ 0238 void SpreadsheetFormulaTest::formulaCell1_2x() { 0239 INIT_SPREADSHEET 0240 0241 sheet.column(1)->setFormula(QLatin1String("cell(1, 2*x)"), variableNames, variableColumns, true); 0242 sheet.column(1)->updateFormula(); 0243 0244 // values 0245 for (int i = 0; i < rows; i++) { 0246 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0247 QCOMPARE(sheet.column(1)->valueAt(i), 2); 0248 } 0249 } 0250 /*! 0251 formula "cell(i, 2*x)" 0252 */ 0253 void SpreadsheetFormulaTest::formulaCelli_2x() { 0254 INIT_SPREADSHEET 0255 0256 sheet.column(1)->setFormula(QLatin1String("cell(i, 2*x)"), variableNames, variableColumns, true); 0257 sheet.column(1)->updateFormula(); 0258 0259 // values 0260 for (int i = 0; i < rows; i++) { 0261 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0262 QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1)); 0263 } 0264 } 0265 /*! 0266 formula "cell(1, x+x)" 0267 */ 0268 void SpreadsheetFormulaTest::formulaCelli_xpx() { 0269 INIT_SPREADSHEET 0270 0271 sheet.column(1)->setFormula(QLatin1String("cell(i, x+x)"), variableNames, variableColumns, true); 0272 sheet.column(1)->updateFormula(); 0273 0274 // values 0275 for (int i = 0; i < rows; i++) { 0276 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0277 QCOMPARE(sheet.column(1)->valueAt(i), 2 * (i + 1)); 0278 } 0279 } 0280 /*! 0281 formula "cell(i, x + 2*x)" 0282 */ 0283 void SpreadsheetFormulaTest::formulaCelli_xp2x() { 0284 INIT_SPREADSHEET 0285 0286 sheet.column(1)->setFormula(QLatin1String("cell(i, x + 2 * x)"), variableNames, variableColumns, true); 0287 sheet.column(1)->updateFormula(); 0288 0289 // values 0290 for (int i = 0; i < rows; i++) { 0291 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0292 QCOMPARE(sheet.column(1)->valueAt(i), 3 * (i + 1)); 0293 } 0294 } 0295 /*! 0296 formula "cell(i, sqrt(x))" 0297 */ 0298 void SpreadsheetFormulaTest::formulaCelli_sqrtx() { 0299 INIT_SPREADSHEET 0300 0301 sheet.column(1)->setFormula(QLatin1String("cell(i, sqrt(x))"), variableNames, variableColumns, true); 0302 sheet.column(1)->updateFormula(); 0303 0304 // values 0305 for (int i = 0; i < rows; i++) { 0306 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0307 QCOMPARE(sheet.column(1)->valueAt(i), std::sqrt(i + 1)); 0308 } 0309 } 0310 /*! 0311 formula "cell(i, x+y)" 0312 */ 0313 void SpreadsheetFormulaTest::formulaCelli_xpy() { 0314 INIT_SPREADSHEET2 0315 0316 sheet.column(2)->setFormula(QLatin1String("cell(i, x+y)"), variableNames, variableColumns, true); 0317 sheet.column(2)->updateFormula(); 0318 0319 // values 0320 for (int i = 0; i < rows; i++) { 0321 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0322 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0323 QCOMPARE(sheet.column(2)->valueAt(i), i + 2); 0324 } 0325 } 0326 0327 /*! 0328 formula "cell(2*i, x+y)" 0329 */ 0330 void SpreadsheetFormulaTest::formulaCell2i_xpy() { 0331 INIT_SPREADSHEET2 0332 0333 sheet.column(2)->setFormula(QLatin1String("cell(2*i, x+y)"), variableNames, variableColumns, true); 0334 sheet.column(2)->updateFormula(); 0335 0336 // values 0337 for (int i = 0; i < rows; i++) { 0338 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0339 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0340 if (i < rows / 2) 0341 QCOMPARE(sheet.column(2)->valueAt(i), sheet.column(0)->valueAt(2 * i + 1) + sheet.column(1)->valueAt(2 * i + 1)); 0342 else 0343 QCOMPARE(sheet.column(2)->valueAt(i), NAN); 0344 } 0345 } 0346 /*! 0347 formula "cell(i, 2*x) + cell (i, 2*y)" 0348 */ 0349 void SpreadsheetFormulaTest::formulaCelli_2xpCelli_2y() { 0350 INIT_SPREADSHEET2 0351 0352 sheet.column(2)->setFormula(QLatin1String("cell(i, 2*x) + cell(i, 2*y)"), variableNames, variableColumns, true); 0353 sheet.column(2)->updateFormula(); 0354 0355 // values 0356 for (int i = 0; i < rows; i++) { 0357 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0358 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0359 QCOMPARE(sheet.column(2)->valueAt(i), 2 * sheet.column(0)->valueAt(i) + 2 * sheet.column(1)->valueAt(i)); 0360 } 0361 } 0362 0363 ///////////////////////// check group separator problem ///////////////////// 0364 0365 void SpreadsheetFormulaTest::formulaLocale() { 0366 Spreadsheet sheet(QStringLiteral("test"), false); 0367 const int cols = 2; 0368 const int rows = 3; 0369 const QVector<double> xData{13000, 14000, 15000}; 0370 0371 sheet.setColumnCount(cols); 0372 sheet.setRowCount(rows); 0373 auto* col0{sheet.column(0)}; 0374 col0->replaceValues(0, xData); 0375 0376 SpreadsheetView view(&sheet, false); 0377 view.selectColumn(0); 0378 0379 QStringList variableNames; 0380 variableNames << QLatin1String("x"); 0381 QVector<Column*> variableColumns; 0382 variableColumns << sheet.column(0); 0383 sheet.column(1)->setFormulaVariableColumn(sheet.column(0)); 0384 0385 sheet.column(1)->setFormula(QLatin1String("mean(x)"), variableNames, variableColumns, true); 0386 sheet.column(1)->updateFormula(); 0387 0388 // values 0389 for (int i = 0; i < rows; i++) 0390 QCOMPARE(sheet.column(1)->valueAt(i), sheet.column(0)->valueAt(1)); 0391 } 0392 0393 ///////////////////////// more methods ///////////////////// 0394 0395 /*! 0396 formula "ma(x)" 0397 */ 0398 void SpreadsheetFormulaTest::formulama() { 0399 INIT_SPREADSHEET 0400 0401 sheet.column(1)->setFormula(QLatin1String("ma(x)"), variableNames, variableColumns, true); 0402 sheet.column(1)->updateFormula(); 0403 0404 // values 0405 for (int i = 0; i < rows; i++) { 0406 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0407 if (i > 0) 0408 QCOMPARE(sheet.column(1)->valueAt(i), i + .5); 0409 else 0410 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0411 } 0412 } 0413 /*! 0414 formula "mr(x)" 0415 */ 0416 void SpreadsheetFormulaTest::formulamr() { 0417 INIT_SPREADSHEET 0418 0419 sheet.column(1)->setFormula(QLatin1String("mr(x)"), variableNames, variableColumns, true); 0420 sheet.column(1)->updateFormula(); 0421 0422 // values 0423 for (int i = 0; i < rows; i++) { 0424 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0425 if (i > 0) 0426 QCOMPARE(sheet.column(1)->valueAt(i), 1); 0427 else 0428 QCOMPARE(sheet.column(1)->valueAt(i), NAN); 0429 } 0430 } 0431 /*! 0432 formula "sma(n, x)" 0433 */ 0434 void SpreadsheetFormulaTest::formulasma() { 0435 INIT_SPREADSHEET 0436 0437 sheet.column(1)->setFormula(QLatin1String("sma(4, x)"), variableNames, variableColumns, true); 0438 sheet.column(1)->updateFormula(); 0439 0440 // values 0441 const int N = 4; 0442 for (int i = 0; i < rows; i++) { 0443 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0444 double value = 0.; 0445 for (int index = std::max(0, i - N + 1); index <= i; index++) 0446 value += sheet.column(0)->valueAt(index); 0447 QCOMPARE(sheet.column(1)->valueAt(i), value / N); 0448 } 0449 } 0450 /*! 0451 formula "smr(n, x)" 0452 */ 0453 void SpreadsheetFormulaTest::formulasmr() { 0454 INIT_SPREADSHEET 0455 0456 sheet.column(1)->setFormula(QLatin1String("smr(4, x)"), variableNames, variableColumns, true); 0457 sheet.column(1)->updateFormula(); 0458 0459 // values 0460 QCOMPARE(sheet.column(1)->valueAt(0), 0); 0461 QCOMPARE(sheet.column(1)->valueAt(1), 1); 0462 QCOMPARE(sheet.column(1)->valueAt(2), 2); 0463 for (int i = 3; i < rows; i++) { 0464 QCOMPARE(sheet.column(0)->valueAt(i), i + 1); 0465 QCOMPARE(sheet.column(1)->valueAt(i), 3); 0466 } 0467 } 0468 0469 QTEST_MAIN(SpreadsheetFormulaTest)