随笔- 372 文章- 0 评论- 37 阅读- 75万

Qt自定义插件写Excel表格

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

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

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