Qt使用第三方库Qtxlsx将数据库的表格导出为Excel

一、运行结果

二、实现

1.pro文件

加入如下语句：

QT += sql
include(xlsx/qtxlsx.pri)

2.代码

#include "widget.h"
#include "ui_widget.h"
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
#include <QDateTime>
#include <QFileDialog>
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>
#include <QDebug>

Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);
}

Widget::~Widget()
{
    delete ui;
}
//点击导出excel按钮
void Widget::on_pushButton_clicked()
{
	//SQLite数据库连接
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("../myExcel/test.db");
    /*mysql连接*/
//    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
//    db.setHostName("127.0.0.1");
//    db.setUserName("root");
//    db.setPassword("123456");
//    db.setDatabaseName("Graduation");
    //打开数据库
    if(!db.open())
    {
        QMessageBox::warning(this, "error", db.lastError().text());
        qDebug()<<QString("fail!!!!!!!!!");
        //return;
    }
    else
    {
        QMessageBox::information(this, "数据库", "数据库连接成功！");
        qDebug()<<QString("success!!!!!!!!!");
    }

    QXlsx::Document xlsx;
    QXlsx::Format title_format; /*设置标题的样式*/
    QXlsx::Format format2;/*小标题样式*/
    QXlsx::Format format3;/*数据内容样式*/
    title_format.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    format2.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    format3.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    xlsx.setRowHeight(1,1,25);/*设置标题行高*/
    xlsx.setColumnWidth(1,5,20);/*设置列宽，一共5列参数*/

    title_format.setFontSize(11);
    title_format.setFontColor(QColor(Qt::red));
    title_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    title_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    xlsx.mergeCells("A1:E1",title_format);//合并1~5列写入标题
    xlsx.write("A1","Student Information");

    format2.setFontColor(QColor(Qt::blue));
    format2.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.write("A2", "name", format2);/*写入文字，应该刚才设置的样式*/
    xlsx.write("B2", "age", format2);
    xlsx.write("C2", "ID", format2);
    xlsx.write("D2", "birthday", format2);
    xlsx.write("E2", "hobby", format2);

    format3.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    QSqlQuery query;
    QString selectSql="select * from StuInfo;";
    query.exec(selectSql);
    int i=3;
    while(query.next())//一行一行遍历
    {
        xlsx.write(i,1,query.value(0).toString(),format3);
        qDebug()<<query.value(0).toString();
        xlsx.write(i,2,query.value(1).toInt(),format3);
        xlsx.write(i,3,query.value(2).toInt(),format3);
        xlsx.write(i,4,query.value(3).toString(),format3);
        xlsx.write(i,5,query.value(4).toString(),format3);
        i++;
    }
    //设置excel表格的默认文件名为"Student Information-当前时间"
    QString current_date =QDateTime::currentDateTime().toString(Qt::ISODate);
    QString fileName=tr("Student Information-")+current_date;
    QString dir=QString("../%1").arg(fileName);
    QString dir1=dir.replace(QRegExp(":"),"-");
    /*??QFSFileEngine::open: No file name specified*/
    QString path = QFileDialog::getSaveFileName(this, tr("save"), dir1, "XLSX(*.xlsx)");
    xlsx.saveAs(path);/*保存*/
	db.close();
}