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<ROW_NUM; i++)
        {
            for(int j=0; j<COL_NUM; 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<QList<QVariant>>datas;
    for(int i=0;i<ROW_NUM;i++)
    {

        QList<QVariant> rows;
        switch (i%32)//确定是第几行
        {
            case 0 :
                rows.append(day[d++]);
                for(int k=0;k<COL_NUM*3-1;k++)
                {

                    //该方法必须输入整个矩形区域，空的地方不输入最后写入结果会异常
                    rows.append("");

                }
            break; //  确定是第几篇报文
            case 1 :
                for(int k=0;k<COL_NUM;k++)
                {

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

                }
            break;


            default:
                for(int k=0;k<COL_NUM;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<list<qvariant>>到qvariant的转换过程
     
     * 待写入区域内，每行存为一个QList<QVariant>,         
     * tjgcQList<QVariant> row1,row2,row3;
     
     * 将QList<QVariant> 转换为QVariant类型，
     * QVariant r1(row1),r2(row2),r3(row3);
     
     * 整个写入区域当作一个QList<QVariant>,存入上述QVariant类型r1,r2,r3
     * 得到QList<QVariant> r
     
     * 整个写入区域从QList<QVariant>转换为QVariant类型
     * QVariant v(r); *
    */


    //二维数组转一维
    QList<QVariant> 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);



}