示例#1
0
//加载。获取exeel里的表单,并设置
void MainWindow::on_pushButton_load_clicked()
{
    QXlsx::Document xlsx_tmp(excel_path_file);//E:/Git/Qt/excel_read/Profile TR098.xlsx
    QStringList sheetname_tmp = xlsx_tmp.sheetNames();//表单名
    qDebug() << "表单名:"  << sheetname_tmp;
    ui->comboBox_sheet->addItems(sheetname_tmp);
    QString tmp = sheetname_tmp.join(",");
    qDebug() << "tmp:"  << tmp;
    //ui->textBrowser_print->append("当前文件中的sheet:"+ tmp);
    ui->labelsheet_log->setText("当前文件中表单为:"+ tmp);


   //获取选择表单的行列
   //xlsx_tmp.selectSheet(arg1);//选中表单
   QXlsx::CellRange range;
   range = xlsx_tmp.dimension();
   sheet_row = range.rowCount();//行
   sheet_col = range.columnCount();//列
   qDebug() << "打印当前表的行sheet_row:" << sheet_row << "   打印当前表的列sheet_col:" << sheet_col;

}
示例#2
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;
}
示例#3
0
void MainWindow::checkExcelFile(QString file_name)
{
    bool debug = false;
    QXlsx::Document * xlsx = new QXlsx::Document(file_name);
    QSqlQuery query;
    if (!xlsx->selectSheet("Check"))
    {
        return;
    }
    QXlsx::CellRange range = xlsx->dimension();
    int rowCount = range.rowCount();
//    int columnCount = range.columnCount();
    int columnCount = visible_columns.at(0);
/*
    if (check_box_check->isChecked() &&
        columnCount != visible_columns.at(0))
    {
        QMessageBox::information(this,tr(""), tr("Check columns do not fit database"));
    }
*/
    if (debug)
    {
        QMessageBox::information(this,tr("Rows * Columns"),
           QString::number(rowCount) + tr(" * ") + QString::number(columnCount));
    }
    int newCount = 0;
    int insertCount = 0;

    for (int i = 1; i <= rowCount; ++i)
    {
        QString name = xlsx->read(i, 1).toString();
        if (debug)
        {
            QMessageBox::information(this,tr("name"), name);
        }
        SearchResult result = searchCompany(name);
        if (debug)
        {
            QMessageBox::information(this,tr("Search result: "), result.information);
        }
        if (result.color != Qt::white)
        {
            for (int j = 1; j <= columnCount; ++j)
            {
                QVariant value = xlsx->read(i, j);
                if (!value.isValid())
                {
                    continue;
                }
                QXlsx::Format format = xlsx->cellAt(i, j)->format();
                format.setPatternBackgroundColor(result.color);
                xlsx->write(i, j, value, format);
            }
            // Department;
            xlsx->write(i, columnCount + 1, result.department);
            QXlsx::Format format = xlsx->cellAt(i, columnCount + 1)->format();
            format.setPatternBackgroundColor(result.color);
            xlsx->write(i, columnCount + 1, result.department, format);

            // Row(s)
            QVariant value(result.information);
            xlsx->write(i, columnCount + 2, value);
            format = xlsx->cellAt(i, columnCount + 2)->format();
            format.setPatternBackgroundColor(result.color);
            xlsx->write(i, columnCount + 2, value, format);
        }
        if ((result.color == Qt::white) // add to database
            && check_box_check->isChecked())
        {
            newCount++;
            QString q = "INSERT INTO " + table_names.at(0) + " (";
            for (int k = 1; k < column_names.at(0).size(); ++k)
            {
                q += column_names.at(0).at(k).first;
                if (k < column_names.at(0).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ") VALUES(";
                }
            }
            for (int k = 1; k < column_names.at(0).size(); ++k)
            {
                q += ":" + column_names.at(0).at(k).first;
                if (k < column_names.at(0).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ");";
                }
            }
            query.prepare(q);
//            qDebug() << q;
            QString name = "";
            for (int k = 1; k <= visible_columns.at(0); ++k)
            {
                QVariant value = xlsx->read(i, k);
                query.bindValue(":" + column_names.at(0).at(k).first, value);
//                qDebug() << ":" + column_names.at(0).at(k).first << " = " << value.toString();
                if (column_names.at(0).at(k).first == "Name")
                {
                    name = value.toString();
                }
            }
            QString name1 = refineString1(name);
            QString name2 = refineString2(name1);
            query.bindValue(":Name1", QVariant(name1));
            query.bindValue(":Name2", QVariant(name2));
            if (query.exec())
            {
                insertCount++;
            }
        }
    }
    xlsx->save();
    delete xlsx;
    QMessageBox::information(this,tr(""),
                             tr("Check ok: new = ") + QString::number(newCount) +
                             tr("; insert = ")      + QString::number(insertCount));
}
示例#4
0
void MainWindow::readExcelFile(QString file_name)
{
    QXlsx::Document * xlsx = new QXlsx::Document(file_name);
    QSqlQuery query;
    for (int table_id = number_of_tables - 1; table_id >= 0; --table_id)
    {
        if (!xlsx->selectSheet(table_names.at(table_id)))
        {
//            qDebug() << table_names.at(table_id) << " not found";
            continue;
        }
        QXlsx::CellRange range = xlsx->dimension();

        /*
        if (range.columnCount() != visible_columns.at(table_id))
        {
//            qDebug() << table_names.at(table_id) << " size not correct";
            continue;
        }
        */

        int rowCount = range.rowCount();
        for (int i = 1; i <= rowCount; ++i)
        {
            QString q = "INSERT INTO " + table_names.at(table_id) + " (";
            for (int j = 1; j < column_names.at(table_id).size(); ++j)
            {
                q += column_names.at(table_id).at(j).first;
                if (j < column_names.at(table_id).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ") VALUES(";
                }
            }
            for (int j = 1; j < column_names.at(table_id).size(); ++j)
            {
                q += ":" + column_names.at(table_id).at(j).first;
                if (j < column_names.at(table_id).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ");";
                }
            }
//            qDebug() << q;
            query.prepare(q);
            QString name = "";
            for (int j = 1; j <= visible_columns.at(table_id); ++j)
            {
                QVariant value = xlsx->read(i, j);
//                qDebug() << ":" + column_names.at(table_id).at(j).first << " : " << value.toString();
                query.bindValue(":" + column_names.at(table_id).at(j).first, value);
                if (table_id == 0 && column_names.at(table_id).at(j).first == "Name")
                {
                    name = value.toString();
                }
            }
            if (table_id == 0)
            {
                QString name1 = refineString1(name);
                QString name2 = refineString2(name1);
                query.bindValue(":Name1", QVariant(name1));
//                qDebug() << ":Name1" << " : " << name1;
                query.bindValue(":Name2", QVariant(name2));
//                qDebug() << ":Name2" << " : " << name2;
            }
            query.exec();
        }
    }
    delete xlsx;
}
示例#5
0
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;
}