Qt5下Qxlsx模块安装及使用
- 一、Qt5下Qxlsx模块安装及使用
- 1. 未安装Qxlsx的程序效果
- 2. 安装Perl(编译Qxlsx源码用)
- 2.1 下载 ActivePerl 5.28
- 2.2 安装 ActivePerl 5.28
- 3. 下载并编译Qxlsx源码
- 3.1 下载Qxlsx源码
- 3.2 编译Qxlsx源码
- 4. 将编译好的文件复制到Qt路径下
- 4.1 bin 路径文件复制
- 4.2 include 路径文件复制
- 4.3 lib 路径文件复制
- 4.4 mkspecs 路径文件复制
- 4.5 大功告成!!
- 5. 使用Qxlsx模块
- 5.1 安装 xlsx 模块后的效果演示
- 5.2 使用xlsx加载模板并生成Excel文件
- 5.3 Qt xlsx官方教程
- 6. 教程相关文件下载:
- 二、QT-使用QtXlsx开源库进行excel表格操作(高效稳定)
- 1、效果预览查看
- 2、创建线程,执行写
- 3、 将数据写入EXCEL文件
- 4、 程序关键代码源文件
- 三、Qt Xlsx使用教程、Qt操作Excel、Qt生成Excel图表、跨平台不依赖Office(推荐用法:直接使用源代码)
- 3.1 新建工程(推荐用法:直接使用源代码)
- 3.2 工程目录下添加源代码(推荐用法:直接使用源代码)
- 3.3 在工程中引入xlsx源代码(推荐用法:直接使用源代码)
- 3.4 修改main.cpp内容,测试(推荐用法:直接使用源代码)
- 3.5 查看效果(推荐用法:直接使用源代码)
- 4. 官方examples例子
- 4.1 calendar Qt操作Excel生成日历
- 4.2 chart Qt操作Excel生成图表
原文链接: https://blog.csdn.net/qq_34578785/article/details/106916808?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522168248777816800188589212%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=168248777816800188589212&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduend~default-2-106916808-null-null.142v86insert_down28,239v2insert_chatgpt&utm_term=qxlsx&spm=1018.2226.3001.4187
本篇文章讲述了如何在windows环境下为Qt5安装Qxlsx模块,以及Qxlsx模块的简单使用。
Perl+QtXlsx下载:https://download.csdn.net/download/u014779536/12543960
示例工程下载:https://download.csdn.net/download/u014779536/12543987
一、Qt5下Qxlsx模块安装及使用
1. 未安装Qxlsx的程序效果
新建一个程序,在pro文件中加入:
QT += xlsx
在界面中添加一个按钮,设置以下槽函数:
/*
* @brief 点击自动生成按钮
*/
void Widget::on_Btn_clicked()
{
//![0]
QXlsx::Document xlsx("EXCEL模板.xlsx");
//![0]
在未安装Qxlsx模块的情况下程序会报错:
现在我们开始安装Qxlsx模块!
2. 安装Perl(编译Qxlsx源码用)
编译Qxlsx源码需要用到Perl5,所以先安装perl5.
2.1 下载 ActivePerl 5.28
perl5地址:https://www.perl.org/get.html
打开上面的网址,选择 ActiveState Prel
选择 5.28版本(点击后需要登录github,自行登录)
选择 Download Builds下载 exe 格式安装文件
下载完成:
2.2 安装 ActivePerl 5.28
双击安装包:
接受条款:
选择典型安装:
勾选以下选项:
开始安装:
等待安装完成:
安装完成,重启,重启,马上重启电脑:
3. 下载并编译Qxlsx源码
3.1 下载Qxlsx源码
下载地址:https://github.com/dbzhang800/QtXlsxWriter
选择下载zip压缩包到本地:
下载完成,解压:
3.2 编译Qxlsx源码
使用QT5打开 qtxlsx.pro
选择编译器
选择编译方式:
点击“ctrl+B”构建项目,注意是 构建!构建!构建! 不要“ctrl+R”运行
构建完成,有报错,但是不用管:
文件夹预览:
4. 将编译好的文件复制到Qt路径下
找到Qt安装路径:
4.1 bin 路径文件复制
4.2 include 路径文件复制
4.3 lib 路径文件复制
4.4 mkspecs 路径文件复制
复制构建目录下的 mkspecs\modules\qt_lib_xlsx.pri 到 msvc2017_64\mkspecs\modules 下:
4.5 大功告成!!
开心一下啊~可以使用了
5. 使用Qxlsx模块
5.1 安装 xlsx 模块后的效果演示
我们再来看一下效果,安装xlsx模块前:
安装xlsx模块后:
5.2 使用xlsx加载模板并生成Excel文件
模板文件:
程序界面:
关键代码:
/*
* @brief 点击自动生成按钮
*/
void Widget::on_Btn_StartGenerate_clicked()
{
//![0]
QXlsx::Document xlsx("EXCEL模板.xlsx");
//![0]
成功生成:
预览:
5.3 Qt xlsx官方教程
教程地址:http://qtxlsx.debao.me/
6. 教程相关文件下载:
Perl+QtXlsx下载:https://download.csdn.net/download/u014779536/12543960
示例工程下载:https://download.csdn.net/download/u014779536/12543987
二、QT-使用QtXlsx开源库进行excel表格操作(高效稳定)
原文链接:https://blog.csdn.net/u013083044/article/details/113407235?spm=1001.2101.3001.6650.9&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-9-113407235-blog-106916808.235%5Ev32%5Epc_relevant_default_base3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-9-113407235-blog-106916808.235%5Ev32%5Epc_relevant_default_base3&utm_relevant_index=13
1、效果预览查看
2、创建线程,执行写
创建线程的目的就是为了让excel表写的动作放到后台去执行,这样的好处就是主线程只是专门负责数据的追加,不会造成主线程添加excel表数据写入的时候主线程的卡顿。
// 后台线程执行写入
void cReportThread::run()
{
while (true)
{
QThread::msleep(100);
if (m_ptr->bAppExit)
return;
QVariant varDev;
if (operatorDevReport(2, varDev))
cExcelWriter::getInstance().writeDevRunState(varDev); // 后台执行数据写入动作
}
}
// 将对象添加到链表,然后后台线程执行对象写到文档
void cReportThread::addDevReport(QVariant &var)
{
if (var.isNull() || !var.isValid())
return;
operatorDevReport(1, var);
}
// 操作方法
bool cReportThread::operatorDevReport(int nType, QVariant &var)
{
bool bRet = true;
static QList<QVariant> varList;
static QMutex mutex;
mutex.lock();
switch (nType)
{
case 1: // 添加到数据链表
{
if (var.isNull() || !var.isValid())
bRet = false;
else
varList.push_back(var);
}break;
case 2: // 从数据链表获取元素,并剔除元素
{
if (0 == varList.size())
bRet = false;
else
var = varList.takeFirst();
}break;
default:
break;
}
mutex.unlock();
return bRet;
}
3、 将数据写入EXCEL文件
定义sDevReport对象,通过该对象结构进行测试数据赋值,通过获取到的数据来执行excel数据表的写入。
struct sDevReport
{
QString strDevState = "";
QString strBeginWork = "";
QString strEndWork = "";
QString strRemarks = "";
};
Q_DECLARE_METATYPE(sDevReport)
添加测试数据,写入excel表里面。
// test data
cWorkReport ExcelReport;
for (int i = 0; i < 20; i++)
{
sDevReport dev;
dev.strBeginWork = QString("begin-%1").arg(i);
dev.strDevState = QString("state-%1").arg(i);
dev.strEndWork = QString("end-%1").arg(i);
dev.strRemarks = QString("remaks-%1").arg(i);
ExcelReport.addDevReport(QVariant::fromValue(dev));
}
这里是对sDevReport数据对象写入excel表的具体实现方式。
int cExcelWriter::writeDevRunState(QVariant var)
{
sDevReport report = var.value<sDevReport>();
QString strReportFile = QApplication::applicationDirPath() + DIR_WORK_REPORT + DIR_DEV_REPORT + "/Dev-" + m_strToday + ".xlsx";
int nRowHeight = 15;
int nColWidth = 25;
if (!QFile::exists(strReportFile))
{
// 写入excel表头
QStringList strTableNameList;
strTableNameList << QString::fromLocal8Bit("状态(State)")
<< QString::fromLocal8Bit("开始时间(Begin Work)")
<< QString::fromLocal8Bit("结束时间(End Work)")
<< QString::fromLocal8Bit("耗时/秒(Use Time)")
<< QString::fromLocal8Bit("备注(Remarks)");
QXlsx::Document xlsx(strReportFile);
QXlsx::Format format;
format.setFontBold(true);
format.setPatternBackgroundColor(QColor(RGB_TABLE)); // 设置列背景颜色
format.setFontSize(10);
format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
for (int i = 0; i < strTableNameList.size(); i++)
{
xlsx.setColumnWidth(i + 1, nColWidth);
xlsx.setRowHeight(1, nRowHeight);
xlsx.write(1, i + 1, strTableNameList.at(i), format);
}
xlsx.saveAs(strReportFile);
}
QXlsx::Document xlsx(strReportFile);
int nRows = xlsx.dimension().rowCount() + 1;
QXlsx::Format format;
format.setHorizontalAlignment(QXlsx::Format::AlignLeft);
// 设置行高
xlsx.setRowHeight(nRows, nRowHeight);
xlsx.write(nRows, 1, report.strDevState, format);
xlsx.write(nRows, 2, report.strBeginWork, format);
xlsx.write(nRows, 3, report.strEndWork, format);
xlsx.write(nRows, 4, "", format);
xlsx.write(nRows, 5, report.strRemarks, format);
xlsx.saveAs(strReportFile);
return 0;
}
4、 程序关键代码源文件
全部代码下载链接: https://download.csdn.net/download/u013083044/14993348
#include "ReportThread.h"
#include "QMutex"
#include <QVariant>
#include "ExcelWriter.h"
#include "ExcelStruct.h"
struct sReportThread
{
bool bAppExit = false;
};
cReportThread::cReportThread(QObject *parent)
: QThread(parent)
{
m_ptr = new sReportThread;
this->start();
}
cReportThread::~cReportThread()
{
if (m_ptr != nullptr)
{
m_ptr->bAppExit = true;
this->wait(1000);
delete m_ptr;
}
}
cReportThread& cReportThread::getInstance()
{
static cReportThread instance;
return instance;
}
// 后台线程执行写入
void cReportThread::run()
{
while (true)
{
QThread::msleep(100);
if (m_ptr->bAppExit)
return;
QVariant varDev;
if (operatorDevReport(2, varDev))
cExcelWriter::getInstance().writeDevRunState(varDev);
}
}
// 将对象添加到链表,然后后台线程执行对象写到文档
void cReportThread::addDevReport(QVariant &var)
{
if (var.isNull() || !var.isValid())
return;
operatorDevReport(1, var);
}
// 操作方法
bool cReportThread::operatorDevReport(int nType, QVariant &var)
{
bool bRet = true;
static QList<QVariant> varList;
static QMutex mutex;
mutex.lock();
switch (nType)
{
case 1:
{
if (var.isNull() || !var.isValid())
bRet = false;
else
varList.push_back(var);
}break;
case 2:
{
if (0 == varList.size())
bRet = false;
else
var = varList.takeFirst();
}break;
default:
break;
}
mutex.unlock();
return bRet;
}
三、Qt Xlsx使用教程、Qt操作Excel、Qt生成Excel图表、跨平台不依赖Office(推荐用法:直接使用源代码)
原文链接:https://blog.csdn.net/u014779536/article/details/111769792?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522168249367516800225585426%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=168249367516800225585426&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2alltop_positive~default-1-111769792-null-null.142v86insert_down1,239v2insert_chatgpt&utm_term=qtxlsx&spm=1018.2226.3001.4187
用法②:直接使用源代码
该包包含一个 qtxlsx.pri文件,允许您将组件集成到使用 qmake 进行生成步骤的应用程序中。
3.1 新建工程(推荐用法:直接使用源代码)
3.2 工程目录下添加源代码(推荐用法:直接使用源代码)
来到工程目录下:
打开网上下载的源码路径:
3.3 在工程中引入xlsx源代码(推荐用法:直接使用源代码)
# 使用qtxlsx源代码
include(qtxlsx/src/xlsx/qtxlsx.pri)
3.4 修改main.cpp内容,测试(推荐用法:直接使用源代码)
#include <QtCore>
#include "xlsxdocument.h"
int main()
{
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
xlsx.write("A1", "Hello Qt!");
xlsx.write("A2", 12345);
xlsx.write("A3", "=44+33");
xlsx.write("A4", true);
xlsx.write("A5", "http://qt-project.org");
xlsx.write("A6", QDate(2013, 12, 27));
xlsx.write("A7", QTime(6, 30));
//![1]
//![2]
xlsx.save();
//![2]
return 0;
}
3.5 查看效果(推荐用法:直接使用源代码)
内容一致:
4. 官方examples例子
4.1 calendar Qt操作Excel生成日历
#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxcellrange.h"
#include "xlsxworksheet.h"
QTXLSX_USE_NAMESPACE
int main(int argc, char **argv)
{
QCoreApplication app(argc, argv);
// Select a proper locale
// QLocale::setDefault(QLocale(QLocale::English));
Document xlsx;
QDate today(QDate::currentDate());
for (int month = 1; month <= 12; ++month) {
xlsx.addSheet(QLocale().monthName(month));
xlsx.currentWorksheet()->setGridLinesVisible(false);
// the header row
Format headerStyle;
headerStyle.setFontSize(48);
headerStyle.setFontColor(Qt::darkBlue);
headerStyle.setHorizontalAlignment(Format::AlignHCenter);
headerStyle.setVerticalAlignment(Format::AlignVCenter);
xlsx.setRowHeight(1, 80);
xlsx.write("A1", QString("%1 %2").arg(QLocale().monthName(month)).arg(today.year()));
xlsx.mergeCells("A1:N1", headerStyle);
// header with month titles
for (int day = 1; day <= 7; ++day) {
Format monthStyle;
monthStyle.setFontSize(12);
monthStyle.setFontColor(Qt::white);
monthStyle.setFontBold(true);
monthStyle.setHorizontalAlignment(Format::AlignHCenter);
monthStyle.setVerticalAlignment(Format::AlignVCenter);
monthStyle.setFillPattern(Format::PatternSolid);
monthStyle.setPatternBackgroundColor(Qt::darkBlue);
xlsx.setColumnWidth(day * 2 - 1, day * 2 - 1, 5);
xlsx.setColumnWidth(day * 2, day * 2, 13);
xlsx.write(2, day * 2 - 1, QLocale().dayName(day));
xlsx.mergeCells(CellRange(2, day * 2 - 1, 2, day * 2), monthStyle);
}
QColor borderColor = QColor(Qt::gray);
Format weekendLeftStyle;
weekendLeftStyle.setFontSize(14);
weekendLeftStyle.setFontBold(true);
weekendLeftStyle.setHorizontalAlignment(Format::AlignLeft);
weekendLeftStyle.setVerticalAlignment(Format::AlignTop);
weekendLeftStyle.setPatternBackgroundColor(QColor("#93CCEA"));
weekendLeftStyle.setLeftBorderStyle(Format::BorderThin);
weekendLeftStyle.setLeftBorderColor(borderColor);
weekendLeftStyle.setBottomBorderStyle(Format::BorderThin);
weekendLeftStyle.setBottomBorderColor(borderColor);
Format weekendRightStyle;
weekendRightStyle.setHorizontalAlignment(Format::AlignHCenter);
weekendRightStyle.setVerticalAlignment(Format::AlignTop);
weekendRightStyle.setPatternBackgroundColor(QColor("#93CCEA"));
weekendRightStyle.setRightBorderStyle(Format::BorderThin);
weekendRightStyle.setRightBorderColor(borderColor);
weekendRightStyle.setBottomBorderStyle(Format::BorderThin);
weekendRightStyle.setBottomBorderColor(borderColor);
Format workdayLeftStyle;
workdayLeftStyle.setHorizontalAlignment(Format::AlignLeft);
workdayLeftStyle.setVerticalAlignment(Format::AlignTop);
workdayLeftStyle.setPatternBackgroundColor(Qt::white);
workdayLeftStyle.setLeftBorderStyle(Format::BorderThin);
workdayLeftStyle.setLeftBorderColor(borderColor);
workdayLeftStyle.setBottomBorderStyle(Format::BorderThin);
workdayLeftStyle.setBottomBorderColor(borderColor);
Format workdayRightStyle;
workdayRightStyle.setHorizontalAlignment(Format::AlignHCenter);
workdayRightStyle.setVerticalAlignment(Format::AlignTop);
workdayRightStyle.setPatternBackgroundColor(Qt::white);
workdayRightStyle.setRightBorderStyle(Format::BorderThin);
workdayRightStyle.setRightBorderColor(borderColor);
workdayRightStyle.setBottomBorderStyle(Format::BorderThin);
workdayRightStyle.setBottomBorderColor(borderColor);
Format greyLeftStyle;
greyLeftStyle.setPatternBackgroundColor(Qt::lightGray);
greyLeftStyle.setLeftBorderStyle(Format::BorderThin);
greyLeftStyle.setLeftBorderColor(borderColor);
greyLeftStyle.setBottomBorderStyle(Format::BorderThin);
greyLeftStyle.setBottomBorderColor(borderColor);
Format greyRightStyle;
greyRightStyle.setPatternBackgroundColor(Qt::lightGray);
greyRightStyle.setRightBorderStyle(Format::BorderThin);
greyRightStyle.setRightBorderColor(borderColor);
greyRightStyle.setBottomBorderStyle(Format::BorderThin);
greyRightStyle.setBottomBorderColor(borderColor);
int rownum = 3;
for (int day = 1; day <= 31; ++day) {
QDate date(today.year(), month, day);
if (!date.isValid())
break;
xlsx.setRowHeight(rownum, 100);
int dow = date.dayOfWeek();
int colnum = dow * 2 - 1;
if (dow <= 5) {
xlsx.write(rownum, colnum, day, workdayLeftStyle);
xlsx.write(rownum, colnum + 1, QVariant(), workdayRightStyle);
} else {
xlsx.write(rownum, colnum, day, weekendLeftStyle);
xlsx.write(rownum, colnum + 1, QVariant(), weekendRightStyle);
}
if (day == 1 && dow != 1) { // First day
for (int i = 1; i < dow; ++i) {
xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
}
} else if (day == date.daysInMonth() && dow != 7) { // Last day
for (int i = dow + 1; i <= 7; ++i) {
xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
}
}
if (dow == 7)
rownum++;
}
}
xlsx.saveAs("Book1.xlsx");
// Make sure that read/write works well.
Document xlsx2("Book1.xlsx");
xlsx2.saveAs("Book2.xlsx");
return 0;
}
4.2 chart Qt操作Excel生成图表
#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
using namespace QXlsx;
int main()
{
//![0]
Document xlsx;
for (int i = 1; i < 10; ++i) {
xlsx.write(i, 1, i * i * i); // A1:A9
xlsx.write(i, 2, i * i); // B1:B9
xlsx.write(i, 3, i * i - 1); // C1:C9
}
//![0]
//![1]
Chart *pieChart = xlsx.insertChart(3, 3, QSize(300, 300));
pieChart->setChartType(Chart::CT_Pie);
pieChart->addSeries(CellRange("A1:A9"));
pieChart->addSeries(CellRange("B1:B9"));
pieChart->addSeries(CellRange("C1:C9"));
Chart *pie3DChart = xlsx.insertChart(3, 9, QSize(300, 300));
pie3DChart->setChartType(Chart::CT_Pie3D);
pie3DChart->addSeries(CellRange("A1:C9"));
Chart *barChart = xlsx.insertChart(23, 3, QSize(300, 300));
barChart->setChartType(Chart::CT_Bar);
barChart->addSeries(CellRange("A1:C9"));
Chart *bar3DChart = xlsx.insertChart(23, 9, QSize(300, 300));
bar3DChart->setChartType(Chart::CT_Bar3D);
bar3DChart->addSeries(CellRange("A1:C9"));
Chart *lineChart = xlsx.insertChart(43, 3, QSize(300, 300));
lineChart->setChartType(Chart::CT_Line);
lineChart->addSeries(CellRange("A1:C9"));
Chart *line3DChart = xlsx.insertChart(43, 9, QSize(300, 300));
line3DChart->setChartType(Chart::CT_Line3D);
line3DChart->addSeries(CellRange("A1:C9"));
Chart *areaChart = xlsx.insertChart(63, 3, QSize(300, 300));
areaChart->setChartType(Chart::CT_Area);
areaChart->addSeries(CellRange("A1:C9"));
Chart *area3DChart = xlsx.insertChart(63, 9, QSize(300, 300));
area3DChart->setChartType(Chart::CT_Area3D);
area3DChart->addSeries(CellRange("A1:C9"));
Chart *scatterChart = xlsx.insertChart(83, 3, QSize(300, 300));
scatterChart->setChartType(Chart::CT_Scatter);
// Will generate three lines.
scatterChart->addSeries(CellRange("A1:A9"));
scatterChart->addSeries(CellRange("B1:B9"));
scatterChart->addSeries(CellRange("C1:C9"));
Chart *scatterChart_2 = xlsx.insertChart(83, 9, QSize(300, 300));
scatterChart_2->setChartType(Chart::CT_Scatter);
// Will generate two lines.
scatterChart_2->addSeries(CellRange("A1:C9"));
Chart *doughnutChart = xlsx.insertChart(103, 3, QSize(300, 300));
doughnutChart->setChartType(Chart::CT_Doughnut);
doughnutChart->addSeries(CellRange("A1:C9"));
//![1]
//![2]
xlsx.saveAs("Book1.xlsx");
//![2]
Document xlsx2("Book1.xlsx");
xlsx2.saveAs("Book2.xlsx");
return 0;
}