导出Excel封装类
#pragma once
#include <QStringList>
#include <QVariant>
#include <QWidget>
class Export
{
public:
Export(const QVariant& content,int row,int col,QWidget* parent = nullptr);
void ExportExcel();
//线程中导出excel
void SetExportFilePath(const QString&);
void ThreadExportExcel();
private:
void convertToColName(int data, QString &res);
QString to26AlphabetString(int data);
QVariant mContents;
int mRow;
int mCol;
QWidget* mParent;
QString mFilePath;
};
#include "Export.h"
#include <QAxObject>
#include <QFileDialog>
#include <QMessageBox>
#include <QProcess>
#include <Windows.h>
Export::Export(const QVariant & content, int row, int col, QWidget* parent)
:mContents(content),
mRow(row),
mCol(col),
mParent(parent)
{
}
void Export::ExportExcel()
{
QAxObject* excelObj = new QAxObject("Excel.Application");
if (excelObj->isNull())
{
QMessageBox::critical(nullptr, QString("错误"), QString("未检测到Office Excel组件!"));
return;
}
QString strFilePath = QFileDialog::getSaveFileName(nullptr, QString("保存导出结果"), QString(), QString("Office Excel(*.xlsx *.xls)"));
if (strFilePath.isEmpty())
return;
excelObj->dynamicCall("SetVisible(bool)", false);
excelObj->setProperty("DisplayAlerts", false);
QAxObject* pWorkBooks = excelObj->querySubObject("WorkBooks");
pWorkBooks->dynamicCall("Add");
QAxObject* pWorkBook = excelObj->querySubObject("ActiveWorkBook");
QAxObject* pSheet = pWorkBook->querySubObject("Worksheets(int)",1);
//写入单个数据
//QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", 2,3);
//pRange->setProperty("Value", QString("hehe"));
//范围写入
QString strRang;
convertToColName(mCol, strRang);
strRang += QString::number(mRow);
strRang = "A1:" + strRang;
QAxObject *pRange = pSheet->querySubObject("Range(const QString&)", strRang);
if (pRange->isNull())
return;
//mContents为 QVariantList<QVariantList> 数据形式
pRange->setProperty("Value", mContents);
pWorkBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(strFilePath));
excelObj->dynamicCall("Quit()");
excelObj->deleteLater();
QProcess pro;
QString cmd = QString("explorer.exe /select,%1").arg(QDir::toNativeSeparators(strFilePath));
pro.startDetached(cmd);
}
void Export::SetExportFilePath(const QString &name)
{
mFilePath = name;
}
void Export::ThreadExportExcel()
{
::CoInitializeEx(NULL, COINIT_MULTITHREADED);
QAxObject* excelObj = new QAxObject("Excel.Application");
if (excelObj->isNull())
return;
QString strFilePath = mFilePath;
if (strFilePath.isEmpty())
return;
excelObj->dynamicCall("SetVisible(bool)", false);
excelObj->setProperty("DisplayAlerts", false);
QAxObject* pWorkBooks = excelObj->querySubObject("WorkBooks");
pWorkBooks->dynamicCall("Add");
QAxObject* pWorkBook = excelObj->querySubObject("ActiveWorkBook");
QAxObject* pSheet = pWorkBook->querySubObject("Worksheets(int)", 1);
//范围写入
QString strRang;
convertToColName(mCol, strRang);
strRang += QString::number(mRow);
strRang = "A1:" + strRang;
QAxObject *pRange = pSheet->querySubObject("Range(const QString&)", strRang);
if (pRange->isNull())
return;
//mContents为 QVariantList<QVariantList> 数据形式
pRange->setProperty("Value", mContents);
pWorkBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(strFilePath));
excelObj->dynamicCall("Quit()");
excelObj->deleteLater();
}
/// \brief 把列数转换为excel的字母列号
/// \param data 大于0的数
/// \return 字母列号,如1->A 26->Z 27 AA
void Export::convertToColName(int data, QString &res)
{
Q_ASSERT(data > 0 && data < 65535);
int tempData = data / 26;
if (tempData > 0)
{
int mode = data % 26;
convertToColName(mode, res);
convertToColName(tempData, res);
}
else
{
res = (to26AlphabetString(data) + res);
}
}
/// \brief 数字转换为26字母
/// 1->A 26->Z
QString Export::to26AlphabetString(int data)
{
QChar ch = data + 0x40;//A对应0x41
return QString(ch);
}
sqlite数据库操作封装类
#pragma once
#include <QObject>
#include <QSQLDataBase>
#include <QVariant>
class DataBase : public QObject
{
Q_OBJECT
public:
static DataBase* getInstance();
//执行
bool executeSql(const QString&);
//查询
bool querySql(const QString&,QVariant&);
bool querySql(const QString& sql, QStringList& list, int&row, int&col);
//查询单个值
bool querySqlOne(const QString&, QVariant&);
protected:
DataBase();
private:
static DataBase* mDataBase;
QSqlDatabase mDB;
};
#include "DataBase.h"
#include "Logs.h"
#include <QDir>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QApplication>
#include <QMessageBox>
DataBase* DataBase::mDataBase = nullptr;
DataBase* DataBase::getInstance()
{
if (mDataBase == nullptr)
mDataBase = new DataBase();
return mDataBase;
}
DataBase::DataBase()
{
mDB = QSqlDatabase::addDatabase("QSQLITE");
QString strDest = QApplication::applicationDirPath() + "/db";
QDir dir(strDest);
if (!dir.exists())
dir.mkdir(strDest);
mDB.setDatabaseName(strDest + "/data.db"); //如果本目录下没有该文件,则会在本目录下生成,否则连接该文件
if (!mDB.open())
{
QMessageBox::warning(0, QObject::tr("Database Error"), mDB.lastError().text());
}
else
{
//创建表
QString strTable = R"(CREATE TABLE IF NOT EXISTS [logins](
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[user] VARCHAR(20),
[pwd] VARCHAR(20));)";
executeSql(strTable);
}
}
bool DataBase::executeSql(const QString &sql)
{
QSqlQuery query;
if (!query.exec(sql))
{
writeLog(QString("%1\n[%2]").arg(query.lastError().text()).arg(sql));
return false;
}
return true;
}
bool DataBase::querySql(const QString &sql, QVariant& var)
{
var.clear();
QSqlQuery query;
if (!query.exec(sql))
{
writeLog(QString("%1\n[%2]").arg(query.lastError().text()).arg(sql));
return false;
}
int nFieldCount = query.record().count();
QVariantList list;
while (query.next())
{
QVariantList tmp;
for (int i = 0; i < nFieldCount; i++)
tmp.push_back(query.value(i));
list.push_back(tmp);
}
var = list;
return true;
}
bool DataBase::querySql(const QString &sql, QStringList & list, int & row, int & col)
{
row = 0;
col = 0;
list.clear();
QSqlQuery query;
if (!query.exec(sql))
{
writeLog(query.lastError().text());
return false;
}
col = query.record().count();
while (query.next())
{
for (int i = 0; i < col; i++)
list.push_back(query.value(i).toString());
row++;
}
return true;
}
bool DataBase::querySqlOne(const QString &sql, QVariant& var)
{
if (!querySql(sql, var))
return false;
QVariantList list = var.toList();
if (list.count() > 0)
{
QVariantList vals = list[0].toList();
if (vals.count() > 0)
{
var = vals[0];
return true;
}
}
return true;
}
写个日志文件
#include <QTime>
#include <QFile>
#include <QFileInfo>
#include <QDir>
#include <QTextStream>
#include <QMutex>
#include <QMutexLocker>
#include <windows.h>
QMutex mutex;
const QString fileName = "logs.txt";
void writeLog(const QString& msg)
{
QMutexLocker locker(&mutex);
//系统当前时间
QString strTime = QTime::currentTime().toString("hh:mm:ss.zzz");
QFileInfo fileInfo(fileName);
if (fileInfo.size() > 1024 * 1024 * 10)
QFile::remove(fileName);
//写日志
QFile file(fileName);
if (file.open(QIODevice::WriteOnly | QIODevice::Append))
{
QTextStream stream(&file);
stream << QString("%1---:%2\r\n\r\n").arg(strTime).arg(msg);
file.close();
}
}
void writeLog(char* pMsg, ...)
{
va_list argList;
va_start(argList, pMsg);
QString strResult = QString::vasprintf(pMsg, argList);
va_end(argList);
writeLog(strResult);
}