Qt实现将qsqlite数据库中的数据导出为Excel表格

qt       += core gui sql axcontainer

#ifndef reportexportthread_h
#define reportexportthread_h

#include <qobject>
#include <qdebug>
#include <qthread>
#include <qfile>
#include <qfileinfo>
#include <qaxobject>
#include <qstandardpaths>
#include <qdir>
#include <qsqldatabase>
#include <qsqlerror>
#include <qsqlquery>
#include <qsqlrecord>
#include <qthread>
#include <qfiledialog>
class reportexportthread : public qobject
{
    q_object
public:
    typedef enum
    {
        exporting,
        failed,
        finish,
    }exportstate;
signals:
    void export_res_signal(exportstate state,qstring info,quint32 totalnum,quint32 exportnum);
public:
    explicit reportexportthread(qobject *parent = nullptr);

    void export_thread_run(qstring dbfile,qstring dbtablename,qstring xlsxfile);

private:
    void castlistlistvariant2variant(const qlist<qlist<qvariant> > &cells, qvariant &res);
private:
    qaxobject *excel = nullptr;//excel操作对象
    qaxobject *workbooks = nullptr;
    qaxobject* workbook = nullptr; //打开
    qaxobject *activeworkbook = nullptr;
    qaxobject *worksheet = nullptr; //表单

};

#endif // reportexportthread_h

#include "reportexportthread.h"
#include "windows.h"
reportexportthread::reportexportthread(qobject *parent)
    : qobject{parent}
{

}

