Qt对Excel表格的自动化调用汇总（新建、打开和保存）

为便于实时采集并保存数据到excel，需要调用QAxObject

首先定义变量excel workbooks workbook worksheets worksheet range 等；

#ifndef EXCEL_H
#define EXCEL_H
//添加头文件
#include <QVariant>
#include <ActiveQt/QAxObject>//Excel
#include <QDebug>//debug输出
#include <QDir>//保存路径

class Excel
{
public:
    Excel();
    void SetTitle1(const QVariant &var,const QVariant &value);
    QVariant getValue(const QVariant &var);
    bool openExcel();
    bool newExcel();
    void exitExcel();
    void autoFit(const QVariant &var);
    bool fileSaveAs(QString path);
    bool readXlsFile(QString path, QList<QStringList> &res);
    void setPath(QString path);
    void convertToColName(int data, QString &res);
    QString to26AlphabetString(int data);
private:
    QString filePath;
    QAxObject *excel;
    QAxObject *workbooks;
    QAxObject *workbook;
    QAxObject *worksheets;
    QAxObject *worksheet;
    QAxObject *range;
    QAxObject *interior;
    QAxObject *cell;
    QAxObject *font;
    int currRow;

};

#endif // EXCEL_H

定义新建文件、打开文件、关闭文件等函数；

注意：异常情况下需要关闭并退出（此时要delete excel 才能退出进程！）

