QT界面开发-QAxObject 读写excel(COM组件)-常用功能封装代码

转载：https://www.cnblogs.com/nxopen2018/p/12197604.html

qtExcelApi.h

#pragma once

#include <ActiveQt/QAxObject>
#include <QDir>

using namespace std;

class qtExcelApi
{
public:
    qtExcelApi(void);
    ~qtExcelApi(void);


    /**
    * @brief 新建Excel
    */
    void CreateExcel();


    /**
    * @brief 打开Excel
    * @param[in] filePath  路径
    * @param[in] type  打开时是否显示EXCEL
    * @return BOOL 打开成功&失败
    */
    bool OpenFile(const char* filePath, bool type);


    /**
    * @brief 关闭Excel
    */
    void CloseExcel();


    /**
    * @brief 保存Excel
    */
    void Save(const char* savePath);


    /**
    * @brief 获取所有的工作表数量
    * @return int 数量
    */
    int GetSheetCount();


    /**
    * @brief 获取单元格数据
    * @param[in] row  行
    * @param[in] column  列
    * @return string 内容
    */
    string GetRangeData(const int row, const int column);


    /**
    * @brief 读取整个sheet
    * @return string 内容
    */
    vector<string> GetUsedRange();


    /**
    * @brief 读取sheet中的一个范围
    * @return string 内容
    */
    vector<string> GetScopeRange(const char* A1, const char* A5);


    /**
    * @brief 当前sheet单元格写入内容
    * @param[in] row  行
    * @param[in] column  列
    * @param[in] Data  内容
    */
    void SetRangeData(const int row, const int column, const char* Data);


    /**
    * @brief 获得当前sheet使用的行数
    * @return int 数量
    */
    int GetRowNum();


    /**
    * @brief 获得当前sheet使用的列数
    * @return int 数量
    */
    int GetColumnNum();


    /**
    * @brief 设置当前工作表
    * @param[in] id  第几个sheet,从1开始
    * @return BOOL 设置成功&失败
    */
    bool SetCurrentSheetByNum(const int& id);



private:

    //Excel应用程序
    QAxObject *ExcelApp;
    //Excel工作簿
    QAxObject *ExcelBooks;
    QAxObject *ExcelBook;
    //Excel工作表
    QAxObject *ExcelSheets;
    QAxObject *ExcelSheet;
    //Excel单元格
    QAxObject *ExcelRange;

};

qtExcelApi.cpp

#include "qtExcelApi.h"


qtExcelApi::qtExcelApi(void)
{

}

qtExcelApi::~qtExcelApi()
{
}


//打开Excel
void qtExcelApi::CreateExcel()
{
    //连接excel
    ExcelApp = new QAxObject("Excel.Application");
    //是否可视化excel
    ExcelApp->dynamicCall("SetVisible(bool Visible)", true);
    //是否弹出警告窗口
    ExcelApp->setProperty("DisplayAlerts", false);
    //获取工作簿集合
    ExcelBooks = ExcelApp->querySubObject("WorkBooks");
    //新建一个工作簿
    ExcelBooks->dynamicCall("Add");
    //获取当前工作簿
    ExcelBook = ExcelApp->querySubObject("ActiveWorkBook");
    //获取工作表格集合
    ExcelSheets = ExcelBook->querySubObject("Sheets");
    //获取当前工作表格1，即sheet1
    ExcelSheet = ExcelSheets->querySubObject("Item(int)", 1);
}


//打开Excel
bool qtExcelApi::OpenFile(const char* filePath, bool type)
{
    //连接excel
    ExcelApp = new QAxObject("Excel.Application");
    //是否可视化excel
    ExcelApp->dynamicCall("SetVisible(bool Visible)", type);
    //是否弹出警告窗口
    ExcelApp->setProperty("DisplayAlerts", false);
    //获取工作簿集合
    ExcelBooks = ExcelApp->querySubObject("WorkBooks");
    //打开一个工作簿
    ExcelBooks->dynamicCall("Open(const QString&)", QDir::toNativeSeparators(filePath));
    //获取当前工作簿
    ExcelBook = ExcelApp->querySubObject("ActiveWorkBook");
    //获取工作表格集合
    ExcelSheets = ExcelBook->querySubObject("Sheets");
    //获取当前工作表格1，即sheet1
    ExcelSheet = ExcelSheets->querySubObject("Item(int)", 1);
    return true;
}


