Exemple #1
0
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;
}