Qt：一键导出客户端（五）-- excel导出

绪论

此部分代码仅供参考，存在一些问题，本打算把49张表所有数据存储在49个sheet页里，但是由于数据量太大，速度太慢，所以放弃掉了。改用导出txt的方式。

源码

myexcel.h

#ifndef MYEXCEL_H
#define MYEXCEL_H

#include <QDate>

class MyExcel
{
public:
    MyExcel();
    // 导出excel
    void exportExcel(QString fileName, QDate start, QDate end);

};

#endif // MYEXCEL_H

myexcel.cpp

#include "myexcel.h"
#include "mydatabase.h"
#include "config.h"

#include <QAxObject>
#include <QDir>
#include <QDebug>

MyExcel::MyExcel()
{

}

void MyExcel::exportExcel(QString fileName, QDate start, QDate end)
{
    qDebug() << "开始：" + QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

    QAxObject *excel = new QAxObject("Excel.Application");
    excel->dynamicCall("SetVisible(bool)", true);
    excel->setProperty("Visible", false);
    QAxObject *workbooks = excel->querySubObject("WorkBooks");
    workbooks->dynamicCall("Add");
    QAxObject *workbook = excel->querySubObject("ActiveWorkBook");

    QAxObject *worksheets = workbook->querySubObject("Worksheets");

    /*********************************************************************************************************************************/

    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
    worksheet->setProperty("Name", Config().getTableName(1));

    QString zd_1 = Config().getSQLColumn(1);
    QStringList zdl_1 = zd_1.split(",");
    for (int var = 0; var < zdl_1.count(); var++) {
        QAxObject *range = worksheet->querySubObject("Cells(int,int)", 1, var + 1);
        // 此处写要插入的内容
        range->setProperty("Value", zdl_1.at(var));
    }

    QSqlQuery query = MyDatabase().getQuery(1, start, end);

    int columnNum = 2;
    while (query.next()) {
        for (int var = 0; var < zdl_1.count(); var++) {
            QAxObject *range = worksheet->querySubObject("Cells(int,int)", columnNum, var + 1);
            // 此处写要插入的内容
            range->setProperty("Value", query.value(var));
        }
        columnNum++;
    }
    qDebug() << "Total：" + QString("%1").arg(columnNum - 2);


    /*********************************************************************************************************************************/

    MyDatabase *myDatabase = new MyDatabase;

    for (int i = 2; i < 50; i++) {
        QAxObject *sheet = worksheets->querySubObject("Add()");
        sheet->setProperty("Name", Config().getTableName(i));

        QString zd = Config().getSQLColumn(i);
        QStringList zdl = zd.split(",");
        for (int var = 0; var < zdl.count(); var++) {
            QAxObject *range = sheet->querySubObject("Cells(int,int)", 1, var + 1);
            // 此处写要插入的内容
            range->setProperty("Value", zdl.at(var));
        }

        QSqlQuery newQuery = myDatabase->getQuery(i, start, end);

        columnNum = 2;
        while (newQuery.next()) {
            for (int var = 0; var < zdl.count(); var++) {
                QAxObject *range = worksheet->querySubObject("Cells(int,int)", columnNum, var + 1);
                // 此处写要插入的内容
                range->setProperty("Value", newQuery.value(var));
            }
            columnNum++;
        }
        qDebug() << "Total：" + QString("%1").arg(columnNum - 2);
    }

    delete myDatabase;


    /********************************************************************************************************************************/


    // 写入文件
    workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));

    // 释放
    workbook->dynamicCall("Close()");
    //释放所有工作表
    worksheet->clear();
    //释放excel
    excel->dynamicCall("Quit()");
    delete excel;

    qDebug() << "结束：" + QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

}

（若有什么错误，请留言指正，3Q）