QT 读取Excel表

一、QAxObject
读取excel表的内容，其仅在windows下生效，当然还有其他跨平台的方案。

config += qaxcontainer
#include <QAxObject>

QStringList GetSheets(const QString& strPath)
{
    QAxObject* excel = new QAxObject("Excel.Application");      // 加载 Microsoft Excel 控件
    excel->dynamicCall("SetVisible(bool)", false);              // 不显示Excel窗体
    QAxObject* workbooks = excel->querySubObject("WorkBooks");  // 获取工作薄集合
    QAxObject* workopen = workbooks->querySubObject("Open(const QString&)", strPath);
    QAxObject* mySheets = workopen->querySubObject("WorkSheets");

    QAxObject* workbook = excel->querySubObject("ActiveWorkBook");  // 获取当前工作薄
    QAxObject* sheets = workbook->querySubObject("Sheets");      // 当前工作薄的工作表集合
    int nSheetCount = sheets->property("Count").toInt();         // 工作表个数

    QStringList SheetsList;
    for (int i = 0; i < nSheetCount; i++)
    {
        QAxObject* sheet = workbook->querySubObject("Sheets(int)", i + 1);
        QString Name = sheet->property("Name").toString();
        SheetsList.push_back(Name);
    }

    workopen->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete excel;
    return SheetsList;
}

QVariant readAll(QAxObject* sheet)
{
    QVariant var;
    if (sheet != nullptr && !sheet->isNull())
    {
        QAxObject* usedRange = sheet->querySubObject("UsedRange");
        if (nullptr == usedRange || usedRange->isNull())
            return var;

        var = usedRange->dynamicCall("Value");
        delete usedRange;
    }
    return var;
}

bool GetVariant(const QString strName, const QVariant& Line, QList<DeviceVariant>& lstParam)
{
    QVariantList thisLine = Line.toList();

    if (thisLine.at(0).type() != QVariant::Invalid)
    {
        qDebug() << thisLine.at(0).toString();
        // 最好将每一列单元格数据记录下来，如果好几行共用同一列的数据，只能读取到首列
        m_oneCol = thisLine.at(0).toString();  
    } 
    else if (thisLine.at(1).type() != QVariant::Invalid)
    {
        qDebug() << thisLine.at(1).toString();
        m_secondCol = thisLine.at(1).toString();
    } 
}

bool ReadExcel(const QStringList& Sheets, QList<DeviceVariant>& Data)
{
    QAxObject* excel = new QAxObject("Excel.Application");
    excel->dynamicCall("SetVisible(bool)", false);
    QAxObject* workbooks = excel->querySubObject("WorkBooks");
    QAxObject* myWork = workbooks->querySubObject("Open(QString&)", "D:\\test.xlsx");
    QAxObject* mySheets = myWork->querySubObject("WorkSheets");

    bool ret = true;
    for (auto& sheetName : Sheets)
    {
        QAxObject* sheet = mySheets->querySubObject("Item(QString)", sheetName);
        if (!sheet)
        {
            ret = false;
            break;
        }

        QVariantList mData = readAll(sheet).toList();

        for (auto& Line : mData)
        {
            QList<DeviceVariant> custormList;

            // 解析每一行数据，存储数据到自定义结构体中
            if (GetVariant(sheetName, Line, custormList))
                continue;
            Data += custormList;
        }

    }

    myWork->dynamicCall("Close()");
    excel->dynamicCall("Quit()");

    delete excel;
    return ret;
}

使用

QStringList strSheets = GetSheets("D:\\test.xlsx");
QList<DeviceVariant> data;
ReadExcel(strSheets, data);