一、环境介绍

操作系统介绍：win10 64位

QT版本: 5.12.6

二、功能介绍

将QTableWidget表格编辑的数据写入到本地execl表格文件中保存。

  

   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Header: #include <QAxObject> 
     
    
   

    

     

    
    

     

      qmake:  QT += axcontainer
     
    
  
 

三、示例代码

  

   

    

     

    
    

     

      /*
     
    
   

    

     

    
    

     

      日期: 2020-12-23
     
    
   

    

     

    
    

     

      作者: DS小龙哥
     
    
   

    

     

    
    

     

      环境: win10 QT5.12.6 MinGW32
     
    
   

    

     

    
    

     

      功能: 导出数据到execl表格
     
    
   

    

     

    
    

     

      */
     
    
   

    

     

    
    

     

      void Widget::SaveExeclData()
     
    
   

    

     

    
    

     

      {
     
    
   

    

     

    
    

     
    int value;
     
    
   

    

     

    
    

     

          value=QMessageBox::question(this,"提示","确定导出表格数据到Execl?",
     
    
   

    

     

    
    

     

          QMessageBox::Yes | QMessageBox::No,QMessageBox::Yes);
     
    
   

    

     

    
    

     
    if(value==QMessageBox::No)return;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
    //获取保存的文件路径
     
    
   

    

     

    
    

     

          QString filepath = QFileDialog::getSaveFileName(this, "选择保存文件名称",
     
    
   

    

     

    
    

     

          QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),
     
    
   

    

     

    
    

     
                        "Excel 文件(*.xls *.xlsx)");
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
    qDebug()<<"filepath:"<<filepath;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
    if(filepath.isEmpty())
     
    
   

    

     

    
    

     

          {
     
    
   

    

     

    
    

     

              QMessageBox::critical(this,"提示","未选择正确的文件.导出失败.",
     
    
   

    

     

    
    

     

              QMessageBox::Ok,QMessageBox::Ok);
     
    
   

    

     

    
    

     
        return;
     
    
   

    

     

    
    

     

          }
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

          QAxObject *excel = new QAxObject(this);
     
    
   

    

     

    
    

     
    if(excel->setControl("Excel.Application"))
     
    
   

    

     

    
    

     

          {
     
    
   

    

     

    
    

     

              excel->dynamicCall("SetVisible (bool Visible)","false");
     
    
   

    

     

    
    

     

              excel->setProperty("DisplayAlerts", false);
     
    
   

    

     

    
    

     

              QAxObject *workbooks = excel->querySubObject("WorkBooks");
     
    
   

    

     

    
    

     

              workbooks->dynamicCall("Add");
     
    
   

    

     

    
    

     

              QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
     
    
   

    

     

    
    

     

              QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
     
    
   

    

     

    
    

     

              QTableWidget *table=ui->tableWidget_Measure;
     
    
   

    

     

    
    

     
        int i,j,colcount=table->columnCount(),rowcount=table->rowCount();
     
    
   

    

     

    
    

     

              QAxObject *cell,*col;
     
    
   

    

     

    
    

     

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

    

     

    
    

     

              cell->dynamicCall("SetValue(const QString&)", "title");
     
    
   

    

     

    
    

     

              cell->querySubObject("Font")->setProperty("Size", 18);
     
    
   

    

     

    
    

     

              worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 60);
     
    
   

    

     

    
    

     

              QString cellTitle;
     
    
   

    

     

    
    

     

              cellTitle.append("A1:");
     
    
   

    

     

    
    

     

              cellTitle.append(QChar(colcount  + 'A'));
     
    
   

    

     

    
    

     

              cellTitle.append(QString::number(1));
     
    
   

    

     

    
    

     

              QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
     
    
   

    

     

    
    

     

              range->setProperty("WrapText", true);
     
    
   

    

     

    
    

     

              range->setProperty("MergeCells", true);
     
    
   

    

     

    
    

     

              range->setProperty("HorizontalAlignment", -4108);
     
    
   

    

     

    
    

     

              range->setProperty("VerticalAlignment", -4108);
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
        //行的表头
     
    
   

    

     

    
    

     
        for(i=0;i<colcount;i++)
     
    
   

    

     

    
    

     

              {
     
    
   

    

     

    
    

     

                  QString columnName;
     
    
   

    

     

    
    

     

                  cell=worksheet->querySubObject("Cells(int,int)", 2, i+2);
     
    
   

    

     

    
    

     

                  columnName=table->horizontalHeaderItem(i)->text();
     
    
   

    

     

    
    

     

                  cell->dynamicCall("SetValue(const QString&)", columnName);//打印到excel
     
    
   

    

     

    
    

     

                  cell->querySubObject("Font")->setProperty("Bold", true);
     
    
   

    

     

    
    

     

                  cell->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
     
    
   

    

     

    
    

     

                  cell->setProperty("HorizontalAlignment", -4108);
     
    
   

    

     

    
    

     

                  cell->setProperty("VerticalAlignment", -4108);
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
        //列的表头
     
    
   

    

     

    
    

     
        for(i=0;i<rowcount;i++)
     
    
   

    

     

    
    

     

              {
     
    
   

    

     

    
    

     
            //读取
     
    
   

    

     

    
    

     

                  QString rowName;
     
    
   

    

     

    
    

     
            //修改内容
     
    
   

    

     

    
    

     

                  cell=worksheet->querySubObject("Cells(int,int)", i+3, 1);
     
    
   

    

     

    
    

     
            //获取垂直表头
     
    
   

    

     

    
    

     
            // rowName=table->verticalHeaderItem(i)->text();
     
    
   

    

     

    
    

     
            //horizontalHeaderItem(i)->text();//获取此处的文本内容，i是列号，就是第几列中的文本内容
     
    
   

    

     

    
    

     
            //写表头数据
     
    
   

    

     

    
    

     

                  cell->dynamicCall("SetValue(const QString&)",i);
     
    
   

    

     

    
    

     

                  cell->querySubObject("Font")->setProperty("Bold", true);
     
    
   

    

     

    
    

     

                  cell->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
     
    
   

    

     

    
    

     

                  cell->setProperty("HorizontalAlignment", -4108);
     
    
   

    

     

    
    

     

                  cell->setProperty("VerticalAlignment", -4108);
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     
		
     
    
   

    

     

    
    

     
        //数据
     
    
   

    

     

    
    

     
        for(i=0;i<table->rowCount();i++)
     
    
   

    

     

    
    

     

              {
     
    
   

    

     

    
    

     
            for (j=0;j<colcount;j++)
     
    
   

    

     

    
    

     

                  {
     
    
   

    

     

    
    

     

                      QString rowdata;
     
    
   

    

     

    
    

     

                      rowdata=table->item(i,j)->text();
     
    
   

    

     

    
    

     

                      worksheet->querySubObject("Cells(int,int)", i+3, j+2)->dynamicCall("SetValue(const QString&)",rowdata);
     
    
   

    

     

    
    

     

                  }
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     
		
     
    
   

    

     

    
    

     

              QString lrange;
     
    
   

    

     

    
    

     

              lrange.append("A2:");
     
    
   

    

     

    
    

     

              lrange.append(colcount + 'A');
     
    
   

    

     

    
    

     

              lrange.append(QString::number(table->rowCount() +2));//终止行
     
    
   

    

     

    
    

     

              range = worksheet->querySubObject("Range(const QString&)", lrange);
     
    
   

    

     

    
    

     

              range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
     
    
   

    

     

    
    

     

              range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
     
    
   

    

     

    
    

     

              QString rowsName;
     
    
   

    

     

    
    

     

              rowsName.append("A2:");
     
    
   

    

     

    
    

     

              rowsName.append(colcount + 'A');
     
    
   

    

     

    
    

     

              rowsName.append(QString::number(table->rowCount() + 2));
     
    
   

    

     

    
    

     

              range = worksheet->querySubObject("Range(const QString&)", rowsName);
     
    
   

    

     

    
    

     

              range->setProperty("RowHeight", 20);
     
    
   

    

     

    
    

     

              range->setProperty("ColumnWidth", 60);
     
    
   

    

     

    
    

     

              workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filepath));
     
    
   

    

     

    
    

     

              workbook->dynamicCall("Close()");
     
    
   

    

     

    
    

     

              excel->dynamicCall("Quit()");
     
    
   

    

     

    
    

     
        delete excel;
     
    
   

    

     

    
    

     

              excel=nullptr;
     
    
   

    

     

    
    

     
        if(QMessageBox::question(nullptr,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
     
    
   

    

     

    
    

     

              {
     
    
   

    

     

    
    

     

                  QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(filepath)));
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     

          }
     
    
   

    

     

    
    

     
    else
     
    
   

    

     

    
    

     

          {
     
    
   

    

     

    
    

     

              QMessageBox::warning(nullptr,"错误","未能创建 Excel 对象，请安装 Microsoft Excel。",QMessageBox::Apply);
     
    
   

    

     

    
    

     

          }
     
    
   

    

     

    
    

     

      }
     
    
  
 

文章来源: xiaolong.blog.csdn.net，作者：DS小龙哥，版权归原作者所有，如需转载，请联系作者。

原文链接：xiaolong.blog.csdn.net/article/details/111571781