vue xlsx 动态列下载
表格如下：
表格为横向接上
数据格式如下：
一、elementUI el-table渲染到网页上
<div v-loading="loading" style="margin-bottom: 16px;">
<div v-if="syncList.length">
<el-table :data="syncList" style="width: 100%" :header-cell-style="getRowClass" :span-method="objectSpanMethod">
<el-table-column align="center" label="项目" max-width="96">
<el-table-column prop="category" width="90" label="类别" align="center">
<template slot-scope="scope">
<span>{{scope.row.category}}</span>
</template>
</el-table-column>
<el-table-column prop="staType" width="120" label="统计方式" align="center">
<template slot-scope="scope">
<span>{{scope.row.staType}}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column v-for="(item, index) in syncList[0].orgs" :key="index" :label="item.subsystemName" align="center"> // 动态列orgs
<template slot-scope="scope">
<span>{{scope.row.orgs?scope.row.orgs[index].subsystemName:''}}</span>
</template>
<el-table-column prop="dayShift" width="90" label="白班" align="center">
<template slot-scope="scope">
<span>{{scope.row.orgs[index].dayShift?scope.row.orgs[index].dayShift:"-"}}</span>
</template>
</el-table-column>
<el-table-column prop="nightShift" width="90" label="晚班" align="center">
<template slot-scope="scope">
<span>{{scope.row.orgs[index].nightShift?scope.row.orgs[index].nightShift:"-"}}</span>
</template>
</el-table-column>
<el-table-column prop="total" width="90" label="合计" align="center">
<template slot-scope="scope">
<span>{{scope.row.orgs[index].total?scope.row.orgs[index].total:"-"}}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="合计" align="center" >
<el-table-column prop="dayShift" width="90" label="白班" align="center">
<template slot-scope="scope">
<span>{{scope.row.thisPeriod.dayShift}}</span>
</template>
</el-table-column>
<el-table-column prop="nightShift" width="90" label="晚班" align="center">
<template slot-scope="scope">
<span>{{scope.row.thisPeriod.nightShift}}</span>
</template>
</el-table-column>
<el-table-column prop="total" width="90" label="合计" align="center">
<template slot-scope="scope">
<span>{{scope.row.thisPeriod.total}}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="上年同期" align="center" >
<el-table-column prop="dayShift" width="90" label="白班" align="center">
<template slot-scope="scope">
<span>{{scope.row.lastPeriod.dayShift}}</span>
</template>
</el-table-column>
<el-table-column prop="nightShift" width="90" label="晚班" align="center">
<template slot-scope="scope">
<span>{{scope.row.lastPeriod.nightShift}}</span>
</template>
</el-table-column>
<el-table-column prop="total" width="90" label="合计" align="center">
<template slot-scope="scope">
<span>{{scope.row.lastPeriod.total}}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column prop="compare" align="center" label="增减%" max-width="96"></el-table-column>
</el-table>
</div>
<div v-else style="display: flex;justify-content: space-around;align-items: center;color: #c7c7c7;min-height: 200px;">
暂无数据
</div>
</div>
js代码：
导出组件： <ImportExcel ref="importExcel" v-if="activeName=='first'" excelTitle="停车场临时收费实时统计表(时间)" :tHeader="tHeader" :filterVal="filterVal" @getDownData="getDownData"></ImportExcel> data: //import tHeader:[], filterVal:[], CellRules:[], hasExcelHeader:[], exportRequest: new Object(), filterList: [], // 用来存放list dataForPaytypeAll: [],//存放查询页面传过来的数据，用于导出 rowSpItem:[], rowSpStatus:[], js: toExcel(v,rowSpItem,rowSpStatus) { console.log("要导出的列表数据：",v,rowSpItem,rowSpStatus) this.dataForPaytypeAll=v this.rowSpItem=rowSpItem this.rowSpStatus=rowSpStatus }, getDownData(){// 获取导出数据 let _this = this; // 此处将this赋值给_this是必须的，不然this.formatJson()会报错 this.filterVal = [] //存放最终导出的数据 this.tHeader = [] //存放最终导出的表头 this.CellRules = [] //存放表头样式 this.hasExcelHeader= [] console.log("---------------------------------paytypeAll-----------------------------------------------------------"); if(this.dataForPaytypeAll.length==0) { return this.$message({type:"info",message:"该时段收费数据为空"}); } //动态计算tHeader console.log("this.dataForPaytypeAll",this.dataForPaytypeAll); let content = this.dataForPaytypeAll[0] console.log("content",content); let alist = content.orgs let a1 = [],a2=[] let len1 = alist.length let datalen = this.dataForPaytypeAll.length for (let i = 0; i < len1; i++ ) { a1.push(alist[i].subsystemName) a1.push('') a1.push('') a2.push("白班") a2.push("晚班") a2.push("合计") } this.tHeader =[ ['项目','','a','合计','','','上年同期','','','增减'], // a,b表示需要动态合并的列数，有多少列，分别添加多少个'' ['类别','统计方式','a','白班','晚班','合计', '白班','晚班','合计',''] // 'a','b'用来添加列的标题名称 // ['收款渠道','付款方式', '支付方式合计(元)','收费名称','a'], // a,b表示需要动态合并的列数，有多少列，分别添加多少个'' // ['', '', '', 'a'] // 'a','b'用来添加列的标题名称 ] this.hasExcelHeader = ['category','staType','orgs','thisPeriod','lastPeriod','percentage','compare'] let a1Index = this.tHeader[0].indexOf('a') this.tHeader[0].splice(a1Index, 1, ...a1) let a2Index = this.tHeader[1].indexOf('a') this.tHeader[1].splice(a2Index, 1, ...a2) console.log('所有收费方式this.tHeader',this.tHeader) //动态构造数据 this.dataForPaytypeAll.forEach(el => { let arr =[] this.hasExcelHeader.forEach(element =>{ if (el.hasOwnProperty(element)) { if (Object.prototype.toString.call(el[element]) === '[object Array]' && (element==='orgs')) { el[element].map(item => { let v = [item.dayShift,item.nightShift,item.total] arr.push(...v) }) }else if(element==='thisPeriod' || element==='lastPeriod') { let v = [el[element].dayShift,el[element].nightShift,el[element].total] arr.push(...v) }else{ arr.push(el[element]) } } }) _this.filterVal.push(arr) }) console.log('所有收费方式this.filterVal',this.filterVal) //动态构造样式 this.CellRules = [ {s: {c: 0, r: 0 }, e: { c: 1, r: 0} }, //合并A1B1单元格 {s: {c: 2+3*len1, r: 0 }, e: { c: 4+3*len1, r: 0} }, {s: {c: 5+3*len1, r: 0 }, e: { c: 7+3*len1, r: 0} }, {s: {c: 8+3*len1, r: 0 }, e: { c: 8+3*len1, r: 1} }, // {s: {c: 8+3*len1, r: 0 }, e: { c: 10+3*len1, r: 0} }, // {s: {c: 11+3*len1, r: 0 }, e: { c: 11+3*len1, r: 1} }, ] for(var i=0;i<len1;i++) { this.CellRules.push( {s: {c: 2+3*i, r: 0 }, e: { c: 4+3*i, r: 0} } ); for(var j = datalen;j < datalen+2;j++){ this.CellRules.push( {s: {c: 2+3*i, r: j }, e: { c: 4+3*i, r: j} } ); } } this.CellRules.push( {s: {c: 2+3*len1, r: datalen }, e: { c: 3*len1+4, r: datalen} } ); this.CellRules.push( {s: {c: 2+3*len1, r: datalen+1 }, e: { c: 3*len1+4, r: datalen+1} } ); this.CellRules.push( {s: {c: 5+3*len1, r: datalen }, e: { c: 3*len1+7, r: datalen} } ); this.CellRules.push( {s: {c: 5+3*len1, r: datalen+1 }, e: { c: 3*len1+7, r: datalen+1} } ); let rows=2 //记录行数 for(var i=0;i<this.rowSpItem.length;i++) { if(this.rowSpItem[i]!=0) { this.CellRules.push( {s: {c: 0, r: rows }, e: { c: 0, r: rows+this.rowSpItem[i]-1} } ); rows+=this.rowSpItem[i] } } console.log('所有收费方式this.CellRules',this.CellRules) //导出 this.$refs.importExcel && this.$refs.importExcel.exportSpecialExcel(this.tHeader, this.filterVal,this.CellRules) },
导出组件:
ImportExcel
<template>
<el-button type="primary" @click="exportExcel">导出</el-button>
</template>
<script>
import XLSX from 'xlsx'
export default {
data(){
return {
}
},
props:{
tHeader:{
type:Array,
required:true,
},
filterVal:{
type:Array,
required:false,
default: () => []
},
// CellRules:{
// type:Array, // 表格合并项
// required:false,
// default: () => []
// },
excelTitle:{
type:String,
default:'excelTitle'
}
},
watch:{
tHeader(v){
},
filterVal(){
}
},
methods: {
exportExcel() {
// var excelJson = [
// ['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
// ['姓名', '性别', '年龄', '注册时间'],
// ['张三', '男', 18, new Date()],
// ['李四', '女', 22, new Date()]
// ];
// sheet['!merges'] = [
// // 设置A1-C1的单元格合并
// {s: {r: 0, c: 0}, e: {r: 0, c: 2}}
// ];
this.$emit("getDownData",{})
},
exportSpecialExcel (tHeader, filterVal, cellRules) {
// console.log('特殊表格导出', tHeader, filterVal)
let excelJson = [...tHeader, ...filterVal]
var sheet = XLSX.utils.aoa_to_sheet(excelJson);
if (cellRules.length) { sheet['!merges'] = cellRules }
this.openDownloadDialog(this.sheet2blob(sheet), `${this.excelTitle}.xlsx`);
},
openDownloadDialog(url, saveName) {
if(typeof url == 'object' && url instanceof Blob)
{
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
var event;
if(window.MouseEvent) event = new MouseEvent('click');
else
{
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
},
sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
}
}
</script>
<style lang="scss" scoped>
</style>
