QT-使用Qt5Xlsx开源库进行excel表格操作（高效稳定）

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;
}

#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;
}