#include "excel.h"
#include <QFileInfo>
void Excel::SetTitle1(const QVariant &var,const QVariant &value)
{
    if(worksheet == nullptr)
    {
        qDebug()<<"查询 worksheet 失败！";
        return;
    }
    range=worksheet->querySubObject("Range(const QString&)",var);
    if(range == nullptr)
    {
        qDebug()<<"查询 range 失败！";
        return;
    }
    //range->setProperty("MergeCells",true);
    range->setProperty("NumberFormatLocal", "@");
    range->setProperty("Value",value);
    range->setProperty("HorizontalAlignment",-4108);    
}
void Excel::autoFit(const QVariant &var)
{
    if(worksheet == nullptr)
    {
        qDebug()<<"查询 worksheet 失败！";
        return;
    }
    range=worksheet->querySubObject("Range(const QString&)",var);
    if(range == nullptr)
    {
        qDebug()<<"查询 range 失败！";
        return;
    }
    QAxObject * cells = range->querySubObject("Columns");
    cells->dynamicCall("AutoFit");
    delete cells;
}
QVariant Excel::getValue(const QVariant &var)
{
    if(worksheet == nullptr)
    {
        qDebug()<<"查询 worksheet 失败！";
        return "";
    }
    range=worksheet->querySubObject("Range(const QString&)",var);
    return range->property("Value");
}
bool Excel::newExcel()
{
    excel = new QAxObject("Excel.Application");
    if (!excel)
    {
       qDebug()<<"创建Excel失败！";
       return false;
    }
    excel->dynamicCall("SetVisible(bool Visible)", false);       //是否可视化excel
    excel->dynamicCall("SetUserControl(bool UserControl)", false);             //是否用户可操作
    //excel->setProperty("DisplayAlerts", true);                //是否弹出警告窗口

    workbooks = excel->querySubObject("WorkBooks");             //获取工作簿集合
    if(workbooks == nullptr)
    {
        qDebug()<<"查询 workbooks 失败！";
        exitExcel();
        return false;
    }
    workbooks->dynamicCall("Add");                              //新建一个工作簿
    workbook = excel->querySubObject("ActiveWorkBook");         //获取当前工作簿
    if(workbook == nullptr)
    {
        qDebug()<<"查询 ActiveWorkBook 失败！";
        exitExcel();
        return false;
    }
    worksheets = workbook->querySubObject("Sheets");            //获取工作表格集合
    if(worksheets == nullptr)
    {
        qDebug()<<"查询 worksheets 失败！";
        exitExcel();
        return false;
    }
    worksheet  = worksheets->querySubObject("Item(int)", 1);    //获取当前工作表格1，即sheet1
    if(worksheet == nullptr)
    {
        qDebug()<<"查询 worksheet 失败！";
        exitExcel();
        return false;
    }
    worksheet->setProperty("Name","数据");                  //修改sheet名称
    return true;
}
bool Excel::openExcel()
{
    excel = new QAxObject("Excel.Application");
    if (!excel)
    {
       qDebug()<<"打开Excel失败！";
       return false;
    }
    excel->dynamicCall("SetVisible(bool Visible)", false);       //是否可视化excel
    excel->dynamicCall("SetUserControl(bool UserControl)", false);             //是否用户可操作
    workbooks = excel->querySubObject("WorkBooks");             //获取工作簿集合
    if(workbooks == nullptr)
    {
        qDebug()<<"查询 workbooks 失败！";
        exitExcel();
        return false;
    }
    workbooks->dynamicCall("Open(const QString&)", filePath);
    //excel->setProperty("DisplayAlerts", true);                //是否弹出警告窗口
    workbook = excel->querySubObject("ActiveWorkBook");         //获取当前工作簿
    if(workbook == nullptr)
    {
        qDebug()<<"查询 workbook 失败！";
        exitExcel();
        return false;
    }
    worksheets = workbook->querySubObject("Sheets");            //获取工作表格集合
    if(worksheets == nullptr)
    {
        qDebug()<<"查询 worksheets 失败！";
        exitExcel();
        return false;
    }
    worksheet = workbook->querySubObject("Worksheets(int)", 1);
    if(worksheet == nullptr)
    {
        qDebug()<<"查询 worksheet 失败！";
        exitExcel();
        return false;
    }
    return true;
}
void Excel::exitExcel()
{
    if(excel != nullptr && workbooks != nullptr)
        workbooks->dynamicCall("Close()");
    excel->dynamicCall("Quit()");//退出
    delete excel;
}
void Excel::setPath(QString path)
{
    filePath = path;
}
bool Excel::fileSaveAs(QString path)
{
    if(!openExcel())
        return false;
    workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(path));
    exitExcel();
    return true;
}
bool Excel::readXlsFile(QString path, QList<QStringList > &res)
{
    res.clear();
    if(!openExcel())
        return false;
    if(worksheet->property("Name").toString() != "数据")
    {
        qDebug()<<"sheet name 不等于 数据！";
        exitExcel();
        return false;
    }
    //快速读取数据
    QString checkRange = "A1:A7";
    QAxObject *Data = worksheet->querySubObject("Range(QString)", checkRange);
    QVariant DataQVariant = Data->property("Value");
    QVariantList DataList = DataQVariant.toList();
    QStringList list = {"任务单号","检验人员","检验日期","从机数量","模块数量","从机地址","相应数量"};
    for(int i = 0; i < DataList.size(); i++)
    {
        QStringList t = DataList[i].toStringList();
        if(t[0] != list[i])
        {
            exitExcel();
            return false;
        }
    }
    int moduleNum = getValue("B5:B5").toInt();
    if(moduleNum <= 0)
    {
        qDebug() << "模块数量 " << moduleNum << endl;
        exitExcel();
        return false;
    }

    QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
    if(usedrange == nullptr)
    {
        qDebug()<<"readXlsFile 查询 usedrange 失败！";
        exitExcel();
        return false;
    }
    QAxObject * rows = usedrange->querySubObject("Rows");
    QAxObject * columns = usedrange->querySubObject("Columns");
    int intRows = rows->property("Count").toInt();
    int intCols = columns->property("Count").toInt();
    qDebug() << "读取 " + filePath +" 行数："<< intRows;
    qDebug() << " xls列数："<<intCols;
    if(intRows <= 8)
    {
        exitExcel();
        return false;//只有8行 无数据
    }
    // 批量载入数据
    QString rangStr;
    convertToColName(moduleNum+1,rangStr);
    QString Range = "A8:" + rangStr + QString::number(intRows);
    QAxObject *allEnvData = worksheet->querySubObject("Range(QString)", Range);
    QVariant allEnvDataQVariant = allEnvData->property("Value");
    QVariantList allEnvDataList = allEnvDataQVariant.toList();
    for(int i=0; i < allEnvDataList.size(); i++)
    {
        QStringList allEnvDataList_i =  allEnvDataList[i].toStringList() ;
        //qDebug()<< allEnvDataList_i << endl;
        res.push_back(allEnvDataList_i);
    }
    exitExcel();
    return true;
}
void Excel::convertToColName(int data, QString &res)
{
    Q_ASSERT(data>0 && data<65535);
    int tempData = data / 26;
    if(tempData > 0)
    {
        int mode = data % 26;
        convertToColName(mode,res);
        convertToColName(tempData,res);
    }
    else
    {
        res=(to26AlphabetString(data)+res);
    }
}
QString Excel::to26AlphabetString(int data)
{
    QChar ch = data + 0x40;//A对应0x41
    return QString(ch);
}

读写数据类型时QVariant（其他数据类型也可转成QVariant，例如QVariantList）不断包装。

且只能按照行进行打包！

range->setProperty("NumberFormatLocal", "@");

设置显示原始数据格式！

Qt版本为5.13