vue 前端导出Excel表格（基础版 + 多级标题）纯前端导出
先看效果
纯前端基础导出的Excel表格
纯前端多级表头导出的Excel表格
基础导出下面赋源代码
1、安装依赖
npm install vue-json-excel2、在项目的入口 main.js 引入
import JsonExcel from 'vue-json-excel'// 引入导出Excel
Vue.component('downloadExcel', JsonExcel)3、直接使用
<!--
:data="tableData" // 要导出的数据 :fields="json_fields" // 导出数据的配置
:header="title" // 导出的标题 name="订单.xls" // 导出的表格名字及格式
-->
<download-excel style="margin-right: 10px;" class="export-excel-wrapper" :data="tableData" :fields="json_fields" :header="title" name="订单.xls">
<el-button >导 出</el-button>
</download-excel>
<------------------------------------分割---------------------------------------->
配置项
json_fields: { // 导出对应表格头部以及数据
"id":'id',
"名字":'name',
"颜色":'amount1',
"字段1":'amount2',
"字段2":'amount3',
},4、完整代码直接复制即可
<template>
<div>
<!--
:data="tableData" // 要导出的数据
:fields="json_fields" // 导出数据的配置
:header="title" // 导出的标题
name="订单.xls" // 导出的表格名字及格式
-->
<download-excel style="margin-right: 10px;" class="export-excel-wrapper" :data="tableData" :fields="json_fields" :header="title" name="订单.xls">
<el-button >导 出</el-button>
</download-excel>
<el-table :data="tableData" border height="200" style="width: 100%; margin-top: 20px">
<el-table-column prop="id" label="ID" width="180"></el-table-column>
<el-table-column prop="name" label="姓名"></el-table-column>
<el-table-column prop="amount1" label="数值 1（元）"></el-table-column>
<el-table-column prop="amount2" label="数值 2（元）"></el-table-column>
<el-table-column prop="amount3" label="数值 3（元）"></el-table-column>
</el-table>
</div>
</template>
<script>
export default {
data() {
return {
title: "标题",//
json_fields: { // 导出对应表格头部以及数据
"id":'id',
"名字":'name',
"颜色":'amount1',
"字段1":'amount2',
"字段2":'amount3',
},
tableData: [
{
id: '12987122',
name: '王小虎',
amount1: '234',
amount2: '3.2',
amount3: 10
},
{
id: '12987123',
name: '王小虎',
amount1: '165',
amount2: '4.43',
amount3: 12
},
{
id: '12987124',
name: '王小虎',
amount1: '324',
amount2: '1.9',
amount3: 9
},
{
id: '12987125',
name: '王小虎',
amount1: '621',
amount2: '2.2',
amount3: 17
},
{
id: '12987126',
name: '王小虎',
amount1: '539',
amount2: '4.1',
amount3: 15
}
]
};
},
methods: {}
};
</script>
<style></style>
<------------------------------------分割-------------------------------------------->
多级表头导出
1、还是npm下载依赖
npm install --save xlsx file-saver2、要新建一个文件，Export2Excel.js 文件，我是从某个网址下载的，我给忘了，这里我直接复制过来,在src目录下创建文件夹vendor，然后创建Export2Excel.js文件
/* eslint-disable */
import {
saveAs
} from 'file-saver'
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function(range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e
.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1,
c: outRow.length + colspan - 1
}
});
};
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = {
v: data[R][C]
};
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
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;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), "test.xlsx")
}
export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header);
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*以第一行为初始值*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
3、直接导出，看注释
<div style="padding: 20px 0;"><el-button size="small" type="primary" @click="handleDownload">导出EXCEl</el-button></div>
<!-- 这里是按钮 -->
// 下面是对应的方法
handleDownload1() { // 手写导出导出
import('@/vendor/Export2Excel').then(excel => {
const multiHeader = [['物料信息', '', '', '', '', '', '', '上月库存', '', '','本月入库', '', '', '本月出库','', '', '本月库存', '', '']];// 这里是第一级的标题,合并的格子一定要用空写出来不能直接不写
const header =['物料名称', '类型', '规格', '颜色', '厚度', '计量单位', '品牌', '单价', '数量', '金额','单价', '数量', '金额','单价', '数量', '金额','单价', '数量', '金额',] //第二行; // 最后一行的表头数据
const filterVal = ['name', 'type', 'specification', 'color', 'thicknes', 'measuring_unit','brand','shang_cash','shang_inventory_num','shangyue_money',
'brdanjia', 'brsum', 'brmoney', 'bcdanjia', 'bcsum', 'bcmoney', 'bdanjia', 'bsum', 'bmoney'];//这里代表字段与上面的对应起来
this.tableData.forEach(item=>{// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!这里不用管我,我这里由于业务需要 修改了一下
item.brdanjia = item.benyueruku.danjia
item.brsum = item.benyueruku.sum
item.brmoney = item.benyueruku.money
item.bcdanjia = item.benyuechuku.danjia
item.bcsum = item.benyuechuku.sum
item.bcmoney = item.benyuechuku.money
item.bdanjia = item.benyuekucun.danjia
item.bsum = item.benyuekucun.sum
item.bmoney = item.benyuekucun.money
})
const list = this.tableData;// 数据 这里对应上面我修改好的数据
const data = this.formatJson(filterVal, list);// 不用改
const merges = ['A1:G1', 'H1:J1', 'K1:M1', 'N1:P1', 'Q1:S1']; // 合并单元格的数据，对应一级表头要合并的格子,可以自己打开Excel表格看看要合并的格子写上对应的字母
const filename = '导出'
excel.export_json_to_excel({
multiHeader,
header,
merges,
data,
filename
});
});
},
formatJson(filterVal, jsonData) {
return jsonData.map(v =>
filterVal.map(j => {
return v[j];
})
);
},4、源代码 直接复制就行 小例子
<template>
<div style="padding: 20px;">
<div style="padding: 20px 0;"><el-button size="small" type="primary" @click="handleDownload">导出EXCEl</el-button></div>
<el-table class="center-table" :data="tableData" size="small">
<el-table-column prop="date" label="日期" width="150" />
<el-table-column label="配送信息">
<el-table-column prop="name" label="姓名" width="120" />
<el-table-column label="地址">
<el-table-column prop="province" label="省份" width="120" />
<el-table-column prop="city" label="市区" width="120" />
<el-table-column prop="address" label="地址" />
<el-table-column prop="zip" label="邮编" width="120" />
</el-table-column>
</el-table-column>
</el-table>
</div>
</template>
<script>
export default {
name: 'MergeHeader',
data() {
return {
tableData: [
{
date: '2016-05-03',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-02',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-04',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-01',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-08',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-06',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-07',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
}
]
};
},
methods: {
handleDownload() {
import('@/vendor/Export2Excel').then(excel => {
const multiHeader = [
['日期', '配送信息', '', '', '', ''], //第一行
['', '姓名', '地址', '', '', ''] //第二行
]; // 前两行的表头数据，二维数组，不够的用空白补全
const header = ['', '', '省份', '市区', '地址', '邮编']; // 最后一行的表头数据
const filterVal = ['date', 'name', 'province', 'city', 'address', 'zip'];
const list = this.tableData;
const data = this.formatJson(filterVal, list);
const merges = ['A1:A3', 'B1:F1', 'B2:B3', 'C2:F2']; // 合并单元格的数据，
excel.export_json_to_excel({
multiHeader,
header,
merges,
data
});
});
},
formatJson(filterVal, jsonData) {
return jsonData.map(v =>
filterVal.map(j => {
return v[j];
})
);
}
}
};
</script>
<style>
.center-table td,
.center-table th {
text-align: center;
}
</style>