在诸多场合,需要离线分析excel表格或者xml,就需要把已经完成的程序从对接数据库改为对接xml或者excel表离线分析数据,这个时候,如果按照excel的改法去大改,耗时耗力,而且得不到好结果,这时候就需要直接分析sql语句,把sql语句与excel表关联起来。
实现思路大致为:所有逻辑运算符使用enum枚举将它们关系从一个运算符转为一个可以存储的类型或者变量。而嵌套关系,在存储的中间件中,则使用链表,可以指向本类的指针,来指向下一个节点或子节点,这样就可以无限指向子节点来维护各种深层次的嵌套关系。这样一个可以存储所有嵌套关系以及逻辑运算符的存储介质就有了。
接下来是关于实现思路,实现思路大致就是递归处理子节点,最外层只需要实现父节点的所有逻辑关系以及表达式,然后一直递归,递归到子节点指向NULL,则递归停止。所有逻辑关系运算最后打包成一个公式,比如1!=2、1<2、1==2||2<3&&3==3等这种公式(注意:逻辑&&比逻辑||优先级高),然后使用QJsEngine或者QJavaScriptEngine对接javaScript代码来运算逻辑表达式,从而返回筛选结果。
本章读取Excel使用了libxl,因为涉及到读取xls以及xlsx,这里实现可以使用其他中间件。直接贴出代码:
读取excel的关键类ReadWriteExcel
readwriteexcel.h
#ifndef READWRITEEXCEL_H
#define READWRITEEXCEL_H
#include <wchar.h>
#include <stdlib.h>
#include <iostream>
#include <QString>
#include "libxl.h"
#include <stdlib.h>
#include <QDebug>
#include <QQueue>
#include <QQmlEngine>
#include <QScriptEngine>
#include <QRegularExpression>
#include <stdio.h>
#include <functional>
#include "cdatetime.h"
using namespace libxl;
#ifdef _UNICODE
#define STR2CHAR(input) (QString(input).toStdWString().c_str())
#define CHAR2STR(input) (QString::fromWCharArray(input))
#else
#define STR2CHAR(input) (QString(input).toStdString().c_str())
#define CHAR2STR(input) (QString::fromLocal8Bit(input))
#endif
enum VSTATE{
DEFAULT,
EQUAL, //“==”等于
UNEQUAL, //“!=”不等
LIKE, //“Like”包含
IN, //满足一个则为true,值以“,”隔开
MORE, //“>”
LESS, //“<”
MOREEQUAL, //“>=”
LESSEQUAL, //“<=”
};
enum LOGICSTATE
{
AND,
OR
};
typedef struct
{
VSTATE v_state = VSTATE::DEFAULT;
QPair<QString,QVariant> valueMap;
// QVariant::Type str_type =QVariant::Invalid;
}VSTATESTR;
// 用于表示逻辑运算符和对应条件的结构体
struct LogicCond;
//把整个where语句串起来
typedef struct LogicCond{
VSTATESTR m_judege; // 本节点
struct LogicCond* subCondNext = NULL; // 如果本节点是列表,则为列表
LOGICSTATE logicState = LOGICSTATE::AND; // 与下一个LogicCondition的关系
// struct LogicCond * lastLogicCond = NULL; //上一个节点
struct LogicCond * nextLogicCond = NULL; //下一个节点
}LogicCondition;
enum DBTYPE
{
unkown,
UInt,
Int,
Double,
String,
Data,
DateTIme
};
class ReadWriteExcel
{
public:
ReadWriteExcel();
~ReadWriteExcel();
//打开文件
bool open(QString path);
//获取所有数据
QVector<QVector<QString> > GetAllData();
//传入规则树,传出匹配的行数
quint64 GetDataCount(LogicCondition logic);
quint64 GetDataCount(QMap<QString,DBTYPE> dbTypeMap,QString where_order);
QList<QVariantHash> GetData(QMap<QString,DBTYPE> dbTypeMap,QString where_order,QPair<QString,bool> orderBy);
QList<QVariantHash> GetData(QMap<QString,DBTYPE> dbTypeMap,QString where_order,QPair<QString,bool> orderBy,int index,int count);
LogicCondition *ConvertToLogicCondition(QMap<QString,DBTYPE> dbTypeMap, QString where_order, QMap<QString,QString> excelMap = QMap<QString,QString>());
void DeleteLogicCondition(LogicCondition* logic);
void DebugLogicCondition(LogicCondition* logic);
//关闭文件
void close();
private:
bool JudegeEvaluate(LogicCondition* logic, QMap<QString,QString> jedgeMap = QMap<QString,QString>());
bool JudegeEvaluate(VSTATESTR jedgeValue,QMap<QString,QString> jedgeMap = QMap<QString,QString>());
QString SplictOutermostBracket(QString text);
VSTATE GetState(QString str);
QList<QVariantHash> SortMapValues(QMultiMap<QString,QVariantHash> map,bool isGreater);
private:
Book* m_book{NULL};
QString m_path;
QScriptEngine engine;
};
#endif // READWRITEEXCEL_H
readwriteexcel.cpp
#include "readwriteexcel.h"
ReadWriteExcel::ReadWriteExcel()
{
m_book = xlCreateBook();
#ifdef Q_OS_WIN
m_book->setKey(STR2CHAR("GCCG"), STR2CHAR("windows-282123090cc0e6036db16b60a1q3q0i9"));
#else
book->setKey(STR2CHAR("libxl"), STR2CHAR("linux-i8i3ibi2i8i4iei3i9iaiei8ibx6x5i3"));
#endif
qRegisterMetaType<CDateTime>("CDateTime");
}
ReadWriteExcel::~ReadWriteExcel()
{
m_book->release();
}
bool ReadWriteExcel::open(QString path)
{
if(!m_book->load(STR2CHAR(path)))
{
qDebug()<<"load file error:"<<m_book->errorMessage();
return false;
}
m_path = path;
return true;
}
QVector<QVector<QString> > ReadWriteExcel::GetAllData()
{
QVector<QVector<QString> > resultList;
if(m_book->load(STR2CHAR(m_path)))
{
libxl::Sheet* sheet = m_book->getSheet(0); // 获取第一个工作表
if (sheet) {
int rowCount = sheet->lastRow(); // 获取行数
int colCount = sheet->lastCol(); // 获取列数
for (int row = 0; row <= rowCount; ++row) {
QVector<QString> resultTempList;
for (int col = 0; col <= colCount; ++col) {
libxl::CellType cellType = sheet->cellType(row, col); // 获取单元格类型
QString value = "";
if (cellType == libxl::CELLTYPE_NUMBER) {
value = QString::number(sheet->readNum(row, col)); // 读取数字类型单元格的值
}
else if (cellType == libxl::CELLTYPE_STRING) {
value = CHAR2STR(sheet->readStr(row, col)); // 读取字符串类型单元格的值
}
else {
}
resultTempList.push_back(value);
}
resultList.push_back(resultTempList);
}
}
// m_book->release(); // 释放Book对象
}
return resultList;
}
quint64 ReadWriteExcel::GetDataCount(LogicCondition logic)
{
// QVector<QVector<QString> > resultList;
quint64 count = 0;
if(m_book->load(STR2CHAR(m_path)))
{
libxl::Sheet* sheet = m_book->getSheet(0); // 获取第一个工作表
if (sheet) {
int rowCount = sheet->lastRow(); // 获取行数
int colCount = sheet->lastCol(); // 获取列数
QList<QString> headerList;
for (int row = 0; row <= rowCount; ++row) {
// QVector<QString> resultTempList;
QMap<QString,QString> value_map;
for (int col = 0; col <= colCount; ++col)
{
libxl::CellType cellType = sheet->cellType(row, col); // 获取单元格类型
QString value = "";
if (cellType == libxl::CELLTYPE_NUMBER)
{
value = QString::number(sheet->readNum(row, col)); // 读取数字类型单元格的值
}
else if (cellType == libxl::CELLTYPE_STRING)
{
value = CHAR2STR(sheet->readStr(row, col)); // 读取字符串类型单元格的值
}
else
{
value = CHAR2STR(sheet->readStr(row, col)); // 读取字符串类型单元格的值
}
// resultTempList.push_back(value);
if(row == 0)
{
headerList.push_back(value);
}
else
{
// resultTempList.push_back(value);
value_map.insert(headerList[col],value);
}
}
if(JudegeEvaluate(&logic,value_map))
{
count++;
}
// resultList.push_back(resultTempList);
}
}
// m_book->release(); // 释放Book对象
}
return count;
// return resultList;
}
quint64 ReadWriteExcel::GetDataCount(QMap<QString, DBTYPE> dbTypeMap, QString where_order)
{
quint64 count = 0;
LogicCondition *logic = ConvertToLogicCondition(dbTypeMap,where_order);
if(m_book->load(STR2CHAR(m_path)))
{
libxl::Sheet* sheet = m_book->getSheet(0); // 获取第一个工作表
if (sheet) {
int rowCount = sheet->lastRow(); // 获取行数
int colCount = sheet->lastCol(); // 获取列数
QList<QString> headerList;
for (int row = 0; row < rowCount; ++row) {
// QVector<QString> resultTempList;
QMap<QString,QString> value_map;
for (int col = 0; col < colCount; ++col)
{
libxl::CellType cellType = sheet->cellType(row, col); // 获取单元格类型
QString value = "";
if (cellType == libxl::CELLTYPE_NUMBER)
{
value = QString::number(sheet->readNum(row, col)); // 读取数字类型单元格的值
}
else if (cellType == libxl::CELLTYPE_STRING)
{
value = CHAR2STR(sheet->readStr(row, col)).trimmed(); // 读取字符串类型单元格的值
}
else
{
value = CHAR2STR(sheet->readStr(row, col)); // 读取字符串类型单元格的值
}
// resultTempList.push_back(value);
if(row == 0)
{
headerList.push_back(value);
}
else
{
// resultTempList.push_back(value);
value_map.insert(headerList[col],value);
}
}
if(value_map.count()>0)
{
// LogicCondition *logic = ConvertToLogicCondition(dbTypeMap,where_order,value_map);
// DebugLogicCondition(logic);
if(JudegeEvaluate(logic,value_map))
{
count++;
}
}
// resultList.push_back(resultTempList);
}
}
// m_book->release(); // 释放Book对象
}
DeleteLogicCondition(logic);
return count;
// CosnvertToLogicCondition(dbTypeMap,where_order);
}
QList<QVariantHash> ReadWriteExcel::GetData(QMap<QString, DBTYPE> dbTypeMap, QString where_order, QPair<QString, bool> orderBy)
{
QList<QVariantHash> resultList;
QMultiMap<QString,QVariantHash> resultMap;
LogicCondition *logic = ConvertToLogicCondition(dbTypeMap,where_order);
if(m_book->load(STR2CHAR(m_path)))
{
libxl::Sheet* sheet = m_book->getSheet(0); // 获取第一个工作表
if (sheet) {
int rowCount = sheet->lastRow(); // 获取行数
int colCount = sheet->lastCol(); // 获取列数
QList<QString> headerList;
for (int row = 0; row < rowCount; ++row) {
// QVector<QString> resultTempList;
QMap<QString,QString> value_map;
QVariantHash value_hash;
for (int col = 0; col < colCount; ++col)
{
libxl::CellType cellType = sheet->cellType(row, col); // 获取单元格类型
QString value = "";
if (cellType == libxl::CELLTYPE_NUMBER)
{
value = QString::number(sheet->readNum(row, col)); // 读取数字类型单元格的值
}
else if (cellType == libxl::CELLTYPE_STRING)
{
value = CHAR2STR(sheet->readStr(row, col)).trimmed(); // 读取字符串类型单元格的值
}
else
{
value = CHAR2STR(sheet->readStr(row, col)); // 读取字符串类型单元格的值
}
// resultTempList.push_back(value);
if(row == 0)
{
headerList.push_back(value);
}
else
{
// resultTempList.push_back(value);
value_map.insert(headerList[col],value);
value_hash.insert(headerList[col],value);
}
}
if(value_map.count()>0)
{
// LogicCondition *logic = ConvertToLogicCondition(dbTypeMap,where_order,value_map);
DebugLogicCondition(logic);
if(JudegeEvaluate(logic,value_map))
{
QString key = orderBy.first;
resultMap.insert(value_map.value(key),value_hash);
}
}
}
}
}
DeleteLogicCondition(logic);
resultList = SortMapValues(resultMap,orderBy.second);
return resultList;
}
QList<QVariantHash> ReadWriteExcel::GetData(QMap<QString, DBTYPE> dbTypeMap, QString where_order, QPair<QString, bool> orderBy, int index, int count)
{
QList<QVariantHash> hashList = GetData(dbTypeMap,where_order,orderBy);
QList<QVariantHash> mid_hashList = hashList.mid(index,count);
return mid_hashList;
}
LogicCondition* ReadWriteExcel::ConvertToLogicCondition(QMap<QString, DBTYPE> dbTypeMap, QString where_order,QMap<QString, QString> excelMap)
{
// 正则表达式,用于匹配'and'或'or'(忽略大小写)
// QString pattern("\\(([^()]*)\\)");
QString pattern("(\\band\\b|\\bor\\b)");
QRegularExpression re("(\\band\\b|\\bor\\b)", QRegularExpression::CaseInsensitiveOption);
QRegularExpressionMatchIterator it = re.globalMatch(where_order);
int lastIndex = 0;
int subIndex = -1;
int subLen = 0;
LogicCondition* headLogic = NULL;
LogicCondition* currentNode = NULL;
auto JudgeIsSubInner=[](int subIndex,int subLen,int index,int segmentCount)
{
// qDebug()<<"index=="<<index<<" subIndex=="<<subIndex<<" segmentCount=="<<segmentCount<<" subLen=="<<subLen;
if(index >= subIndex && (index+segmentCount) <= (subIndex+subLen))
{
return true;
}
return false;
};
auto RunSubInner=[&](QString sun_str)
{
// qDebug()<<"sun"<<where_order.mid(lastIndex).trimmed();
subLen = sun_str.count()+2;
LogicCondition* logic = ConvertToLogicCondition(dbTypeMap,sun_str,excelMap);
return logic;
};
auto ConvertToQVariant=[](QString str,DBTYPE type)
{
QVariant result;
if(type == DBTYPE::DateTIme)
{
CDateTime dateTime;
dateTime.fromString(str);
result = QVariant::fromValue(dateTime);
return result;
}
else
{
result = QVariant::fromValue(str);
return result;
}
};
auto ConvertLinkList=[&](QString segment,LOGICSTATE logic_sta)
{
LogicCondition *logic_cond = new LogicCondition();
if(currentNode == NULL)
{
headLogic = logic_cond;
currentNode = logic_cond;
}
else
{
currentNode->nextLogicCond = logic_cond;
}
if(segment.at(0) == "(")
{
QString sun_str = where_order.mid(lastIndex);
lastIndex+=sun_str.count();
sun_str = sun_str.trimmed();
sun_str = SplictOutermostBracket(sun_str);
subIndex = lastIndex;
LogicCondition *sub_logic_cond = RunSubInner(sun_str);
logic_cond->subCondNext = sub_logic_cond;
}
else
{
QRegularExpression regex("(\\!=|\\=|\\>=|\\<=|\\>|\\<| \\b[Ii][Nn]\\b| \\b[Ll][Ii][Kk][Ee]\\b)");
QStringList splitList = segment.split(regex);
logic_cond->logicState = logic_sta;
if(splitList.count() == 1)
{
logic_cond->m_judege.valueMap.first = splitList.value(0).trimmed();
DBTYPE type = DBTYPE::UInt;
logic_cond->m_judege.v_state = splitList.value(0).trimmed()=="0"?VSTATE::UNEQUAL:VSTATE::EQUAL;
logic_cond->m_judege.valueMap.second = ConvertToQVariant(splitList.value(0).trimmed(),type);
// qDebug()<<"1=="<<logic_cond->m_judege.valueMap;
}
else
{
logic_cond->m_judege.valueMap.first = (excelMap.count() > 0?excelMap.value(splitList.value(0).trimmed()):splitList.value(0).trimmed());
DBTYPE type = dbTypeMap.value(splitList.value(0).trimmed());
logic_cond->m_judege.v_state = GetState(segment);
QString rightValue = splitList.value(1).trimmed().replace("\'","");
logic_cond->m_judege.valueMap.second = ConvertToQVariant(rightValue,type);
}
}
currentNode = logic_cond;
return currentNode;
};
// 遍历所有匹配
while (it.hasNext()) {
QRegularExpressionMatch match = it.next();
QString matchedKeyword = match.captured(0).toLower(); // 捕获'and'或'or'
LOGICSTATE logic_state = LOGICSTATE::AND;
if(matchedKeyword == "or")
{
logic_state = LOGICSTATE::OR;
}
// 获取当前匹配关键字的位置
int index = match.capturedStart(0);
// // 提取匹配之前的表达式片段
QString segment = where_order.mid(lastIndex, index - lastIndex).trimmed();
if(JudgeIsSubInner(subIndex,subLen,lastIndex,segment.count()))
{
continue;
}
ConvertLinkList(segment,logic_state);
if(segment.at(0) != "(")
{
// // 更新最后一个匹配的位置,以备下一次提取使用
lastIndex = index + matchedKeyword.length();
}
}
QString lastSegment = where_order.mid(lastIndex).trimmed();
if((!lastSegment.isEmpty())&&( !JudgeIsSubInner(subIndex,subLen,lastIndex,lastSegment.count())))
{
ConvertLinkList(lastSegment,LOGICSTATE::AND);
}
return headLogic;
}
void ReadWriteExcel::DeleteLogicCondition(LogicCondition *logic)
{
LogicCondition* temp;
while(logic != NULL)
{
temp = logic;
logic = logic->nextLogicCond;
DeleteLogicCondition(temp->subCondNext);
delete temp;
temp = NULL;
}
}
void ReadWriteExcel::DebugLogicCondition(LogicCondition *logic)
{
LogicCondition* temp;
while(logic != NULL)
{
temp = logic;
logic = logic->nextLogicCond;
if(temp->subCondNext != NULL)
{
qDebug()<<"intoSun....";
DebugLogicCondition(temp->subCondNext);
qDebug()<<"sunEnd.....";
}
// qDebug()<<temp->m_judege.valueMap<<" gx:"<<temp->m_judege.v_state;
}
}
void ReadWriteExcel::close()
{
m_book->release(); // 释放Book对象
}
bool ReadWriteExcel::JudegeEvaluate(LogicCondition* logic,QMap<QString,QString> jedgeMap)
{
bool isOK = false;
QString str_eva = "";
// if(jedgeMap.count() == 0)
// {
// return false;
// }
// if(logic->subCondNext != NULL)
// {
// qDebug()<<"sunbegin...";
// isOK = JudegeEvaluate(logic->subCondNext,jedgeMap);
// qDebug()<<"sunend....";
// }
auto JudgeIsSub=[&](LogicCondition* log)
{
bool isOK = false;
if(log->subCondNext != NULL)
{
qDebug()<<"sunbegin...";
isOK = JudegeEvaluate(log->subCondNext,jedgeMap);
qDebug()<<"sunend....";
}
else
{
isOK = JudegeEvaluate(log->m_judege,jedgeMap);
}
return isOK;
};
// if(logic->nextLogicCond == NULL)
// {
// return isOK;
// }
str_eva += "(";
bool leftResult = JudgeIsSub(logic);
str_eva += QString::number(leftResult);
LogicCondition* nowNode = logic;
do
{
//递归到最后一层
if(nowNode->nextLogicCond != NULL)
{
if(nowNode->logicState == LOGICSTATE::AND)
{
str_eva += "&&";
}
else
{
str_eva += "||";
}
bool rightResult = JudgeIsSub(nowNode->nextLogicCond);
str_eva += QString::number(rightResult);
nowNode = nowNode->nextLogicCond;
}
}while(nowNode->nextLogicCond != NULL);
str_eva += ")";
// qDebug()<<"str_eva=="<<str_eva;
QScriptValue result = engine.evaluate(str_eva);
return result.toBool();
}
bool ReadWriteExcel::JudegeEvaluate(VSTATESTR jedgeValue,QMap<QString,QString> jedgeMap)
{
auto JudegeEva=[=](QString leftValue,QVariant rightValue,QString logicEva)
{
QString leftStr,rightStr;
// QJSEngine engine;
if(rightValue.type() == QVariant::DateTime)
{
leftStr = QDateTime::fromString(leftValue,"yyyy-MM-dd hh:mm:ss").toString("yyyy-MM-ddThh:mm:ssZ");
rightStr = rightValue.toDateTime().toString("yyyy-MM-ddThh:mm:ssZ");
QScriptValue time1 = engine.evaluate("new Date('"+leftStr+"')"); // 注意使用ISO格式且附带时区信息
QScriptValue time2 = engine.evaluate("new Date('"+rightStr+"')");
QScriptValue result = engine.evaluate("time1.getTime() "+logicEva+" time2.getTime()");
return result.toBool();
}
else if(rightValue.userType() == qMetaTypeId<CDateTime>())
{
CDateTime leftDateTime,rightDateTime;
leftDateTime.fromString(leftValue);
rightDateTime = rightValue.value<CDateTime>();
leftStr = QString::number(leftDateTime.toTime_t());
rightStr = QString::number(rightDateTime.toTime_t());
QScriptValue result = engine.evaluate(leftStr+logicEva+rightStr);
return result.toBool();
}
else
{
leftStr = leftValue;
rightStr = rightValue.toString();
QScriptValue result = engine.evaluate(leftStr+logicEva+rightStr);
return result.toBool();
}
};
QString key = jedgeValue.valueMap.first;
QString leftValue = "";
if(jedgeMap.count() > 0)
{
if(!jedgeMap.contains(key))
{
return false;
}
leftValue = jedgeMap[key];
}
else
{
leftValue = key;
}
if(jedgeValue.v_state == VSTATE::IN)
{
QString value = jedgeValue.valueMap.second.toString().trimmed();
if (value.startsWith('(') && value.endsWith(')')) {
value = value.mid(1, value.length() - 2);
}
QStringList list = value.split(",");
return list.contains(leftValue);
}
else if(jedgeValue.v_state == VSTATE::LIKE)
{
// return leftValue.contains(jedgeValue.valueMap.second.toString());
QString regexPattern = QRegularExpression::escape(jedgeValue.valueMap.second.toString()).replace("%", ".*");
QRegularExpression regex(regexPattern, QRegularExpression::CaseInsensitiveOption);
QRegularExpressionMatch match = regex.match(leftValue);
return match.hasMatch();
}
else if(jedgeValue.v_state == VSTATE::EQUAL)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,"==");
}
else if(jedgeValue.v_state == VSTATE::UNEQUAL)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,"!=");
}
else if(jedgeValue.v_state == VSTATE::MORE)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,">");
}
else if(jedgeValue.v_state == VSTATE::LESS)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,"<");
}
else if(jedgeValue.v_state == VSTATE::MOREEQUAL)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,">=");
}
else if(jedgeValue.v_state == VSTATE::LESSEQUAL)
{
return JudegeEva(leftValue,jedgeValue.valueMap.second,"<=");
}
return false;
}
QString ReadWriteExcel::SplictOutermostBracket(QString text)
{
int depth = 0;
int startIdx = -1;
for (int i = 0;i<text.length();++i)
{
QChar c = text.at(i);
if(c == "(")
{
if(depth == 0)
{
startIdx = i;
}
++depth;
}
else if(c == ")")
{
--depth;
if(depth == 0 && startIdx != -1)
{
QString matched = text.mid(startIdx+1,i-startIdx-1);
return matched;
}
}
}
return QString();
}
VSTATE ReadWriteExcel::GetState(QString str)
{
VSTATE state;
if(str.toLower().contains("!="))
{
state = VSTATE::UNEQUAL;
}
else if (str.toLower().contains("="))
{
state = VSTATE::EQUAL;
}
else if (str.toLower().contains(">="))
{
state = VSTATE::MOREEQUAL;
}
else if (str.toLower().contains("<="))
{
state = VSTATE::LESSEQUAL;
}
else if (str.toLower().contains("<"))
{
state = VSTATE::LESS;
}
else if (str.toLower().contains(">"))
{
state = VSTATE::MORE;
}
else if (str.toLower().contains("in"))
{
state = VSTATE::IN;
}
else if (str.toLower().contains("like"))
{
state = VSTATE::LIKE;
}
else
{
state = VSTATE::DEFAULT;
}
return state;
}
QList<QVariantHash> ReadWriteExcel::SortMapValues(QMultiMap<QString, QVariantHash> map,bool isGreater)
{
QList<QString> keys = map.keys(); // 获取所有的键
if(isGreater== true)
{
std::sort(keys.begin(), keys.end(), std::greater<QString>()); // 按降序排序键
}
else {
std::sort(keys.begin(), keys.end(), std::less<QString>()); // 按降序排序键
}
// 使用排序后的键来获取它们的值
QList<QVariantHash> values;
for (const QString &key : keys)
{
// 由于可能存在多个相同的键,我们需要获取每个键对应的所有值
auto range = map.values(key);
for (const QVariantHash &value : range)
{
values.append(value);
}
}
return values;
}
CDateTime用来处理微秒级时间格式:
cdatetime.h
#ifndef CDATETIME_H
#define CDATETIME_H
#include <QDateTime>
#include <QVariant>
#include <math.h>
struct CDateTime
{
QDateTime dateTime; // 时间 年-月-日 时:分:秒
qint64 usuS = 0; // 毫秒微妙 000 000
CDateTime();
CDateTime(QDateTime tmpDateTime, qint64 usUSecond = 0);
// 转换至QDateTime 丢失微秒
QDateTime convertToDateTime();
// 添加微妙
CDateTime addUSecond(int nUSecond);
// 转微妙 1970-1-1
qint64 toTime_t();
// 微妙转时间
void fromTime_t(qint64 ulTime);
// 转换为 2021-12-12 12:12:12.123456
QString toQString();
// 字符转时间 yyyy-MM-dd hh:mm:ss.zzzzzz
void fromString(const QString& strDateTime);
};
Q_DECLARE_METATYPE(CDateTime)
#endif // CDATETIME_H
cdatetime.cpp
#include "cdatetime.h"
CDateTime::CDateTime()
{
dateTime = QDateTime::currentDateTime();
}
CDateTime::CDateTime(QDateTime tmpDateTime, qint64 usUSecond)
{
int mSecond = tmpDateTime.time().msec();
usuS = mSecond * 1000;
dateTime = tmpDateTime.addMSecs(-mSecond);
usuS = usUSecond + usuS;
}
QDateTime CDateTime::convertToDateTime()
{
QDateTime tmpDateTime = this->dateTime;
quint16 usMs = this->usuS / pow(10, 3);
return tmpDateTime.addMSecs(usMs);
}
CDateTime CDateTime::addUSecond(int nUSecond)
{
CDateTime stTmpDateTime = *this;
qint64 lTimeUs = stTmpDateTime.toTime_t() + nUSecond;
stTmpDateTime.fromTime_t(lTimeUs);
return stTmpDateTime;
}
qint64 CDateTime::toTime_t()
{
qint64 usTime = dateTime.toMSecsSinceEpoch() * 1000 + usuS;
return usTime;
}
void CDateTime::fromTime_t(qint64 ulTime)
{
quint64 ulPow = pow(10,6);
qint64 usMs = ulTime/ulPow*1000;
usuS = ulTime% ulPow;
dateTime = QDateTime::fromMSecsSinceEpoch(usMs);
}
QString CDateTime::toQString()
{
return dateTime.toString("yyyy-MM-dd hh:mm:ss.")
+ QString("%1").arg(usuS + dateTime.time().msec() * 1000, 6, 10, QChar('0'));
}
void CDateTime::fromString(const QString &strDateTime)
{
QStringList strTmpDateTimeLst = strDateTime.split(" ");
if (strTmpDateTimeLst.size() == 2)
{
// 年月日
QString strDate = strTmpDateTimeLst.at(0);
QStringList strDateLst = strDate.split("-");
if (strDateLst.size() == 3)
{
dateTime.setDate(QDate(strDateLst.at(0).toInt(),
strDateLst.at(1).toInt(),
strDateLst.at(2).toInt()));
}
// 时分秒 微妙
QString strTime = strTmpDateTimeLst.at(1);
QStringList strTimeLst = strTime.split(".");
if (strTimeLst.size() == 2)
{
QStringList strHMSLst = strTimeLst.at(0).split(":");
if (strHMSLst.size() == 3)
{
dateTime.setTime(QTime(strHMSLst.at(0).toInt(),
strHMSLst.at(1).toInt(),
strHMSLst.at(2).toInt()));
}
usuS = strTimeLst.at(1).toInt();
}
}
}
这里使用了libxl的库,具体可到官网下载使用,尊重版权,这里就不细嗦了。可以贴出pro文件添加规则,因为官方给的示例在windows上copy语句在win11上亲测没用起来,具体.pro中添加规则为:
#添加libxl用于读EXCEL
win32 {
message("win32.............")
CONFIG(release,debug|release):NEW_OUT_PWD = $$OUT_PWD/release
else:CONFIG(debug,debug|release):NEW_OUT_PWD = $$OUT_PWD/debug
NEW_OUT_PWD ~= s,/,\\,g
NEW_DLL_PATH = $$PWD/libs/bin/libxl.dll
NEW_DLL_PATH ~= s,/,\\,g
equals(QMAKE_HOST.arch,x86_64):{
NEW_DLL_PATH = $$PWD/libs/bin64/libxl.dll
NEW_DLL_PATH ~= s,/,\\,g
INCLUDEPATH = $$PWD/libs/include_cpp
LIBS += -L$$PWD/libs/lib64/ -llibxl
QMAKE_POST_LINK +=$$quote(cmd /c copy /y $$NEW_DLL_PATH $$NEW_OUT_PWD)
}
else{
NEW_DLL_PATH = $$PWD/libs/bin/libxl.dll
NEW_DLL_PATH ~= s,/,\\,g
INCLUDEPATH = $$PWD/libs/include_cpp
LIBS += -L$$PWD/libs/lib/ -llibxl
QMAKE_POST_LINK +=$$quote(cmd /c copy /y $$NEW_DLL_PATH $$NEW_OUT_PWD)
}
message($$QT_ARCH)
} else:macx {
message("macx.............")
INCLUDEPATH = ../../include_cpp
LIBS += -framework LibXL
QMAKE_LFLAGS += -F../../
QMAKE_POST_LINK +=$$quote(mkdir $${TARGET}.app/Contents/Frameworks;cp -R ../../LibXL.framework $${TARGET}.app/Contents/Frameworks/)
} else {
message("linux.............")
contains(QT_ARCH,i386) {
INCLUDEPATH = $$PWD/lin_libs/include_cpp
LIBS += $$PWD/lin_libs/lib/libxl.so
QMAKE_LFLAGS_DEBUG = "-Wl,-rpath,$$PWD/lin_libs/lib"
QMAKE_LFLAGS_RELEASE = "-Wl,-rpath,$$PWD/lin_libs/lib"
}
contains(QT_ARCH,x86_64) {
INCLUDEPATH = $$PWD/lin_libs/include_cpp
LIBS += $$PWD/lin_libs/lib64/libxl.so
QMAKE_LFLAGS_DEBUG = "-Wl,-rpath,$$PWD/lin_libs/lib64"
QMAKE_LFLAGS_RELEASE = "-Wl,-rpath,$$PWD/lin_libs/lib64"
}
contains(QT_ARCH,aarch64) || contains(QT_ARCH,arm64) {
INCLUDEPATH += $$PWD/lin_libs/include_cpp
LIBS += $$PWD/lin_libs/lib-aarch64/libxl.so
QMAKE_LFLAGS_DEBUG = "-Wl,-rpath,$$PWD/lin_libs/lib-aarch64"
QMAKE_LFLAGS_RELEASE = "-Wl,-rpath,$$PWD/lin_libs/lib-aarch64"
}
message($$QT_ARCH)
}
使用方法:
QString strSqlFilter = M_TIME>'2024-04-02 16:28:30' and (M_ID in (102,103) and M_CODE Like '%1');
QMap<QString, DBTYPE> types;
types.insert("M_TIME",DBTYPE::DateTIme);
types.insert("M_ID",DBTYPE::UInt);
types.insert("M_CODE",DBTYPE::UInt);
quint64 count = rwExcel.GetDataCount(types,strSqlFilter);
QPair<QString, bool> orderBy;
orderBy.first = TABLE_FIELD_EVENTTIME;
orderBy.second = false; //desc
int index = 0; //开始剪切位置
int count = 200; //剪切数量
QList<QVariantHash> dataLst= rwExcel.GetData(types,strSqlFilter,orderBy,index ,count);