QT对Excel进行新增、删除、修改读取从入门到精通

 0  1461

索鸟 2020-08-24

需要：0索币

配置xxx.pro
引入Active Qt库

QT += axcontainer

引入objbase.h，解决返回QAxObject为空的问题

#include <objbase.h>

//在需要创建QAxObject()上方调用如下代码
CoInitializeEx(NULL, COINIT_MULTITHREADED);
m_pExcel = new(std::nothrow) QAxObject();

因为QAxObject默认是在单线程下使用的，因此如果不用上门代码申明多线程, 会导致获取的excel的QAxObject都是NULL

保存或者打开excel的路径需要统一用"\"
用QDir::toNativeSeparators(m_strPath)进行转换
m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
SaveAs必须在所有操作结束后调用，否则不会保存到excel中
可以放到close前调用即可

m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
m_pWorkbook->dynamicCall("Close()");
m_pExcel->dynamicCall("Quit()");
delete m_pExcel;
m_pExcel = NULL;

Demo

#ifndef EXCELOPERATOR_H
#define EXCELOPERATOR_H

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

class ExcelOperator : public QObject
{
    Q_OBJECT
public:
    explicit ExcelOperator(QObject *parent = nullptr);
    ~ExcelOperator();
    //打开文件
    bool open(QString path);
    //关闭文件
    bool close();
    //获取工作表数量
    int getSheetsCount();
    //根据名称创建工作表
    QAxObject* addSheet(QString name);
    //根据名称删除工作表
    bool delSheet(QString name);
    //根据编号删除工作表
    bool delSheet(int index);
    //根据名称获取工作表
    QAxObject* getSheet(QString name);
    //根据编号获取工作表
    QAxObject* getSheet(int index);
    //获取行对象
    QAxObject* getRows(QAxObject* pSheet);
    //获取行数
    int getRowsCount(QAxObject* pSheet);
    //获取列对象
    QAxObject* getColumns(QAxObject* pSheet);
    //获取列数
    int getColumnsCount(QAxObject* pSheet);
    //根据行列值获取单元格值, 如: 3行，5列
    QString getCell(QAxObject* pSheet, int row, int column);
    //根据行列编号获取单元格值, 如: "F6"
    QString getCell(QAxObject* pSheet, QString number);
    //根据行列值设置单元格值
    bool setCell(QAxObject* pSheet, int row, int column, QString value);
    //根据行列编号设置单元格值
    bool setCell(QAxObject* pSheet, QString number, QString value);


signals:

public slots:
private:
    QAxObject*      m_pExcel;
    QAxObject*      m_pWorksheets;
    QAxObject*      m_pWorkbook;
    QString         m_strPath;
};

#endif // EXCELOPERATOR_H

#include "exceloperator.h"
#include <objbase.h>

ExcelOperator::ExcelOperator(QObject *parent) : QObject(parent)
  , m_pExcel(NULL)
  , m_pWorksheets(NULL)
  , m_pWorkbook(NULL)
{

}

ExcelOperator::~ExcelOperator()
{
    close();
}

bool ExcelOperator::open(QString path)
{
    m_strPath = path;
    QAxObject *pWorkbooks = NULL;
    CoInitializeEx(NULL, COINIT_MULTITHREADED);
    m_pExcel = new(std::nothrow) QAxObject();
    if (NULL == m_pExcel) {
        qCritical()<<"创建Excel对象失败...";
        return false;
    }
    try {
        m_pExcel->setControl("Excel.Application");
        m_pExcel->dynamicCall("SetVisible(bool)", false); //true 表示操作文件时可见，false表示为不可见
        m_pExcel->setProperty("DisplayAlerts", false);
        pWorkbooks = m_pExcel->querySubObject("WorkBooks");
        pWorkbooks->dynamicCall("Add");
        m_pWorkbook = m_pExcel->querySubObject("ActiveWorkBook");
        qDebug()<<"excel path: "<<m_strPath;

        // 获取打开的excel文件中所有的工作sheet
        m_pWorksheets = m_pWorkbook->querySubObject("WorkSheets");
    } catch (...) {
        qCritical()<<"打开文件失败...";
        return false;
    }

    return true;
}

bool ExcelOperator::close()
{
    qDebug()<<"excel close...";
    if (m_pExcel)
    {
        qDebug()<<"closing...";
        m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
        m_pWorkbook->dynamicCall("Close()");
        m_pExcel->dynamicCall("Quit()");
        delete m_pExcel;
        m_pExcel = NULL;
    }
    return true;
}

int ExcelOperator::getSheetsCount()
{
    int count =  0;
    count = m_pWorksheets->property("Count").toInt();
    return count;
}


