Qt写入excel文件window和Linux通用，支持超链接

#ifndef COPYALLFILETHREAD_H
#define COPYALLFILETHREAD_H

#include <QThread>
#include <QMap>
#include <QVariantList>
class CopyAllFileThread : public QThread
{
    Q_OBJECT

public:
    CopyAllFileThread();
    ~CopyAllFileThread();
    int StartCopyFile(QMap<QString, QString> selectItemFileTime,QString strTargetPath, QVariantList comparelist,QString strSql,QString table,bool checked);
    void run();
    QString m_strTargetPath = "";
    int SaveToExcelFile(QVariantList& lists);
private:
    bool m_checkAll = false;
    QVariantList m_comparelist;
    QString m_strSql = "";
    QMap<QString, QString> m_selectItemFileTime;
    QString m_table;
};

#endif // COPYFILETHREAD_H

 

#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"
#include <QUrl>
#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);/*边框样式*/
    
    QXlsx::Format formatHyperLink;  //设置内容样式
    formatHyperLink.setPatternBackgroundColor(QColor(218, 238, 243));      //设置背景颜色
    formatHyperLink.setFontSize(fontSize);  //设置字体大小
    formatHyperLink.setHorizontalAlignment(QXlsx::Format::AlignHCenter);  //横向居中
    formatHyperLink.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    formatHyperLink.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/
    formatHyperLink.setFontColor(QColor(0, 0, 255)); // 设置字体颜色为蓝色
    formatHyperLink.setFontUnderline(QXlsx::Format::FontUnderlineSingle); // 设置下划                                              //建立Sheet
    QStrSheetName = m_table;  //工作簿的名称
    xlsx.addSheet(QStrSheetName);  //添加该命名的工作簿
    QXlsx::Worksheet* worksheet = xlsx.currentWorksheet();
    //写表头
    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); 
                if (worksheet)
                {
                    QString strRelatePath = "目标小图/" + file.fileName();
                    QUrl url(strRelatePath);
                    worksheet->writeHyperlink(i + dataline, s + 1, url, formatHyperLink, file.fileName(), "点击查看图片");
                }
                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);
                if (worksheet)
                {
                    QString strRelatePath = "背景大图/" + file.fileName();
                    QUrl url(strRelatePath);
                    worksheet->writeHyperlink(i + dataline, s + 1, url, formatHyperLink,file.fileName(), "点击查看图片");
                }
            }
            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;
}

文中使用Qxlsx是开源组件

https://github.com/QtExcel/QXlsx/tree/master/QXlsx