Qt ------ excel 操作

 写 excel

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

    // step2: 打开工作簿
    QAxObject* workbooks = excel->querySubObject("WorkBooks"); // 获取工作簿集合
    workbooks->dynamicCall("Add"); // 新建一个工作簿
    QAxObject* workbook = excel->querySubObject("ActiveWorkBook"); // 获取当前工作簿

    // step3: 打开sheet
    QAxObject* worksheet = workbook->querySubObject("WorkSheets(int)", 1); // 获取工作表集合的工作表1， 即sheet1


    QAxObject* cell = worksheet->querySubObject("Cells(int, int)", 1, 1);

    cell->dynamicCall("SetValue(conts QVariant&)", QVariant(147852));

    workbook->dynamicCall("SaveAs(const QString&)",QDir::currentPath() + "/IMEI.xlsx");
    workbook->dynamicCall("Save()");  //保存文件
    workbook->dynamicCall("Close(Boolean)", false);  //关闭文件

    delete excel;

 

 

 读 excel

    QAxObject excel("Excel.Application");
    excel.setProperty("Visible", false); //隐藏打开的excel文件界面
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    QAxObject *workbook = workbooks->querySubObject("Open(QString, QVariant)", QDir::currentPath() + "/IMEI.xls"); //打开文件
    QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1); //访问第一个工作表
    QAxObject * usedrange = worksheet->querySubObject("UsedRange");
    QAxObject * rows = usedrange->querySubObject("Rows");
    int intRows = rows->property("Count").toInt(); //行数
    qDebug() << "使用了 " << intRows << " 行";
    
    QString Range = "A1:B" +QString::number(intRows);
    QAxObject *allEnvData = worksheet->querySubObject("Range(QString)", Range); //读取范围
    QVariant allEnvDataQVariant = allEnvData->property("Value");
    QVariantList allEnvDataList = allEnvDataQVariant.toList();

    for(int i=0; i< intRows; i++)
    {
        QVariantList allEnvDataList_i =  allEnvDataList[i].toList() ;
        QString data1 = allEnvDataList_i[0].toString(); //第i行第0列的数据
        qDebug() << data1;
    }
    workbooks->dynamicCall("Close()");
    excel.dynamicCall("Quit()");

 

如果出现以下情况，可能是所选的Excel控件不支持要打开的excel文件（.xls、.xlsx）

 

 

 

读写 excel

    QAxObject *excel = new QAxObject("Excel.Application");
    uint8_t flag = 0;
    excel->setProperty("Visible", false); //隐藏打开的excel文件界面
    QAxObject *workbooks = excel->querySubObject("WorkBooks");

    QFile file_xls(QDir::currentPath() + "/IMEI.xls");
    QAxObject *workbook;
    if(file_xls.exists())
    {
        qDebug() << "文件已存在";
        workbook = workbooks->querySubObject("Open(QString, QVariant)", QDir::currentPath() + "/IMEI.xls"); //打开文件
    }else
    {
        qDebug() << "文件不存在，创建一个";
        workbooks->dynamicCall("Add"); // 新建一个工作簿
        workbook = excel->querySubObject("ActiveWorkBook"); // 获取当前工作簿
    }



    QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1); //访问第一个工作表
    QAxObject * usedrange = worksheet->querySubObject("UsedRange");
    QAxObject * columns = usedrange->querySubObject("Columns");
    columns->dynamicCall("AutoFit");
    QAxObject * rows = usedrange->querySubObject("Rows");
    int intRows = rows->property("Count").toInt(); //行数，实际使用0行或者1行，这个值都是1
    QString Range = "A1:B" +QString::number(intRows);
    QAxObject *allEnvData = worksheet->querySubObject("Range(QString)", Range); //读取范围
    QVariant allEnvDataQVariant = allEnvData->property("Value");
    QVariantList allEnvDataList = allEnvDataQVariant.toList();

    QString data;
    for(int i=0; i< intRows; i++)
    {
        QVariantList allEnvDataList_i =  allEnvDataList[i].toList() ;
        data = allEnvDataList_i[0].toString(); //第i行第1列的数据
        if(data == IMEI_str)
        {
            flag = 1;
            qDebug() << "excel 内已含此 IMEI";
            break;
        }
        qDebug() << data;
    }
    if((data == "") && (intRows == 1))
    {
        intRows = intRows -1;
    }
    qDebug() << "使用了 " << intRows << " 行";
    if(flag == 0)
    {
        QAxObject* cell = worksheet->querySubObject("Cells(int, int)", intRows+1, 1);
        cell->setProperty("NumberFormatLocal", "@");  // 设置为文本
        cell->dynamicCall("SetValue(conts QVariant&)", QVariant(IMEI_str));
    }

    if(!file_xls.exists())
    {
        workbook->dynamicCall("SaveAs(const QString&)",QDir::currentPath() + "/IMEI.xls");
    }
    workbook->dynamicCall("Close(Boolean)", true);  //true为关闭前保存
    excel->dynamicCall("Quit()");
    delete excel;