linux qt写入excel文件内容,Qt创建excel与快速写入的方法

用Qt5写了个生成excel数据的程序，发现生成数据速度很慢，

经查证，

在单元格很多的时候，按单元格写入很慢，按范围批量读写速度快很多，excel部分代码片段如下，

初学者代码不好，另外设置页面和单元格格式有更好具体意见建议者，欢迎留言指导

这段代码生成同一种格式的7页数据，最终结果如下：

void mainwindow::produce_excel()

{

//获取桌面路径，设置为表格的绝对路径

QString excel_file_path = QStandardPaths::writableLocation(QStandardPaths::DesktopLocation)

+ "/baodi.xlsx";

//把"/baodi.xlsx"中//替换成所在系统分隔符，否则路径读取会失败

excel_file_path = QDir::toNativeSeparators(excel_file_path);

QAxObject *excel = new QAxObject(this);

excel->setControl("Excel.Application"); //连接EXCEL控件

excel->setProperty("DisplayAlerts", true); //显示窗体

QAxObject *workbooks = excel->querySubObject("WorkBooks"); // 获取工作薄(EXCEL文件)集合

workbooks->dynamicCall("Add"); //创建新工作薄

//workbooks->dynamicCall("Open(const QString&)", excel_file_path);

QAxObject *workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作薄

workbook->dynamicCall("SaveAs(const QString&, int, const QString&, const QString&, bool,bool)",

excel_file_path, 51, QString(""),QString(""),false,false);//51xlsx,56xls

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

//根据序号获取EXCEL下方第int张工作表

/*

//按单元格写入，数据多时，速度很慢，真的很慢，十分不推荐，已经注释掉了，按范围写入在后面

QAxObject *usedRange = worksheet->querySubObject("UsedRange"); //sheet范围

int Row = usedRange->property("Row").toInt();// 获得起始行数

int Col = usedRange->property("Column").toInt();//获得起始列数

QAxObject *cell = worksheet->querySubObject("Cells(int,int)", Row, Col);

const int ROW_NUM {30};

const int COL_NUM {3};

const int HIGHT {26};

const int WIDE {10};

long t = 0;//用于选择时间time

for(auto d:day)

{

//输入表头

//auto t = time.begin();

cell->setProperty("Value", d);

cell->setProperty("RowHeight", HIGHT+9);//设置行高

//cell->setProperty("ColumnWidth", WIDE-4); //设置单元格列宽

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

//左对齐(xlLeft)：-4131 居中(xlCenter)：-4108 右对齐(xlRight)：-4152

cell = worksheet->querySubObject("Cells(int,int)", ++Row, Col);

cell->setProperty("Value", sn);

cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE-4); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

cell->setProperty("Value", time[t++%6]);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

//cell->setProperty("Value", sn);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

cell->setProperty("Value", sn);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE-4); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

cell->setProperty("Value", time[t++%6]);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

//cell->setProperty("Value", sn);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

cell->setProperty("Value", sn);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE-4); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, ++Col);

cell->setProperty("Value", time[t++%6]);

//cell->setProperty("RowHeight", HIGHT);

cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

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

Col = usedRange->property("Column").toInt();//获得起始列数

cell = worksheet->querySubObject("Cells(int,int)", ++Row, Col);

//输入数据

for(int i=0; i

{

for(int j=0; j

{

cell = worksheet->querySubObject("Cells(int,int)", Row, Col++);

cell->setProperty("Value", i+1);

cell->setProperty("RowHeight", HIGHT);

//cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, Col++);

cell->setProperty("Value", rand());

//cell->setProperty("RowHeight", HIGHT);

// cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

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

cell = worksheet->querySubObject("Cells(int,int)", Row, Col++);

cell->setProperty("Value", right_model(model));

//cell->setProperty("RowHeight", HIGHT);

//cell->setProperty("ColumnWidth", WIDE); //设置单元格列宽

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

}

Col = usedRange->property("Column").toInt();//获得起始列数

cell = worksheet->querySubObject("Cells(int,int)", ++Row, Col);

}

}

*/

//按范围写入，因为不用重复调用QAxObject，比按单元格写入的方式速度提升巨大

const int HIGHT{26};

const int ROW_NUM {224};

const int COL_NUM {3};

unsigned int d = 0;

unsigned int t = 0;

//以二维数组的形式存储预写入数据

QList>datas;

for(int i=0;i

{

QList rows;

switch (i%32)//确定是第几行

{

case 0 :

rows.append(day[d++]);

for(int k=0;k

{

//该方法必须输入整个矩形区域，空的地方不输入最后写入结果会异常

rows.append("");

}

break; // 确定是第几篇报文

case 1 :

for(int k=0;k

{

rows.append(sn);

rows.append(time[t++%6]);

rows.append("");

}

break;

default:

for(int k=0;k

{

rows.append(i%32? i%32-1 : 30);

rows.append(rand());

rows.append(right_model(model));

}

break;

}

datas.append(rows);

}

/*

*QVariant封装绝大多数Qt提供的数据类型，只要放入和取出类型对应即可，

* 相当于一个普遍的类型联合，

* canConvert可以查询是否能转换当前类型，转换类型以toT()命名

* 以下为类型list>到qvariant的转换过程

* 待写入区域内，每行存为一个QList,

* tjgcQList row1,row2,row3;

* 将QList 转换为QVariant类型，

* QVariant r1(row1),r2(row2),r3(row3);

* 整个写入区域当作一个QList,存入上述QVariant类型r1,r2,r3

* 得到QList r

* 整个写入区域从QList转换为QVariant类型

* QVariant v(r); *

*/

//二维数组转一维

QList vars;

for(auto v:datas)

{

vars.append(QVariant(v));

}

//一维数组转变量

QVariant var = QVariant(vars);

QAxObject *user_range = worksheet->querySubObject("Range(const QString&)", "A1:I224");//指定范围

user_range->setProperty("Value", var);//调用一次QAxObject即可完成写入

user_range->setProperty("RowHeight", HIGHT);//设置行高

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

//左对齐(xlLeft)：-4131 居中(xlCenter)：-4108 右对齐(xlRight)：-4152

workbook->dynamicCall("Save()"); //保存文件

workbook->dynamicCall("Close(Boolean)", false);

excel->dynamicCall("Quit(void)"); //EXE结束前需要关闭EXCEL

delete excel;

//wait->close();

//delete wait;

QMessageBox::information(this,tr("注意"),QStringLiteral("报底已保存在桌面"),QMessageBox::Ok);

}