vue前端使用xlsx和 xlsx-style 导出excel,并修改样式
vue前端使用xlsx和 xlsx-style 导出excel,修改样式
·
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
vue前端使用xlsx和 xlsx-style 下载表格 使用 xlsx-style可以合并单元格,修改样式
一、下载xlsx xlsx-style
npm i xlsx
npm i xlsx-style
二、使用步骤
1.引入库xlxs
这里注意,单纯引用xlsx会有问题,然后我这边是吧xlsx.js下载下来,然后再index.html中引用 我在页面是这个使用的,代码如下(示例):
2.引入库xlsx-style
使用xlsx-style 的时候要注意,使用的是会报错误,所以,我们需要改他的源码,下面是正确的修改方式,代码如下(示例):
# 第一步 修改nod_modules 里面xlsx-style文件夹下面dist文件夹下的cpexcel.js文件
807: var cpt = cptable;
# 第二步 修改xlsx-style文件夹下面ods.js文件
10: return require('./' + 'xlsx').utils;
12: try { return require('./' + 'xlsx').utils; }
# 第三步 修改xlsx-style文件夹下面的xlsx.js文件 替换write_ws_xml_data以下方法
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
for(R = range.s.r; R <= range.e.r; ++R) {
r = [];
rr = encode_row(R);
for(C = range.s.c; C <= range.e.c; ++C) {
ref = cols[C] + rr;
if(ws[ref] === undefined) continue;
if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
}
if(r.length > 0){
params = ({r:rr});
if(rows && rows[R]) {
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
}
o[o.length] = (writextag('row', r.join(""), params));
}
}
if(rows) for(; R < rows.length; ++R) {
if(rows && rows[R]) {
params = ({r:R+1});
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
o[o.length] = (writextag('row', "", params));
}
}
return o.join("");
}
三、页面中下载表格
1. html代码
代码如下(示例):
<template>
<div class="page">
<el-button type="primary" @click="upload">主要按钮</el-button>
<el-table :data="tableData" style="width: 100%">
<el-table-column prop="date" label="日期" width="180"> </el-table-column>
<el-table-column prop="name" label="姓名" width="180"> </el-table-column>
<el-table-column prop="address" label="地址"> </el-table-column>
</el-table>
</div>
</template>
2. js代码
import XLSXStyle from "xlsx-style";
data() {
return {
tableData: [
{
date: "2016-05-02",
name: "王小虎",
address: "上海市普陀区金沙江路 1518 弄",
},
{
date: "2016-05-04",
name: "王小虎",
address: "上海市普陀区金沙江路 1517 弄",
},
{
date: "2016-05-01",
name: "王小虎",
address: "上海市普陀区金沙江路 1519 弄",
},
{
date: "2016-05-03",
name: "王小虎",
address: "上海市普陀区金沙江路 1516 弄",
},
],
};
},
methods: {
// 下载excel
upload() {
if (this.tableData.length > 0) {
// 表头设置
const aoa = [["固收指标表"], [""]];
// const time = this.$moment(this.searchParam.vDate).format("YYYYMMDD");
console.log(this.tableData);
aoa.push(["中文指标名称", "英文指标名称", "指标值"]);
this.tableData.map((item) => {
aoa.push([item.date, item.name, item.address]);
});
const sheet = this.xlsxAddStyle(aoa);
this.openDownloadDialog(this.sheet2blob(sheet), `1.xlsx`);
} else {
this.$message.warning({
message: "没有数据无法导出",
duration: 1000,
});
}
},
/**
* 通用的打开下载对话框方法,没有测试过具体兼容性
* @param url 下载地址,也可以是一个blob对象,必选
* @param saveName 保存文件名,可选
*/
openDownloadDialog(url, saveName) {
var urlA;
if (typeof url === "object" && url instanceof Blob) {
urlA = URL.createObjectURL(url); // 创建blob地址
}
const aLink = document.createElement("a");
aLink.href = urlA;
// HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
aLink.download = saveName || "";
let 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);
},
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
sheet2blob(sheet, sheetName) {
var sheetNameS = sheetName || "sheet1";
var workbook = {
SheetNames: [sheetNameS],
Sheets: {},
};
workbook.Sheets[sheetNameS] = sheet;
// 生成excel的配置项
var wopts = {
bookType: "xlsx", // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: "binary",
};
var wbout = XLSXStyle.write(workbook, wopts);
// XLSXStyle.write(wb, { bookType: bookType, bookSST: false, type: 'binary' });
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;
},
// 表格样式的设置
xlsxAddStyle(xlsx) {
const sheet = XLSX.utils.aoa_to_sheet(xlsx);
// console.log(sheet);
const mergeArr = []; // 合并的单元格
const rowH = []; // 表格每列高度
// 单元格外侧有框线
const borderAll = {
top: { style: "thin" },
bottom: { style: "thin" },
left: { style: "thin" },
right: { style: "thin" },
};
// 单元格外侧无框线
const noBorder = {
top: { style: "" },
bottom: { style: "" },
left: { style: "" },
right: { style: "" },
};
for (const key in sheet) {
if (Object.hasOwnProperty.call(sheet, key)) {
const element = sheet[key];
if (typeof element === "object") {
const index = Number(key.slice(1)) - 1;
rowH[index] = { hpx: 24 };
element.s = {
alignment: {
horizontal: "left", // 所有单元格右对齐
vertical: "center", // 所有单元格垂直居中
},
font: {
name: "宋体",
sz: 10,
italic: false,
underline: false,
},
border: borderAll,
fill: {
fgColor: { rgb: "FFFFFFFF" },
},
};
// 指标值表格的样式
if (key.indexOf("C") > -1) {
element.s.alignment.horizontal = "right";
}
// 标题的样式
if (index === 2) {
element.s.font.bold = true;
element.s.fill.fgColor = { rgb: "FFCCFFFF" };
}
// 处理合并单元格数组 s 开始 e 结束 c 列 r行
if (element.v === "固收指标表") {
mergeArr.push({
s: { c: 0, r: 0 },
e: { c: 2, r: 1 },
});
}
}
}
}
// 表头的样式设置
sheet["A1"].s.alignment.horizontal = "center";
sheet["A1"].s.font.underline = true;
sheet["A1"].s.font.sz = 18;
sheet["A1"].s.border = noBorder;
// 单元格的列宽
sheet["!cols"] = [{ wpx: 350 }, { wpx: 450 }, { wpx: 160 }];
sheet["!rows"] = rowH;
sheet["!merges"] = mergeArr;
return sheet;
},
},
3.页面的效果
4下载下来的效果
更多推荐
已为社区贡献1条内容
所有评论(0)