学习日记3：Qt将excel文件导入MySQL数据库，tableview数据导出至excel文件

---

一、成果展示

本文实现的功能
以下两种功能用QAxObject 实现
1、点击导入按钮可将指定的excel文件中的所有sheet导入MySQL中，一个sheet一张表；
2、数据在tableview发生改变后，可导出成excel文件
以下功能用QXlsx
3、将excel文件内容直接显示到tableview上

---

二、 准备工作

Qt为我们提供了两个操作excel的类：QXlsx和QAxObject

1、关于QXlsx的准备工作，具体可看其它大佬教程
https://blog.csdn.net/qq_43627907/article/details/124175572

2、QAxObject 的相关操作流程

3、QAxObject 的常用函数
https://blog.csdn.net/aggie4628/article/details/102809700

---

三、代码示例

功能1

• 点击导入按钮可将指定的excel文件中的所有sheet导入MySQL中，一个sheet一张表；

• 需要注意的是在MySQL数据库中创建新表格时，是需要所有字段名的，所以我这里选择将excel文件的第一行单独取出来做了表头，同理在写excel文件时也要将tableview中的表头取出来。

//导入按钮
void MainWindow::on_pushButton_import_clicked()
{
    QString filePath = QFileDialog::getOpenFileName(this, tr("Open Excel file"), "", tr("Excel Files (*.xlsx *.xls)"));//打开选择文件窗口
    if (!filePath.isEmpty())
    {
        qDebug() << "当前打开的文件路径为"<<filePath;//显示文件路径
    }
    QAxObject *excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false);// 不显示 Excel 窗体
    QAxObject *work_books = excel->querySubObject("WorkBooks"); //获取工作簿集合
    work_books->dynamicCall("Open (const QString&)", filePath);//打开已存在的工作簿
    QAxObject *work_book = excel->querySubObject("ActiveWorkBook");//获取当前工作簿

    QAxObject *work_sheets = work_book->querySubObject("Sheets");  //Sheets也可换用WorkSheets
    int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目
    qDebug() << "当前文件有"<<sheet_count<< "张sheet";

    for(int i=1; i<=sheet_count; i++)//循环操作每张sheet
    {
        QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", i);
        QString work_sheet_name = work_sheet->property("Name").toString();  //获取工作表名称


        //查看数据库中是否有相应表格，没有就创建
        if(isTableExists(work_sheet_name))
        {
            int ok = QMessageBox::warning(this,tr("提示："),tr("当前数据库中已经存在该表，"
                                                            "确认替换吗？ "),QMessageBox::Yes,QMessageBox::No);
            if(ok == QMessageBox::No);
            else  //替换
            {
                QSqlQuery query(dbMYSQL);
                query.exec(QString("drop table if exists %1").arg(work_sheet_name));
                creatNewTable(work_sheet);
                InsertData(work_sheet);
            }
        }
        else
        {
            creatNewTable(work_sheet);
            InsertData(work_sheet);
        }
    }
    work_book->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭 excel
    delete excel;
    excel = NULL;
}
//判断表是否存在,存在为真，不存在为假
bool MainWindow::isTableExists(QString &table)
{
    QSqlQuery query(dbMYSQL);
    QString sql = QString("show tables;");  // 查询数据库中是否存在表名
    query.exec(sql);
    while(query.next())
    {
        QString biaoming=query.value(0).toString().trimmed();
        qDebug()<<"数据库中已存在："<<biaoming;
        if(QString::compare(biaoming,table,Qt::CaseInsensitive));
        else//存在
            return true;
    }
    return false;
}


//在mysql中创建新表格
void MainWindow::creatNewTable(QAxObject *work_sheet)
{
    //获取表头
    QString work_sheet_name = work_sheet->property("Name").toString();  //获取工作表名称
    QAxObject *used_range = work_sheet->querySubObject("UsedRange");//选取当前页面所有已使用单元格
    QAxObject *columns = used_range->querySubObject("Columns");
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int column_count = columns->property("Count").toInt();  //获取列数
    QString keyType[column_count];//表头数列

    for(int i = column_start;i < column_count+column_start;i ++)//获取表头
    {
        QAxObject *cell = work_sheet->querySubObject("Cells(int,int)",1, i);
        QString value = cell->dynamicCall("Value2()").toString();
        keyType[i-column_start] = value;
        qDebug() <<i-column_start<<":"<< keyType[i-column_start];
    }
    QString creatsql=QString("create table %1(").arg(work_sheet_name);//按表头在MySQL中创建新表
    for(int i=0;i<=column_count-1;i++)
    {
        creatsql=creatsql+QString("%1").arg(keyType[i]);
        if(i < column_count-1 )
        {
            creatsql =creatsql+ QString(" varchar(20),");
        }
        else
        {
            creatsql =creatsql+ QString(" varchar(20));");
        }
    }
    qDebug() << creatsql;
    QSqlQuery creatquery(dbMYSQL);
    if(creatquery.exec(creatsql))
        qDebug() << "成功创建表格："<<work_sheet_name;
    else
        qDebug() << "创建表格"<<work_sheet_name<< "失败";
}


