纯前端导出xlsx文件
1.utile文件中新建downloadXlsx.js
import xlsx from "xlsx" //版本 "xlsx": "^0.14.5"
import xlsxStyle from "xlsx-style" // "xlsx-style": "^0.8.13"
export function downloadXlsx (fileName = '', dataTable = [], colsWpxList = [], titleObj = {}, titleStyleObj = {}, mergesList = []) {
//生成表格
var sheet = xlsx.utils.json_to_sheet(dataTable);
var tmpdata = xlsx.utils.sheet_add_json(sheet, dataTable, { origin: "A2" });
// 设置每个单元格宽度
tmpdata["!cols"] = colsWpxList
//给A1单元格赋值
tmpdata["A1"] = titleObj
// 设置A1标题字体样式
tmpdata["A1"].s = titleStyleObj
//单元格合并
tmpdata["!merges"] = mergesList
openDownloadDialog(sheet2blob(tmpdata), `${fileName}.xlsx`);
}
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {},
};
workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = xlsxStyle.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}); // 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
function openDownloadDialog(url, saveName) {
let href = URL.createObjectURL(url)
let a = document.createElement('a');
a.href = href;
a.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
document.body.appendChild(a)
a.click()
document.body.removeChild(a)
setTimeout(() => {
URL.revokeObjectURL(url)
}, 200);
}
2.使用
timeSlotResult (result) {
const loading = this.$loading({
lock: true,
text: 'Loading',
spinner: 'el-icon-loading',
background: 'rgba(0, 0, 0, 0.7)'
})
if (result) {
this.exportTrackData // 导出的table数据
let fieldKeys = ['currTime', 'lineName', 'lineType', 'busName', 'drvName', 'busStateDesc', 'busSpeed', 'currMile', 'staName']
let fieldLabels = ['时间', '线路', '上下行', '车牌号/自编号', '司机', '状态', '速度(km/h)', '里程(km)', '位置']
let fileName = `${this.exportTrackParams.busName}在${this.exportTrackParams.startTime}至${this.exportTrackParams.endTime}轨迹回放数据统计`
let dataTable = []
for (let i in this.exportTrackData) {
let obj = {
'时间': this.exportTrackData[i].currTime ? this.exportTrackData[i].currTime : '',
'线路': this.exportTrackData[i].lineName ? this.exportTrackData[i].lineName : '',
'上下行': this.exportTrackData[i].lineType ? this.exportTrackData[i].lineType : '',
'车牌号/自编号': this.exportTrackData[i].busName ? this.exportTrackData[i].busName : '',
'司机': this.exportTrackData[i].drvName ? this.exportTrackData[i].drvName : '',
'状态': this.exportTrackData[i].busStateDesc ? this.exportTrackData[i].busStateDesc : '',
'速度(km/h)': this.exportTrackData[i].busSpeed ? this.exportTrackData[i].busSpeed : '',
'里程(km)': this.exportTrackData[i].currMile ? this.exportTrackData[i].currMile : '',
'位置': this.exportTrackData[i].staName ? this.exportTrackData[i].staName : '',
}
dataTable.push(obj);
}
let colsWpxList = [
{ wpx: 150 },
{ wpx: 120 },
{ wpx: 120 },
{ wpx: 150 },
{ wpx: 120 },
{ wpx: 120 },
{ wpx: 120 },
{ wpx: 120 },
{ wpx: 120 },
]
let titleObj = {
t: "s",
v: fileName
}
let titleStyleObj = {
font: {
name: '宋体',
sz: 20,
bold: true,
},
alignment: {
horizontal: "center",
vertical: "center",
wrap_text: true
},
}
let mergesList = [
{
//合并单元格 index都从0开始
s: { // start 开始
c: 0, //开始列
r: 0 //开始行
},
e: { // end 结束
c: fieldKeys.length - 1, //结束列
r: 0 //结束行
}
}
]
downloadXlsx (fileName, dataTable, colsWpxList, titleObj, titleStyleObj, mergesList)
}
loading.close()
}
注意: 需要在webpack配置 ① 或者vue.config.js中配置 ②
①
externals: {
‘./cptable’: ‘var cptable’
}
②
configureWebpack: {
externals: [{
‘./cptable’: ‘var cptable’
}]
},