//关闭Excel
void qtExcelApi::CloseExcel()
{
    if (ExcelApp != NULL)
    {
        ExcelApp->dynamicCall("Quit()");
        delete ExcelApp;
        ExcelApp = NULL;
    }
}


//保存Excel
void qtExcelApi::Save(const char* savePath)
{
    ExcelBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(savePath));
}


//获取单元格数据
string qtExcelApi::GetRangeData(const int row, const int column)
{
    QVariant ret;
    QByteArray cdata;
    if (this->ExcelSheet != NULL && !this->ExcelSheet->isNull())
    {
        QAxObject* range = this->ExcelSheet->querySubObject("Cells(int, int)", row, column);
        ret = range->dynamicCall("Value()");
        QString qs = ret.toString();
        cdata = qs.toLocal8Bit();
        delete range;
    }
    return string(cdata);;
}


//读取整个sheet
vector<string> qtExcelApi::GetUsedRange()
{
    //读取当前工作表所有数据
    QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
    QVariant var = usedRange->dynamicCall("Value()");
    QVariantList varRows = var.toList();
    const int rowCount = varRows.size();
    QVariantList rowData;
    QByteArray cdata;
    vector<string> vecUsedRange;
    for (int i = 0; i < rowCount; ++i)
    {
        rowData = varRows[i].toList();
        QString qs = rowData[0].toString();
        cdata = qs.toLocal8Bit();
        vecUsedRange.push_back(string(cdata));
    }
    delete usedRange;
    return vecUsedRange;
}


//读取sheet中的一个范围
vector<string> qtExcelApi::GetScopeRange(const char* A1, const char* A5)
{
    QVariantList params;
    params << A1 << A5;  //A1至A5的数据
    //读取当前工作表所有数据
    QAxObject *cell = ExcelSheet->querySubObject("Range(QVariant,QVariant)", params);
    QVariant var = cell->dynamicCall("Value2()");
    QVariantList varRows = var.toList();
    const int rowCount = varRows.size();
    QVariantList rowData;
    QByteArray cdata;
    vector<string> vecUsedRange;
    for (int i = 0; i < rowCount; ++i)
    {
        rowData = varRows[i].toList();
        QString qs = rowData[0].toString();
        cdata = qs.toLocal8Bit();
        vecUsedRange.push_back(string(cdata));
    }
    delete cell;
    return vecUsedRange;
}


//当前sheet单元格写入内容
void qtExcelApi::SetRangeData(const int row, const int column, const char* Data)
{
    QAxObject *cell = ExcelSheet->querySubObject("Cells(int,int)", row, column);
    cell->setProperty("Value", Data);
}


//获取所有的工作表数量
int qtExcelApi::GetSheetCount()
{
    int intCount = ExcelSheets->property("Count").toInt();
    return intCount;
}


//获得当前sheet使用的行数
int qtExcelApi::GetRowNum()
{
    QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
    QAxObject *rows = usedRange->querySubObject("Rows");
    int nRows = rows->property("Count").toInt();
    return nRows;
}


//获得当前sheet使用的列数
int qtExcelApi::GetColumnNum()
{
    QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
    QAxObject *columns = usedRange->querySubObject("Columns");
    int nCols = columns->property("Count").toInt();
    return nCols;
}


//设置当前工作表
bool qtExcelApi::SetCurrentSheetByNum(const int& id)
{
    ExcelSheet = ExcelBook->querySubObject("Worksheets(int)", 2);
    return true;
}