1 下载相关依赖
1 npm install xlsx@0.17.0 下载固定版本(目前这个可以)
2 npm install xlsx-style
2 在 src/utils/下新建一个 htmlToExcel.js 文件
一下代码直接放到 htmlToExcel.js 文件
import XLSX from "xlsx";
import XlsxStyle from "xlsx-style";
import { Notification, Message } from 'element-ui';
/**
* 导出表格方法
* @param id 需要导出的table的id
* @param type 表格类型(默认表头不合并) 默认 normal,合并 merge
* @param fileName 导出表格的名称
*
**/
const exportExcel = function (id, type, fileName) {
let loadingText = ".";
let notif = Notification({
message: fileName + " " + loadingText,
duration: 0,
showClose: false,
type: 'info'
});
let loadingTimer = setInterval(() => {
if (loadingText == ".") {
loadingText = ".."
} else if (loadingText == "..") {
loadingText = "..."
} else if (loadingText == "...") {
loadingText = "...."
} else if (loadingText == "....") {
loadingText = "."
}
notif.message = fileName + " " + loadingText;
}, 500)
let leftArr = [];
let rightArr = [];
let dateArr = [];
let numArr = [];
let date = new Date();
let today = date.toLocaleString().split(' ')[0];
let todayArr = today.split('/');
todayArr[1] = todayArr[1] < 10 ? '0' + todayArr[1] : todayArr[1];
todayArr[2] = todayArr[2] < 10 ? '0' + todayArr[2] : todayArr[2];
today = todayArr.join('');
var filedata
if (!id) {
//id参数不能为空
Message({
message: "参数id不能为空!",
});
} else {
var xlsxParam = { raw: true }; //这个保证表格只进行解析 不做运算
var wb = XLSX.utils.table_to_sheet(
document.querySelector(id),
xlsxParam
); //id为导出的table的id,通过参数获取
setExlStyle(wb, [leftArr, rightArr, dateArr, numArr]); //设置excel样式
if (!type) {
//type类型不能为空
Message({
message: "参数type不能为空!",
});
} else if (type == 'normal') {
// 常规表格执行-无合并单元格
filedata = sheet2blob(wb);
} else if (type == 'merge') {
// 表格有合并单元格时执行-只支持表头合并单元格
let data = addRangeBorder(wb['!merges'], wb) //合并项添加边框
filedata = sheet2blob(data)
}
// 有无合并单元格都要执行
openDownloadDialog(filedata, fileName ? fileName + ".xlsx" : today + ".xlsx");
clearInterval(loadingTimer);
notif.close();
Notification({
type: 'success',
message: fileName,
});
}
};
//将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
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;
}
//excel命名+下载
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);
}
// 设置导出excel样式
function setExlStyle(data, setArr) {
// 提取参数-第一组数组为左对齐表头name、第二组为右对齐表头name、
// 第三组为日期类型表头name、第四组为数字类型表头name
if (!setArr[0]) setArr[0] = []; // 不存在左对齐时为空
if (!setArr[1]) setArr[1] = []; // 不存在右对齐时为空
if (!setArr[2]) setArr[2] = []; // 不存在日期格式设置时为空
if (!setArr[3]) setArr[3] = []; // 不存在数字格式设置时为空
let leftArr = []//左对齐列数组
let rightArr = []//右对齐列数组
let dateArr = []//日期格式数组
let numArr = []//数字格式数组
//设置列宽度
for (var i = 0; i < 50; i++) {
data["!cols"][i] = { wpx: 180 };
}
// https://www.npmjs.com/package/xlsx-style-样式的文档地址
//设置通用样式
let styleAll = {
font: {
//字体设置
sz: 13,
bold: false,
color: {
rgb: "000000", //文字颜色设置-十六进制,不带#
},
},
alignment: {
//文字居中
horizontal: "center", //水平居中
vertical: "center", //垂直居中
wrap_text: true,
},
border: {
// 设置边框
top: { style: "thin" },
bottom: { style: "thin" },
left: { style: "thin" },
right: { style: "thin" },
},
};
// console.log(Object.keys(data));
let keys = Object.keys(data);
let headerKeys = [];
keys.forEach((ele) => {
if (ele.indexOf('1') != -1 && ele.length < 3) {
headerKeys.push(ele)
}
})
let lastKey = headerKeys[headerKeys.length - 1];
for (const key in data) {
if (key.indexOf("!") === -1 && data[key].v) {
// 给指定单元格添加特定样式-如下代码以添加背景色为例
if (key.split("")[1] === "1" && key.split("").length === 2) {
var styleS = JSON.stringify(styleAll); //必须要转一下格式,否则styleAll也会跟着styleS一起改变
styleS = JSON.parse(styleS);
styleS.fill = {
fgColor: { rgb: "EEF4FA" }, //设置背景色
};
data[key].s = styleS;
// 根据参数-匹配表头内容-组成左、右对齐、规定格式列字母数组
let keyVal = data[key].v;
if (setArr[0].length > 0) { // 左对齐
setArr[0].forEach((ele) => {
if (ele == keyVal) {
leftArr.push(key.split("")[0])
}
})
}
if (setArr[1].length > 0) { // 右对齐
setArr[1].forEach((ele) => {
if (ele == keyVal) {
rightArr.push(key.split("")[0])
}
})
}
if (setArr[2].length > 0) { // 日期格式
setArr[2].forEach((ele) => {
if (ele == keyVal) {
dateArr.push(key.split("")[0])
}
})
}
if (setArr[3].length > 0) { // 数字格式
setArr[3].forEach((ele) => {
if (ele == keyVal) {
numArr.push(key.split("")[0])
}
})
}
} else {
//其他单元格设置通用样式
data[key].s = styleAll;
let keyLet = key.split('')[0];
//设置日期格式
if (dateArr.length > 0) {
dateArr.forEach((ele) => {
if (ele == keyLet) {
//设置指定列数据类型-t表示类型,s是字符串,n是数字,d是日期类型
data[key].t = 'd'
data[key].z = 'yyyy-mm-dd HH:mm:ss'
data[key].s = styleAll;
}
})
}
//设置数字格式
if (numArr.length > 0) {
numArr.forEach((ele) => {
if (ele == keyLet) {
//设置指定列数据类型-t表示类型,s是字符串,n是数字,d是日期类型
data[key].t = 'n'
data[key].z = '0.00'
data[key].s = styleAll;
}
})
}
// 设置左对齐
if (leftArr.length > 0) {
let styleL = JSON.stringify(styleAll); //必须要转一下格式,否则styleAll也会跟着styleL一起改变
styleL = JSON.parse(styleL);
styleL.alignment = {
horizontal: "left",
};
leftArr.forEach((ele) => {
if (ele == keyLet) {
data[key].s = styleL;
}
})
}
// 设置右对齐
if (rightArr.length > 0) {
let styleR = JSON.stringify(styleAll); //必须要转一下格式,否则styleAll也会跟着styleR一起改变
styleR = JSON.parse(styleR);
styleR.alignment = {
horizontal: "right",
};
rightArr.forEach((ele) => {
if (ele == keyLet) {
data[key].s = styleR;
}
})
}
}
} else {
if (key.indexOf("!") === -1) {
if (lastKey != key) {
data[key].s = "";
data[key].s = styleAll;
}
}
}
}
}
// 存在合并单元格的表格添加边框-只对表头有效
function addRangeBorder(range, ws) {
let arr = [
"A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z",
];
range.forEach((item) => {
let startRowNumber = Number(item.s.c),
endRowNumber = Number(item.e.c);
for (let i = startRowNumber + 1; i <= endRowNumber; i++) {
const name = "A3";
ws[name] = {
s: {
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" },
},
},
};
ws[arr[i] + (Number(item.e.r) + 1)] = {
s: {
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" },
},
},
};
}
});
// console.log(ws)
return ws;
}
export default exportExcel
3 引入 xlsx-style 包的时候会报错 需要改一下依赖包
** 1 在页面中找到这两个文件修改**
node_modules/xlsx/dist、cpexcel.js 文件 代码替换
val cpt = require('./cpt' + 'able') 改为 val cpt = cptable
** node_modules/xlsx-style/dist/xlsx.js文件的 write_ws_xml_data方法替换一下代码**
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("");
}