Qt导出数据到EXCEL的两种方式

一、导出到csv格式，这种格式不用安装office

QString DModel::exportDataToCSV(QString lx)
{
       QString dtStr="d:/"+lx+"DbToExcel"+QDateTime::currentDateTime().toString("yyyyMMddHHmmss")+".csv";
       //以下是保存csv
       QFile file;
       file.setFileName(dtStr);
       if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
       {
           qDebug()<<"open file fail";
           return "";
       }
       QTextStream out(&file);  //创建一个文本流，向保存文件中写入文本

       //写表头
       out<<tr("编号")<<",";
       out<<tr("摄像头名称")<<",";
       out<<tr("检测点名称")<<",";
       out<<tr("通道")<<",";
       out<<tr("直")<<",";
       out<<tr("左")<<",";
       out<<tr("右")<<",";
       out<<tr("时间")<<",";
       out<<tr("计数")<<",";
       out<<tr("速度")<<",";
       out<<tr("车道占有率")<<",";
       out<<"\n";

       //设置内容
       for(int i=0;i<m_map.count();++i)
       {
           QMap<QString,QVariant> temp=m_map[i];
           out<<temp["Id"].toString()<<",";
           out<<temp["cameraName"].toString()<<",";
           out<<temp["checkPointName"].toString()<<",";
           out<<temp["channelId"].toString()<<",";
           out<<temp["containStraight"].toString()<<",";
           out<<temp["containLeft"].toString()<<",";
           out<<temp["containRight"].toString()<<",";
           out<<temp["countTime"].toString()+"\t"<<",";
           out<<temp["todayCount"].toString()<<",";
           out<<temp["averageSpeed"].toString()<<",";
           out<<temp["timeShare"].toString()<<",";
           out<<"\n";

       }

      file.close();
      return dtStr;
}

二、导出到excel格式，这种格式必须安装office,否则崩溃

QString DModel::exportData(QString lx)
{
  //流程：
  //1.新建一个excel
  //2.增加1个Worksheet
  //3.向Excel单元格中写入数据
  //4.保存Excel
  //5.释放Excel

  //注意事项：使用此功能时，每次都用新建文件的方式，否则会崩溃，即把原有文件删除，新建
    QString dtStr="d:/"+lx+"DbToExcel"+QDateTime::currentDateTime().toString("yyyyMMddHHmmss")+".xlsx";
    newExcel(dtStr);

  // appendSheet("车检数据",1);

  //新建数据的步骤：
  //1、标题
  //2、内容

  //设置标题
  setCellValue(1,1,"编号");
  setCellValue(1,2,"摄像头名称");
  setCellValue(1,3,"检测点名称");
  setCellValue(1,4,"通道");
  setCellValue(1,5,"直");
  setCellValue(1,6,"左");
  setCellValue(1,7,"右");
  setCellValue(1,8,"时间");
  setCellValue(1,9,"计数");
  setCellValue(1,10,"速度");
  setCellValue(1,11,"车道占有率");


  //设置内容
  for(int i=0;i<m_map.count();++i)
  {
      QMap<QString,QVariant> temp=m_map[i];

      setCellValue(i+2,1,temp["Id"].toString());
      setCellValue(i+2,2,temp["cameraName"].toString());
      setCellValue(i+2,3,temp["checkPointName"].toString());
      setCellValue(i+2,4,temp["channelId"].toString());
      setCellValue(i+2,5,temp["containStraight"].toString());
      setCellValue(i+2,6,temp["containLeft"].toString());
      setCellValue(i+2,7,temp["containRight"].toString());
      setCellValue(i+2,8,temp["countTime"].toString());
      setCellValue(i+2,9,temp["todayCount"].toString());
      setCellValue(i+2,10,temp["averageSpeed"].toString());
      setCellValue(i+2,11,temp["timeShare"].toString());
//      std::cout<<temp["Id"].toString().toStdString()<<temp["countTime"].toString().toStdString()<<std::endl;
  }
  saveExcel(dtStr);
  freeExcel();
  return dtStr;
}

void DModel::newExcel(const QString &fileName)
{
    pApplication = new QAxObject();
    pApplication->setControl("Excel.Application");//连接Excel控件
    pApplication->dynamicCall("SetVisible(bool)", false);//false不显示窗体
    pApplication->setProperty("DisplayAlerts", false);//不显示任何警告信息。
    pWorkBooks = pApplication->querySubObject("Workbooks");
    QFile file(fileName);
    file.remove();
//    if (file.exists())
//    {
//        pWorkBook = pWorkBooks->querySubObject("Open(const QString &)", fileName);
//    }
//    else
//    {
        pWorkBooks->dynamicCall("Add");//新建一个工作簿,如果不新建会崩溃
        pWorkBook = pApplication->querySubObject("ActiveWorkBook");//获取当前工作簿
//    }
    pSheets = pWorkBook->querySubObject("Sheets");//获取所有工作表sheet

    pSheet = pSheets->querySubObject("Item(int)", 1);//获取第一个工作表sheet，从1开始
}

void DModel::appendSheet(const QString &sheetName,int cnt)
{
    QAxObject *pLastSheet = pSheets->querySubObject("Item(int)", cnt);
    pSheets->querySubObject("Add(QVariant)", pLastSheet->asVariant());
    pSheet = pSheets->querySubObject("Item(int)", cnt);
    pLastSheet->dynamicCall("Move(QVariant)", pSheet->asVariant());
    pSheet->setProperty("Name", sheetName);
}

void DModel::setCellValue(int row, int column, const QString &value)
{   if(column!=8)
    {
       QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", row, column);
       pRange->dynamicCall("Value", value);
    }
    else
    {
        QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", row, column);
        pRange->dynamicCall("NumberFormatLocal","yyyy-mm-dd HH::mm:ss");//当为日期时间型数据时，按此格式设置
        pRange->dynamicCall("Value", value);
    }
}

void DModel::saveExcel(const QString &fileName)
{
    pWorkBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(fileName));
}

void  DModel::freeExcel()
{
    if (pApplication != NULL)
    {
        pApplication->dynamicCall("Quit()");
        delete pApplication;
        pApplication = NULL;
    }
}

秋风写于淄博，业务联系与技术交流：q375172665