void reportexportthread::export_thread_run(qstring dbfile, qstring dbtablename, qstring xlsxfile)
{
    #define single_opt_row 1000
    qvariant var;
    qvariantlist varlist;
    qlist<qvariant> listvar;
    qlist<qlist<qvariant> > listlistvar;
    quint32 writerowcnt = 1;//写入起始行计数

    qstring range;
    qstring cmd;
    qaxobject *writerange = nullptr;
    quint32 row,col;
    //数据库操作数据
    qsqldatabase *db = nullptr;
    qsqldatabase dba;
    qsqlquery *query = nullptr;
    qstring dbconnectname = "export";

    qaxobject* cellinterior = nullptr;
    qaxobject *font = nullptr;

    qcolor bkcolor(255, 255, 0 ,50);

    quint32 i,j;

    #define asset_null(a,info)\
    {\
        if(a == nullptr)\
        {\
            emit export_res_signal(failed,info,0,0);\
            goto exit;\
        }\
    }\

    qdebug()<<"export_thread_run   "<<qthread::currentthreadid();

    //判断数据库文件是否存在
    qfile dbfiles(dbfile);
    if(!dbfiles.exists())
    {
        emit export_res_signal(failed,tr("数据库文件不存在！"),0,0);
        return;
    }
    dbfiles.close();
    //判断表是否存在
    if(dbtablename.isempty())
    {
        emit export_res_signal(failed,tr("数据库表不存在！"),0,0);
        return;
    }
    //判断xlsx文件是否存在
    qfile xlsxfiles(xlsxfile);
    if(!xlsxfiles.exists())
    {
        if(!xlsxfiles.open(qiodevice::writeonly))
        {
            emit export_res_signal(failed,tr("xlsx文件创建失败！"),0,0);
            return;
        }
        xlsxfiles.close();
    }
    xlsxfiles.close();
    //判断文件是否为只读
    qfileinfo fileinfo(xlsxfile);
    fileinfo.setfile(fileinfo.filepath()+"/~$"+fileinfo.filename());
    if(fileinfo.exists())          //判断一下,有没有"~$xxx.xlsx"文件存在,是不是为只读
    {
        emit export_res_signal(failed,tr("xlsx文件为只读文件！"),0,0);
        return;
    }
    fileinfo.setfile(xlsxfile);
    //创建qaxobject对象
    coinitializeex(null, coinit_multithreaded);
    this->excel = new qaxobject();//建立excel操作对象

    connect(excel,&qaxobject::exception,this,
            [](int code, const qstring &source, const qstring &desc, const qstring &help)
    {
        qdebug()<<code;
        qdebug()<<source;
        qdebug()<<desc;
        qdebug()<<help;

    });
    this->excel->setcontrol("excel.application");//连接excel控件
    this->excel->setproperty("visible", false);//显示窗体看效果
    this->excel->setproperty("displayalerts", false);//显示警告看效果
    this->excel->setproperty("caption", "qt excel");      //标题为qt excel
    this->workbooks = excel->querysubobject("workbooks");
    this->workbook = workbooks->querysubobject("open(const qstring&)",qdir::tonativeseparators(fileinfo.absolutefilepath())); //打开
    this->activeworkbook = excel->querysubobject("activeworkbook");
    this->worksheet = activeworkbook->querysubobject("sheets(int)",1); //获取表单

    //创建qsqldatabase对象
//    dba = qsqldatabase::adddatabase("qsqlite",dbconnectname);
//    dba.setdatabasename("sqltest.db");

    db = new qsqldatabase(qsqldatabase::adddatabase("qsqlite",dbconnectname));
//    db->adddatabase("qsqlite",dbconnectname);
    db->setdatabasename(dbfile);
    //打开数据库
//    if(!dba.open())
//    {
//        emit export_res_signal(failed,tr("数据库打开失败！"),0,0);
//        goto exit;
//    }
//    db = &dba;

    if(!db->open())
    {
        emit export_res_signal(failed,tr("数据库打开失败！"),0,0);
        goto exit;
    }

    query = new qsqlquery(*db);

    //开始设置表头
    cmd =qstring("pragma table_info(%1);").arg(dbtablename);
    if(!query->exec(cmd))
    {
        emit export_res_signal(failed,tr("数据库表头操作失败！"),0,0);
        goto exit;
    }
    col = query->record().count();      //获取列数
    listvar.clear();
    listlistvar.clear();
    while(query->next())
    {
        listvar.append(qvariant(query->value(1).tostring()));
        //qdebug()<<query->value(1).tostring();
    }
    listlistvar.append(listvar);

    this->castlistlistvariant2variant(listlistvar,var);
    range = qstring("a1:%1%2").arg(qchar('a'+col-1)).arg(writerowcnt++);
    //qdebug()<<range;
    writerange = this->worksheet->querysubobject("range(const qstring&)",range);
    asset_null(writerange,tr("excel打开失败！"));
    writerange->dynamiccall("value", var);
    //开始设置表头背景颜色
    cellinterior = writerange->querysubobject("interior");
    asset_null(cellinterior,tr("excel打开失败！"));
    cellinterior->setproperty("color", bkcolor);

    //开始调整背景字体
    font = writerange->querysubobject("font");  //获取单元格字体
    asset_null(font,tr("excel打开失败！"));
    font->setproperty("bold", true);  //字体加粗
    font->setproperty("color", qcolor(255, 0, 0));  //设置单元格字体颜色（红色）
    delete writerange;

    //获取数据行数
    cmd =qstring("select count(*) from %1;").arg(dbtablename);
    if(!query->exec(cmd))
    {
        emit export_res_signal(failed,tr("数据库行数获取失败！"),0,0);
        goto exit;
    }
    query->next();
    row = query->value(0).toint();
    //开始导出数据
    for(i =0;i < row; i+=single_opt_row)
    {
        listlistvar.clear();

        query->clear();
        cmd =qstring("select * from %1 limit %2,%3").arg(dbtablename).arg(i).arg(single_opt_row);
        //qdebug()<<cmd;
        if(!query->exec(cmd))
        {
            emit export_res_signal(failed,tr("数据库数据获取失败！"),0,0);
            goto exit;
        }
        //读取数据
        int datarowcnt = 0;
        while(query->next())
        {
            listvar.clear();
            for(j=0;j<col;j++)
            {
                listvar.append(query->value(j));
            }
            listlistvar.append(listvar);
            datarowcnt++;
        }
        //写入数据
        this->castlistlistvariant2variant(listlistvar,var);
        range = qstring("a%1:%2%3").arg(writerowcnt).arg(qchar('a'+col-1)).arg(datarowcnt+writerowcnt-1);
        //qdebug()<<range;
        writerange = this->worksheet->querysubobject("range(const qstring&)",range);
        asset_null(writerange,tr("excel打开失败！"));
        writerange->dynamiccall("value", var);

        writerowcnt+=datarowcnt;

        delete writerange;
        emit export_res_signal(exporting,tr("数据正在导出！"),row,writerowcnt-2);
    }



    emit export_res_signal(finish,tr("数据导出成功！"),row,row);

    exit:

    if(this->excel != nullptr)
    {
        this->workbook->dynamiccall("save()" );
        this->workbook->dynamiccall("close()");  //关闭文件
        this->excel->dynamiccall("quit()");//关闭excel

        delete excel;
        excel = nullptr;
    }
    if(query != nullptr)
    {
        delete query;
        query = nullptr;
    }
    if(db->isopen())
    {
        db->close();
        delete db;
        qsqldatabase::removedatabase(dbconnectname);
    }
}

void reportexportthread::castlistlistvariant2variant(const qlist<qlist<qvariant> > &cells, qvariant &res)
{
    qvariantlist vars;
    const int rows = cells.size();
    for(int i=0;i<rows;++i)
    {
        vars.append(qvariant(cells[i]));
    }
    res = qvariant(vars);
}

	reportexportthread->start();
    qstring savepath = qfiledialog::getexistingdirectory(this,"请选择导入的文件夹！",
                                                         "",
                                                         qfiledialog::showdirsonly);


    emit start_export_thread("sqltest.db","producetable",savepath+"/"+qdatetime::currentdatetime().tostring("yyyy_mm_dd_hh_mm_ss")+".xlsx");