sheetjs/js-xlsx前端数据导出为excel文件
在日常项目开发过程中经常会用到将数据导出excel，大部分项目是在后端实现，但是项目如果导出使用人数过多，后端生成excel会占用很大服务器资源，有时候数据量大可能会造成后端服务直接奔溃或者超时，鉴于以上问题和导出数据量大的需求，结合实际考虑将数据导出放在前端实现，数据量少可以一次性导出，但是如果数据量大的情况下，如几w条，甚至几十万条，如果一次性返回数据，后端和前端都有可能奔溃，所以采用分批查询后端，然后再合并数据导出的方案，开源社区有个插件SheetJS/js-xlsx就能实现前端的数据导出excel文件，以下大概为代码实现原理：使用插件，向后端查询有多少数据量需要导出，获取数据总数，再通过前端分批向后端查询，并合并数据，最后导出excel文件，代码如下：
案例实现：vue+sheetjs
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"/>
<link rel="stylesheet" href="/export/css/bootstrap.min.css">
<link href="/export/css/bootstrap-datetimepicker.min.css" rel="stylesheet">
<script src="/export/js/vue.min.js"></script>
<script src="/export/js/vue-resource.min.js"></script>
<script src="/export/js/jquery-2.0.3.min.js"></script>
<script src="/export/js/bootstrap.min.js"></script>
<!-- SheetJS js-xlsx library -->
<script src="/export/js/shim.min.js"></script>
<script src="/export/js/xlsx.full.min.js"></script>
<!-- FileSaver.js is the library of choice for Chrome -->
<script src="/export/js/Blob.js"></script>
<script src="/export/js/FileSaver.js"></script>
<!-- FileSaver doesn't work in older IE and newer Safari; Downloadify is the flash fallback -->
<script src="/export/js/swfobject.js"></script>
<script src="/export/js/downloadify.min.js"></script>
<script src="/export/js/moment-with-locales.js"></script>
<script src="/export/js/bootstrap-datetimepicker.min.js"></script>
<!-- 以上js插件均可以网上下载最新版本，本文末提供案例下载 -->
</head>
<body>
<div class="alert alert-success" role="alert">+发货清单下载</div>
<div class="form-group">
<div class='col-xs-4'>
<div class="form-group">
<label>开始时间：</label>
<!--指定 date标记-->
<div class='input-group date' id='datetimepicker1'>
<input type='text' class="form-control" id='date_start'/>
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
</div>
</div>
</div>
<div class='col-xs-4'>
<div class="form-group">
<label>结束时间：</label>
<!--指定 date标记-->
<div class='input-group date' id='datetimepicker2'>
<input type='text' class="form-control" id='date_end'/>
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
</div>
</div>
</div>
</div>
<div class="row" style="width: 100%"></div>
<div class="form-group">
<div id="app" class="col-xs-3">
<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>
</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 aoa=[];
var funcs=[]; //Promise.all要用到的参数, 存放每次请求的Promise对象
var complete_count=0; //成功请求次数
var date_start=$('#date_start').val();
var date_end=$('#date_end').val();
self.$http.get(
'/export/v2_excel_xf_order.php',//后端获取数据量以及将要导出的excel表头（第一行）
{ params: {iscount:1,date_start:date_start,date_end:date_end} }
).then(function(response){
if (response.body.code==200) {
console.log(response.body.pagination.count);
//设置表头数组
for(var i=0; i<response.body.data.length; i++){
var item=[];
var jdata=response.body.data[i];
for(var p in jdata){//遍历json对象的每个key/value对,p为key
item.push(jdata[p]);
}
aoa.push(item);
}
var request_times=Math.ceil(response.body.pagination.count/1000); //计算分几次请求
this.export_percentage=0; //设置当前进度百分比为0
for (var i=1; i<=request_times; i++) { // 循环请求次数，构造请求的Promise对象并插入funcs数组
var func=new Promise(function(resolve, reject){ //定义Promise并处理请求逻辑
self.$http.get(
'/export/v2_excel_xf_order.php',//分批获取数据
{ params: {page:i,date_start:date_start,date_end:date_end} }
).then(function(response){
if (response.body.code==200) {
var data=[];
for(var i=0; i<response.body.data.length; i++){ //将后台返回中需要的字段取出
var item=[];
var jdata=response.body.data[i];
for(var p in jdata){//遍历json对象的每个key/value对,p为key
item.push(jdata[p]);
}
data.push(item);
}
complete_count++; //成功请求次数+1
self.export_percentage=Math.floor(100*complete_count/request_times); //设置当前进度百分比
resolve(data);
} else {
reject();
}
});
});
funcs.push(func);
}
Promise.all(funcs).then(function(values){ //使用Promise.all调用funcs里面的函数，并合并数据，最后给js-xlsx生成Excel
//将数据合并
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"}), date_start+'至'+date_end+'新风空净产品发货清单.xlsx');
self.export_percentage=-1;
});
} else {
return alert(response.body.msg);
}
});
}
}
});
$(function () {
var picker1 = $('#datetimepicker1').datetimepicker({
format: 'YYYY-MM-DD',
locale: moment.locale('zh-cn'),
//minDate: '2016-7-1'
defaultDate:getQueryVariable('StartDate')
});
var picker2 = $('#datetimepicker2').datetimepicker({
format: 'YYYY-MM-DD',
locale: moment.locale('zh-cn'),
defaultDate:getQueryVariable('OverDate')
});
//动态设置最小值
picker1.on('dp.change', function (e) {
picker2.data('DateTimePicker').minDate(e.date);
});
//动态设置最大值
picker2.on('dp.change', function (e) {
picker1.data('DateTimePicker').maxDate(e.date);
});
});
function getQueryVariable(variable)
{
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if(pair[0] == variable){return pair[1];}
}
return(false);
}
</script>
