vue 前端导出Excel表格(基础版 + 多级标题)纯前端导出

先看效果

 

纯前端基础导出的Excel表格

 纯前端多级表头导出的Excel表格

基础导出下面赋源代码

1、安装依赖

npm install vue-json-excel

2、在项目的入口 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-saver

2、要新建一个文件,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>