qt 使用CRUD方式操作excel

//注意点,列名不能使用单独使用time,不能有-,最好不要有冒号和空格,这些容易出错,导致无法插入

 

    //#include <QFileDialog>
    //#include <QSqlError>
    //#include <QSqlDatabase>
    //#include <QSqlQuery>
    //#include <QDateTime>
    //QT       += sql

    //myDebug;

    ui->data_fileinfo->setText(QStringLiteral("写入excel中..."));
    QString qStringExcelName = fileName+".xls";
    QString sheetName = "";


    //=================================================
    QString qstringSql = "";
    //if(qStringExcelSheetName.length()<=0) qStringExcelSheetName = QDateTime::currentDateTime().toString("yyyy_MM_dd___hh_mm_ss");//Excel内sheet页的名字//
    //if(qStringExcelSheetName.length()<=0) qStringExcelSheetName = qStringExcelName+"_"+QString::number(languageflag);//Excel内sheet页的名字//
    QSqlDatabase qsqldatabaseDB = QSqlDatabase::addDatabase("QODBC","excelexport");//连接到excel。使用QODBC的方式操作Excel，该方式同样可操作数据库
    if( !qsqldatabaseDB.isValid()){
        return;//type error
    }

    QString qstringDsn =  "DRIVER={Microsoft Excel Driver (*.xls)};"
                  "DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=";
    qstringDsn += ("\"" + qStringExcelName + "\"" + ";DBQ=" + qStringExcelName);//确定要操作的excel信息，可直接百度，有源码教程。
    qsqldatabaseDB.setDatabaseName(qstringDsn);

    if( !qsqldatabaseDB.open()){// open connection
        return;  //! db error
    }
    QSqlQuery qsqlqueryDB(qsqldatabaseDB);//查询命令

    sheetName="688";
    qstringSql = QString("CREATE TABLE [%1] (").arg(sheetName);
    qstringSql += "["+sheetName+"byte0_1] varchar(200),["+sheetName+"byte2_3] varchar(200),["+sheetName+"byte4_5] varchar(200),["+sheetName+"byte6_7] varchar(200),["+sheetName+"_time] varchar(200))";
    qsqlqueryDB.prepare( qstringSql); if( !qsqlqueryDB.exec()) {  }

    //myDebugMsg(qstringSql);


    QString qstringSql688 = QString("INSERT INTO [%1] (").arg( sheetName);//insert a record
    qstringSql688 += sheetName+"byte0_1,"+sheetName+"byte2_3,"+sheetName+"byte4_5,"+sheetName+"byte6_7,"+sheetName+"_time)VALUES(:p0,:p1,:p2,:p3,:p4)";

    //myDebugMsg(qstringSql688);

    sheetName="788";
    qstringSql = QString("CREATE TABLE [%1] (").arg(sheetName);
    qstringSql += "["+sheetName+"byte0_1] varchar(200),["+sheetName+"byte2_3] varchar(200),["+sheetName+"byte4_5] varchar(200),["+sheetName+"byte6_7] varchar(200),["+sheetName+"_time] varchar(200))";
    qsqlqueryDB.prepare( qstringSql); if( !qsqlqueryDB.exec()) {  }

    QString qstringSql788 = QString("INSERT INTO [%1] (").arg( sheetName);//insert a record
    qstringSql788 += sheetName+"byte0_1,"+sheetName+"byte2_3,"+sheetName+"byte4_5,"+sheetName+"byte6_7,"+sheetName+"_time)VALUES(:p0,:p1,:p2,:p3,:p4)";


    sheetName="794";
    qstringSql = QString("CREATE TABLE [%1] (").arg(sheetName);
    qstringSql += "["+sheetName+"byte0_1] varchar(200),["+sheetName+"byte2_3] varchar(200),["+sheetName+"byte4_5] varchar(200),["+sheetName+"byte6_7] varchar(200),["+sheetName+"_time] varchar(200))";
    qsqlqueryDB.prepare( qstringSql); if( !qsqlqueryDB.exec()) {  }

    QString qstringSql794 = QString("INSERT INTO [%1] (").arg( sheetName);//insert a record
    qstringSql794 += sheetName+"byte0_1,"+sheetName+"byte2_3,"+sheetName+"byte4_5,"+sheetName+"byte6_7,"+sheetName+"_time)VALUES(:p0,:p1,:p2,:p3,:p4)";


    sheetName="894";
    qstringSql = QString("CREATE TABLE [%1] (").arg(sheetName);
    qstringSql += "["+sheetName+"byte0_1] varchar(200),["+sheetName+"byte2_3] varchar(200),["+sheetName+"byte4_5] varchar(200),["+sheetName+"byte6_7] varchar(200),["+sheetName+"_time] varchar(200))";
    qsqlqueryDB.prepare( qstringSql); if( !qsqlqueryDB.exec()) { myDebug;  }

    //myDebugMsg(qstringSql);

    QString qstringSql894 = QString("INSERT INTO [%1] (").arg(sheetName);//insert a record
    qstringSql894 += sheetName+"byte0_1,"+sheetName+"byte2_3,"+sheetName+"byte4_5,"+sheetName+"byte6_7,"+sheetName+"_time)VALUES(:p0,:p1,:p2,:p3,:p4)";

    //myDebugMsg(qstringSql894);


    //从log中读取文件,然后分别写入对应的位置
    QFile file(fileName+".log");
    if(!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        myDebug;return;
    }
    while(!file.atEnd()) {
        QByteArray line = file.readLine();
        QString info(line);

        info = info.trimmed();
        if(info.length()==0 || info.contains(":")==false || info.split(":").at(1).length()<16)continue;
        QStringList li = info.split(";"); //688:0102030405060708,2021_11_19__14_00_30__333,space //688:0102030405060708,space,space //688:0102030405060708,space
        li.append(" ");
        QString t = li.at(1);
        QString data = li.at(0).split(":").at(1);

        // 小端格式     高字节在后，低字节在前
        short tem = 0; unsigned char _tem[10];memset(_tem,0,10);
        for(int i=0;i<8;i++){ _tem[i]=data.mid(i*2,2).toInt(nullptr,16); }
        memcpy(&tem,_tem+0,2); QString byte0 = QString::number(tem);
        memcpy(&tem,_tem+2,2); QString byte2 = QString::number(tem);
        memcpy(&tem,_tem+4,2); QString byte4 = QString::number(tem);
        memcpy(&tem,_tem+6,2); QString byte6 = QString::number(tem);

//        QString tem;
//        tem = data.mid(2,2)+data.mid(0,2);   QString byte0 = QString::number(tem.toShort(nullptr,16));
//        tem = data.mid(6,2)+data.mid(4,2);   QString byte2 = QString::number(tem.toShort(nullptr,16));
//        tem = data.mid(10,2)+data.mid(8,2);  QString byte4 = QString::number(tem.toShort(nullptr,16));
//        tem = data.mid(14,2)+data.mid(12,2); QString byte6 = QString::number(tem.toShort(nullptr,16));


        if(false){myDebug;}
        else if(info.startsWith("688")){ qsqlqueryDB.prepare( qstringSql688); }
        else if(info.startsWith("788")){ qsqlqueryDB.prepare( qstringSql788); }
        else if(info.startsWith("794")){ qsqlqueryDB.prepare( qstringSql794); }
        else if(info.startsWith("894")){ qsqlqueryDB.prepare( qstringSql894); }
        else { continue; }

        qsqlqueryDB.bindValue(":p0",byte0);
        qsqlqueryDB.bindValue(":p1",byte2);
        qsqlqueryDB.bindValue(":p2",byte4);
        qsqlqueryDB.bindValue(":p3",byte6);
        qsqlqueryDB.bindValue(":p4",t);
        if( !qsqlqueryDB.exec()) {
            //myDebug;
        }

    }
    ui->data_fileinfo->setText(QStringLiteral("写入excel完毕."));
    qsqldatabaseDB.close();

 

//　　数据如下

688:FFFF010001000100
788:FFFF010001000100
794:FFFF010001000100