多线程Excel操作实战-优快云博客

本文链接：https://blog.youkuaiyun.com/jjjstephen/article/details/108537925

为了不影响界面显示，通常应该把读写等操作放到子线程中实现，这样即保证了界面可以同时实现其他功能又能实现读写的功能，大大提升了工作的效率。
本次实验在界面中添加了一个定时显示递增数字，以此来判断读写影响不影响界面的其他功能。

首先

为了能够操作Excel，需要在。pro(工程文件)中添加如下代码

QT += axcontainer

其他操作源码如下

tool.h

#ifndef TOOL_H
#define TOOL_H
#include<QVariant>
//写入数据的结构
struct WriteData{

    int bookID;
    int ExcelRow;
    int ExcelCol;
    QVariant ExcelValue;
};

#endif // TOOL_H

generatereportthread.h

#ifndef GENERATEREPORTTHREAD_H
#define GENERATEREPORTTHREAD_H

#include <QThread>
#include<QAxObject>
#include<QTime>
#include<QDebug>
#include"tool.h"
#include <QThread>
#include <QAxObject>
#include "qt_windows.h"

class GenerateReportThread : public QThread
{
    Q_OBJECT
public:

    GenerateReportThread(){}
    ~GenerateReportThread();

signals:
    //信号：保存数据
    void saveData(QString str);
    //信号：保存完成
    void finish();

public slots:

    //槽：开始保存
    void startSave();
    //槽：获取数据
    void getData(const WriteData & _writedata);

private:
    //写数据
    void writeExcel();
private:
   WriteData writedata;

   //Excel服务
   QAxObject *pApplication = nullptr;
   //工作簿列表
   QAxObject *pWorkbooks = nullptr;
   //当前工作簿
   QAxObject *pWorkbook = nullptr;
   //工作表列表
   QAxObject *pWorkSheets = nullptr;
   //需要操作的工作表
   //工作表编号2
   QAxObject *pWorkSheet2 = nullptr;
   //工作表编号3
   QAxObject *pWorkSheet3 = nullptr;
};

#endif // GENERATEREPORTTHREAD_H

generatereportthread.cpp

#include "generatereportthread.h"

void GenerateReportThread::startSave(){

    //验证是否为多线程操作
    qDebug() << "子线程" << QThread::currentThreadId();
    //获取数据

    //写入Excel
    writeExcel();
}

void GenerateReportThread::getData(const WriteData & _writedata){

    writedata = _writedata;
}

void GenerateReportThread::writeExcel(){

    //多线程必须初始化
    CoInitializeEx(nullptr, COINIT_MULTITHREADED);
    //启动Excel的COM服务
    pApplication = new QAxObject();
    pApplication->setControl("Excel.Application");
    //设置窗口为可见
    //pApplication->setProperty("Visible",true);
    //不显示所有警告
    pApplication->setProperty("DisplayAlerts",false);
    //查询工作簿列表子对象
    pWorkbooks = pApplication->querySubObject("WorkBooks");

    pWorkbooks->dynamicCall("Open(QString)","F:\\01work\\02DustProof\\Excel\\1.xlsx",QString(),false);
    pWorkbook = pApplication->querySubObject("ActiveWorkBook");

    pWorkSheets = pWorkbook->querySubObject("Worksheets");

    pWorkSheet2 = pWorkSheets->querySubObject("Item(int)",2);
    pWorkSheet3 = pWorkSheets->querySubObject("Item(int)",3);
    //写入
    if(writedata.bookID == 2)
    {
        if(pWorkSheet2)
        {
            QAxObject *pCell = pWorkSheet2->querySubObject("Cells(int,int)",writedata.ExcelRow,writedata.ExcelCol);
            if (pCell)
                pCell->setProperty("Value",writedata.ExcelValue);
            delete pWorkSheet2;
        }
    }
    else if(writedata.bookID == 3)
    {
        if(pWorkSheet3)
        {
            QAxObject *pCell = pWorkSheet3->querySubObject("Cells(int,int)",writedata.ExcelRow,writedata.ExcelCol);
            if (pCell)
                pCell->setProperty("Value",writedata.ExcelValue);
            delete pWorkSheet3;
        }
    }
    delete pWorkSheets;

    //获取当前系统时间
    QDateTime timeCurrent = QDateTime::currentDateTime();
    QString time = timeCurrent.toString("yyyyMMddhhmmss");
    QString path = "C:\\Users\\Administrator\\Desktop\\"+time+".xlsx";
    //另存为
    if(pWorkbook)
        pWorkbook->dynamicCall("SaveAs(QString)",path);
    delete pWorkbook;
    pWorkbooks->dynamicCall("Close()",false);
}

GenerateReportThread::~GenerateReportThread(){

    if(pApplication)
    {
        pApplication->dynamicCall("Quit()");
        delete pApplication;
    }
    if(pWorkbooks)
        delete pWorkbooks;
    if(pWorkbook)
        delete pWorkbook;
    if(pWorkSheets)
        delete pWorkSheets;
    if(pWorkSheet2)
        delete pWorkSheet2;
    if(pWorkSheet3)
        delete pWorkSheet3;

}

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include<QTimer>
#include"generatereportthread.h"

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_btn_generate_clicked();

private:
    Ui::MainWindow *ui;
    //计时器
    QTimer * timer = nullptr;
    int i = 0;
    //生成报告线程
    GenerateReportThread * generateReportThread = nullptr;
    //写数据结构
    WriteData mainWritedata;
};

#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#pragma execution_character_set("utf-8")

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    //验证是否为多线程操作
    qDebug() << "主线程" << QThread::currentThreadId();
    timer = new QTimer();
    connect(timer,&QTimer::timeout,[=](){
        ++i;
        ui->plainTextEdit->appendPlainText(QString::number(i));
    });
    timer->setInterval(500);
    timer->start();


}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_btn_generate_clicked()
{
    //generateReportThread->writeData(2,8,5,"J9");
    mainWritedata.bookID = 2;
    mainWritedata.ExcelRow = 8;
    mainWritedata.ExcelCol = 5;
    mainWritedata.ExcelValue = "stephen";
    //初始化生成报告线程
    generateReportThread = new GenerateReportThread();
    generateReportThread->getData(mainWritedata);
    QThread *thread = new QThread;
    generateReportThread->moveToThread(thread);
    // 处理数据
    connect(thread, SIGNAL(started()), generateReportThread, SLOT(startSave()), Qt::QueuedConnection);

    thread->start();
}

main.cpp

#include "mainwindow.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec();
}