Qt自定义插件写Excel表格

网上找到一个开源的Qt插件，下载下来，生成头文件和库文件，可以不依赖电脑是否安装Excel软件，是否有Excel驱动，可以直接输出excel文档

https://github.com/dbzhang800/QtXlsxWriter/tree/master/src/xlsx

#include "CopyAllFileThread.h"
#include<QFileInfo>
#include "LocalDb.h"
#include <QDir>
#include "xlsxabstractooxmlfile.h"
#include "xlsxabstractsheet.h"
#include "xlsxcell.h"
#include "xlsxcellformula.h"
#include "xlsxcellrange.h"
#include "xlsxcellreference.h"
#include "xlsxchart.h"
#include "xlsxchartsheet.h"
#include "xlsxconditionalformatting.h"
#include "xlsxdatavalidation.h"
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxglobal.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"
#include "xlsxworksheet.h"
#include "qtxlsxversion.h"
#include <QDateTime>
#include "ConfigInfo.h"
#pragma execution_character_set("utf-8")
CopyAllFileThread::CopyAllFileThread()
{

}

CopyAllFileThread::~CopyAllFileThread()
{

}

int CopyAllFileThread::StartCopyFile(QMap<QString, QString> selectItemFileTime, QString strTargetPath, QVariantList comparelist, QString strSql,QString table, bool checked)
{
	m_selectItemFileTime = selectItemFileTime;
	m_strTargetPath = strTargetPath;
	m_checkAll = checked;
	m_comparelist = comparelist;
	m_strSql = strSql;
	m_table = table;
	this->start();
	return 0;
}

void CopyAllFileThread::run()
{
	QString strSourcePath = "";
	QString strTargetPath = "";
	QVariantMap datainfo;
	QVariantList listSave;
	if (m_checkAll==true)//排除法
	{
		
		if (m_strSql!="")//查询sql语句获取所有值
		{
			QVariantMap replyData;
			QString strMsg="";
			if (LocalDb::instance()->ExcuateSql(m_strSql, replyData, strMsg)!=0)
			{
				LOG_ERROR("ExcuateSql to Get result failed %s", m_strSql.toStdString().c_str());
				return;
			}
			m_comparelist = replyData.value("data").toList();
		}
		for (int i = 0; i < m_comparelist.size(); i++)
		{
			datainfo = m_comparelist.at(i).toMap();
			strSourcePath = datainfo.value("targetpic").toString();
			if (strSourcePath == "")
			{
				continue;
			}
			if (m_selectItemFileTime.find(strSourcePath) != m_selectItemFileTime.end())
			{
				continue;
			}
			listSave.push_back(datainfo);
			//QFileInfo file(strSourcePath);
			//if (!file.exists())
			//{
			//	continue;
			//}
			//strTargetPath = m_strTargetPath + "/" + file.fileName();
			//QFile::copy(strSourcePath, strTargetPath);//从源路径将文件复制到目标路径
		}

	}
	else//根据查找信息
	{
		QStringList listFiles = m_selectItemFileTime.keys();
		if (listFiles.size()==0)
		{
			return;
		}
		QString strFileCondition = LocalDb::instance()->GetStringCondition(listFiles);
		QString strUrl = QString("select * from %1 where targetpic in (%2)").arg(m_table).arg(strFileCondition);
		QString strMsg = "";
		QVariantMap retValue;
		if (0 != LocalDb::instance()->ExcuateSql(strUrl, retValue, strMsg))
		{
			return;
		}
		listSave = retValue.value("data").toList();
	}
	SaveToExcelFile(listSave);
	
}

