1.原始html写的table表格的导出：
<table v-for="(workerDays,index) in rawHtml.groupClockRecords" :key="(workerDays,index)" border="1" align="center" style="border-collapse:collapse;" class="creditrecord">
<caption align="top" style="font-weight: bold">
<br><br>
{{rawHtml.projectName}}
<br><br>
</caption>
<thead>
<tr>
<th colspan="15" rowspan="1" align="left">企业名称:{{rawHtml.projectName}} 承包内容:{{workerDays.groupName?workerDays.groupName:"-"}} <span>统计周期:{{dateRange[0]}}至{{dateRange[1]}}</span></th>
<!-- <th>{{rawHtml.dateStr}}</th> -->
</tr>
<tr>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center" style="width:60px;"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center" style="width:100px;"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center"></th>
<th rowspan="1" colspan="1" align="center" style="width:80px;"></th>
<!-- <th :colspan="colspanNum" rowspan="1" align="center">{{dateRange[0]}}至{{dateRange[1]}}</th> -->
<!-- <th :colspan="colspanNum" rowspan="1">至</th> -->
<!-- <th rowspan="1" colspan="1" align="center"></th> -->
</tr>
<!-- <tr>
<th rowspan="1" align="center" v-for="(data,index) in tableTitle" :key="(data,index)">{{data.split('-')[2]}}</th>
</tr> -->
</thead>
<tbody>
<tr v-for="(data,indexX) in workerDays.workerDaysClockRecords" :key="(data,indexX)">
<td rowspan="1" colspan="1" align="center">{{indexX+1}}</td>
<td rowspan="1" colspan="1" align="center"><span style="display:inline-block;width:70px;">{{data.workerName?data.workerName:"-"}}</span></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<td rowspan="1" colspan="1" align="center"></td>
<!-- <td rowspan="1" colspan="1" align="center" v-for="(day,indexY) in tableTitle" :key="(day,indexY)">
{{data.daysClockRecord ? (data.daysClockRecord[tableTitle[indexY]] ? data.daysClockRecord[tableTitle[indexY]].firstClockedTime : "-") : "-"}}<br/>{{data.daysClockRecord ? (data.daysClockRecord[tableTitle[indexY]] ? data.daysClockRecord[tableTitle[indexY]].lastClockedTime : "-") : "-"}}
</td> -->
</tr>
</tbody>
<tfoot>
<tr>
<td rowspan="2" colspan="15" style="line-height:25px; text-align:left;"></td>
</tr>
<tr></tr>
<tr>
<td rowspan="1" colspan="15"></td>
</tr>
<!--<tr>-->
<!--<td rowspan="1" :colspan="tableTitle.length+4" style="text-align: left"></td>-->
<!--</tr>-->
</tfoot>
</table>
像这种单独定制的表格可以直接使用以下方式导出，支持多个表格导出
exportExcel () {
// 使用outerHTML属性获取整个table元素的HTML代码（包括<table>标签），然后包装成一个完整的HTML文档，设置charset为urf-8以防止中文乱码
let tableHtml = document.getElementsByClassName("creditrecord");
var appendHtml = "";
for(var i=0;i<tableHtml.length;i++){
appendHtml+=tableHtml[i].outerHTML
}
let html = "<html><head><meta charset='utf-8' /></head><body>" + appendHtml + "</body></html>";
// 实例化一个Blob对象，其构造函数的第一个参数是包含文件内容的数组，第二个参数是包含文件类型属性的对象
let blob = new Blob([html], { type: "application/vnd.ms-excel" }); //application/octet-stream
//也可以用js创建一个a标签
let a = document.createElement('a');
// var a = document.getElementsByTagName("a")[0];
// 利用URL.createObjectURL()方法为a元素生成blob URL
a.href = URL.createObjectURL(blob);
// 设置文件名
a.download = "基本从业信息表("+this.dateRange[0]+"至"+this.dateRange[1]+").xls"; //xlsx
a.click();
}
}
2.vue搭配elementUI的表格导出：
1.先安装依赖
npm install --save xlsx file-saver
2.在需要的组件内引入
import FileSaver from "file-saver"
import XLSX from "xlsx"
3、完整代码
html
<template>
<div>
<!-- 导出按钮 -->
<div class="toexcel">
<el-button @click="exportExcel" type="primary" class="button" style="width:70px;position:absolute;top:0;right:30px">导出</el-button>
</div>
<el-table
class="table"
:data="tableData"
border
style="width: 100%">
<el-table-column
prop="date"
label="日期"
width="180">
</el-table-column>
<el-table-column
prop="name"
label="姓名"
width="180">
</el-table-column>
<el-table-column
prop="address"
label="地址">
</el-table-column>
</el-table>
</div>
</template>
js
<script>
import FileSaver from "file-saver";
import XLSX from "xlsx";
export default {
data() {
return {
tableData: [{
date: '2016-05-02',
name: '王小虎',
address: '上海市普陀区金沙江路 1518 弄'
}, {
date: '2016-05-04',
name: '王小虎',
address: '上海市普陀区金沙江路 1517 弄'
}, {
date: '2016-05-01',
name: '王小虎',
address: '上海市普陀区金沙江路 1519 弄'
}, {
date: '2016-05-03',
name: '王小虎',
address: '上海市普陀区金沙江路 1516 弄'
}]
};
},
methods: {
// 导出表格所用
exportExcel() {
// 设置当前日期
let time = new Date();
let year = time.getFullYear();
let month = time.getMonth() + 1;
let day = time.getDate();
let name = year + "" + month + "" + day;
// console.log(name)
/* generate workbook object from table */
// .table要导出的是哪一个表格
var wb = XLSX.utils.table_to_book(document.querySelector(".table"));
/* get binary string as output */
var wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: true,
type: "array"
});
try {
// name+'.xlsx'表示导出的excel表格名字
FileSaver.saveAs(
new Blob([wbout], { type: "application/octet-stream" }),
name + ".xlsx"
);
} catch (e) {
if (typeof console !== "undefined") console.log(e, wbout);
}
return wbout;
}
}
};
</script>
3、Koa前后端联调
前端
一、axios下载的版本
// 导出excel
const exportExcel = () => {
let data = {
username:formInline.username
}
exportUserExcel(data).then(res => {
// fileDownload(res, 'userExcel.xlsx');
if(!res) return
const blob = new Blob([res], { type: 'application/vnd.ms-excel' }) // 构造一个blob对象来处理数据，并设置文件类型
if (window.navigator.msSaveOrOpenBlob) { //兼容IE10
navigator.msSaveBlob(blob, this.filename)
} else {
const href = URL.createObjectURL(blob) //创建新的URL表示指定的blob对象
const a = document.createElement('a') //创建a标签
a.style.display = 'none'
a.href = href // 指定下载链接
a.download = 'userExcel.xlsx' //指定下载文件名
a.click() //触发下载
URL.revokeObjectURL(a.href) //释放URL对象
}
})
}
二、使用插件
1、先安装依赖
npm install js-file-download
2、在需要的组件内引入
import fileDownload from 'js-file-download'
3、在api/index.js中
//导出用户excel接口
export const exportUserExcel = (data) => {
return axios({
method:'get',
url:'/users/exportUserExcel',
responseType: 'blob',
data
})
}
4、在使用页面中
//点击按钮
<el-button
size="default"
@click="exportExcel"
>导出</el-button>
//引入接口
import {exportUserExcel } from '@/api';
// 点击按钮触发的事件
const exportExcel = () =>{
let data = {
username:formInline.username
}
exportUserExcel(data).then(res => {
fileDownload(res, 'userExcel.xlsx');
})
}
后端
1、下载
npm install node-xlsx --save
2、
//引入
const NXlSX = require("node-xlsx");
//接口
router.get('/exportUserExcel',async ctx=>{
// 默认值
let { username = '' } = ctx.query;
username = new RegExp(username);
let userList = await User.find({username}).sort({createData: -1});
console.log(userList);
//表头
const _headers = ['序号', '姓名', '创建时间']
//表格数据
let data = [];
data.push(_headers);
userList.forEach(item=>{
let ele = [];
ele.push(item.id);
ele.push(item.username);
ele.push(item.createDate);
data.push(ele);
})
//由于各列数据长度不同，可以设置一下列宽
const options = {'!cols': [{ wch: 90 }, { wch: 10 }, { wch: 15 }]};
let buffer = NXlSX.build([{name:'sheet1',data}], options);
// 返回buffer流到前端
ctx.body = buffer
})
只写了后端 前端没写时
在浏览器直接输入http://localhost:3000/users/exportUserExcel 可直接下载Excel
（users是自己定义的接口头 ， /exportUserExcel是自己定义的后端接口数据名称）
