qt中数据库和excel互导数据————附带详细步骤和代码 ...

0 背景

因为需要批量导入和导出数据，所以需要用到excel。实现把数据库的数据导入到excel中，把excel中的数据导出到数据库。这里使用了开源代码库QXlsx。
1 准备QXlsx情况

官网中的qmake的使用方法，cmake的使用方法。
1.1 cmake安装使用

• 1，输入下列指令安装：
  

1. mkdir build
2. cd build
3. cmake ../QXlsx/ -DCMAKE_INSTALL_PREFIX=... -DCMAKE_BUILD_TYPE=Release
4. cmake --build .
5. cmake --install .

复制代码

在CMakeLists.txt中添加如下内容:

1. find_package(QXlsxQt5 REQUIRED) # or QXlsxQt6
2. target_link_libraries(myapp PRIVATE QXlsx::QXlsx)

复制代码

• 2，下面是无需安装的两种使用方法：
  

使用cmake的子目录在 CMakeLists.txt:

1. add_subdirectory(QXlsx)
2. target_link_libraries(myapp PRIVATE QXlsx::QXlsx)

复制代码

使用 cmake FetchContent 在 CMakeLists.txt:

1. FetchContent_Declare(
2.   QXlsx
3.   GIT_REPOSITORY https://github.com/QtExcel/QXlsx.git
4.   GIT_TAG        sha-of-the-commit
5.   SOURCE_SUBDIR  QXlsx
6. )
7. FetchContent_MakeAvailable(QXlsx)
8. target_link_libraries(myapp PRIVATE QXlsx::QXlsx)

复制代码

如果 QT_VERSION_MAJOR没有设置, QXlsx’s的 CMakeLists.txt 将实验本身寻找 Qt 版本（5 或 6）。
1.2 qmake使用

下载QXsx的github项目代码。

• 1，把QXsx项目中的代码（选中的三个项目）复制到本身项目下；
  

复制到本身项目下（新建一个QXlxs文件夹，存储文件）：

• 2，在pro中添加如下代码；
  

1. QXLSX_PARENTPATH=./         # current QXlsx path is . (. means curret directory)
2. QXLSX_HEADERPATH=./QXlsx/header/  # current QXlsx header path is ./header/
3. QXLSX_SOURCEPATH=./QXlsx/source/  # current QXlsx source path is ./source/
4. include(./QXlsx/QXlsx.pri)

复制代码

• 3，编译文件后，会自动把文件添加到项目中（绿色的那一部门）；
  

• 4，添加如下头文件，就可以开始项目编写；
  

1. #include "xlsxdocument.h"
2. #include "xlsxchartsheet.h"
3. #include "xlsxcellrange.h"
4. #include "xlsxchart.h"
5. #include "xlsxrichstring.h"
6. #include "xlsxworkbook.h"

复制代码

测试程序：