//在表格中插入数据
void MainWindow::InsertData(QAxObject *work_sheet)
{
    QString work_sheet_name = work_sheet->property("Name").toString();  //获取工作表名称
    QAxObject *used_range = work_sheet->querySubObject("UsedRange");//选取当前页面所有已使用单元格
    QAxObject *rows = used_range->querySubObject("Rows");
    QAxObject *columns = used_range->querySubObject("Columns");
    int row_start = used_range->property("Row").toInt();  //获取起始行
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int row_count = rows->property("Count").toInt();  //获取行数
    int column_count = columns->property("Count").toInt();  //获取列数
    qDebug() << "column_count:"<<column_count;

    QSqlQuery insertquery(dbMYSQL);
    for(int i = row_start; i < row_count+row_start;i++)//从行开始
    {
        QString strSql = QString("insert into %1 values(").arg(work_sheet_name);
        for(int j = column_start; j < column_count+column_start;j++)
        {
            QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", i+1, j);//去除第一行表头
            QString Value = cell->dynamicCall("Value2()").toString();
            strSql = strSql + QString("'%1'").arg(Value);
            if(j < column_count )
            {
                strSql = strSql + QString(",");
            }
            else
            {
                strSql = strSql + QString(")");
            }
        }
        qDebug()<<strSql;
        //调用写数据函数,将一行数据插入数据库
        insertquery.exec(strSql);
    }

    //验证是否缺少行,不缺少则数据导入成功
    QString resuresql=QString("select count(1) from %1").arg(work_sheet_name);//获取当前行数
    insertquery.exec(resuresql);
    insertquery.next();
    if(insertquery.value(0).toInt())
        QMessageBox::warning(this,tr("提示："),tr("数据导入成功"));
    else
        QMessageBox::warning(this,tr("提示："),tr("数据缺失，请重新导入"));
}

功能2

• 将tableview中的数据取出，并保存至excel文件

void MainWindow::on_pushButton_export_clicked()
{
    QAxObject *excel = new QAxObject("Excel.Application");//创建了一个QAxObject对象，用于访问Excel应用程序的COM接口。
    if (excel->isNull())//判断是否存在excel
    {
        if (excel != NULL)//网络中很多使用 excel==NULL 判断，是错误的
        {
            excel->dynamicCall("Quit()");//关闭
            delete excel;
        }
        QMessageBox::critical(0, "错误信息", "没有找到 EXCEL 应用程序");
        return;
    }
    QAxObject *workbooks = NULL;
    QAxObject *workbook = NULL;
    QAxObject *worksheets = NULL;
    QAxObject *worksheet = NULL;
    excel->dynamicCall("SetVisible (bool)", true);//不显示窗体
    excel->setProperty("DisplayAlerts", false); //不显示任何警告信息。如果为 true 那么在关闭是会出现类似“文件已修改，是否保存” 的提示
    workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
    QString filePath = QFileDialog::getSaveFileName(this, tr("Save File"),"",tr("Excel Files (*.xlsx *.xls)"));
    if (QFile::exists(filePath))
    {
        workbook = workbooks->querySubObject("Open(const QString &)", filePath);
    }
    else
    {
        workbooks->dynamicCall("Add");//新建一个工作簿
        workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
    }
    worksheets = workbook->querySubObject("Sheets");//获取工作表集合
    worksheet = worksheets->querySubObject("Item(int)",1);//获取工作表集合的工作表 1，即sheet1
    QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该 sheet 的使用范围对象
    QAxObject * rows = usedrange->querySubObject("Rows");
    QAxObject * columns = usedrange->querySubObject("Columns");
    int intRowStart = usedrange->property("Row").toInt();
    int intColStart = usedrange->property("Column").toInt();
    int intCols = columns->property("Count").toInt();
    int intRows = rows->property("Count").toInt();
    QAxObject *cell = NULL;
    int rowCount = ui->tableView->model()->rowCount();
    int colCount = ui->tableView->model()->columnCount();
    // 清空数据
    for(int i=intRowStart;i <intRowStart + intRows;i++)
    {
        for(int j=intColStart ;j<intColStart+intCols;j++)
        {
            cell = worksheet->querySubObject("Cells(int,int)", i,j ); //获取单元格
            cell->setProperty("Value", "");
        }
    }
    // 插入数据
    for (int i = intRowStart; i < intRowStart+rowCount; i++)
    {
        for(int j = intColStart; j < intColStart+colCount; j++)
        {
            QModelIndex indextemp = ui->tableView->model()->index(i-intRowStart, j-intColStart);
            QVariant str = ui->tableView->model()->data(indextemp);
            cell = worksheet->querySubObject("Cells(int,int)", i, j);//获取单元格
            cell->setProperty("Value", str);
        }
    }
    QMessageBox::information(this,"提示","数据导出完毕");
    workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filePath));//保存至 filepath，注意一定要用 QDir::toNativeSeparators 将路径中的"/"转换为"\"，不然一定保存不了。
    qDebug() <<"保存路径为："<< QDir::toNativeSeparators(filePath);
    workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭 excel
    delete excel;
    excel = NULL;
}