int CopyAllFileThread::SaveToExcelFile(QVariantList& lists)
{
	int iRet = -1;
	QString  strBackDir = m_strTargetPath + "/背景大图/";
	QString strTargetDir= m_strTargetPath + "/目标小图/";
	QString strFilePath = m_strTargetPath + "/" + m_table+"_"+QDateTime::currentDateTime().toString("yyyyMMddhhmmsszzz")+".xlsx";
	QDir dirBack(strBackDir);
	if (!dirBack.exists())
	{
		if (!dirBack.mkpath(strBackDir))
		{
			LOG_ERROR("make path error :%s", strBackDir.toStdString().c_str());
			return iRet;
		}
	}
	QDir dirTarget(strTargetDir);
	if (!dirTarget.exists())
	{
		if (!dirTarget.mkpath(strTargetDir))
		{
			LOG_ERROR("make path error :%s", strTargetDir.toStdString().c_str());
			return iRet;
		}
	}
	QStringList title;
	QStringList dataname;
	QList<int> columnwidth;
	if (m_table == "human")
	{
		title << "时间" << "来源视频" << "目标小图" << "背景大图" << "性别" << "年龄段" << "戴眼镜" << "戴口罩" << "戴帽子" << "上衣颜色" << "下衣颜色" << "背包" << "拎东西";
		dataname << "time" << "location" << "targetpic" << "relatepic" << "sex" << "agestate" << "glass" << "mask" << "hat" << "upclothecolor" << "downclothecolor" << "bag" << "takething";
		columnwidth << 25 <<    30    <<    30       <<    30      << 10    <<    10    <<    10    <<     10    <<    10   <<     12    <<       12    <<     10 <<     10;
	}
	else if (m_table == "vehicle")
	{
		title << "时间" << "来源视频" << "目标小图" << "背景大图" << "车牌号" << "车身颜色" << "车辆类型" <<"车辆品牌"<< "车牌颜色" <<"驾驶安全带"<< "副驾驶" << "挂件" << "遮阳板" << "危险品";
		dataname << "time" << "location" << "targetpic" << "relatepic" << "vehiclenum"<<"vehiclecolor" << "vehicletype"<<"brand" << "platecolor" << "pilotSafebelt" << "visepilot" << "hangthing" << "hidesunplate" << "danger";
		columnwidth << 25   << 30      <<      30   <<    30    <<      13   <<     12      <<     15     <<       15  <<     12   <<     13    <<     10    <<    10    << 10     <<10;
	}
	else if (m_table == "unvehicle")
	{
		title << "时间" << "来源视频" << "目标小图" << "背景大图" <<"车辆类型"<<"骑行人数"<< "性别" << "年龄段" << "戴眼镜" << "戴口罩" << "戴帽子" << "上衣颜色" << "下衣颜色" << "背包" << "拎东西";
		dataname << "time" << "location" << "targetpic" << "relatepic" << "ridetype"<<"ridenum"<<"sex" << "agestate" << "glass" << "mask" << "hat" << "upclothecolor" << "downclothecolor" << "bag" << "takething";
		columnwidth << 25   << 30      <<      30   <<    30    <<      12   <<     12      <<   10 <<    10  <<     10   <<     10    <<     10    <<    12      <<     12     <<   10      <<  10 ;
	}
	else if (m_table == "GaitAnaResult")
	{
		title << "时间" << "来源视频" << "目标小图" << "背景大图" ;
		dataname << "time" << "location" << "targetpic" << "relatepic";
		columnwidth << 25   << 30      <<      30   <<    30    ;
	}
	else
	{
		return iRet;
	}
	//填写表头
	QXlsx::Document xlsx;  //创建Excel文件
	QXlsx::Format Format1;  //设置该单元的样式
	QString QStrSheetName;  //定义表格名称字符串QStrSheetName
	int fontSize = 12;//字体大小
	Format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/
	Format1.setBorderStyle(QXlsx::Format::BorderDashDotDot);/*边框样式*/
	Format1.setVerticalAlignment(QXlsx::Format::AlignVCenter);//横向居中
															  // Format1.setPatternBackgroundColor(QColor(218,238,243));
	Format1.setFontSize(fontSize);

	QXlsx::Format headFormat;  //设置表头样式
	headFormat.setPatternBackgroundColor(QColor(17, 93, 157));      //设置背景颜色
	headFormat.setFontSize(fontSize);  //设置字体大小
	headFormat.setFontBold(true);/*设置加粗*/
	headFormat.setHorizontalAlignment(QXlsx::Format::AlignHCenter);  //横向居中
	headFormat.setVerticalAlignment(QXlsx::Format::AlignVCenter);
	headFormat.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

	QXlsx::Format Format2;  //设置内容样式
	Format2.setPatternBackgroundColor(QColor(218, 238, 243));      //设置背景颜色
	Format2.setFontSize(fontSize);  //设置字体大小
	Format2.setHorizontalAlignment(QXlsx::Format::AlignHCenter);  //横向居中
	Format2.setVerticalAlignment(QXlsx::Format::AlignVCenter);
	Format2.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/
	
													  //建立Sheet
	QStrSheetName = m_table;  //工作簿的名称
	xlsx.addSheet(QStrSheetName);  //添加该命名的工作簿
	
	//写表头
	int titleline = 1;
	for (int i = 0; i <title.size(); i++)
	{
		xlsx.write(titleline, i+1, title[i], headFormat);
		xlsx.setColumnWidth(i+1, columnwidth[i]);//设置列宽
	}
	//写了内容
	int dataline = 2;
	int extra = dataname.size()+1;
	QString strBaseDir = CConfig::instance()->GetResaultPath();
	for (int i = 0; i < lists.size(); i++)
	{
		QVariantMap tempInfo = lists.at(i).toMap();
		for (int s = 0; s < dataname.size(); s++)
		{//行列
			if (dataname[s] == "targetpic")
			{
				QString sorucepic = tempInfo.value(dataname.at(s)).toString();
				QFileInfo file(sorucepic);
				if (file.exists())
				{
					QFile::copy(sorucepic, strTargetDir+ file.fileName());//从源路径将文件复制到目标路径
				}
				xlsx.write(i + dataline, s + 1, file.fileName(), Format2); 
				QString strPath = file.absolutePath();
				if (strPath.contains(strBaseDir))
				{
					strPath.remove(strBaseDir);
				}
				if (strPath.right(1)=="/")
				{
					strPath = strPath.left(strPath.size() - 1);
				}
				//C:/Program Files (x86)/OVIT/components/AnalysisCenter/AnalysisPic/渣土车/观沙岭/NVR_ch7_main_20240307010003_20240307020003.mp4/StructPic/_6_45064_1gPWb5S3f10.JPG
				QStringList listDir = strPath.split("/");
				if (listDir.size()>2)
				{
					listDir.pop_back();
					listDir.pop_back();
					for (int m=0;m<listDir.size();m++)
					{
						xlsx.write(i + dataline, extra + m, listDir[m], Format2);
					}
				}

			}
			else if (dataname[s] == "relatepic")
			{
				QString backpic = tempInfo.value(dataname.at(s)).toString();
				QFileInfo file(backpic);
				if (file.exists())
				{
					QFile::copy(backpic, strBackDir+ file.fileName());//从源路径将文件复制到目标路径
				}
				xlsx.write(i + dataline, s + 1, file.fileName(), Format2);
			}
			else
			{
				xlsx.write(i + dataline, s + 1, tempInfo.value(dataname.at(s)).toString(), Format2);
			}
		}
	}

	bool b = xlsx.saveAs(strFilePath);
	if (!b)
	{
		LOG_ERROR("Save to Excel failed %s", strFilePath.toStdString().c_str());
		return iRet;
	}
	return 0;
}