File indexing completed on 2024-04-28 03:48:18
0001 /* 0002 File : XLSXFilterTest.cpp 0003 Project : LabPlot 0004 Description : Tests for the XLSX filter 0005 -------------------------------------------------------------------- 0006 SPDX-FileCopyrightText: 2022-2023 Stefan Gerlach <stefan.gerlach@uni.kn> 0007 0008 SPDX-License-Identifier: GPL-2.0-or-later 0009 */ 0010 0011 #include "XLSXFilterTest.h" 0012 #include "backend/datasources/filters/XLSXFilter.h" 0013 #include "backend/spreadsheet/Spreadsheet.h" 0014 0015 void XLSXFilterTest::importFile2Cols() { 0016 const QString& fileName = QFINDTESTDATA(QLatin1String("data/2col.xlsx")); 0017 0018 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0019 XLSXFilter filter; 0020 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0021 filter.setCurrentRange(QStringLiteral("A1:B5")); 0022 filter.readDataFromFile(fileName, &spreadsheet); 0023 0024 QCOMPARE(spreadsheet.columnCount(), 2); 0025 QCOMPARE(spreadsheet.rowCount(), 5); 0026 0027 // WARN(spreadsheet.column(0)->valueAt(0)) 0028 QCOMPARE(spreadsheet.column(0)->valueAt(0), 1); 0029 QCOMPARE(spreadsheet.column(0)->valueAt(1), 2); 0030 QCOMPARE(spreadsheet.column(0)->valueAt(2), 3); 0031 QCOMPARE(spreadsheet.column(0)->valueAt(3), 4); 0032 QCOMPARE(spreadsheet.column(0)->valueAt(4), 5); 0033 QCOMPARE(spreadsheet.column(1)->valueAt(0), 25); 0034 QCOMPARE(spreadsheet.column(1)->valueAt(1), 16); 0035 QCOMPARE(spreadsheet.column(1)->valueAt(2), 9); 0036 QCOMPARE(spreadsheet.column(1)->valueAt(3), 4); 0037 QCOMPARE(spreadsheet.column(1)->valueAt(4), 1); 0038 } 0039 0040 void XLSXFilterTest::importFile3Cols() { 0041 const QString& fileName = QFINDTESTDATA(QLatin1String("data/3col.xlsx")); 0042 0043 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0044 XLSXFilter filter; 0045 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0046 filter.setCurrentRange(QStringLiteral("A1:C5")); 0047 filter.readDataFromFile(fileName, &spreadsheet); 0048 0049 QCOMPARE(spreadsheet.columnCount(), 3); 0050 QCOMPARE(spreadsheet.rowCount(), 5); 0051 0052 // WARN(spreadsheet.column(0)->valueAt(0)) 0053 QCOMPARE(spreadsheet.column(0)->valueAt(0), 1.1); 0054 QCOMPARE(spreadsheet.column(0)->valueAt(1), 2.2); 0055 QCOMPARE(spreadsheet.column(0)->valueAt(2), 3.3); 0056 QCOMPARE(spreadsheet.column(0)->valueAt(3), 4.4); 0057 QCOMPARE(spreadsheet.column(0)->valueAt(4), 5.5); 0058 QCOMPARE(spreadsheet.column(1)->valueAt(0), 25); 0059 QCOMPARE(spreadsheet.column(1)->valueAt(1), 16); 0060 QCOMPARE(spreadsheet.column(1)->valueAt(2), 9); 0061 QCOMPARE(spreadsheet.column(1)->valueAt(3), 4); 0062 QCOMPARE(spreadsheet.column(1)->valueAt(4), 1); 0063 QCOMPARE(spreadsheet.column(2)->valueAt(0), 1); 0064 QCOMPARE(spreadsheet.column(2)->valueAt(1), 20); 0065 QCOMPARE(spreadsheet.column(2)->valueAt(2), 300); 0066 QCOMPARE(spreadsheet.column(2)->valueAt(3), 4000); 0067 QCOMPARE(spreadsheet.column(2)->valueAt(4), 50000); 0068 } 0069 0070 void XLSXFilterTest::importFile3ColsStartEndRow() { 0071 const QString& fileName = QFINDTESTDATA(QLatin1String("data/3col.xlsx")); 0072 0073 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0074 XLSXFilter filter; 0075 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0076 filter.setCurrentRange(QStringLiteral("A1:C5")); 0077 // set start/end row and check result 0078 filter.setStartRow(2); 0079 filter.setEndRow(3); 0080 filter.readDataFromFile(fileName, &spreadsheet); 0081 0082 QCOMPARE(spreadsheet.columnCount(), 3); 0083 QCOMPARE(spreadsheet.rowCount(), 2); 0084 0085 // WARN(spreadsheet.column(0)->valueAt(0)) 0086 QCOMPARE(spreadsheet.column(0)->valueAt(0), 2.2); 0087 QCOMPARE(spreadsheet.column(0)->valueAt(1), 3.3); 0088 QCOMPARE(spreadsheet.column(1)->valueAt(0), 16); 0089 QCOMPARE(spreadsheet.column(1)->valueAt(1), 9); 0090 QCOMPARE(spreadsheet.column(2)->valueAt(0), 20); 0091 QCOMPARE(spreadsheet.column(2)->valueAt(1), 300); 0092 0093 // set end row too high 0094 filter.setCurrentRange(QStringLiteral("A1:C5")); 0095 filter.setStartRow(2); 0096 filter.setEndRow(8); 0097 filter.readDataFromFile(fileName, &spreadsheet); 0098 0099 QCOMPARE(spreadsheet.columnCount(), 3); 0100 QCOMPARE(spreadsheet.rowCount(), 4); 0101 0102 QCOMPARE(spreadsheet.column(0)->valueAt(0), 2.2); 0103 QCOMPARE(spreadsheet.column(0)->valueAt(1), 3.3); 0104 QCOMPARE(spreadsheet.column(0)->valueAt(2), 4.4); 0105 QCOMPARE(spreadsheet.column(0)->valueAt(3), 5.5); 0106 QCOMPARE(spreadsheet.column(1)->valueAt(0), 16); 0107 QCOMPARE(spreadsheet.column(1)->valueAt(1), 9); 0108 QCOMPARE(spreadsheet.column(1)->valueAt(2), 4); 0109 QCOMPARE(spreadsheet.column(1)->valueAt(3), 1); 0110 QCOMPARE(spreadsheet.column(2)->valueAt(0), 20); 0111 QCOMPARE(spreadsheet.column(2)->valueAt(1), 300); 0112 QCOMPARE(spreadsheet.column(2)->valueAt(2), 4000); 0113 QCOMPARE(spreadsheet.column(2)->valueAt(3), 50000); 0114 0115 // set start row too high 0116 filter.setCurrentRange(QStringLiteral("A1:C5")); 0117 filter.setStartRow(8); 0118 filter.setEndRow(3); 0119 filter.readDataFromFile(fileName, &spreadsheet); 0120 0121 QCOMPARE(spreadsheet.columnCount(), 3); 0122 QCOMPARE(spreadsheet.rowCount(), 3); 0123 0124 QCOMPARE(spreadsheet.column(0)->valueAt(0), 1.1); 0125 QCOMPARE(spreadsheet.column(0)->valueAt(1), 2.2); 0126 QCOMPARE(spreadsheet.column(0)->valueAt(2), 3.3); 0127 QCOMPARE(spreadsheet.column(1)->valueAt(0), 25); 0128 QCOMPARE(spreadsheet.column(1)->valueAt(1), 16); 0129 QCOMPARE(spreadsheet.column(1)->valueAt(2), 9); 0130 QCOMPARE(spreadsheet.column(2)->valueAt(0), 1); 0131 QCOMPARE(spreadsheet.column(2)->valueAt(1), 20); 0132 QCOMPARE(spreadsheet.column(2)->valueAt(2), 300); 0133 } 0134 0135 void XLSXFilterTest::importFile3ColsStartEndColumn() { 0136 const QString& fileName = QFINDTESTDATA(QLatin1String("data/3col.xlsx")); 0137 0138 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0139 XLSXFilter filter; 0140 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0141 filter.setCurrentRange(QStringLiteral("A1:C5")); 0142 // set start/end row and check result 0143 filter.setStartColumn(2); 0144 filter.setEndColumn(3); 0145 filter.readDataFromFile(fileName, &spreadsheet); 0146 0147 QCOMPARE(spreadsheet.columnCount(), 2); 0148 QCOMPARE(spreadsheet.rowCount(), 5); 0149 0150 QCOMPARE(spreadsheet.column(0)->valueAt(0), 25); 0151 QCOMPARE(spreadsheet.column(0)->valueAt(1), 16); 0152 QCOMPARE(spreadsheet.column(0)->valueAt(2), 9); 0153 QCOMPARE(spreadsheet.column(0)->valueAt(3), 4); 0154 QCOMPARE(spreadsheet.column(0)->valueAt(4), 1); 0155 QCOMPARE(spreadsheet.column(1)->valueAt(0), 1); 0156 QCOMPARE(spreadsheet.column(1)->valueAt(1), 20); 0157 QCOMPARE(spreadsheet.column(1)->valueAt(2), 300); 0158 QCOMPARE(spreadsheet.column(1)->valueAt(3), 4000); 0159 QCOMPARE(spreadsheet.column(1)->valueAt(4), 50000); 0160 0161 // set end column too high 0162 filter.setCurrentRange(QStringLiteral("A1:C5")); 0163 filter.setStartColumn(2); 0164 filter.setEndColumn(7); 0165 filter.readDataFromFile(fileName, &spreadsheet); 0166 0167 QCOMPARE(spreadsheet.columnCount(), 2); 0168 QCOMPARE(spreadsheet.rowCount(), 5); 0169 0170 QCOMPARE(spreadsheet.column(0)->valueAt(0), 25); 0171 QCOMPARE(spreadsheet.column(0)->valueAt(1), 16); 0172 QCOMPARE(spreadsheet.column(0)->valueAt(2), 9); 0173 QCOMPARE(spreadsheet.column(0)->valueAt(3), 4); 0174 QCOMPARE(spreadsheet.column(0)->valueAt(4), 1); 0175 QCOMPARE(spreadsheet.column(1)->valueAt(0), 1); 0176 QCOMPARE(spreadsheet.column(1)->valueAt(1), 20); 0177 QCOMPARE(spreadsheet.column(1)->valueAt(2), 300); 0178 QCOMPARE(spreadsheet.column(1)->valueAt(3), 4000); 0179 QCOMPARE(spreadsheet.column(1)->valueAt(4), 50000); 0180 0181 // set start column too high 0182 filter.setCurrentRange(QStringLiteral("A1:C5")); 0183 filter.setStartColumn(6); 0184 filter.setEndColumn(2); 0185 filter.readDataFromFile(fileName, &spreadsheet); 0186 0187 QCOMPARE(spreadsheet.column(0)->valueAt(0), 1.1); 0188 QCOMPARE(spreadsheet.column(0)->valueAt(1), 2.2); 0189 QCOMPARE(spreadsheet.column(0)->valueAt(2), 3.3); 0190 QCOMPARE(spreadsheet.column(0)->valueAt(3), 4.4); 0191 QCOMPARE(spreadsheet.column(0)->valueAt(4), 5.5); 0192 QCOMPARE(spreadsheet.column(1)->valueAt(0), 25); 0193 QCOMPARE(spreadsheet.column(1)->valueAt(1), 16); 0194 QCOMPARE(spreadsheet.column(1)->valueAt(2), 9); 0195 QCOMPARE(spreadsheet.column(1)->valueAt(3), 4); 0196 QCOMPARE(spreadsheet.column(1)->valueAt(4), 1); 0197 } 0198 0199 void XLSXFilterTest::importFileEmptyCells() { 0200 const QString& fileName = QFINDTESTDATA(QLatin1String("data/datatypes-empty.xlsx")); 0201 0202 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0203 XLSXFilter filter; 0204 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0205 filter.setCurrentRange(QStringLiteral("A1:F5")); 0206 filter.readDataFromFile(fileName, &spreadsheet); 0207 0208 QCOMPARE(spreadsheet.columnCount(), 6); 0209 QCOMPARE(spreadsheet.rowCount(), 5); 0210 0211 WARN(spreadsheet.column(0)->valueAt(0)) 0212 WARN(spreadsheet.column(0)->valueAt(1)) 0213 WARN(spreadsheet.column(0)->valueAt(2)) 0214 WARN(spreadsheet.column(0)->valueAt(3)) 0215 WARN(spreadsheet.column(0)->valueAt(4)) 0216 WARN(spreadsheet.column(1)->valueAt(0)) 0217 WARN(spreadsheet.column(2)->valueAt(0)) 0218 WARN(spreadsheet.column(3)->valueAt(0)) 0219 WARN(spreadsheet.column(4)->valueAt(0)) 0220 WARN(spreadsheet.column(5)->valueAt(0)) 0221 // QCOMPARE(spreadsheet.column(0)->valueAt(0), 45107.7371295949); 0222 QCOMPARE(spreadsheet.column(0)->valueAt(1), 10.5); 0223 // QCOMPARE(spreadsheet.column(0)->valueAt(2), 43747); 0224 // QCOMPARE(spreadsheet.column(0)->valueAt(3), 0.422974537037037); 0225 QCOMPARE(spreadsheet.column(0)->valueAt(4), 40000); 0226 // QCOMPARE(spreadsheet.column(1)->valueAt(0), 45107.7371180556); 0227 QCOMPARE(spreadsheet.column(2)->valueAt(0), 10.5); 0228 // QCOMPARE(spreadsheet.column(3)->valueAt(0), 43747); 0229 // QCOMPARE(spreadsheet.column(4)->valueAt(0), 0.422974537037037); 0230 QCOMPARE(spreadsheet.column(5)->valueAt(0), 40000); 0231 0232 for (int col = 1; col < 6; col++) 0233 for (int row = 1; row < 5; row++) 0234 QCOMPARE(spreadsheet.column(col)->valueAt(row), 0); 0235 } 0236 0237 void XLSXFilterTest::importFileDatetime() { 0238 const QString& fileName = QFINDTESTDATA(QLatin1String("data/datatypes-excel.xlsx")); 0239 0240 Spreadsheet spreadsheet(QStringLiteral("test"), false); 0241 XLSXFilter filter; 0242 filter.setCurrentSheet(QStringLiteral("Sheet1")); 0243 filter.setCurrentRange(QStringLiteral("A1:E4")); 0244 filter.readDataFromFile(fileName, &spreadsheet); 0245 0246 QCOMPARE(spreadsheet.columnCount(), 5); 0247 QCOMPARE(spreadsheet.rowCount(), 4); 0248 0249 QCOMPARE(spreadsheet.column(0)->valueAt(0), 1); 0250 QCOMPARE(spreadsheet.column(0)->valueAt(1), 2); 0251 QCOMPARE(spreadsheet.column(0)->valueAt(2), 4); 0252 QCOMPARE(spreadsheet.column(0)->valueAt(3), 3); 0253 QCOMPARE(spreadsheet.column(1)->dateTimeAt(0).toString(), QStringLiteral("Sun Jan 1 00:00:00 2023")); 0254 QCOMPARE(spreadsheet.column(1)->dateTimeAt(1).toString(), QStringLiteral("Thu Mar 2 00:00:00 2023")); 0255 QCOMPARE(spreadsheet.column(1)->dateTimeAt(2).toString(), QStringLiteral("Sun Jun 4 00:00:00 2023")); 0256 QCOMPARE(spreadsheet.column(1)->dateTimeAt(3).toString(), QStringLiteral("Mon Aug 7 00:00:00 2023")); 0257 QCOMPARE(spreadsheet.column(2)->valueAt(0), 1.1); 0258 QCOMPARE(spreadsheet.column(2)->valueAt(1), 2.3); 0259 QCOMPARE(spreadsheet.column(2)->valueAt(2), 4.2); 0260 QCOMPARE(spreadsheet.column(2)->valueAt(3), 7.4); 0261 QCOMPARE(spreadsheet.column(3)->dateTimeAt(0).toString(), QStringLiteral("Sat Nov 11 01:02:03 2023")); 0262 QCOMPARE(spreadsheet.column(3)->dateTimeAt(1).toString(), QStringLiteral("Thu Mar 27 03:17:24 2014")); 0263 QCOMPARE(spreadsheet.column(3)->dateTimeAt(2).toString(), QStringLiteral("Sun Sep 19 12:12:12 1999")); 0264 QCOMPARE(spreadsheet.column(3)->dateTimeAt(3).toString(), QStringLiteral("Mon Aug 8 23:23:23 1988")); 0265 QCOMPARE(spreadsheet.column(4)->valueAt(0), 2.5); 0266 QCOMPARE(spreadsheet.column(4)->valueAt(1), 3.14); 0267 QCOMPARE(spreadsheet.column(4)->valueAt(2), 0.22); 0268 QCOMPARE(spreadsheet.column(4)->valueAt(3), 0.01); 0269 } 0270 0271 QTEST_MAIN(XLSXFilterTest)