Qt浅谈之五十四excel与mdb与shape的相互转换

 

一、简介

        Qt5下使用QtXlsx的库（QXlsx::Worksheet）操作excel、sql模块（QSqlDatabase::database）操作mdb、ogr的第三方库（OGRDataSource）解析shape，来实现excel、mdb、shape的相互转换。

       效果如下：

二、详解

1、QXlsx代码

由于Qt5.9还没有xlsx的模块，因此需要自己下载QtXlsx的工程进行编译成静态库libQtXlsx.a

bool FieldMap::setTables(QString mdb_file, QString point_file, QString line_shape, QString polygon_shape, QString output_file)
{
    m_excel_path = mdb_file + "/";
    m_output_file = output_file + "/";
    QString v_point_xlsx = mdb_file + "/" + point_file + ".xlsx";
    QXlsx::Document xlsx_pt(v_point_xlsx);
    QXlsx::Workbook *workBook = xlsx_pt.workbook();
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
    QString value;

    ui->pointNameCBox->insertItem(0, "");
    ui->pointXCBox->insertItem(0, "");
    ui->pointYCBox->insertItem(0, "");
    int number = workSheet->dimension().columnCount();
    for (int index = 1; index <= number; index++)
    {
        QXlsx::Cell *cell = workSheet->cellAt(1, index);
        if (cell == NULL) continue;
        value = cell->value().toString();

        ui->pointNameCBox->insertItem(index+1, value);
        ui->pointXCBox->insertItem(index+1, value);
        ui->pointYCBox->insertItem(index+1, value);
    }

    QString v_line_xlsx = mdb_file + "/" + line_shape + ".xlsx";
    QXlsx::Document xlsx_ln(v_line_xlsx);
    workBook = xlsx_ln.workbook();
    workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
    ui->lineStartCBox->insertItem(0, "");
    ui->lineEndCBox->insertItem(0, "");
    for (int index = 1; index <= number; index++)
    {
        QXlsx::Cell *cell = workSheet->cellAt(1, index);
        if (cell == NULL) continue;
        value = cell->value().toString();

        ui->lineStartCBox->insertItem(index+1, value);
        ui->lineEndCBox->insertItem(index+1, value);
    }
    return true;
}

 

2、OGR（shape）代码

bool FieldMap::write_shape_file(QVector<QString>v_point_files, QVector<QString>v_line_files, QVector<QString>v_polygon_files)
{
    const char *pszDriverName = "ESRI Shapefile";
    CPLSetConfigOption("SHAPE_ENCODING","");
    CPLSetConfigOption("GDAL_FILENAME_IS_UTF8","NO");
    RegisterOGRShape();
    //OGRRegisterAll();
    OGRSFDriverRegistrar* pReg = OGRSFDriverRegistrar::GetRegistrar();
    OGRSFDriver* poDriver = pReg->GetDriverByName(pszDriverName);
    if (!poDriver)
    {
        return false;
    }
    emit process_value(0);

    unsigned int v_total_num = 0;
    unsigned int v_gap = 0;
    unsigned int v_step = 0;
    unsigned int v_current_value = 0;
    QXlsx::Workbook *workBook = NULL;
    QXlsx::Worksheet *workSheet = NULL;
    for(int index = 0; index < v_point_files.size(); index++)
    {
        if (!v_point_files.at(index).isEmpty())
        {
            QString v_point_xlsx = m_excel_path + v_point_files.at(index) + ".xlsx";
            QXlsx::Document xlsx_pt(v_point_xlsx);
            workBook = xlsx_pt.workbook();
            workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
            v_total_num += workSheet->dimension().rowCount() - 1;
        }
    }
    for(int index = 0; index < v_line_files.size(); index++)
    {
        if (!v_line_files.at(index).isEmpty())
        {
            QString v_line_xlsx = m_excel_path + v_line_files.at(index) + ".xlsx";
            QXlsx::Document xlsx_ln(v_line_xlsx);
            workBook = xlsx_ln.workbook();
            workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
            v_total_num += workSheet->dimension().rowCount() - 1;
        }
    }

    v_gap = v_total_num / 100 + 1;

    OGRDataSource *poDs = NULL;
    QString v_shape_path = m_output_file;
    if (v_shape_path.at(v_shape_path.length() - 1) != '/') v_shape_path += "/";
    m_point_info.clear();

    for(int iter = 0; iter < v_point_files.size(); iter++)
    {
        QString m_point_file = v_point_files.at(iter);
        if (!m_point_file.isEmpty())
        {
            QString tmp_point = v_point_files.at(iter);

            QString v_name = m_output_file + "/" + tmp_point + ".shp";
            poDs = poDriver->CreateDataSource( v_name.toLocal8Bit().data(), NULL );
            OGRLayer *poLayer = poDs->CreateLayer(tmp_point.toLocal8Bit().data(), NULL, wkbPoint,NULL);
            if (poLayer == NULL) continue;
            write_point_shape(tmp_point, poLayer, v_step, v_current_value, v_gap);
            OGRDataSource::DestroyDataSource(poDs);
        }
    }
    for(int iter = 0; iter < v_line_files.size(); iter++)
    {
        QString m_line_file = v_line_files.at(iter);
        if (!m_line_file.isEmpty())
        {
            QString tmp_line = v_line_files.at(iter);

            QString v_name = m_output_file + "/" + tmp_line + ".shp";
            poDs = poDriver->CreateDataSource( v_name.toLocal8Bit().data(), NULL );
            OGRLayer *poLayer = poDs->CreateLayer(tmp_line.toLocal8Bit().data(), NULL, wkbLineString25D,NULL);
            if (poLayer == NULL) continue;
            write_line_shape(tmp_line, poLayer, v_step, v_current_value, v_gap);
            OGRDataSource::DestroyDataSource(poDs);
        }
    }

    emit process_value(100);
    return true;
}

