本文共 6081 字,大约阅读时间需要 20 分钟。
方法一:qt使用odbc打开excel并写入
[cpp]
使用qt通过odbc打开一个excel,读取数据,变换格式后写入另外一个excel。 QMessageBox::information(NULL, "提示", "请选择读取的xls文件"); QFileDialog fileSelect; fileSelect.setWindowTitle("请选择读取数据xls文件"); fileSelect.exec(); QStringList fileList = fileSelect.selectedFiles(); if (fileList.count() == 0) return; QString dbfile = fileList.first(); //指定excel文件 //输出数据结果文件 QMessageBox::information(NULL, "提示", "请选择输出结果的xls文件"); fileSelect.setWindowTitle("请选择输出数据xls文件"); fileSelect.exec(); fileList = fileSelect.selectedFiles(); if (fileList.count() == 0) return; QString strResultFile = fileList.first(); //指定excel文件 qDebug() << "result file: " << strResultFile; //ui->textBrowser->setText(fileList.first()); //打开输入数据xls QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "input_source"); //QStringList dbList = QSqlDatabase::drivers(); //qDebug() << dbList; QString dbName = "Driver={Microsoft Excel Driver (*.xls)};Dbq=" + dbfile; //qDebug() << dbName; //db.setDatabaseName("Driver={Microsoft Excel Driver (*.xls)};Readonly=0;DriverId=790;Dbq=" + dbfile); db.setDatabaseName(dbName); if (!db.open()) { qDebug() << "Database Error"; } //qDebug() << "Connected Successed!"; //打开输出数据xls QSqlDatabase resultDB = QSqlDatabase::addDatabase("QODBC", "output_result"); QString resultDBName = "Driver={Microsoft Excel Driver (*.xls)};readonly = 0;Dbq=" + strResultFile; resultDB.setDatabaseName(resultDBName); if (!resultDB.open()) { qDebug() << "result Database Error"; } //初始化结果数据格式, 默认为每月30天 QSqlQuery resultQuery(resultDB); resultQuery.exec("drop table kaoqin_result"); qDebug() << resultQuery.lastError().text(); resultQuery.exec("CREATE TABLE kaoqin_result (name text, a1 text,a2 text, a3 text, a4 text, a5 text, a6 text, " " a7 text, a8 text, a9 text, a10 text, a11 text, a12 text, a13 text, a14 text, a15 text, " " a16 text, a17 text, a18 text, a19 text, a20 text, a21 text)"); qDebug() << resultQuery.lastError().text(); resultQuery.exec("insert into kaoqin_result(name) values('zps')"); QString strUpdateSql = "update kaoqin_result set "; //查询输入数据同时输出到输出xls QSqlQuery query(db); //注意此处aa为实际要读取的页名 一般为Sheet1 Sheet2等 if (! query.exec("select * from [" + QString("aa") + "$] where b='zps'")) { qDebug() << "select from sheet 1 failed"; qDebug() << "error eason: " << query.lastError().text(); } qDebug() << "begin select from excel"; qDebug() << "result count: " << query.record().count(); //读取输入文件并写入结果文件 int dateCount = 1; while (query.next()) { QString upColumn = "a" + QString::number(dateCount); QString column1= query.value(0).toString(); QString column2 = query.value(1).toString(); QString column3 = query.value(2).toString(); QString column4 = query.value(3).toString(); QString column5 = query.value(4).toString(); if (dateCount == 1) strUpdateSql += upColumn + "='" + column4 + " " + column5 +"'"; else strUpdateSql += ", " + upColumn + "='" + column4 + " " + column5 +"'"; dateCount++; QString lineText = column1 + " " + column2 + " " +column3 + " " + column4 + " " + column5; ui->textBrowser->setText(ui->textBrowser->toPlainText() + lineText + "\n"); //qDebug() << column1; } strUpdateSql += " where name='zps'"; qDebug() << "after debug result"; //将结果插入到结果xls中 qDebug() << strUpdateSql; resultQuery.exec(strUpdateSql); qDebug() << resultQuery.lastError().text(); db.close(); resultDB.close();
方法二:
qt版本是qt5.5, 需先在.pro文件下添加axcontainer组件,
[cpp]
解决方案:
m_odbc_db.tables(QSql::AllTables)本身就不支持非英文,此处是QT的一个BUG,读出来的就是乱码,根本就没法通过编码转换得出中文。
只需要编译sqldrivers/odbc这一个插件就行了。
用qtcreater打开C:\Qt\Qt5.9.5\5.9.5\Src\qtbase\src\plugins\sqldrivers\sqldrivers.pro,进行编译注意2376这一行QString fieldVal = qGetStringData(hStmt, 2, -1, false);
改为QString fieldVal = qGetStringData(hStmt, 2, -1, true);就行了
附件有编译好的qsqlodbc.dll,替换掉5.9.5\mingw53_32\plugins\sqldrivers\qsqlodbc.dll即可
转载地址:http://nblai.baihongyu.com/