import_export/Ods/OdsFilterTest.cpp

0001 /*
0002     File                 : OdsFilterTest.cpp
0003     Project              : LabPlot
0004     Description          : Tests for the Ods filter
0005     --------------------------------------------------------------------
0006     SPDX-FileCopyrightText: 2023 Stefan Gerlach <stefan.gerlach@uni.kn>
0007
0008     SPDX-License-Identifier: GPL-2.0-or-later
0009 */
0010
0011 #include "OdsFilterTest.h"
0012 #include "backend/core/Workbook.h"
0013 #include "backend/datasources/filters/OdsFilter.h"
0014 #include "backend/matrix/Matrix.h"
0015 #include "backend/spreadsheet/Spreadsheet.h"
0016
0017 void OdsFilterTest::importFile3SheetsRangesFormula() {
0018     const QString& fileName = QFINDTESTDATA(QLatin1String("data/ranges-formula.ods"));
0019
0020     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0021     OdsFilter filter;
0022
0023     // sheet 1
0024     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet1"));
0025     filter.readDataFromFile(fileName, &spreadsheet);
0026
0027     QCOMPARE(spreadsheet.columnCount(), 2);
0028     QCOMPARE(spreadsheet.rowCount(), 5);
0029
0030     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Double);
0031     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0032
0033     QCOMPARE(spreadsheet.column(0)->valueAt(0), 1);
0034     QCOMPARE(spreadsheet.column(0)->valueAt(1), 2);
0035     QCOMPARE(spreadsheet.column(0)->valueAt(2), 3);
0036     QCOMPARE(spreadsheet.column(0)->valueAt(3), 4);
0037     QCOMPARE(spreadsheet.column(0)->valueAt(4), 5);
0038     QCOMPARE(spreadsheet.column(1)->valueAt(0), 1);
0039     QCOMPARE(spreadsheet.column(1)->valueAt(1), 2.1);
0040     QCOMPARE(spreadsheet.column(1)->valueAt(2), 3.21);
0041     QCOMPARE(spreadsheet.column(1)->valueAt(3), 4.321);
0042     QCOMPARE(spreadsheet.column(1)->valueAt(4), 5.4321);
0043
0044     // sheet 2
0045     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet2"));
0046     filter.readDataFromFile(fileName, &spreadsheet);
0047
0048     QCOMPARE(spreadsheet.columnCount(), 4);
0049     QCOMPARE(spreadsheet.rowCount(), 2);
0050
0051     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Double);
0052     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0053     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Double);
0054     QCOMPARE(spreadsheet.column(3)->columnMode(), AbstractColumn::ColumnMode::Double);
0055
0056     QCOMPARE(spreadsheet.column(0)->valueAt(0), 1);
0057     QCOMPARE(spreadsheet.column(0)->valueAt(1), 3);
0058     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.2);
0059     QCOMPARE(spreadsheet.column(1)->valueAt(1), 2.1);
0060     QCOMPARE(spreadsheet.column(2)->valueAt(0), 3);
0061     QCOMPARE(spreadsheet.column(2)->valueAt(1), 1);
0062     QCOMPARE(spreadsheet.column(3)->valueAt(0), qQNaN());
0063     QCOMPARE(spreadsheet.column(3)->valueAt(1), 12.3);
0064
0065     // sheet 3
0066     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet3"));
0067     filter.readDataFromFile(fileName, &spreadsheet);
0068
0069     QCOMPARE(spreadsheet.columnCount(), 3);
0070     QCOMPARE(spreadsheet.rowCount(), 4);
0071
0072     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0073     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0074     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Double);
0075
0076     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("A"));
0077     QCOMPARE(spreadsheet.column(0)->textAt(1), QLatin1String("B"));
0078     QCOMPARE(spreadsheet.column(0)->textAt(2), QLatin1String("C"));
0079     QCOMPARE(spreadsheet.column(0)->textAt(3), QLatin1String("0")); // formula
0080     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.2);
0081     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1.1);
0082     QCOMPARE(spreadsheet.column(1)->valueAt(2), 3.3);
0083     QCOMPARE(spreadsheet.column(1)->valueAt(3), 6.6); // formula
0084     QCOMPARE(spreadsheet.column(2)->valueAt(0), 42);
0085     QCOMPARE(spreadsheet.column(2)->valueAt(1), 23);
0086     QCOMPARE(spreadsheet.column(2)->valueAt(2), 5);
0087     QCOMPARE(spreadsheet.column(2)->valueAt(3), 70); // formula
0088 }
0089
0090 void OdsFilterTest::importFile3SheetsWorkbook() {
0091     const QString& fileName = QFINDTESTDATA(QLatin1String("data/ranges-formula.ods"));
0092
0093     Workbook workbook(QStringLiteral("test"));
0094     auto* spreadsheet1 = new Spreadsheet(QStringLiteral("sheet 1"));
0095     workbook.addChildFast(spreadsheet1);
0096     auto* spreadsheet2 = new Spreadsheet(QStringLiteral("sheet 2"));
0097     workbook.addChildFast(spreadsheet2);
0098
0099     OdsFilter filter;
0100
0101     // sheet 1
0102     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet1"));
0103     filter.readDataFromFile(fileName, spreadsheet1);
0104
0105     QCOMPARE(spreadsheet1->columnCount(), 2);
0106     QCOMPARE(spreadsheet1->rowCount(), 5);
0107
0108     QCOMPARE(spreadsheet1->column(0)->columnMode(), AbstractColumn::ColumnMode::Double);
0109     QCOMPARE(spreadsheet1->column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0110
0111     QCOMPARE(spreadsheet1->column(0)->valueAt(0), 1);
0112     QCOMPARE(spreadsheet1->column(0)->valueAt(1), 2);
0113     QCOMPARE(spreadsheet1->column(0)->valueAt(2), 3);
0114     QCOMPARE(spreadsheet1->column(0)->valueAt(3), 4);
0115     QCOMPARE(spreadsheet1->column(0)->valueAt(4), 5);
0116     QCOMPARE(spreadsheet1->column(1)->valueAt(0), 1);
0117     QCOMPARE(spreadsheet1->column(1)->valueAt(1), 2.1);
0118     QCOMPARE(spreadsheet1->column(1)->valueAt(2), 3.21);
0119     QCOMPARE(spreadsheet1->column(1)->valueAt(3), 4.321);
0120     QCOMPARE(spreadsheet1->column(1)->valueAt(4), 5.4321);
0121
0122     // sheet 2
0123     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet2"));
0124     filter.readDataFromFile(fileName, spreadsheet2);
0125
0126     QCOMPARE(spreadsheet2->columnCount(), 4);
0127     QCOMPARE(spreadsheet2->rowCount(), 2);
0128
0129     QCOMPARE(spreadsheet2->column(0)->columnMode(), AbstractColumn::ColumnMode::Double);
0130     QCOMPARE(spreadsheet2->column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0131     QCOMPARE(spreadsheet2->column(2)->columnMode(), AbstractColumn::ColumnMode::Double);
0132     QCOMPARE(spreadsheet2->column(3)->columnMode(), AbstractColumn::ColumnMode::Double);
0133
0134     QCOMPARE(spreadsheet2->column(0)->valueAt(0), 1);
0135     QCOMPARE(spreadsheet2->column(0)->valueAt(1), 3);
0136     QCOMPARE(spreadsheet2->column(1)->valueAt(0), 2.2);
0137     QCOMPARE(spreadsheet2->column(1)->valueAt(1), 2.1);
0138     QCOMPARE(spreadsheet2->column(2)->valueAt(0), 3);
0139     QCOMPARE(spreadsheet2->column(2)->valueAt(1), 1);
0140     QCOMPARE(spreadsheet2->column(3)->valueAt(0), qQNaN());
0141     QCOMPARE(spreadsheet2->column(3)->valueAt(1), 12.3);
0142 }
0143
0144 void OdsFilterTest::importFileMatrix() {
0145     const QString& fileName = QFINDTESTDATA(QLatin1String("data/ranges-formula.ods"));
0146
0147     Matrix matrix(QStringLiteral("test"), false);
0148     OdsFilter filter;
0149
0150     // sheet 3
0151     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet3"));
0152     filter.readDataFromFile(fileName, &matrix);
0153
0154     QCOMPARE(matrix.columnCount(), 3);
0155     QCOMPARE(matrix.rowCount(), 4);
0156
0157     // first text column is set to 0 (only numeric values are read)
0158     QCOMPARE(matrix.cell<double>(0, 0), 0.);
0159     QCOMPARE(matrix.cell<double>(0, 1), 2.2);
0160     QCOMPARE(matrix.cell<double>(0, 2), 42);
0161     QCOMPARE(matrix.cell<double>(1, 0), 0.);
0162     QCOMPARE(matrix.cell<double>(1, 1), 1.1);
0163     QCOMPARE(matrix.cell<double>(1, 2), 23);
0164     QCOMPARE(matrix.cell<double>(2, 0), 0.);
0165     QCOMPARE(matrix.cell<double>(2, 1), 3.3);
0166     QCOMPARE(matrix.cell<double>(2, 2), 5);
0167     QCOMPARE(matrix.cell<double>(3, 0), 0.);
0168     QCOMPARE(matrix.cell<double>(3, 1), 6.6);
0169     QCOMPARE(matrix.cell<double>(3, 2), 70);
0170 }
0171
0172 void OdsFilterTest::importFileSheetStartEndRow() {
0173     const QString& fileName = QFINDTESTDATA(QLatin1String("data/start-end.ods"));
0174
0175     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0176     OdsFilter filter;
0177
0178     // check sheet
0179     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet2"));
0180     filter.readDataFromFile(fileName, &spreadsheet);
0181
0182     QCOMPARE(spreadsheet.columnCount(), 4);
0183     QCOMPARE(spreadsheet.rowCount(), 4);
0184
0185     // set start/end row and check result
0186     filter.setStartRow(2);
0187     filter.setEndRow(3);
0188     filter.readDataFromFile(fileName, &spreadsheet);
0189
0190     QCOMPARE(spreadsheet.columnCount(), 4);
0191     QCOMPARE(spreadsheet.rowCount(), 2);
0192
0193     QCOMPARE(spreadsheet.column(0)->valueAt(0), 3);
0194     QCOMPARE(spreadsheet.column(0)->valueAt(1), 4);
0195     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.1);
0196     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1.9);
0197     QCOMPARE(spreadsheet.column(2)->valueAt(0), 1);
0198     QCOMPARE(spreadsheet.column(2)->valueAt(1), 42);
0199     QCOMPARE(spreadsheet.column(3)->valueAt(0), 12.3);
0200     QCOMPARE(spreadsheet.column(3)->valueAt(1), qQNaN());
0201
0202     // set end row too high
0203     filter.setStartRow(2);
0204     filter.setEndRow(8);
0205     filter.readDataFromFile(fileName, &spreadsheet);
0206
0207     QCOMPARE(spreadsheet.columnCount(), 4);
0208     QCOMPARE(spreadsheet.rowCount(), 3);
0209
0210     QCOMPARE(spreadsheet.column(0)->valueAt(0), 3);
0211     QCOMPARE(spreadsheet.column(0)->valueAt(1), 4);
0212     QCOMPARE(spreadsheet.column(0)->valueAt(2), qQNaN());
0213     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.1);
0214     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1.9);
0215     QCOMPARE(spreadsheet.column(1)->valueAt(2), qQNaN());
0216     QCOMPARE(spreadsheet.column(2)->valueAt(0), 1);
0217     QCOMPARE(spreadsheet.column(2)->valueAt(1), 42);
0218     QCOMPARE(spreadsheet.column(2)->valueAt(2), 46);
0219     QCOMPARE(spreadsheet.column(3)->valueAt(0), 12.3);
0220     QCOMPARE(spreadsheet.column(3)->valueAt(1), qQNaN());
0221     QCOMPARE(spreadsheet.column(3)->valueAt(2), qQNaN());
0222
0223     // set start row too high
0224     filter.setStartRow(8);
0225     filter.setEndRow(3);
0226     filter.readDataFromFile(fileName, &spreadsheet);
0227
0228     QCOMPARE(spreadsheet.columnCount(), 4);
0229     QCOMPARE(spreadsheet.rowCount(), 3);
0230
0231     QCOMPARE(spreadsheet.column(0)->valueAt(0), 1);
0232     QCOMPARE(spreadsheet.column(0)->valueAt(1), 3);
0233     QCOMPARE(spreadsheet.column(0)->valueAt(2), 4);
0234     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.2);
0235     QCOMPARE(spreadsheet.column(1)->valueAt(1), 2.1);
0236     QCOMPARE(spreadsheet.column(1)->valueAt(2), 1.9);
0237     QCOMPARE(spreadsheet.column(2)->valueAt(0), 3);
0238     QCOMPARE(spreadsheet.column(2)->valueAt(1), 1);
0239     QCOMPARE(spreadsheet.column(2)->valueAt(2), 42);
0240     QCOMPARE(spreadsheet.column(3)->valueAt(0), qQNaN());
0241     QCOMPARE(spreadsheet.column(3)->valueAt(1), 12.3);
0242     QCOMPARE(spreadsheet.column(3)->valueAt(2), qQNaN());
0243 }
0244
0245 void OdsFilterTest::importFileSheetStartEndColumn() {
0246     const QString& fileName = QFINDTESTDATA(QLatin1String("data/start-end.ods"));
0247
0248     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0249     OdsFilter filter;
0250
0251     // check sheet
0252     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet2"));
0253     filter.readDataFromFile(fileName, &spreadsheet);
0254
0255     QCOMPARE(spreadsheet.columnCount(), 4);
0256     QCOMPARE(spreadsheet.rowCount(), 4);
0257
0258     // set start/end column and check result
0259     filter.setStartColumn(2);
0260     filter.setEndColumn(3);
0261     filter.readDataFromFile(fileName, &spreadsheet);
0262
0263     QCOMPARE(spreadsheet.columnCount(), 2);
0264     QCOMPARE(spreadsheet.rowCount(), 4);
0265
0266     QCOMPARE(spreadsheet.column(0)->valueAt(0), 2.2);
0267     QCOMPARE(spreadsheet.column(0)->valueAt(1), 2.1);
0268     QCOMPARE(spreadsheet.column(0)->valueAt(2), 1.9);
0269     QCOMPARE(spreadsheet.column(0)->valueAt(3), qQNaN());
0270     QCOMPARE(spreadsheet.column(1)->valueAt(0), 3);
0271     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1);
0272     QCOMPARE(spreadsheet.column(1)->valueAt(2), 42);
0273     QCOMPARE(spreadsheet.column(1)->valueAt(3), 46);
0274
0275     // set end column too high
0276     filter.setStartColumn(2);
0277     filter.setEndColumn(7);
0278     filter.setStartRow(2);
0279     filter.setEndRow(3);
0280     filter.readDataFromFile(fileName, &spreadsheet);
0281
0282     QCOMPARE(spreadsheet.columnCount(), 3);
0283     QCOMPARE(spreadsheet.rowCount(), 2);
0284
0285     QCOMPARE(spreadsheet.column(0)->valueAt(0), 2.1);
0286     QCOMPARE(spreadsheet.column(0)->valueAt(1), 1.9);
0287     QCOMPARE(spreadsheet.column(1)->valueAt(0), 1);
0288     QCOMPARE(spreadsheet.column(1)->valueAt(1), 42);
0289     QCOMPARE(spreadsheet.column(2)->valueAt(0), 12.3);
0290     QCOMPARE(spreadsheet.column(2)->valueAt(1), qQNaN());
0291
0292     // set start column too high
0293     filter.setStartColumn(6);
0294     filter.setEndColumn(3);
0295     filter.setStartRow(2);
0296     filter.setEndRow(3);
0297     filter.readDataFromFile(fileName, &spreadsheet);
0298
0299     QCOMPARE(spreadsheet.columnCount(), 3);
0300     QCOMPARE(spreadsheet.rowCount(), 2);
0301
0302     QCOMPARE(spreadsheet.column(0)->valueAt(0), 3);
0303     QCOMPARE(spreadsheet.column(0)->valueAt(1), 4);
0304     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.1);
0305     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1.9);
0306     QCOMPARE(spreadsheet.column(2)->valueAt(0), 1);
0307     QCOMPARE(spreadsheet.column(2)->valueAt(1), 42);
0308 }
0309
0310 void OdsFilterTest::importFileSheetWithHeader() {
0311     const QString& fileName = QFINDTESTDATA(QLatin1String("data/header.ods"));
0312
0313     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0314     OdsFilter filter;
0315
0316     // check sheet
0317     filter.setSelectedSheetNames(QStringList() << QStringLiteral("Sheet3"));
0318     filter.setFirstRowAsColumnNames(true);
0319     filter.readDataFromFile(fileName, &spreadsheet);
0320
0321     QCOMPARE(spreadsheet.columnCount(), 3);
0322     QCOMPARE(spreadsheet.rowCount(), 4);
0323
0324     // check header type
0325     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0326     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Double);
0327     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Double);
0328
0329     // check header name
0330     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("Name"));
0331     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("time"));
0332     QCOMPARE(spreadsheet.column(2)->name(), QLatin1String("COUNT"));
0333
0334     // check data
0335     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("A"));
0336     QCOMPARE(spreadsheet.column(0)->textAt(1), QLatin1String("B"));
0337     QCOMPARE(spreadsheet.column(0)->textAt(2), QLatin1String("C"));
0338     QCOMPARE(spreadsheet.column(0)->textAt(3), QLatin1String("0")); // formula
0339     QCOMPARE(spreadsheet.column(1)->valueAt(0), 2.2);
0340     QCOMPARE(spreadsheet.column(1)->valueAt(1), 1.1);
0341     QCOMPARE(spreadsheet.column(1)->valueAt(2), 3.3);
0342     QCOMPARE(spreadsheet.column(1)->valueAt(3), 6.6); // formula
0343     QCOMPARE(spreadsheet.column(2)->valueAt(0), 42);
0344     QCOMPARE(spreadsheet.column(2)->valueAt(1), 23);
0345     QCOMPARE(spreadsheet.column(2)->valueAt(2), 5);
0346     QCOMPARE(spreadsheet.column(2)->valueAt(3), 70); // formula
0347 }
0348
0349 QTEST_MAIN(OdsFilterTest)