Qt中Sql查询语句查询Excel表，带where语句中各种嵌套关系以及各种逻辑表达式

        在诸多场合，需要离线分析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);