element-ui 导出table中的数据并下载到本地|方法2(可设定导出的表格格式)-实测可行
1.安装依赖:
npm install xlsx xlsx-style
2.js文件的内容,直接复制粘贴到js文件即可
import * as XLSX from 'xlsx';
import XLSXStyle from 'xlsx-style';
export function createWs(data, fields, titles) {
const ws = XLSX.utils.json_to_sheet(data, {
header: fields,
});
/*const = merges: [
// 合并所需的单元格 我这个导出中没有用到 就没用 视业务而定
{ s: { r: 0, c: 2 }, e: { r: 0, c: 5 } },
{ s: { r: 0, c: 0 }, e: { r: 1, c: 0 } },
{ s: { r: 0, c: 1 }, e: { r: 1, c: 1 } },
{ s: { r: 0, c: 6 }, e: { r: 1, c: 6 } },
{ s: { r: 0, c: 7 }, e: { r: 1, c: 7 } },
{ s: { r: 0, c: 8 }, e: { r: 1, c: 8 } },
{ s: { r: 0, c: 9 }, e: { r: 1, c: 9 } },
{ s: { r: 0, c: 10 }, e: { r: 1, c: 10 } },
{ s: { r: 0, c: 11 }, e: { r: 1, c: 11 } },
{ s: { r: 0, c: 12 }, e: { r: 1, c: 12 } },
{ s: { r: 0, c: 13 }, e: { r: 1, c: 13 } },
{ s: { r: 0, c: 14 }, e: { r: 1, c: 14 } },
{ s: { r: 0, c: 15 }, e: { r: 1, c: 15 } },
{ s: { r: 0, c: 16 }, e: { r: 1, c: 16 } },
{ s: { r: 0, c: 17 }, e: { r: 1, c: 17 } },
],
ws['!merges'] = merges;*/
ws['!cols'] = [ //设置表格的宽度
{
wpx: 30,
},
{
wpx: 50,
},
{
wpx: 150,
},
{
wpx: 50,
},
{
wpx: 120,
},
{
wpx: 80,
},
{
wpx: 80,
},
{
wpx: 90,
},
{
wpx: 90,
},
{
wpx: 200,
},
{
wpx: 80,
},
{
wpx: 200,
},
];
const range = XLSX.utils.decode_range(ws['!ref']);
// 设置表格样式
for (let i = range.s.c; i < range.e.c + 1; i++) {
for (let j = range.s.r; j < range.e.r + 1; j++) {
let cell_address = {
c: i,
r: j,
};
let column = XLSX.utils.encode_cell(cell_address);
ws[column].s = {
font: {
name: '宋体',
sz: 10,
color: {
auto: 1,
},
},
border: {
color: {
auto: 1,
},
top: {
style: 'thin',
},
bottom: {
style: 'thin',
},
left: {
style: 'thin',
},
right: {
style: 'thin',
},
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: 'center',
vertical: 'center',
indent: 0,
},
};
}
}
//设置中文标题 因为后台返回的数据中属性只能以英文返回 所以需要转化为中文
for (let c = range.s.c; c <= range.e.c; c++) {
const header = XLSX.utils.encode_col(c) + '1';
ws[header].v = titles[ws[header].v];
}
return ws;
}
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
export function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {},
};
workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
// 下载这里一定要用 xlsx-style 的write() 方法才可以使导出excel表格带样式
var wbout = XLSXStyle.write(workbook, { type: 'buffer' });
var blob = new Blob([wbout], {
type: 'application/octet-stream',
}); // 字符串转ArrayBuffer
return blob;
}
export function openDownloadDialog(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent(
'click',
true,
false,
window,
0,
0,
0,
0,
0,
false,
false,
false,
false,
0,
null
);
}
aLink.dispatchEvent(event);
}
3.vue中导出并下载的函数调用
const titles = {
num: "序号",
env: "环境",
waybillNumber: "单号",
packageNumber: "数量",
feeName: "名称",
feeCode: "编码",
financeStatus: "状态",
feeAmount_fms: "财务金额",
feeAmount_pda: "PDA金额",
fee_source: "来源",
checkResult: "结果",
remark: "备注"
}
const fields = ['num', 'env', 'waybillNumber', 'packageNumber', 'feeName', 'feeCode', 'financeStatus',
'feeAmount_fms', 'feeAmount_pda', 'fee_source', 'checkResult', 'remark',
];
const ws = createWs(
this.tableData, // 后台返回的json数据
fields,
titles
)
openDownloadDialog(sheet2blob(ws), '导出数据.xlsx')
导出时可自定义文件名
其他方法参考这里: