今天给大家分享一种无需依赖任何第三方类库实现前端的表格数据导出为Excel文件。
一、实现原理
利用Blob对象构造一个a标签的href链接，从而实现Excel文件下载，因为Excel支持html格式，所以只需要将构造好的html字符串内容放到Blob对象中，就可以通过浏览器下载为Excel文件。
二、blob对象介绍
一个 Blob对象表示一个不可变的, 原始数据的类似文件对象。Blob表示的数据不一定是一个JavaScript原生格式 blob对象本质上是js中的一个对象，里面可以储存大量的二进制编码格式的数据。
var aBlob = new Blob( array, options );
三、blod支持的mimeType类型
在 Blob 的构造函数中options参数的接受一个参数type,这个参数代表的是媒体类型,通过这个参数告诉浏览器是什么类型的文件,常见的类型如下：
文件扩展名 | MIME类型 |
|---|---|
.3gp | video/3gpp |
.apk | application/vnd.android.package-archive |
.asf | video/x-ms-asf |
.avi | video/x-msvideo |
.bin | application/octet-stream |
.bmp | image/bmp |
.c | text/plain |
.class | application/octet-stream |
.conf | text/plain |
.cpp | text/plain |
.doc | application/msword |
.docx | application/vnd.openxmlformats-officedocument.wordprocessingml.document |
.xls | application/vnd.ms-excel |
.xlsx | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
.exe | application/octet-stream |
.gif | image/gif |
.gtar | application/x-gtar |
.gz | application/x-gzip |
.h | text/plain |
.htm | text/html |
.html | text/html |
.jar | application/java-archive |
.java | text/plain |
.jpeg | image/jpeg |
.jpg | image/jpeg |
.js | application/x-javascript |
.log | text/plain |
.m3u | audio/x-mpegurl |
.m4a | audio/mp4a-latm |
.m4b | audio/mp4a-latm |
.m4p | audio/mp4a-latm |
.m4u | video/vnd.mpegurl |
.m4v | video/x-m4v |
.mov | video/quicktime |
.mp2 | audio/x-mpeg |
.mp3 | audio/x-mpeg |
.mp4 | video/mp4 |
.mpc | application/vnd.mpohun.certificate |
.mpe | video/mpeg |
.mpeg | video/mpeg |
.mpg | video/mpeg |
.mpg4 | video/mp4 |
.mpga | audio/mpeg |
.msg | application/vnd.ms-outlook |
.ogg | audio/ogg |
application/pdf | |
.png | image/png |
.pps | application/vnd.ms-powerpoint |
.ppt | application/vnd.ms-powerpoint |
.pptx | application/vnd.openxmlformats-officedocument.presentationml.presentation |
.prop | text/plain |
.rc | text/plain |
.rmvb | audio/x-pn-realaudio |
.rtf | application/rtf |
.sh | text/plain |
.tar | application/x-tar |
.tgz | application/x-compressed |
.txt | text/plain |
.wav | audio/x-wav |
.wma | audio/x-ms-wma |
.wmv | audio/x-ms-wmv |
.wps | application/vnd.ms-works |
.xml | text/plain |
.z | application/x-compress |
.zip | application/x-zip-compressed |
(无扩展名) | / |
四、示例
4.1 新建exportExcel.js
用来定义生成Excel文件的方法，大家可以根据自己实际的业务需要进行格式、和导出功能的传参功能的调整。
代码语言：javascript复制/*
万能流 application/octet-stream
word文件 application/msword
excel文件 application/vnd.ms-excel
txt文件 text/plain
图片文件 image/png、jpeg、gif、bmp
*/
function downloadByBlob(fileName, text) {
let a = document.createElement("a");
a.href = URL.createObjectURL(new Blob([text], { type: "application/octet-stream" }));
a.download = fileName || 'Blob导出测试.txt';
a.click();
a.remove();
URL.revokeObjectURL(a.href);
}
// fileName 文件名称
// columns 列名属性 主要用来匹配后台数据比如 name ,遍历tableDatas的时候 item.name 获取对应列的值
// columnNames 列标题名
// tableDatas 传递过来的参数值
function exportExcel(fileName, columns,columnNames, tableDatas) {
//列名
let columnHtml = "";
columnHtml = "<tr style="text-align: center;">n";
for (let key in columnNames) {
columnHtml = "<td style="font-weight:bold;background-color:#bad5fd">" columnNames[key] "</td>n";
}
columnHtml = "</tr>n";
//数据
let dataHtml = "";
for (let data of tableDatas) {
dataHtml = "<tr style="text-align: center;">n";
for (let key in columns) {
dataHtml = "<td>" data[columns[key]] "</td>n";
}
dataHtml = "</tr>n";
}
//完整拼接Excel内容的html
let excelHtml = "<html xmlns:o="urn:schemas-microsoft-com:office:office"n"
" xmlns:x="urn:schemas-microsoft-com:office:excel"n"
" xmlns="http://www.w3.org/TR/REC-html40">n"
"<head>n"
" <!-- 加这个，其他单元格带边框 -->"
" <xml>n"
" <x:ExcelWorkbook>n"
" <x:ExcelWorksheets>n"
" <x:ExcelWorksheet>n"
" <x:Name></x:Name>n"
" <x:WorksheetOptions>n"
" <x:DisplayGridlines/>n"
" </x:WorksheetOptions>n"
" </x:ExcelWorksheet>n"
" </x:ExcelWorksheets>n"
" </x:ExcelWorkbook>n"
" </xml>n"
" <style>td{font-family: "宋体";}</style>n"
"</head>n"
"<body>n"
"<table border="1">n"
" <thead>n"
columnHtml
" </thead>n"
" <tbody>n"
dataHtml
" </tbody>n"
"</table>n"
"</body>n"
"</html>";
//浏览器下载为本地文件
downloadByBlob((fileName || "导出Excel") ".xls", excelHtml);
}4.2 新建HTML测试页面 Test.html
用来进行导出功能演示和页面表格数据展示。
代码语言：javascript复制<html>
<head>
<meta charset="UTF-8">
<title>Excel导出测试</title>
<!-- import CSS -->
<link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
</head>
<body>
<div id="app">
<el-row>
<el-col :span="24">
<el-button type="primary" @click="exportFile" size="mini">导出</el-button>
</el-col>
</el-row>
<el-row>
<el-col :span="24">
<el-table :data="tableData" height="650" border style="width: 99%;margin-top: 50px;">
<el-table-column prop="name" label="姓名">
</el-table-column>
<el-table-column prop="age" label="年龄">
</el-table-column>
<el-table-column prop="address" label="住址">
</el-table-column>
<el-table-column prop="hobby" label="兴趣爱好">
</el-table-column>
</el-table>
</el-col>
</el-row>
</div>
</body>
<!-- import Vue before Element -->
<script src="vue.js"></script>
<!-- import JavaScript -->
<script src="index.js"></script>
<script src="exportExcel.js"></script>
<script src="axios.min.js"></script>
<script>
new Vue({
el: '#app',
data() {
return {
tableData: [{
"name": "小明",
"age": 35,
"address": "苏州",
"hobby": "音乐"
}, {
"name": "小李",
"age": 30,
"address": "北京",
"hobby": "读书、跑步"
}, {
"name": "张三",
"age": 28,
"address": "南京",
"hobby": "美食"
}, {
"name": "小明",
"age": 35,
"address": "苏州",
"hobby": "音乐"
}, {
"name": "王五",
"age": 38,
"address": "上海",
"hobby": "健身"
}, {
"name": "李四",
"age": 33,
"address": "深圳",
"hobby": "户外运动"
}]
}
},
mounted() {
},
methods: {
onSearch() {
// 一般都是通过后台接口获取数据进行导出操作
},
// 导出文件
exportFile() {
var self = this;
var columnNames = ['姓名', '年龄', '住址', '兴趣爱好'];
var columnDatas = ['name', 'age', 'address', 'hobby'];
exportExcel(name "人员信息导出.xlsx", columnDatas, columnNames, self.tableData,);
}
}
})
</script>
</html>五、 运行效果
表格数据
导出效果展示