QAxObject* ExcelOperator::addSheet(QString name)
{
    QAxObject *pWorkSheet = NULL;
    try {
        int count = m_pWorksheets->property("Count").toInt();  //获取工作表数目
        QAxObject *pLastSheet = m_pWorksheets->querySubObject("Item(int)", count);
        pWorkSheet = m_pWorksheets->querySubObject("Add(QVariant)", pLastSheet->asVariant());
        pLastSheet->dynamicCall("Move(QVariant)", pWorkSheet->asVariant());
        pWorkSheet->setProperty("Name", name);  //设置工作表名称
    } catch (...) {
        qCritical()<<"创建sheet失败...";
    }
    return pWorkSheet;
}

bool ExcelOperator::delSheet(QString name)
{
    try {
        QAxObject *pFirstSheet = m_pWorksheets->querySubObject("Item(QString)", name);
        pFirstSheet->dynamicCall("delete");
    } catch (...) {
        qCritical()<<"删除sheet失败...";
        return false;
    }
    return true;
}

bool ExcelOperator::delSheet(int index)
{
    try {
        QAxObject *pFirstSheet = m_pWorksheets->querySubObject("Item(int)", index);
        pFirstSheet->dynamicCall("delete");
    } catch (...) {
        qCritical()<<"删除sheet失败...";
        return false;
    }
    return true;
}

QAxObject* ExcelOperator::getSheet(QString name)
{
    QAxObject* pWorkSheet = NULL;
    try {
        pWorkSheet = m_pWorksheets->querySubObject("Item(QString)", name);
    } catch (...) {
        qCritical()<<"获取sheet失败...";
    }
    return pWorkSheet;
}

QAxObject* ExcelOperator::getSheet(int index)
{
    QAxObject* pWorkSheet = NULL;
    try {
        pWorkSheet = m_pWorksheets->querySubObject("Item(int)", index);
    } catch (...) {
        qCritical()<<"获取sheet失败...";
    }
    return pWorkSheet;
}

QAxObject* ExcelOperator::getRows(QAxObject* pSheet)
{
    QAxObject* pRows = NULL;
    try {
        pRows = pSheet->querySubObject("Rows");
    } catch (...) {
        qCritical()<<"获取行失败...";
    }
    return pRows;
}

int ExcelOperator::getRowsCount(QAxObject* pSheet)
{
    int rows = 0;
    try {
        QAxObject* pRows = getRows(pSheet);
        rows = pRows->property("Count").toInt();
    } catch (...) {
        qCritical()<<"获取行数失败...";
    }
    return rows;
}

QAxObject* ExcelOperator::getColumns(QAxObject* pSheet)
{
    QAxObject* pColumns = NULL;
    try {
        pColumns = pSheet->querySubObject("Columns");
    } catch (...) {
        qCritical()<<"获取列失败...";
    }
    return pColumns;
}

int ExcelOperator::getColumnsCount(QAxObject* pSheet)
{
    int columns = 0;
    try {
        QAxObject* pColumns = getColumns(pSheet);
        columns = pColumns->property("Count").toInt();
    } catch (...) {
        qCritical()<<"获取列数失败...";
    }
    return columns;
}

QString ExcelOperator::getCell(QAxObject* pSheet, int row, int column)
{
    QString strCell = "";
    try {
        QAxObject* pCell = pSheet->querySubObject("Cells(int, int)", row, column);
        strCell = pCell->property("Value").toString();
    } catch (...) {
        qCritical()<<"获取单元格信息失败...";
    }

    return strCell;
}

QString ExcelOperator::getCell(QAxObject* pSheet, QString number)
{
    QString strCell = "";
    try {
        QAxObject* pCell = pSheet->querySubObject("Range(QString)", number);
        strCell = pCell->property("Value").toString();
    } catch (...) {
        qCritical()<<"获取单元格信息失败...";
    }

    return strCell;
}

bool ExcelOperator::setCell(QAxObject* pSheet, int row, int column, QString value)
{
    try {
        QAxObject* pCell = pSheet->querySubObject("Cells(int, int)", row, column);
        pCell->setProperty("Value", value);
    } catch (...) {
        qCritical()<<"写入单元格信息失败...";
        return false;
    }
    return true;
}

bool ExcelOperator::setCell(QAxObject* pSheet, QString number, QString value)
{
    try {
        QAxObject* pCell = pSheet->querySubObject("Range(QString)", number);
        pCell->setProperty("Value", value);
    } catch (...) {
        qCritical()<<"写入单元格信息失败...";
        return false;
    }
    return true;
}

作者：你猜_19ca
链接：https://www.jianshu.com/p/392c563e2430
来源：简书

回帖

消灭零回复