QList<QVariantList> XlsxReader::read(const QString &xlsxFilePath)
{
QList<QVariantList> data;
QXlsx::Document doc(xlsxFilePath);
QXlsx::CellRange range = doc.dimension();
for(int row = range.firstRow(); row <= range.lastRow(); ++row)
{
QVariantList rowData;
for(int col = range.firstColumn(); col <= range.lastColumn(); ++col)
{
QXlsx::Cell *cell = doc.cellAt(row, col);
if(cell)
rowData.append(cell->value());
else
rowData.append(QVariant());
}
data.append(rowData);
}
return data;
}
DATA::Agent LiliHelper::parseAgent(const QString aPath, const QStringList aAliases, const QString aSheet)
{
QXlsx::Document aDocument (aPath);
DATA::Agent aAgent;
QStringList aStringList;
if (aSheet.length() != 0)
{
aStringList.append(aSheet);
}
else
{
aStringList = aDocument.sheetNames();
}
for (auto aSheet : aStringList)
{
aDocument.selectSheet(aSheet);
QXlsx::CellRange aSheetRange (aDocument.dimension());
QHash<QString, QDate> aRefDateMap;
QDate aCurrentDate;
QString aNote;
for (int nRow = aSheetRange.firstRow(); nRow <= aSheetRange.lastRow(); ++nRow)
{
QVariant aCell = aDocument.read(nRow, 2);
const bool bFirst = aCell.type() == QVariant::String && s_aWeekDays.contains(aCell.toString());
if (bFirst)
{
if (aDocument.read(nRow, 19).type() == QVariant::String)
{
aNote = aDocument.read(nRow, 19).toString();
}
QString aCellRef = QXlsx::CellReference (nRow, 9).toString();
QVariant aDateVariant = aDocument.read(aCellRef);
// Looking for date without reference
if (!aCurrentDate.isValid() && aDateVariant.type() == QVariant::Date)
{
aCurrentDate = aDateVariant.toDate();
aRefDateMap.insert(aCellRef, aCurrentDate);
}
// Looking for date with reference
else if (aCurrentDate.isValid() && aDateVariant.type() == QVariant::String)
{
QRegularExpression aRx;
QRegularExpressionMatchIterator aRxIterator;
aRx.setPattern("=(\\w+\\d+)\\+(\\d+)");
aRxIterator = aRx.globalMatch(aDateVariant.toString());
if (aRxIterator.hasNext())
{
QRegularExpressionMatch aMatch = aRxIterator.next();
QString aReferencedCell = aMatch.captured(1);
if (aRefDateMap.contains(aReferencedCell))
{
aCurrentDate = aRefDateMap[aReferencedCell].addDays(aMatch.captured(2).toInt());
aRefDateMap.insert(aCellRef, aCurrentDate);
}
}
}
}
else if (aCurrentDate.isValid())
{
QVariant aNameVariant = aDocument.read(nRow, 1);
if (aNameVariant.type() == QVariant::String && aAliases.contains(aNameVariant.toString()))
{
int nHourHead = 2;
while (nHourHead <= 54)
{
QVariant aVariant = aDocument.read(nRow, nHourHead);
int nTempHead = nHourHead + 1;
if (aVariant.type() == QVariant::Double && aVariant.toInt() == 1)
{
QTime aStartTime (7, 0);
if (nHourHead > 2)
{
aStartTime = aStartTime.addSecs(1800 + (nHourHead - 3) * 900);
}
QTime aEndTime = aStartTime.addSecs(15 * 60);
aVariant = aDocument.read(nRow, nTempHead);
while (nTempHead <= 54 && aVariant.type() == QVariant::Double && aVariant.toInt() == 1)
{
aEndTime = aEndTime.addSecs(15 * 60);
++nTempHead;
aVariant = aDocument.read(nRow, nTempHead);
}
aAgent.getEvents().append(DATA::CalEvent (QDateTime (aCurrentDate, aStartTime),
QDateTime (aCurrentDate, aEndTime),
aNote));
}
nHourHead = nTempHead;
}
}
}
}
}
return aAgent;
}