//1.npm引入
npm install xlsx --save
//2.线上地址引入
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
import * as XLSX from 'xlsx'
//导出方法
exportTableData(){
const now = new Date();
const year = now.getFullYear();
const month = ('0' + (now.getMonth() + 1)).slice(-2);
const day = ('0' + now.getDate()).slice(-2);
const formattedTime = year+ '-'+ month+ '-' + day;
this.tableDataAll=[{
"beforetotalinTwo": 912847124871,
"beforetotalin": 0,
"beforetotaloutTwo": 1016271393944,
"beforetotalinjindu": "0.00",
"beforetotaloutjindu": "0.00",
"colum": "福建省",
"beforetotalout": 0
},{
"beforetotalinTwo": 912847124871,
"beforetotalin": 0,
"beforetotaloutTwo": 1016271393944,
"beforetotalinjindu": "0.00",
"beforetotaloutjindu": "0.00",
"colum": "福建省1",
"beforetotalout": 0
}];
const headers = ['表头1','表头2','表头3'];
// 数字较长转换为字符串导出才不会变为科学计数格式
const data = this.tableDataAll.map(item => [item.colum, ''+item.beforetotalinTwo, ''+item.beforetotaloutTwo, item.beforetotalin, item.beforetotalout, item.beforetotalinjindu, item.beforetotaloutjindu])
const worksheet = XLSX.utils.aoa_to_sheet([headers, ...data])
const workbook = XLSX.utils.book_new()
worksheet["!cols"] = [{ wch:20 },{ wch: 30 },{ wch: 30 },{ wch: 20 },{ wch: 20 },{ wch: 20 },{ wch: 20 }];
worksheet['raw']=true;
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
XLSX.writeFile(workbook, '导出的表名'+formattedTime+'.xlsx')
},
1.自适应表格宽度
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta
name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0"
/>
<meta http-equiv="X-UA-Compatible" content="ie=edge" />
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<title>json to excel</title>
</head>
<body>
<button onclick="toExcel()">导出</button>
<script>
function toExcel() {
const excel = XLSX.utils.book_new()
const demo = [
{
'IMEI(设备编号)': '86482351421321111',
设备名称: '饭少吃',
设备类型: '空开',
设备型号: 'ML-200',
NB卡号: '32113213',
批次号: '11113333111',
出厂编号: '4213231231215431',
出厂日期是发哈是开放的粉红色: '2020-01-22 12:44:10',
产品标识: '7665323144642124',
设备密钥: 'cc76w454321a2674j3g65'
},
{
'IMEI(设备编号)': '86482351422131231321111',
设备名称: '上点饭',
设备类型: '电能表',
设备型号: 'ML-2100',
NB卡号: '323213',
批次号: '111133763433444441153531',
出厂编号: '215431',
出厂日期是发哈是开放的粉红色: '2020-01-22 12:44:10',
产品标识: '7665323144642124',
设备密钥: 'cc76w45432142312312312312312312a2674j3g65'
},
{
'IMEI(设备编号)': '1231321111',
设备名称: '粉丝地方撒',
设备类型: '空开',
设备型号: 'ML-200',
NB卡号: '3213213213',
批次号: '1111333344444111',
出厂编号: '21543881',
出厂日期是发哈是开放的粉红色: '2020-01-22 12:44:10',
产品标识: '766534642124',
设备密钥: 'cc76w45432142312312312a2674j3g65'
}
]
let data = XLSX.utils.json_to_sheet(demo, {
// origin: "A2", // 设置插入位置
})
// 表头的样式
data['A1'].s = {
font: {
bold: true
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
// 合并单元格 s: 起始位置, e: 结束位置, r: 行, c: 列
// data["!merges"] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 10 } }];
// 设置列宽
// data["!cols"] = [{ wch: 50 }, { wch: 20 }, { wch: 40 }];
// 1.所有表头的宽度
const headsWidth = Object.keys(demo[0]).map((value) => {
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
return parseFloat(value.toString().length * 2.1)
} else {
return parseFloat(value.toString().length * 1.1)
}
})
// console.log("所有表头的宽度:", headsWidth);
// 2.所有表体值的宽度
const rowsWidth = demo.map((item) => {
// 每行数据中值的宽度
const maxValue = Object.values(item).map((value, index) => {
let valueWidth
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
valueWidth = parseFloat(value.toString().length * 2.1)
} else {
valueWidth = parseFloat(value.toString().length * 1.1)
}
// console.log("每行数据中值的宽度:", valueWidth);
// 对比出表头和表体值的最大数
return Math.max(valueWidth, headsWidth[index])
})
// console.log("本行值中最大宽度:", maxValue);
return maxValue
})
// console.log("每行数据对比出的最大宽度:", rowsWidth);
// 3.对比每列最大值
let aotuWidth = []
rowsWidth.map((row, index) => {
let maxWidth = []
row.map((value, i) => {
if (index === 0) {
maxWidth.push({
wch: value
})
} else {
maxWidth.push({
wch: Math.max(value, aotuWidth[i].wch)
})
}
})
console.log('最大值:', maxWidth)
aotuWidth = maxWidth
})
// console.log("每列最大宽度:", aotuWidth);
// 4.给excel设置自适应宽度
data['!cols'] = aotuWidth
XLSX.utils.book_append_sheet(excel, data)
XLSX.writeFile(excel, '空开填写模板.xlsx')
}
</script>
</body>
</html>
2.下载本地文件
mubanDownLoad(){
console.log(window.location.href.split('包名')[0],window.location,'----------模板下载地址')
const a = document.createElement('a');
a.href ='文件服务器地址';
a.download ='增值税进项税额抵扣还原的申请.docx';
// console.log(a.href);
// 障眼法藏起来a标签
a.style.display = 'none';
// 将a标签追加到文档对象中
document.body.appendChild(a);
// 模拟点击了<a>标签,会触发<a>标签的href的读取,浏览器就会自动下载了
a.click();
// 一次性的,用完就删除a标签
},
3.blob文件流下载 接口添加responseType: "blob",//文件流
let obj={
...this.filterObj,
}
let currentDate = this.getNowFormatDate();//获取当前日期
downloadListCompanyGroup(obj).then((res)=>{
this.$message.success('导出成功')
let blob = new Blob([res], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" //将会被放入到blob中的数组内容的MIME类型
// type: "application/vnd.ms-excel" //将会被放入到blob中的数组内容的MIME类型
});
let objectUrl = URL.createObjectURL(blob); //生成一个url
let a = document.createElement("a");
a.href = objectUrl;
a.download = '查集团企业报表'+currentDate;
a.click();
})