Qt 写Excl文件

参考资料：https://blog.csdn.net/czyt1988/article/details/52121360

 

                 https://blog.csdn.net/qq_35057840/article/details/54318443

 

                https://www.cnblogs.com/techiel/p/7932329.html

 

需要在.pro文件中添加 QT += axcontainer

然后自己尝试写的代码如下：

#include "exclhelper.h"
#include <QFile>
#include <QDebug>
#include <QDir>

ExclHelper::ExclHelper(QObject *parent) : QObject(parent)
{
    mAxObj = NULL;
    mData.clear();
}

void ExclHelper::ExportExcl(QString fileName)
{
    mSaveName = fileName;
    mAxObj = new QAxObject();
    // 连接Excel控件
    mAxObj->setControl("Excel.Application");
    // 不显示窗体
    mAxObj->dynamicCall("SetVisible (bool Visible)", "false");
    // 不显示任何警告信息。如果为true, 那么关闭时会出现类似"文件已修改，是否保存"的提示
    mAxObj->setProperty("DisplayAlerts", false);

}

void ExclHelper::AddRowData(QList<QVariant> row)
{
    mData.append(row);
}

void ExclHelper::Finish()
{
   if(mAxObj == NULL ||  mData.size() <= 0)
   {
       return;
   }

   // 获取工作簿集合
   QAxObject* workbooks = mAxObj->querySubObject("WorkBooks");
   // 新建一个工作簿
   workbooks->dynamicCall("Add");
   QAxObject* workbook = mAxObj->querySubObject("ActiveWorkBook");
   // step3: 打开sheet
   QAxObject* worksheet = workbook->querySubObject("WorkSheets(int)", 1);

   int row = mData.size();
   int col = mData.at(0).size();
   QString rangStr;
   convertToColName(col,rangStr);
   rangStr += QString::number(row);
   rangStr = "A1:" + rangStr;

   qDebug()<<rangStr;
   QAxObject *range = worksheet->querySubObject("Range(const QString&)",rangStr);
   if(NULL == range || range->isNull())
   {
       return;
   }

   QVariant var;
   castListListVariant2Variant(mData,var);
   range->setProperty("WrapText", true);
   //range->setProperty("Value", var);  //此方法写不进去
   range->dynamicCall("SetValue(conts QVariant&)", var);

   workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(mSaveName)); //保存到filepath
   // 注意一定要用QDir::toNativeSeparators, 将路径中的"/"转换为"\", 不然一定保存不了
   workbook->dynamicCall("Close (Boolean)", false);  //关闭文件
   mAxObj->dynamicCall("Quit (void)");
   delete range;
   delete worksheet;
   delete workbook;
   delete mAxObj;
   mAxObj = NULL;
}

void ExclHelper::convertToColName(int col, QString &str)
{
    Q_ASSERT(col>0 && col<65535);
    int tempData = col / 26;
    if(tempData > 0)
    {
        int mode = col % 26;
        convertToColName(mode,str);
        convertToColName(tempData,str);
    }
    else
    {
        str=(to26AlphabetString(col)+str);
    }

}

QString ExclHelper::to26AlphabetString(int col)
{
    QChar ch = col + 0x40;//A对应0x41
    return QString(ch);

}

void ExclHelper::castListListVariant2Variant(const QList<QList<QVariant> > &res, QVariant &var)
{

    QVariantList mAllData;
    for(int i = 0; i< res.size();i++)
    {
        QVariantList mRowData;
        auto a = res.at(i);
        mRowData.append(a);
        mAllData.append((QVariant)mRowData);
    }
    var = (QVariant)mAllData;
}