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