1.自定义导出组件
点击查看代码
//
//TODO: 此组件功能为生成excel并下载。
//
//
//
//
//
<template>
<div></div>
</template>
<script>
import XLSX from "xlsx-js-style";
// import XLSX from "xlsx";
// 还需下载模块包 xlsx
////ps 需要修改源码：在\node_modules\xlsx-style\dist\cpexcel.js 807行 的
//var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable 不然会报错;
//https://github.com/markatil/xlsx-style
export default {
name: "xlsxdown",
methods: {
/** json:[{时间: "2020-01-22",新注册人数: "22", 在线人数: "22",累计用户数: "13"}]
* @param {Array} json
* @param {string} dataTitle
* @param {string} fileName
* @param {Object} paramStyle 样式参数
*/
downloadExl(json, dataTitle, fileName = "导出文件",paramStyle) {
if(json.length ==0) return;
const type = {
bookType: "xlsx",
bookSST: true,
type: "binary",
cellStyles: true,
};
let col = []; // 列的样式集合
let tmpdata = json[0];
var keyMap = []; //获取keys
json.unshift({});
for (var k in tmpdata) {
keyMap.push(k);
col.push({ wpx: paramStyle.w || 150});
json[0][k] = k;
}
// 是否有title，无titl从第一列开始部署数据
let titleIndex = dataTitle ? 2 : 1;
// 设置单元格样式
let borderAll = {
//单元格外侧框线
top: {
style: paramStyle.bdType||"thin",
color: {
rgb: paramStyle.bdColor||"00000000",
},
},
bottom: {
style: paramStyle.bdType||"thin",
color: {
rgb: paramStyle.bdColor||"00000000",
},
},
left: {
style: paramStyle.bdType||"thin",
color: {
rgb: paramStyle.bdColor||"00000000",
},
},
right: {
style: paramStyle.bdType||"thin",
color: {
rgb: paramStyle.bdColor||"00000000",
},
},
};
let styleCell = {
border: borderAll,
alignment: { vertical: "center", horizontal: "center" },
// font: { sz: 14, bold: false},
// fill: { bgColor: { rgb: "ffffffff" }, fgColor: { rgb: "E8E8E8" } },
};
json
.map((v, i) => {
let data = keyMap.map((k, j) => {
return Object.assign(
{},
{
v: v[k],
position:
(j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) +
(i + titleIndex),
}
);
});
return data;
})
.reduce((prev, next) => prev.concat(next))
.forEach((v, i) => {
tmpdata[v.position] = { v: v.v, s: styleCell };
});
//是否加载标题
var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
outputPos = ["A1"].concat(outputPos); //输出范围
if (dataTitle) {
tmpdata["A1"] = { v: dataTitle, s: {
border: borderAll,
alignment: { vertical: "center", horizontal: "center" },
font: { sz: 14, bold: true},
fill: {fgColor: { rgb: "E8E8E8" } },
} };
tmpdata["!merges"] = [
{
s: { c: 0, r: 0 },
e: { c: col.length-1, r: 0 },
},
];
}
tmpdata["!cols"] = col;
var tmpWB = {
SheetNames: ["mySheet"], //保存的表标题
Sheets: {
// my:tmpdata,
mySheet: Object.assign(
{},
tmpdata, //内容
{
"!ref": outputPos[0] + ":" + outputPos[outputPos.length - 1], //设置填充区域
}
),
},
};
var tmpDown = new Blob(
[
this.s2ab(
XLSX.write(
tmpWB,
{
bookType: type == undefined ? "xlsx" : type.bookType,
bookSST: false,
type: "binary",
} //这里的数据是用来定义导出的格式类型
)
),
],
{
type: "",
}
);
this.saveAs(
tmpDown,
fileName + "." + (type.bookType == "biff2" ? "xls" : type.bookType)
);
},
// 下载功能
saveAs(obj, fileName) {
var tmpa = document.createElement("a");
tmpa.download = fileName || "未命名";
// 兼容ie
if ("msSaveOrOpenBlob" in navigator) {
window.navigator.msSaveOrOpenBlob(obj, "导出文件" + ".xlsx");
} else {
tmpa.href = URL.createObjectURL(obj);
}
tmpa.click();
setTimeout(function () {
URL.revokeObjectURL(obj);
}, 100);
},
// 获取26个英文字母用来表示excel的列
getCharCol(n) {
let temCol = "",
s = "",
m = 0;
while (n > 0) {
m = (n % 26) + 1;
s = String.fromCharCode(m + 64) + s;
n = (n - m) / 26;
}
return s;
},
s2ab(s) {
if (typeof ArrayBuffer !== "undefined") {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var j = 0; j != s.length; ++j) {
view[j] = s.charCodeAt(j) & 0xff;
}
return buf;
} else {
buf = new Array(s.length);
for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
return buf;
}
},
},
};
</script>
2.安装插件依赖（xlsx、xlsx-js-style）
（1）例：npm i xlsx@0.18.5 --save
（2）例：npm i xlsx-js-style@1.2.0 -- save
3.utils封装函数
点击查看代码
export function formatDate(date, str) {
date = new Date(date)
str = str ? str : 'yyyy/MM/dd hh:mm:ss'
if (/(y+)/.test(str)) {
str = str.replace(RegExp.$1, String(date.getFullYear()).substr(4 - RegExp.$1.length))
}
let o = {
'M+': date.getMonth() + 1,
'd+': date.getDate(),
'h+': date.getHours(),
'm+': date.getMinutes(),
's+': date.getSeconds()
}
for (let k in o) {
if (new RegExp(`(${k})`).test(str)) {
let stry = String(o[k])
str = str.replace(RegExp.$1, RegExp.$1.length === 1 ? stry : ('00' + stry).substr(stry.length))
}
}
return str
}
export function excelDataFormat(json, forma) {
//处理一下导出数据
let data = []
for (let i = 0; i < json.length; i++) {
const jd = json[i]
let obj = {}
for (const key in forma) {
if (forma[key] == 'Results') {
obj[key] = jd[forma[key]] ? '达标' : '未达标'
} else if (forma[key] == 'AnswerType') {
obj[key] =
jd[forma[key]] == '1'
? '纯文本'
: jd[forma[key]] == '2'
? 'URL链接'
: jd[forma[key]] == '3'
? '图片信息'
: jd[forma[key]] == '4'
? '视频音频信息'
: '链接与描述'
} else if (forma[key] == 'I') {
obj[key] = jd[forma[key]] == '-1' ? '--' : jd[forma[key]]
} else {
obj[key] = jd[forma[key]] ? jd[forma[key]] : ''
}
}
data.push(obj)
}
return data
}
4.vue文件使用
点击查看代码
<template>
<div>
<Button @click="exportHandleClick">导出</Button>
<DownExcel ref="downExcel"></DownExcel>
</div>
</template>
<script>
import { excelDataFormat, formatDate } from '../../utils/tool'
import DownExcel from '../../components/downExcel/downExcel'
export default {
components: {
DownExcel
},
data(){
return {
tableData: [
{
id: 1,
EscapeName: '122107工作面火灾避灾路线',
DisasterType_Text: '火灾',
Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
Status: '0'
},
{
id: 2,
EscapeName: '122107工作面火灾避灾路线',
DisasterType_Text: '火灾',
Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
Status: '0'
},
{
id: 3,
EscapeName: '122107工作面火灾避灾路线',
DisasterType_Text: '火灾',
Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
Status: '1'
},
{
id: 4,
EscapeName: '122107工作面火灾避灾路线',
DisasterType_Text: '火灾',
Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
Status: '1'
}
],
}
},
methos: {
exportHandleClick() {
console.log('导出')
let foramText = {
路线名称: 'EscapeName',
灾害类型: 'DisasterType_Text',
路线坐标: 'Location',
状态: 'Status'
}
let list = excelDataFormat(this.tableData, foramText)
this.$refs.downExcel.downloadExl(list, '', formatDate(new Date(), 'yyyy-MM-dd hh:mm:ss') + '避灾路线数据', {
w: 300
})
},
}
}
</script>
