Qt简单封装的sqlite数据库操作、导出Excel等等(复制粘贴直接可用,大概吧)

导出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);
}

  • 3
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值