最近做商城后台管理系统,遇到需求把数据表导出到excel,翻阅资料,大概整理如下俩种方式:
一、单纯的导出一个excel表格:
/**
* @description 导出数据到excel,拼接key,value,扔到csv文件,创建a标签。
* @date 2019-07-16 16:00
*/
function exportExcel () {
let data = [
{
no:1,
withdrawNo:'W160211111111',
withdrawTime:'2019-07-16 16:00',
phone:'152111111111',
withdrawType:'支付宝提现',
withdrawMount:'10000.00',
},
{
no:4,
withdrawNo:'W160211111111',
withdrawTime:'2019-07-16 16:00',
phone:'152111111111',
withdrawType:'支付宝提现',
withdrawMount:'10000.00',
},
{
no:4,
withdrawNo:'W160211111111',
withdrawTime:'2019-07-16 16:00',
phone:'152111111111',
withdrawType:'支付宝提现',
withdrawMount:'10000.00',
},
{
no:4,
withdrawNo:'W160211111111',
withdrawTime:'2019-07-16 16:00',
phone:'152111111111',
withdrawType:'支付宝提现',
withdrawMount:'10000.00',
}
]
let str = `序号,提现单号,提现申请时间,会员手机号,提现类型,提现金额\n`
// 出现科学计数法用\t解决
for(let i = 0 ; i < data.length ; i++) {
for(let key in data[i]) {
str += `${data[i][key]}\t`
}
str +='\n';
}
// encodeURIComponent解决中文乱码
let url = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
// 创建a标签
let link = document.createElement("a");
link.href = url;
// 命名文件
link.download = `${common.dateCoutince(new Date())}_提现数据表_${data.length}条.csv`;
// 下载删除
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
上面的方法可以导出excel,但是我们发现excel里面的高度,宽度明显是默认的,追求更好,我们采用以下方式设置样式:
<template>
<table id="table">
<tr>
<th>序号</th>
<th>提现单号</th>
<th>提现申请时间</th>
<th>会员手机号</th>
<th>提现类型</th>
<th>提现金额</th>
</tr>
<tr v-for="(item,index) in list">
<td>{{item.num}}</td>
<td>{{item.withdrawNo}}</td>
<td>{{item.withdrawApplyTime}}</td>
<td>{{item.memberPhone}}</td>
<td>{{item.withdrawType}}</td>
</tr>
</table>
</template>
--------------------------------------------------------------------------------------------------------------
exportExcel(){
// 获取table的全部节点
let oHtml = document.getElementById('table').outerHTML
// 创建一个html页面
var excelHtml = `
<html>
<head>
<meta charset='utf-8' />
<style>
table{
min-width: calc(100vw - 290px);
/*margin-top: 50px;*/
border-collapse: collapse;
border-spacing: 0;
border: 1px #eee solid;
border-radius: 50px;
}
table tr{
height:50px;
border: 1px #eee solid;
white-space: nowrap;
}
table tr th{
border: 1px #eee solid;
background-color: #e1f0fe;
}
table tr td{
border: 1px #eee solid;
white-space: nowrap;
padding: 30px 20px;
text-align: center;
}
</style>
</head>
<body>
${oHtml}
</body>
</html> `
// 创建blob对象
var excelBlob = new Blob([excelHtml], {type: 'application/vnd.ms-excel'})
// 创建一个a标签
var link = document.createElement('a');
// 利用URL.createObjectURL()方法为a元素生成blob URL
link.href = URL.createObjectURL(excelBlob);
// 文件命名
oA.download = `${common.dateCoutince(new Date())}_提现数据表_${this.list.length}条.xlsx`;
oA.click();
}
解决科学计数法:数据加一个& nbsp;