stockWolf/excel/xexcel.cpp

#include "xexcel.h"
#include <QAxObject>
#include <QDebug>

XExcel::XExcel(QObject *parent) : QObject(parent)
{
    m_pExcel = nullptr;
    m_pWorkbooks = nullptr;
}
//打开excel com
void XExcel::openExcelCom()
{
    if(m_pExcel == nullptr)
    {
        m_pExcel = new QAxObject(this);
        m_pExcel->setControl("Excel.Application");//连接Excel控件
        qDebug() << "after set control.";
        m_pExcel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
        qDebug() << "after set SetVisible.";
        m_pExcel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
        qDebug() << "after set DisplayAlerts.";
        m_pWorkbooks = m_pExcel->querySubObject("WorkBooks");
    }
}

//关闭excel com
void XExcel::closeExcelCom()
{
    if(m_pExcel)
    {
        m_pExcel->dynamicCall("Quit(void)");
        m_pWorkbooks->deleteLater();
        m_pExcel->deleteLater();
        m_pWorkbooks = nullptr;
        m_pExcel = nullptr;
    }
}

QList<QList<QVariant> >  XExcel::readExcel(QString fileName)
{
    QList<QList<QVariant> > res;
    if(m_pExcel == nullptr || m_pWorkbooks == nullptr)
        return res;
    QAxObject *workbook = m_pWorkbooks->querySubObject("Open (const QString &)", fileName);//获取当前工作簿
    qDebug() << "after workbook.";
    QAxObject *worksheet = workbook->querySubObject("Sheets(int)",1);//工作表1，即sheet1
    qDebug() << "after Sheets.";

    QVariant var;
    if (worksheet != NULL && ! worksheet->isNull())
    {
        QAxObject *usedRange = worksheet->querySubObject("UsedRange");
        qDebug() << "after UsedRange.";
        if(NULL == usedRange || usedRange->isNull())
        {
            qDebug() << "usedRange is null.";
        }
        var = usedRange->dynamicCall("Value");
        delete usedRange;
    }
    workbook->dynamicCall("Close(Boolean)", false);
//    excel->dynamicCall("Quit(void)");
    //转list

    QVariantList varRows = var.toList();
    if(varRows.isEmpty())
    {
        qDebug() << "varRows is empty.";
        return res;
    }
    const int rowCount = varRows.size();
    qDebug() << "rows: " << rowCount;
    QVariantList rowData;
    for(int i = 0; i < rowCount; ++i)
    {
        rowData = varRows[i].toList();
        res.push_back(rowData);
//        qDebug() << "row index: " << i << " column count: " << rowData.count();
//        for(int c = 0 ; c < rowData.count() ; c++)
//        {
//            QVariant value = rowData[c];
//            qDebug()<< "row : " << i << " column : " << c << "  value : " << value.toString();
//        }
    }
    return res;

//    qDebug() << " excel file : " << fileName;
//    QAxObject* excel = new QAxObject(this);
//    excel->setControl("Excel.Application");//连接Excel控件
//    qDebug() << "after set control.";
//    excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
//    qDebug() << "after set SetVisible.";
//    excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
//    qDebug() << "after set DisplayAlerts.";
//    QAxObject *workbooks = excel->querySubObject("WorkBooks");
//    qDebug() << "after WorkBooks.";
//    QAxObject *workbook = workbooks->querySubObject("Open (const QString &)", fileName);//获取当前工作簿
//    qDebug() << "after workbook.";
//    QAxObject *worksheet = workbook->querySubObject("Sheets(int)",1);//工作表1，即sheet1
//    qDebug() << "after Sheets.";

//    QVariant var;
//    if (worksheet != NULL && ! worksheet->isNull())
//    {
//        QAxObject *usedRange = worksheet->querySubObject("UsedRange");
//        qDebug() << "after UsedRange.";
//        if(NULL == usedRange || usedRange->isNull())
//        {
//            qDebug() << "usedRange is null.";
//        }
//        var = usedRange->dynamicCall("Value");
//        delete usedRange;
//    }
//    workbook->dynamicCall("Close(Boolean)", false);
//    excel->dynamicCall("Quit(void)");
//    //转list
//    QList<QList<QVariant> > res;
//    QVariantList varRows = var.toList();
//    if(varRows.isEmpty())
//    {
//        qDebug() << "varRows is empty.";
//        return res;
//    }
//    const int rowCount = varRows.size();
//    qDebug() << "rows: " << rowCount;
//    QVariantList rowData;
//    for(int i = 0; i < rowCount; ++i)
//    {
//        rowData = varRows[i].toList();
//        res.push_back(rowData);
//        qDebug() << "row index: " << i << " column count: " << rowData.count();
//        for(int c = 0 ; c < rowData.count() ; c++)
//        {
//            QVariant value = rowData[c];
//            qDebug()<< "row : " << i << " column : " << c << "  value : " << value.toString();
//        }
//    }
//    return res;
}

void XExcel::readExcels(QList<QString> fileNames)
{
    qDebug() << " excel files : " << fileNames;
    QAxObject* excel = new QAxObject(this);
    excel->setControl("Excel.Application");//连接Excel控件
    qDebug() << "after set control.";
    excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
    qDebug() << "after set SetVisible.";
    excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    qDebug() << "after set DisplayAlerts.";
    QAxObject *workbooks = excel->querySubObject("WorkBooks");
    qDebug() << "after WorkBooks.";
    int fileCount = fileNames.count();
    for(int i = 0 ;  i < fileCount ; i++)
    {
        QString fileName = fileNames.at(i);
        QAxObject *workbook = workbooks->querySubObject("Open (const QString &)", fileName);//获取当前工作簿
        qDebug() << "after workbook.";
        QAxObject *worksheet = workbook->querySubObject("Sheets(int)",1);//工作表1，即sheet1
        qDebug() << "after Sheets.";

        QVariant var;
        if (worksheet != NULL && ! worksheet->isNull())
        {
            QAxObject *usedRange = worksheet->querySubObject("UsedRange");
            qDebug() << "after UsedRange.";
            if(NULL == usedRange || usedRange->isNull())
            {
                qDebug() << "usedRange is null.";
            }
            var = usedRange->dynamicCall("Value");
            delete usedRange;
        }
        workbook->dynamicCall("Close(Boolean)", false);

        //转list
         QList<QList<QVariant> > res;
        QVariantList varRows = var.toList();
        if(varRows.isEmpty())
        {
            qDebug() << "varRows is empty.";
            return;
        }
        const int rowCount = varRows.size();
        qDebug() << "rows: " << rowCount;
        QVariantList rowData;
        for(int i = 0; i < rowCount; ++i)
        {
            rowData = varRows[i].toList();
            res.push_back(rowData);
            qDebug() << "row index: " << i << " column count: " << rowData.count();
        }
    }
    excel->dynamicCall("Quit(void)");
}