void FieldMap::write_point_shape(QString v_point, OGRLayer *poLayer, unsigned int &v_step, unsigned int &v_current_value, unsigned int v_gap)
{
    QString value;
    int pointCode = -1, pointX = -1, pointY = -1;
    QString v_point_xlsx = m_excel_path + v_point + ".xlsx";
    QXlsx::Document xlsx_pt(v_point_xlsx);
    QXlsx::Workbook *workBook = xlsx_pt.workbook();
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
    for(int index = 1; index <= workSheet->dimension().columnCount(); ++index)
    {
        QXlsx::Cell *cell = workSheet->cellAt(1, index);
        if (cell == NULL) continue;
        value = cell->value().toString();
        if (value == ui->pointNameCBox->currentText())
        {
            pointCode = index;
        }
        else if (value == ui->pointXCBox->currentText())
        {
            pointX = index;
        }
        else if (value == ui->pointYCBox->currentText())
        {
            pointY = index;
        }
        OGRFieldDefn oField(value.toLocal8Bit().data(), get_type(QVariant::String));
        oField.SetWidth(32);
        oField.SetPrecision(3);
        poLayer->CreateField(&oField);
    }
    for (int index = 2; index <= workSheet->dimension().rowCount(); ++index)
    {
        PointInfo v_info;
        OGRFeature *poFeature = new OGRFeature( poLayer->GetLayerDefn() );
        for(int pt = 1; pt <= workSheet->dimension().columnCount(); ++pt)
        {
            QXlsx::Cell *cell = workSheet->cellAt(index, pt);
            if (cell == NULL) continue;
            value = cell->value().toString();
            poFeature->SetField(pt-1, value.toLocal8Bit().data());
            if(pt == pointCode)
            {
                v_info.m_point_name = value;
            }
            else if (pt == pointX)
            {
                v_info.x = value.toDouble();
            }
            else if (pt == pointY)
            {
                v_info.y = value.toDouble();
            }
        }

        if (!v_info.m_point_name.isEmpty())
        {
            m_point_info.insert(v_info.m_point_name, v_info);
        }
        OGRPoint poPoint;
        poPoint.setX(v_info.x);
        poPoint.setY(v_info.y);
        poFeature->SetGeometry( &poPoint );
        poLayer->CreateFeature( poFeature );
        OGRFeature::DestroyFeature(poFeature);
        if (++v_step >= v_gap)
        {
            v_step = 0;
            emit process_value(++v_current_value);
        }
    }
}

 

3、MDB代码

bool FieldMap::setTables(QString mdb_file, QString point_file, QString line_shape, QString polygon_shape, QString output_file)
{
    m_output_file = output_file;
    QString connID = "access.connection-mdb";
    v_db = QSqlDatabase::database(connID, false);

    if (!v_db.isValid()) {
        v_db = QSqlDatabase::addDatabase("QODBC", connID);
    }
    if(v_db.isOpen()) {
        v_db.close();
    }
    QString databaseName = QStringLiteral("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=%1").arg(mdb_file);
    v_db.setDatabaseName(databaseName);
    bool ok = v_db.open();
    if (!ok) {
        return false;
    }
    QSqlQuery v_query(v_db);
    QString v_sql = "select * from %1";
    if (!point_file.isEmpty())
    {
        QString v_point_sql = v_sql.arg(point_file);
        if (v_query.exec(v_point_sql))
        {
            QSqlRecord v_record = v_query.record();
            ui->pointCodeCBox->insertItem(0, "");
            ui->pointXCBox->insertItem(0, "");
            ui->pointYCBox->insertItem(0, "");
            ui->pointNameCBox->insertItem(0, "");
            for(int index = 0; index < v_record.count(); index++)
            {
                ui->pointCodeCBox->insertItem(index+1, v_record.fieldName(index));
                ui->pointXCBox->insertItem(index+1, v_record.fieldName(index));
                ui->pointYCBox->insertItem(index+1, v_record.fieldName(index));
                ui->pointNameCBox->insertItem(index+1, v_record.fieldName(index));
            }
        }
    }
    if (!line_shape.isEmpty())
    {
        QString v_line_sql = v_sql.arg(line_shape);
        if (v_query.exec(v_line_sql))
        {
            QSqlRecord v_record = v_query.record();
            ui->lineCodeCBox->insertItem(0, "");
            ui->lineStartCBox->insertItem(0, "");
            ui->lineEndCBox->insertItem(0, "");
            for(int index = 0; index < v_record.count(); index++)
            {
                ui->lineCodeCBox->insertItem(index+1, v_record.fieldName(index));
                ui->lineStartCBox->insertItem(index+1, v_record.fieldName(index));
                ui->lineEndCBox->insertItem(index+1, v_record.fieldName(index));
            }
        }
    }
    if (!polygon_shape.isEmpty())
    {
        QString v_polygon_sql = v_sql.arg(polygon_shape);
        if (v_query.exec(v_polygon_sql))
        {
            QSqlRecord v_record = v_query.record();
            ui->polygonCodeCBox->insertItem(0, "");
            for(int index = 0; index < v_record.count(); index++)
            {
                ui->polygonCodeCBox->insertItem(index+1, v_record.fieldName(index));
            }
        }
    }
    return true;
}

4、编译运行

其中mdb或excel对应shape的字段映射表

三、总结

（1）上述代码只是部分代码，参考其操作就能实现数据的访问，实现数据转换。

（2）要想做成通用性的转换工具，支持各种不同字段的数据，还需要各个方面的考虑。