Qt Excel

在pro文件添加

 QT +=axcontainer

头文件

 #include <QAxObject>

void MainWindow::on_btnSelectFileDialog_clicked()
{
    QString fileName=QFileDialog::getOpenFileName(this,"select Excel",excelDir,tr("Excel Files (*.xlsx *.xls)"));
    if(fileName.lastIndexOf("/")>0)
    {
        excelDir=fileName.mid(0,fileName.lastIndexOf("/")+1);
        excelNewName=fileName.mid(fileName.lastIndexOf("/")+1);
        excelNewName=excelNewName.mid(0,excelNewName.lastIndexOf("."))+"_new"+excelNewName.mid(excelNewName.lastIndexOf("."));

    }
    ui->leSourceExcel->setText(fileName);

}

QList<QString> tempData;
void MainWindow::on_btnGenerate_clicked()
{

    //读取excel
    QAxObject *myExcel=new QAxObject("Excel.Application");
    if(!myExcel){

        QMessageBox::critical(this, "错误信息", "EXCEL对象丢失");
        return;

    }
    myExcel->dynamicCall("SetVisible(bool)",false);
    QAxObject *workBooks=myExcel->querySubObject("WorkBooks");
    QAxObject *workBook=workBooks->querySubObject("Open(QString,QVariant",ui->leSourceExcel->text());
    QAxObject * workSheet = workBook->querySubObject("WorkSheets(int)", 1);//打开第一个sheet

    //QAxObject * worksheet = workbook->querySubObject("WorkSheets");//获取sheets的集合指针
    //int intCount = worksheet->property("Count").toInt();//获取sheets的数量

    QAxObject * usedRange = workSheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
    QAxObject * rows = usedRange->querySubObject("Rows");
    QAxObject * columns = usedRange->querySubObject("Columns");

    //获取行数和列数
    int intRowStart = usedRange->property("Row").toInt();
    int intColStart = usedRange->property("Column").toInt();
    int intCols = columns->property("Count").toInt();
    int intRows = rows->property("Count").toInt();

   //获取excel内容
    QString rowData;
    tempData.clear();
    for (int i = intRowStart; i < intRowStart + intRows; i++)  //行
    {
        rowData="";
        for (int j = intColStart; j < intColStart + intCols; j++)  //列
        {
            QAxObject * cell = workSheet->querySubObject("Cells(int,int)", i, j );  //获取单元格

            qDebug() << i << j <<cell->dynamicCall("Value2()").toString(); //正确
            rowData+=cell->dynamicCall("Value2()").toString()+",";
        }
        tempData.append(rowData);

    }
    workBook->dynamicCall("Close (Boolean)", false);
    myExcel->dynamicCall("Quit(void)");
    delete myExcel;//一定要记得删除，要不线程中会一直打开excel.exe
    myExcel=NULL;

}

void MainWindow::saveExcel()
{

    QAxObject *myExcel=new QAxObject("Excel.Application");
    if(!myExcel){

        QMessageBox::critical(this, "错误信息", "EXCEL对象丢失");
        return;

    }
    myExcel->dynamicCall("SetVisible(bool)",false);//不显示窗体
    myExcel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    QAxObject *workBooks=myExcel->querySubObject("WorkBooks");
    workBooks->dynamicCall("Add");//新建一个工作簿
    QAxObject *workBook = myExcel->querySubObject("ActiveWorkBook");//获取当前工作簿
    QAxObject *workSheets = workBook->querySubObject("Sheets");//获取工作表集合
    QAxObject *workSheet = workSheets->querySubObject("Item(int)",1);//获取工作表集合的工作表1，即sheet1

   for(int i=0;i<tempData.count();i++){

        QStringList rowData=tempData[i].split(",");
        QAxObject *cellA=workSheet->querySubObject("Range(QVariant, QVariant)","A"+QString::number(i+1));
        cellA->dynamicCall("SetValue(const QVariant&)",QVariant(rowData[0]));

        QAxObject *cellB=workSheet->querySubObject("Range(QVariant, QVariant)","B"+QString::number(i+1));
        cellB->dynamicCall("SetValue(const QVariant&)",QVariant(rowData[1]));

   }
   QString filePath=excelDir+excelNewName;
   qDebug()<<filePath;
   //保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为""，不然一定保存不了。
   workBook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filePath));

   workBook->dynamicCall("Close (Boolean)", false);
   myExcel->dynamicCall("Quit(void)");
   delete myExcel;
   myExcel=NULL;
   tempData.clear();


}


void MainWindow::on_pushButton_clicked()
{
    saveExcel();
}