一、导出到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