项目需求导出大量excel数据,发现导出excel数据特别慢。原低效率代码
QString filepath=QFileDialog::getSaveFileName(this,tr("Save orbit"),".",tr("Microsoft Office 2007 (*.xlsx)"));//获取保存路径
if(!filepath.isEmpty()){
QAxObject *excel = new QAxObject(this);
excel->setControl("Excel.Application");//连接Excel控件
excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改,是否保存”的提示
QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
workbooks->dynamicCall("Add");//新建一个工作簿
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
QAxObject *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);//获取工作表集合的工作表1,即sheet1
QAxObject *cellA,*cellB,*cellC,*cellD;
//设置标题
int cellrow=1;
QString A="A"+QString::number(cellrow);//设置要操作的单元格,如A1
QString B="B"+QString::number(cellrow);
QString C="C"+QString::number(cellrow);
QString D="D"+QString::number(cellrow);
cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
cellC=worksheet->querySubObject("Range(QVariant, QVariant)",C);
cellD=worksheet->querySubObject("Range(QVariant, QVariant)",D);
cellA->dynamicCall("SetValue(const QVariant&)",QVariant("流水号"));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)",QVariant("用户名"));
cellC->dynamicCall("SetValue(const QVariant&)",QVariant("金额"));
cellD->dynamicCall("SetValue(const QVariant&)",QVariant("日期"));
cellrow++;
int rows=this->model->rowCount();
for(int i=0;i<rows;i++){
QString A="A"+QString::number(cellrow);//设置要操作的单元格,如A1
QString B="B"+QString::number(cellrow);
QString C="C"+QString::number(cellrow);
QString D="D"+QString::number(cellrow);
cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
cellC=worksheet->querySubObject("Range(QVariant, QVariant)",C);
cellD=worksheet->querySubObject("Range(QVariant, QVariant)",D);
cellA->dynamicCall("SetValue(const QVariant&)",QVariant(this->model->item(i,0)->data(Qt::DisplayRole).toString()));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)",QVariant(this->model->item(i,1)->data(Qt::DisplayRole).toString()));
cellC->dynamicCall("SetValue(const QVariant&)",QVariant(this->model->item(i,2)->data(Qt::DisplayRole).toString()));
cellD->dynamicCall("SetValue(const QVariant&)",QVariant(this->model->item(i,3)->data(Qt::DisplayRole).toString()));
cellrow++;
}
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filepath));//保存至filepath,注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\",不然一定保存不了。
workbook->dynamicCall("Close()");//关闭工作簿
excel->dynamicCall("Quit()");//关闭excel
delete excel;
excel=NULL;
}
return true;
导致效率低原因
频繁访问 excel 查找添加位置,读取8000条数据需要8分钟。
优化后代码
QMessageBox::information(NULL, QString::fromLocal8Bit("注意"), QString::fromLocal8Bit("保存excel时间较长请耐心等待约1分钟左右"), QMessageBox::Yes, QMessageBox::Yes);
QString filepath = QFileDialog::getSaveFileName(this, tr("Save log"), ".", tr("Microsoft Office 2013 (*.xlsx)"));//获取保存路径
if (!filepath.isEmpty()) {
QAxObject *excel = new QAxObject(this);
excel->setControl("Excel.Application");//连接Excel控件
excel->dynamicCall("SetVisible (bool Visible)", "false");//不显示窗体
excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改,是否保存”的提示
QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
workbooks->dynamicCall("Add");//新建一个工作簿
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
QAxObject *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
QAxObject *worksheet = worksheets->querySubObject("Item(int)", 1);//获取工作表集合的工作表1,即sheet1
QAxObject *cellA, *cellB, *cellC;
QList<QVariant> rowdata;
int rows = Ad0.length();
for (int i = 0;i < rows;i++) {
QList<QVariant> aline;
aline.append(QVariant(Ad2[i]));
aline.append(QVariant(Ad0[i]));
aline.append(QVariant(Ad1[i]));
QVariant conv(aline);
rowdata.append(conv);
}
QVariant d(rowdata);
QString A = "A" + QString::number(1) + ":" + "C" + QString::number(rows);
QAxObject *range = worksheet->querySubObject("Range(QString)", A);
range->setProperty("Value", d);
//设置标题
//int cellrow = 1;
//
//int rows = Ad0.length();
//for (int i = 0;i<rows;i++) {
// QString A = "A" + QString::number(cellrow) + ":" + "C" + QString::number(cellrow);//设置要操作的单元格,如A1
// QList<QVariant> aline;
// aline.append(QVariant(Ad2[i]));
// aline.append(QVariant(Ad0[i]));
// aline.append(QVariant(Ad1[i]));
// QAxObject *range = worksheet->querySubObject("Range(QString)", A);
// range->setProperty("Value", aline);//aline 是一个QList<QVaiant>变量
// cellrow++;
//}
//int rows = Ad0.length();
//for (int i = 0;i<rows;i++) {
// QString A = "A" + QString::number(cellrow);//设置要操作的单元格,如A1
// QString B = "B" + QString::number(cellrow);
// QString C = "C" + QString::number(cellrow);
// cellA = worksheet->querySubObject("Range(QVariant, QVariant)", A);//获取单元格
// cellB = worksheet->querySubObject("Range(QVariant, QVariant)", B);
// cellC = worksheet->querySubObject("Range(QVariant, QVariant)", C);
// cellA->dynamicCall("SetValue(const QVariant&)", QVariant(Ad2[i]));//设置单元格的值
// cellB->dynamicCall("SetValue(const QVariant&)", QVariant(Ad0[i]));
// cellC->dynamicCall("SetValue(const QVariant&)", QVariant(Ad1[i]));
// cellrow++;
//}
workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(filepath));//保存至filepath,注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\",不然一定保存不了。
workbook->dynamicCall("Close()");//关闭工作簿
excel->dynamicCall("Quit()");//关闭excel
delete excel;
excel = NULL;
}
优化后导出时间在一分钟以内。优化部分为 先将数据存为二维的Q变量数组,然后依次写入excel。