nodejs+exceljs百万数据导出

整体思路：分页查询，利用首次查询的数据创建excel文件，将后续查询的数据添加到excel中

代码示例：

//每次分页的limit
const base = 10000;
//查询第一批数据，写入excel文件，并传递文件名称
let searchSourceArray = getSourceNameConditions(countObject, sourceNames, base,0);
return searchPromise(searchSourceArray,params,order,countObject,limit,offset).then((result)=>{
    return createDownloadSearch(sourceName,result);
}).then((data)=>{
    //获取excel地址
    dataAll=data;
    filename=data.filename;
    fileUrl=path.join(config.bizUploadTemplateOptions.uploadDir,data.url);
    //创建每个Promise.each的分页条件
    const division=searchCount/base;

    const round=Math.floor(division); //取整数

    const pages=[];
    for(let i=0;i<searchCount;i=i+base){
        if(i==0||i==base){
            continue;
        }
        pages.push({offset:i-base,limit:base});
    }

    if(division>Math.floor(division)){
        pages.push({offset:base*round,limit: searchCount-Math.floor(division)*base})
    }else{
        pages.push({offset:searchCount-base,limit:base});
    }
    //按分页条件查询数据，并更新到excel中
    return Promise.each(pages,page=>{
        searchSourceArray = getSourceNameConditions(countObject, sourceNames, page.limit,page.offset);
        return searchPromise(searchSourceArray,params,order,countObject,limit,offset).then((result)=>{
            //将查询到的数据更新到excel
            return readExcel(result,fileUrl);
        });
    });
}).then(()=>{
    return {filename:dataAll.filename,url: path.join(config.bizUploadTemplateOptions.baseUrl,dataAll.url)}
});

/**
 * 读取文件后，追加行，生成新文件
 * @param data    要追加的数据
 * @param filePath    文件地址
 */
let readExcel = function (result, filePath) {
    let workbook = new ExcelJS.Workbook();
    return workbook.xlsx.readFile(filePath).then(
        function () {
            //核心方法
            dealExcel(workbook, result);
            return workbook.xlsx.writeFile(filePath);
        });
};

function dealExcel(workbook, result) {
    let worksheet = workbook.getWorksheet(1);
    let lastRow = worksheet.lastRow;
    let num = lastRow.number;
    result.forEach((data)=>{
        num=num+1;
        let getRowInsert = worksheet.getRow(num);
        let number = 0;
        for(let v in data){
            number=number+1;
            getRowInsert.getCell(number).value=data[v];
        }
        getRowInsert.commit();
    });
}