功能3

• 将excel文件内容直接显示到tableview上

1、先在.h文件中创建私有变量

    QXlsx::Document* m_xlsx = nullptr;

2、 .cpp文件

//导入数据,显示全部数据
void MainWindow::on_pushButton_import_clicked()
{
    QString filePath = QFileDialog::getOpenFileName(this, tr("Open Excel file"), "", tr("Excel Files (*.xlsx *.xls)"));
    if (!filePath.isEmpty())
    {
        ui->lineEdit_dir->setText(filePath);//显示文件路径
        if(!m_xlsx)
            m_xlsx = new QXlsx::Document(filePath,this);        // 打开EXCEL_NAME文件，将所有数据读取到内存中，然后关闭excel文件
        if(m_xlsx->load())  // 判断文件是否打开成功（也可以使用isLoadPackage）,如果文件不存在则为false
            qDebug() << "excel打开并加载成功!";
        else
            qWarning() << "excel打开失败!";
      }
    if(!m_xlsx)  // 判断文件是否打开成功（也可以使用isLoadPackage）,如果文件不存在则为false
    {
        QMessageBox::information(this,"提示","请先点击'打开文件'按钮");
        return;
    }
    rowLen = m_xlsx->dimension().rowCount();           // 获取最大行数
    columnLen = m_xlsx->dimension().columnCount();     // 获取最大列数
    qDebug() << "row:"<<rowLen<<"  column:"<<columnLen;

    QStandardItemModel *model = new QStandardItemModel();//QStandardItemModel 是包含单元格的容器（在这里可以看作表）
    for(int i = 1; i <= rowLen; i++)                       // 遍历每一行
    {
        QString data = QString("第%1行：").arg(i);
        for(int j = 1; j <= columnLen; j++)                // 遍历每一个单元格(列)
        {
            QVariant value = m_xlsx->read(i, j);  // 通过单元格行号、列号读取数据
            if(i==1)   // 将复选框显示出来
                if(j>3)
                {
                    QListWidgetItem *item = new QListWidgetItem();
                    QCheckBox *checkBox= new QCheckBox (value.toString(),this);

                    ui->listWidget->addItem(item);//在ListWidget中添加一个条目
                    ui->listWidget->setItemWidget(item, checkBox);//在这个条目中放置CheckBox
                }

            if(!value.isNull())
                data.append(value.toString()).append(" ");
            else
                data.append("NULL ");
            QStandardItem *item = new QStandardItem(value.toString());//QStandardItem是存储数据的单元格，它存储的是QString
            model->setItem(i-1, j-1, item);
        }
    }
    ui->tableView->setModel(model);//表视图，用于显示QStandardItemModel
}

---

四、小结

1、由于反复对excel文档进行操作，一查后台，发现开了很多个他的进程，可能会导致电脑卡顿，在查文章时看到以下文字，可能和卡顿有关，目前还未解决。

• 需要注意的是上文代码中除了获取单元格范围 (UsedRange)操作之外，其余得到的都是该对象的引用，不会占用内存空间，不需要释放，而获取单元格适用范围 (UsedRange) 则得到一个 new出来新分配的对象，因此在读取或写入操作结束后需要手动释放该对象。

2、excel文件名中带有汉字时，会创建表格失败，可能和转码有关，加上上次的反引号也行不通，目前只能尽量避免使用汉字。

3、使用insert语句时，字段要和create语句的字段完全一致。

---

五、代码下载

链接：https://pan.quark.cn/s/8524a2134357
提取码：j9rX