PHP配合SheetJS/js-xlsx导出Excel大量数据

  • 时间:
  • 分类: 开发

一般使用PHP导出Excel表格都会用PHPExcel，但是当遇到要导出大量数据时，就会导致超时，内存溢出等问题。因此在项目中放弃使用这种方式，决定采用前段生成Excel的方式来解决问题

步骤如下:

  1. 前端ajax先请求一次后端，获取总的数据个数，假设有1000条
  2. 然后前端分10次，每次100条ajax请求后台数据，
  3. 10次ajax都请求成功后（这里使用Promise.all()来处理多次异步请求），将数据合并在一个数组里
  4. 使用 SheetJS/js-xlsx 生成Excel文件并下载

这种方案额外的好处:

  1. 用户体验友好，前端可以用进度条来展示10次请求的百分比
  2. 后台不用额外提供导出Excel的接口，只需要使用查询数据的接口，因为一般查询数据的接口都会有：结果里包含总个数字段、分页查询功能，因此前端只需按上述逻辑调用数据查询接口合并数据即可

效果:

Untitled.gif

示例代码:

https://github.com/hegoku/php-sheetJs-excel

PHP接口代码,这里数据随机生成，实际情况应该是调用数据库获取:

$data=[];

for ($i=1;$i<=100;$i++) {  //随机生成数据
    $tmp=[
        'id'=>($_GET['page']-1)*100+$i,
        'name'=>chr(mt_rand(33, 126)).chr(mt_rand(33, 59)).chr(mt_rand(33, 126)).chr(mt_rand(33, 126)) //生成随机名字
    ];
    array_push($data, $tmp);
}

sleep(2); //模拟数据库耗时

echo json_encode([
    'code'=>200,
    'pagination'=>['count'=>1000],
    'data'=>$data
]);

接口返回的json格式为：

{
    "code": 200,
    "pagination": {
        "count": 1000
    },
    "data": [
        {
            "id": 101,
            "name": "$5yG"
        },
        {
            "id": 102,
            "name": "g'zP"
        },
        {
            "id": 103,
            "name": "2!$`"
        },
        {
            "id": 104,
            "name": "a&2j"
        },
        {
            "id": 105,
            "name": "[/N+"
        },
        ...
    ]
}