1. // main.cpp#include <QCoreApplication>#include "xlsxdocument.h"
2. #include "xlsxchartsheet.h"
3. #include "xlsxcellrange.h"
4. #include "xlsxchart.h"
5. #include "xlsxrichstring.h"
6. #include "xlsxworkbook.h"
7. using namespace QXlsx;int main(int argc, char *argv[]){    QCoreApplication a(argc, argv);    QXlsx::Document xlsx;    xlsx.write("A1", "Hello Qt!"); // write "Hello Qt!" to cell(A,1). it's shared string.    xlsx.saveAs("Test.xlsx"); // save the document as 'Test.xlsx'    return 0;    // return a.exec();}

复制代码

2 把excel数据导出到mysql数据库

• 1，准备要导入的账号和密码的excel表（第一举动数据库的字段名，必须一样；如果数据库中字段值不能为空，excel中数据也不能为空）；
  
  
  
  账号信息.xlsx
  

数据库中的login_information表

• 2，在数据库中创建表格；
  

1. DROP TABLE IF EXISTS `login_information`;
2. CREATE TABLE `login_information`  (
3.   `account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
4.   `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
5.   PRIMARY KEY (`account`) USING BTREE
6. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

复制代码

• 3，创建数据库连接；
  

方法：

1. static bool CreateConnection(){
2.     //        qDebug()<<"查看目前可用驱动";
3.     //        QStringList drivers = QSqlDatabase::drivers();
4.     //        for(auto driver: drivers){
5.     //            qDebug()<<driver<<" ";
6.     //        }
8.     //设置数据库驱动
9.     QSqlDatabase mysqlDB = QSqlDatabase::addDatabase("QMYSQL", "mysql_connection1");
10.     mysqlDB.setHostName("192.168.0.104");
11.     mysqlDB.setUserName("root");
12.     mysqlDB.setPassword("password");
13.     mysqlDB.setPort(8889);
14.     mysqlDB.setDatabaseName("test_db");
16.     //根据系统环境设计数据库路径
17.     // Q_OS_LINUX：Q_OS_WIN： Q_OS_MAC   Q_OS_WIN32
19.     //如果远程mysql数据库没有打开
20.     if(!mysqlDB.open()){
21.         return false;
22.     }else{
24.         // #ifdef Q_OS_WIN
25.         mysqlDB.exec("SET NAMES 'GBK'");
26.         // #endif
27.         // #ifdef Q_OS_MAC
29.         // #endif
30.     }
32.     //QSqlDatabase sqliteDB = QSqlDatabase::addDatabase("QSQLITE", "sqlite_connection1");
34.     // #ifdef Q_OS_WIN //Q_OS_WIN32
35.     //     qDebug()<<"QCoreApplication::applicationDirPath():"<<QCoreApplication::applicationDirPath();
36.     //     sqliteDB.setDatabaseName(QCoreApplication::applicationDirPath() + QString("/database/LocalSystemDatabse.db"));
37.     // #endif
39.     //如果本地sqlite数据库没有打开
40.     // if(!sqliteDB.open()){
41.     //     QMessageBox* databaseInformationBox = new QMessageBox(QMessageBox::Critical, ("信息提示"),  ("不能建立本地数据库连接！"), QMessageBox::Yes);
42.     //     auto button =  databaseInformationBox->exec();
43.     //     if(button == QMessageBox::Yes){
44.     //         databaseInformationBox->deleteLater();
45.     //     }
46.     //     return false;
47.     // }
49.     return true;
51. }

复制代码

调用：

1. //main中创建数据库连接
2. int main(int argc, char *argv[])
3. {
4.     QCoreApplication a(argc, argv);
6.     //连接数据库
7.     if (!CreateConnection()){
8.         qDebug()<<"数据库连接失败";
9.     }
10.     return a.exec();
11. }

复制代码

• 4，把excel中的数据导入到数据库中；
  

1. bool exportExcel2Database(QStringList filePaths,  QString xlsxName, QString sqlSentence){
2.     QList<bool> execResultList;//操作的结果集
3.     bool execResult = false;
4.     QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
5.     QSqlQuery query(db);
6.     if(db.transaction()){
7.         foreach(QString filePath, filePaths) {
8.             QXlsx::Document xlsx(filePath);
10.             if(!xlsx.selectSheet(xlsxName)){/*在当前打开的xlsx文件中，找一个名字为ziv的sheet*/
11.                 //xlsx.addSheet(xlsxName);//找不到的话就添加一个名为ziv的sheet
12.                 qDebug()<<"没有对应的xlsx表";
13.                 return false;
14.             }else{
16.             }
18.             QQueue<QString> tableFieldQueue;
19.             QHash<QString, QVariantList> tableAlterFiledValue;
21.             for(int row = 1; row <= xlsx.dimension().rowCount(); row++) {
22.                 // 获取每行的数据并插入到数据库中
23.                 for(int col = 1; col <= xlsx.dimension().columnCount();col++){
24.                     if(row == 1){
25.                         tableFieldQueue.enqueue(xlsx.read(row, col).toString());
27.                     }else{
28.                         tableAlterFiledValue[tableFieldQueue[col-1]].append(xlsx.read(row, col));
29.                     }
30.                 }
31.             }
33.             query.prepare(sqlSentence);
34.             foreach (QString tableFiled, tableFieldQueue) {
35.                 query.addBindValue(tableAlterFiledValue[tableFiled]);
36.             }
38.             execResult = query.execBatch();
39.             execResultList.append(execResult);
40.             if(!execResult) {//批量执行数据插入
41.                 qDebug() <<  query.lastError().databaseText();
42.             }
44.         }
46.         foreach (bool result, execResultList) {
47.             if(result == false){
48.                 if(!db.rollback())
49.                 {
50.                     qDebug() << "数据库回滚失败"<<db.lastError().databaseText(); //回滚
51.                 }else{
52.                     qDebug()<<"数据库回滚成功";
53.                 }
54.                 return false;
55.             }
57.         }
59.         if(db.commit()){
60.             return true;
61.         }else{
62.             return false;
63.         }
64.     }
65.     return false;
66. }

复制代码

调用：

1.     QStringList filePaths;
2.     filePaths<<"D:/test/账号信息.xlsx";
4.     //考试细节步骤
5.     QString sql2 = QString("INSERT INTO  login_information(account, password)  VALUES (?, ?)");
6.     QString xlsxName2 = "账号信息";
7.     // qDebug()<<sql2;
9.     if(exportExcel2Database(filePaths, xlsxName2, sql2)){
10.         qDebug()<<"导入成功";
11.     }else{
12.         qDebug()<<"导入失败";
13.     }

复制代码

,
3 把mysql数据库的数据写入到excel

• 1，创建数据库连接，同上；
  
• 2，把数据库中表的数据导出到excel中；
  

1. bool exportData2XLSX(QString fileName, QString tableName)
2. {
4.     QXlsx::Document xlsx;
5.     QXlsx::Format format1;/*设置标题单元的样式*/
6.     format1.setFontSize(12);/*设置字体大小*/
7.     format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/
8.     //format1.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/
9.     //format1.setFontBold(true);/*设置加粗*/
11.     if(!xlsx.selectSheet("表格数据")){/*在当前打开的xlsx文件中，找一个名字为ziv的sheet*/
12.         xlsx.addSheet("表格数据");//找不到的话就添加一个名为ziv的sheet
13.     }
15.     QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
16.     QString tmpSql = QString("SELECT * FROM %1").arg(tableName);
17.     QSqlQuery query(db);
18.     if(query.exec(tmpSql)){
19.         //表头列
20.         QSqlRecord queryRecord(query.record());
21.         qDebug()<<"queryRecord.count():"<<queryRecord.count();
22.         for(int colNum = 0; colNum < queryRecord.count(); colNum++){
23.             //qDebug() <<  queryRecord.fieldName(colNum);
24.             xlsx.write(1, colNum+1,  queryRecord.fieldName(colNum),format1);
25.         }
27.         //表格数据
28.         int rowNum = 2;
29.         while(query.next()){
30.             for(int colNum = 0; colNum < queryRecord.count(); colNum++){
31.                 xlsx.write(rowNum, colNum + 1, query.value(colNum),format1);
32.             }
33.             rowNum++;
34.         }
35.     }else{
36.         return false;
37.     }
39.     if(fileName.isEmpty())
40.         return false;
42.     xlsx.saveAs(fileName);//保存文件
44.     return true;
45. }

复制代码

调用：

2. int main(int argc, char *argv[])
3. {
4.     QCoreApplication a(argc, argv);
6.     //连接数据库
7.     if (!CreateConnection()){
8.         qDebug()<<"数据库连接失败";
9.     }
10.    
11.     QString tableName = "login_information";
12.     QString fileName = "D:/账号.xlsx";
13.     if(exportData2XLSX(fileName, tableName)){
14.         qDebug()<<"导入excel成功";
15.     }else{
16.         qDebug()<<"导入excel失败";
17.     }
18.    
19.     return a.exec();
20. }

复制代码

4 完整代码

1. #include <QCoreApplication>#include "create_connection.h"#include "xlsxdocument.h"
2. #include "xlsxchartsheet.h"
3. #include "xlsxcellrange.h"
4. #include "xlsxchart.h"
5. #include "xlsxrichstring.h"
6. #include "xlsxworkbook.h"
7. #include <QSqlError>#include <QQueue>#include <QHash>#include <QSqlRecord>bool exportExcel2Database(QStringList filePaths,  QString xlsxName, QString sqlSentence){    QList<bool> execResultList;    bool execResult = false;    QSqlDatabase db = QSqlDatabase::database("mysql_connection1");    QSqlQuery query(db);    if(db.transaction()){        foreach(QString filePath, filePaths) {            QXlsx::Document xlsx(filePath);            if(!xlsx.selectSheet(xlsxName)){/*在当前打开的xlsx文件中，找一个名字为ziv的sheet*/                //xlsx.addSheet(xlsxName);//找不到的话就添加一个名为ziv的sheet                qDebug()<<"没有对应的xlsx表";                return false;            }else{            }            QQueue<QString> tableFieldQueue;            QHash<QString, QVariantList> tableAlterFiledValue;            for(int row = 1; row <= xlsx.dimension().rowCount(); row++) {                // 获取每行的数据并插入到数据库中                for(int col = 1; col <= xlsx.dimension().columnCount();col++){                    if(row == 1){                        tableFieldQueue.enqueue(xlsx.read(row, col).toString());                    }else{                        tableAlterFiledValue[tableFieldQueue[col-1]].append(xlsx.read(row, col));                    }                }            }            query.prepare(sqlSentence);            foreach (QString tableFiled, tableFieldQueue) {                query.addBindValue(tableAlterFiledValue[tableFiled]);            }            execResult = query.execBatch();            execResultList.append(execResult);            if(!execResult) {//批量实行数据插入                qDebug() <<  query.lastError().databaseText();            }        }        foreach (bool result, execResultList) {            if(result == false){                if(!db.rollback())                {                    qDebug() << "数据库回滚失败"<<db.lastError().databaseText(); //回滚                }else{                    qDebug()<<"数据库回滚成功";                }                return false;            }        }        if(db.commit()){            return true;        }else{            return false;        }    }    return false;}bool exportData2XLSX(QString fileName, QString tableName)
8. {
10.     QXlsx::Document xlsx;
11.     QXlsx::Format format1;/*设置标题单元的样式*/
12.     format1.setFontSize(12);/*设置字体大小*/
13.     format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/
14.     //format1.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/
15.     //format1.setFontBold(true);/*设置加粗*/
17.     if(!xlsx.selectSheet("表格数据")){/*在当前打开的xlsx文件中，找一个名字为ziv的sheet*/
18.         xlsx.addSheet("表格数据");//找不到的话就添加一个名为ziv的sheet
19.     }
21.     QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
22.     QString tmpSql = QString("SELECT * FROM %1").arg(tableName);
23.     QSqlQuery query(db);
24.     if(query.exec(tmpSql)){
25.         //表头列
26.         QSqlRecord queryRecord(query.record());
27.         qDebug()<<"queryRecord.count():"<<queryRecord.count();
28.         for(int colNum = 0; colNum < queryRecord.count(); colNum++){
29.             //qDebug() <<  queryRecord.fieldName(colNum);
30.             xlsx.write(1, colNum+1,  queryRecord.fieldName(colNum),format1);
31.         }
33.         //表格数据
34.         int rowNum = 2;
35.         while(query.next()){
36.             for(int colNum = 0; colNum < queryRecord.count(); colNum++){
37.                 xlsx.write(rowNum, colNum + 1, query.value(colNum),format1);
38.             }
39.             rowNum++;
40.         }
41.     }else{
42.         return false;
43.     }
45.     if(fileName.isEmpty())
46.         return false;
48.     xlsx.saveAs(fileName);//保存文件
50.     return true;
51. }
53. int main(int argc, char *argv[]){    QCoreApplication a(argc, argv);    //连接数据库    if (!CreateConnection()){        qDebug()<<"数据库连接失败";    }        QString tableName = "login_information";    QString fileName = "D:/账号.xlsx";    if(exportData2XLSX(fileName, tableName)){        qDebug()<<"导入excel成功";    }else{        qDebug()<<"导入excel失败";    }    return a.exec();}

复制代码

5 项目代码仓库

代码仓库(欢迎star)：
github仓库
码云

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。