利用QAxObject简单操作Excel

需要在pro中添加QT += axcontainer，将模块引入

1、头文件copertexcelobj.h

#ifndef COPERTEXCELOBJ_H
#define COPERTEXCELOBJ_H

#include <QObject>
#include <QAxObject>
#include <QDir>
#include <QVariant>

/*
 * 利用QAxObject实现对Excel的相关操作
*/

class COpertExcelObj : public QObject
{
    Q_OBJECT
public:
    explicit COpertExcelObj(QObject *parent = nullptr);
    ~COpertExcelObj();

    void createrNewFile(const QString filename); //带路径的
    bool openFile(const QString filename); //带路径的
    QList<QVariantList> readSheetData(int index);
    QList<QVariantList> readSheetDataByName(QString name);

    QAxObject *getWorkSheet(int index);
    QAxObject *getWorkSheetByName(QString name);

    void setCellText(QAxObject *work_sheet, int row, int column, QString value);

    /*
         setCellValue fieldname 的值需要EXCEL支持才行，比如Color String
    */
    void setCellValue(QAxObject *work_sheet, int row, int column, QString fieldname, QVariant value);
    QAxObject *getCell(QAxObject *work_sheet, int row, int column);


private:
    QAxObject *m_excelobj;
    QAxObject *m_workbooks;

};

#endif // COPERTEXCELOBJ_H

2、cpp文件copertexcelobj.cpp

#include "copertexcelobj.h"

#include <QDebug>

COpertExcelObj::COpertExcelObj(QObject *parent)
    : QObject{parent}
{
    m_excelobj = new QAxObject();
    if (!m_excelobj->setControl("Excel.Application")) //连接Excel控件
    {
        m_excelobj->setControl("ket.Application");  //??wps??
    }
    m_excelobj->setProperty("Visible", false);//显示窗体看效果
    m_excelobj->setProperty("DisplayAlerts", false);//显示警告看效果
    m_excelobj->setProperty("Caption", "Qt Excel");      //标题为Qt Excel

    m_workbooks = m_excelobj->querySubObject("WorkBooks");

    /*
    QAxObject* workbook = workbooks->querySubObject("Open(const QString&)",QDir::toNativeSeparators(ExcelFile) ); //打开
    QAxObject *work_book = excel->querySubObject("ActiveWorkBook");
    QAxObject *worksheet = work_book->querySubObject("Sheets(int)",1);     //获取表单1
    Excel_SetCell(worksheet,2,2,QColor(74,51,255),"12345");     //设置B2单元格内容为12345
    workbook->dynamicCall("Save()" );
    */

}

COpertExcelObj::~COpertExcelObj()
{
    m_workbooks->dynamicCall("Close(Boolean)", false);  //关闭文件
    m_excelobj->dynamicCall("Quit(void)");  //退出
    delete  m_excelobj;
    m_excelobj = nullptr;
}

void COpertExcelObj::createrNewFile(const QString filename)
{
    QAxObject *workbook = m_workbooks->querySubObject("Open(const QString&)",QDir::toNativeSeparators(filename) );

}

bool COpertExcelObj::openFile(const QString filename)
{
    QAxObject *workbook = m_workbooks->querySubObject("Open(const QString&)",QDir::toNativeSeparators(filename) );
    if (workbook) return true;
    return false;
}

QList<QVariantList> COpertExcelObj::readSheetData(int index)
{
    QList<QVariantList> rlist;
    QAxObject *work_book = m_excelobj->querySubObject("ActiveWorkBook");
    QAxObject *work_sheet = work_book->querySubObject("Sheets(int)",index);     //获取表单1
    if (work_sheet == nullptr) return rlist;

    QString work_sheet_name = work_sheet->property("Name").toString();  //获取工作表名称
    QAxObject *used_range = work_sheet->querySubObject("UsedRange");
    QAxObject *rows = used_range->querySubObject("Rows");
    QAxObject *columns = used_range->querySubObject("Columns");
    int row_start = used_range->property("Row").toInt();  //获取起始行
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int row_count = rows->property("Count").toInt();  //获取行数
    int column_count = columns->property("Count").toInt();  //获取列数


    for (int i = row_start; i < row_count; ++i)
    {
        QVariantList rowlist;
        for (int j = column_start; j < column_count; ++j)
        {
            QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", i, j);
            QVariant cell_value = cell->property("Value");  //获取单元格内容
            rowlist << cell_value;
        }
        rlist << rowlist;
    }

    return rlist;


}

QList<QVariantList> COpertExcelObj::readSheetDataByName(QString name)
{
    QList<QVariantList> rlist;
    QAxObject *work_book = m_excelobj->querySubObject("ActiveWorkBook");
    QAxObject *work_sheet = work_book->querySubObject("Sheets(string)",name);     //获取表单1
    if (work_sheet == nullptr) return rlist;
    QString work_sheet_name = work_sheet->property("Name").toString();  //获取工作表名称
    QAxObject *used_range = work_sheet->querySubObject("UsedRange");
    QAxObject *rows = used_range->querySubObject("Rows");
    QAxObject *columns = used_range->querySubObject("Columns");
    int row_start = used_range->property("Row").toInt();  //获取起始行
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int row_count = rows->property("Count").toInt();  //获取行数
    int column_count = columns->property("Count").toInt();  //获取列数


    for (int i = row_start; i < row_count; ++i)
    {
        QVariantList rowlist;
        for (int j = column_start; j < column_count+1; ++j)
        {
            QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", i, j);
            QVariant cell_value = cell->property("Value");  //获取单元格内容
//            if (cell_value.type() == QVariant::Invalid)
//            {
//                cell_value = "";
//            }
            rowlist << cell_value;
        }
        rlist << rowlist;

    }

    return rlist;
}

QAxObject *COpertExcelObj::getWorkSheet(int index)
{
    QAxObject *work_book = m_excelobj->querySubObject("ActiveWorkBook");
    QAxObject *work_sheet = work_book->querySubObject("Sheets(int)",index);     //获取表单1
    return work_sheet;
}

QAxObject *COpertExcelObj::getWorkSheetByName(QString name)
{
    QAxObject *work_book = m_excelobj->querySubObject("ActiveWorkBook");
    QAxObject *work_sheet = work_book->querySubObject("Sheets(string)",name);     //获取表单1
    return work_sheet;
}

void COpertExcelObj::setCellText(QAxObject *work_sheet, int row, int column, QString value)
{
    if (work_sheet == nullptr) return;
    QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", row, column);
    cell->setProperty("Value", value);
}

void COpertExcelObj::setCellValue(QAxObject *work_sheet, int row, int column, QString valuefield, QVariant value)
{
    if (work_sheet == nullptr) return;
    QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", row, column);
    cell->setProperty(valuefield.toLocal8Bit().constData(), value);
}

QAxObject *COpertExcelObj::getCell(QAxObject *work_sheet, int row, int column)
{
    QAxObject *cell = nullptr;
    if (work_sheet == nullptr) return cell;
    cell = work_sheet->querySubObject("Cells(int,int)", row, column);
    return cell;
}

3、使用案例

    QString ExcelFile = "E://test12.xlsx";
    COpertExcelObj obj;
    obj.openFile(ExcelFile);
    //obj.readSheetData(1);
    QList<QVariantList> dataList = obj.readSheetDataByName("Sheet1"); //读取Sheet1的表单数据

其他C++读写Excel有许多开源库_缘如风的博客-CSDN博客_c++ excel库