前端代码，这里用了Vue作为前端模板:

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8"/>
        <link rel="stylesheet" href="css/bootstrap.min.css">
        <script src="js/vue.min.js"></script>
        <script src="js/vue-resource.min.js"></script>
        <script src="js/jquery-2.0.3.min.js"></script>
        <script src="js/bootstrap.min.js"></script>
        <!-- SheetJS js-xlsx library -->
        <script src="js/shim.min.js"></script>
        <script src="js/xlsx.full.min.js"></script>
        <!-- FileSaver.js is the library of choice for Chrome -->
        <script src="js/Blob.js"></script>
        <script src="js/FileSaver.js"></script>
        <!-- FileSaver doesn't work in older IE and newer Safari; Downloadify is the flash fallback -->
        <script src="js/swfobject.js"></script>
        <script src="js/downloadify.min.js"></script>
    </head>
    <body>
        <div id="app">
            <button ref="export_btn" class="btn btn-primary" style="float:none;margin-left:0;" type="button" @click="exportExcel">
                <span v-show="export_percentage==-1">下载报表</span>
                <span v-show="export_percentage!=-1">导出中:{{export_percentage}}%</span>
            </button>
            <div v-show="export_percentage!=-1" class="progress" style="margin:10px;">
                <div class="progress-bar progress-bar-striped active" role="progressbar" aria-valuemin="0" aria-valuemax="100" :style="{width:export_percentage+'%'}"></div>
            </div>
        </div>
    </body>

    <script>
    var app=new Vue({
        el: '#app',
        data: function(){
            return {
                export_percentage:-1,
                count:10000
            }
        },
        methods: {
            exportExcel: function(){
                var self=this;
                var request_times=Math.ceil(this.count/100); //计算分几次请求
                var funcs=[]; //Promise.all要用到的参数, 存放每次请求的Promise对象
                var complete_count=0; //成功请求次数
                this.export_percentage=0; //设置当前进度百分比为0

                for (var i=1; i<=request_times; i++) { // 循环请求次数，构造请求的Promise对象并插入funcs数组
                    var func=new Promise(function(resolve, reject){ //定义Promise并处理请求逻辑
                        var data=[];
                        self.$http.get(
                            'a.php',
                            { params: {page:i} }
                        ).then(function(response){
                            if (response.body.code==200) {
                                for(var i=0; i<response.body.data.length; i++){ //将后台返回中需要的字段取出
                                    var item=[];
                                    item.push(response.body.data[i].id);
                                    item.push(response.body.data[i].name);
                                    data.push(item);
                                }
                                complete_count++; //成功请求次数+1
                                self.export_percentage=100*complete_count/request_times; //设置当前进度百分比
                                resolve(data);
                            } else {
                                reject();
                            }
                        });
                    });
                    funcs.push(func);
                }

                Promise.all(funcs).then(function(values){ //使用Promise.all调用funcs里面的函数，并合并数据，最后给js-xlsx生成Excel
                    var aoa=[
                            ['ID', '名称'] //第一行标题
                    ];
                    //将数据合并
                    for (var i=0; i<values.length; i++) {
                        for (var j=0; j<values[i].length; j++) {
                            aoa.push(values[i][j]);
                        }
                    }
                    var ws = XLSX.utils.aoa_to_sheet(aoa);
                    var wb=XLSX.utils.book_new();
                    wb.SheetNames.push('sheet1');
                    wb.Sheets['sheet1'] = ws;
                    var wopts = { bookType:'xlsx', bookSST:false, type:'array' };
                    var wbout = XLSX.write(wb,wopts);
                    saveAs(new Blob([wbout],{type:"application/octet-stream"}), "data.xlsx");

                    self.export_percentage=-1;
                });
            }
        }
    });
    </script>
</html>
标签: php, javascript
评论列表
  1. php 資料匯出 | 程式前沿

    [...]前端解決方案PHP配合SheetJS/js-xlsx匯出Excel大量資料[...]

    回复
  2. embryo embryo

    那个new Promise里的i 我赋值给一个对象中的参数，执行时i都是最后的数，也就是总共请求的数。
    这个问题在哪啊，我。。。
    remoteRequest.data.queryPage=i;
    console.log(remoteRequest.data.queryPage);
    这样打印没事，可最后都变成了总请求数结果数据都是最后的。

    回复
    1. Jack Jack

      是不是因为最后一个赋值把最后的i给过去了

      回复
  3. 不太对吧 不太对吧

    我用js-xlsx 试过6w条数据，也是多次取出，最后再在拼装excel单元格的时候，浏览器就崩溃了，这方法不太行。oh 对，我用的是xlsx-style，因为有特殊样式，感觉xlsx-style这个版本也没有js-xlsx优化的好，在很多地方特别冗余，运行缓慢。后来尝试了js-worker、js-zip、streamsaver等等，效果越来越好，最近空闲下来正准备优化一下xlsx-style的代码，同时将一部分逻辑放到服务端去运行.....目标最少10w数据前端导出excel不能卡顿啊。

    回复
  4. Guo Guo

    saveAs方法在哪？

    回复
  5. 清儿 清儿

    我下载csv,y用这个办法，超过8000条，就报网络错误了，不能下载了

    回复
  6. xuri xuri

    推荐一个功能丰富、兼容性好、高性能的 Excel 文档基础库 Excelize：https://github.com/xuri/excelize
    提供了流式 API 支持读写包含大规模数据的电子表格文档。

    回复
  7. 地藏 地藏

    这个插件太垃圾了 除了样式控制的多 稍微几万条数据就崩溃了 还是老老实实用假导出吧 几十上百万条都没问题 唯一的缺点就是只能火狐浏览器

    回复
添加新评论