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);