超详细纯前端导出excel并完成各种样式的修改（xlsx-style）
一杠正在上传…重新上传取消
2020年12月08日 17:53 · 阅读 6247
一、前言
最近做的项目涉及到了excel的导出，在这块真的花了很多的时间，起初需求是不需要样式层面的修改的，所以选择了XLSX.JS，没有过多的去考虑样式的修改。但是随着项目进行，客户又提出了需要按照格式修改样式的需求，故而只能去查找相关修改excel样式的资料，本想直接用XLSX.js，但显然不行XLSX.js的基础版本只有宽高、合并单元格等比较基础的修改，要更加复杂样式的修改得升级pro版本，怎么说，在这上面花钱并不是咱的风格，在查找众多资料后，发现能导出xlsx文件且能满足需求中的样式修改的插件只有XLSX-STYLE了(或许还有合适的工具但我不知道的吧)，但是XLSX-STYLE貌似已经许久不维护了，所以在上手使用的过程中还是有许多的问题出现。
二、梳理需求
在我的项目中，我需要导出页面中antd的表格，同时还需要对数据进行处理以达到统计的效果，导出的excel内容要根据需求来定，样式上需要修改的有宽度、高度、背景色、字体、字体大小、合并单元格、单元格边框、字体颜色、字体加粗居中等。同时需要兼容到ie11，根据需求的优先级，我们来排排序
- 1、实现excel导出
- 2、兼容ie11
- 3、统计数据
- 4、修改宽高、合并单元格
- 5、修改其它样式 总结起来的内容看着不多，但其实实现起来并不简单，导出和兼容ie11的功能在之前就已经完善，待会我们会细讲，而重点在于统计数据，也就是说，我们并不是要1比1的还原antd表格，我们要的是自己定义数据，进行整理计算，然后再进行导出，所以我们得将所有数据进行整合然后计算成xlsx接受的二维数组，当然，XLSX也支持直接通过table标签导出，这里我们没用到，暂时不提。
三、前期准备
- 1、
npm install xlsx-style将插件导入项目 - 2、
import XLSX from 'xlsx-style'在组件中引入XLSX - 3、
This relative module was not found:./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js完美报错 - 4、找到在\node_modules\xlsx-style\dist\cpexcel.js 807行的
var cpt = require('./cpt' + 'able');更换成var cpt = cptable;保存继续。 - 5、
Error: Can't resolve 'fs' in 'E:\\xlsx-style'完美报错 - 6、这个错误应该来源于在编译过程中xlsx源码中使用了node的fs模块，fs模块是服务器端模块，webpack虽然可以将fs代码加载到前端，但是浏览器是无法解释并且运行的，所以说，要修复这个错误，要么想办法修改代码使调用fs的操作发生在服务端，要么想办法安装一个能使浏览器识别到fs的浏览器插件，使fs能在浏览器被识别和运行，在我们需求中只涉及到了excel的导出，所以应该使用不到fs模块，因此我们选择在配置文件加上相关配置项使fs不被使用，所以我们在webpack.config.js的配置中加上
node: { fs: 'empty', },大家的项目脚手架可能都不一样，配置该放哪可以自己尝试，这里就只能告诉大家在最顶层的配置项下面。 - 7、出现的错误修复完毕，开始针对性的对不完整的xlsx-style进行扩展，首先第一点，找到xlsx-style目录下的xlsx.js，找到defaultCellStyle，由命名可知，defaultCellStyle是所有单元格的默认样式，我们把它改成我们所需要的整体样式，这样可以大大的减少我们后期修改样式所花费的时间。
- 8、扩展修改行高的功能：xlsx-style不包含修改行高的功能，这里我们要扩展它，当然不是自己写，因为xlsx-style来源于xlsxjs，所以二者源码极为相似，我们npm安装xlsxjs，然后找到行高修改相应方法，复制扩展到xlsx-style即可，具体如下
// 本部分代码来源CSDN
// xlsx-style版本0.8.13
// xlsx版本0.14.1
//这是xlsx-style文件中的xlsx.js的需要修改的代码，是从xlsx文件夹中的xlsx.js中复制出来的
// write_ws_xml_data找到这个方法名字，全部替换
// 把xlsx中能修改高度的代码复制到xlsx-style中
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=0, C=0, rows = ws['!rows'];
var dense = Array.isArray(ws);
var params = ({r:rr}), row, height = -1;
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;
var _cell = dense ? (ws[R]||[])[C]: ws[ref];
if(_cell === undefined) continue;
if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
}
if(r.length > 0 || (rows && rows[R])) {
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("");
}
复制代码- 9、如果找不到defaultCellStyle，那么可能时没有对应的有二维数组转工作表对象的方法，从官方文档可以copy到相应的代码块，然后扩展到XLSX.Util上，如下图
官方代码 为了减少大家去查找的时间，在这里我贴出来：
// 从json转化为sheet，xslx中没有aoaToSheet的方法，该方法摘自官方test
function aoa_to_sheet(data){
var defaultCellStyle = {
font: { name: "Meiryo UI", sz: 11, color: { auto: 1 } },
border: {
top: {
style:'thin',
color: {
auto: 1
}
},
left: {
style:'thin',
color: {
auto: 1
}
},
right: {
style:'thin',
color: {
auto: 1
}
},
bottom: {
style:'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
};
function dateNum(date){
let year = date.getFullYear();
let month = (date.getMonth()+1)>9?date.getMonth()+1:'0'+(date.getMonth()+1);
let day = date.getDate()>9?date.getDate():'0'+date.getDate();
return year+'/'+month+'/'+day;
};
const ws = {};
const range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(let R = 0; R !== data.length; ++R) {
for(let C = 0; C !== data[R].length; ++C) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
/// 这里生成cell的时候，使用上面定义的默认样式
const cell = {v: data[R][C], s: defaultCellStyle};
const cell_ref = XLSX.utils.encode_cell({c:C,r:R});
/* TEST: proper cell types and value handling */
if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = XLSX.SSF._table[14];
cell.v = dateNum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
/* TEST: proper range */
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
};
复制代码- 10、扩展根据dom节点生成excel的功能：这个功能虽然项目中没用到，但是还是顺手调整好吧，找到xlsxjs的utils的table_to_sheet()方法，复制到xlsx-style中，并把其它相关引用的函数变量等都移植到xlsx-style中，直至能正常运行，稍微做个demo尝试一下，正常运行，完美。
- 11、扩展根据二维数组生成excel的功能：同上，在原xlsx内找aoa_to_sheet方法进行移植。
四、导出excel以及兼容ie11
- 1、使用xlsx导出excel流程：根据二维数组（aoa_to_sheet）或者dom节点(table_to_sheet)将数据转换成excel sheet数据->将sheet数据转换成二进制大对象blob->URL.createObjectURL(blob)创建url->其它:创建a标签，初始化a标签src属性，触发点击事件,ie11：window.navigator.msSaveOrOpenBlob.
- 2、兼容ie11,如上所述，最后一步导出时方式不相同，以下是整个导出过程code
export default function downLoadExcel(data, type, config, ele) {
console.log(config, 'excel配置参数');
var blob = IEsheet2blob(ws);
if (IEVersion() !== 11) {
openDownloadXLSXDialog(blob, `${type}.xlsx`);
} else {
window.navigator.msSaveOrOpenBlob(blob, `${type}.xlsx`);
}
}
function dislodgeLetter(str) {
var result;
var reg = /[a-zA-Z]+/; //[a-zA-Z]表示bai匹配字母，dug表示全局匹配
while (result = str.match(reg)) { //判断str.match(reg)是否没有字母了
str = str.replace(result[0], ''); //替换掉字母 result[0] 是 str.match(reg)匹配到的字母
}
return str;
}
function IEsheet2blob(sheet, sheetName) {
try {
new Uint8Array([1, 2]).slice(0, 2);
} catch (e) {
//IE或有些浏览器不支持Uint8Array.slice()方法。改成使用Array.slice()方法
Uint8Array.prototype.slice = Array.prototype.slice;
}
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 = XLSX.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 getDefaultStyle() {
let defaultStyle = {
fill: {
fgColor: {
rgb: ''
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
rgb: ''
},
bold: true
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
};
return defaultStyle;
}
function IEVersion() {
var userAgent = navigator.userAgent; //取得浏览器的userAgent字符串
var isIE = userAgent.indexOf("compatible") > -1 && userAgent.indexOf("MSIE") > -1; //判断是否IE<11浏览器
var isEdge = userAgent.indexOf("Edge") > -1 && !isIE; //判断是否IE的Edge浏览器
var isIE11 = userAgent.indexOf('Trident') > -1 && userAgent.indexOf("rv:11.0") > -1;
if (isIE) {
var reIE = new RegExp("MSIE (\\d+\\.\\d+);");
reIE.test(userAgent);
var fIEVersion = parseFloat(RegExp["$1"]);
if (fIEVersion == 7) {
return 7;
} else if (fIEVersion == 8) {
return 8;
} else if (fIEVersion == 9) {
return 9;
} else if (fIEVersion == 10) {
return 10;
} else {
return 6; //IE版本<=7
}
} else if (isEdge) {
return 'edge'; //edge
} else if (isIE11) {
return 11; //IE11
} else {
return -1; //不是ie浏览器
}
}
function openDownloadXLSXDialog(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);
}
复制代码五、xlsx的认识和使用
- 1、worksheet object工作表对象： 可以理解为存储excel数据的对象，里面每个不包含！的属性都代表了一个单元格，我们修改样式其实就是对这里的单元格做操作。
- 2、单元格对象，如上所述，每个单元格为一个单独的对象存储在worksheet内，worksheet属性名为单元格坐标，也就是在excel表显示的单元格位置，遵循excel的格式：'A1','AB2'等，每个单元格对象的格式都为
{t:'n',s:{},v:'值'}，这里t为值的类型，n代表数字，那么很容易猜到，t的值s代表字符串；s为样式对象，这里有许多的字段可以对单元格进行样式配置，我们后面再说，v就是单元格的值。 - 3、!col: worksheet对象内一个特殊属性，数据格式为数组如：
[{wpx:100},{wpx:100},{wpx:80}],设置!col的值可以修改列宽，这里wpx代表的就是以px为单位的宽度，当然还有其它的书写方式如wch等 - 4、!merge: 合并单元格所用配置属性，数据格式为数组如：
[{s:{r:0,c:2},e:{r:0,c:6}},{s:{r:1,c:3},e:{r:8,c:3}}],s为开始单元格坐标，r行，c列，e为结束单元格坐标，这里的例子代表合并第0行第2到6个单元格和第三列第1到8个单元格。 - 5、workbook： 存储工作表的对象，数据格式为
{sheetName:[],sheets:{}},sheetName存储工作表名称，sheets存储工作表对象，字段名为工作表名称，通过对workbook的处理可以导出多表文件。 - 6、单元格范围转化
//编码行号
XLSX.utils.encode_row(2); //"3"
//解码行号
XLSX.utils.decode_row("2"); //1
//编码列标
XLSX.utils.encode_col(2); //"C"
//解码列标
XLSX.utils.decode_col("A"); //0
//编码单元格
XLSX.utils.encode_cell({ c: 1, r: 1 }); //"B2"
//解码单元格
XLSX.utils.decode_cell("B1"); //{c: 1, r: 0}
//编码单元格范围
XLSX.utils.encode_range({ s: { c: 1, r: 0 }, e: { c: 2, r: 8 } }); //"B1:C9"
//解码单元格范围
XLSX.utils.decode_range("B1:C9"); //{s:{c: 1, r: 0},e: {c: 2, r: 8}}
复制代码- 7、单元格样式，这里列举常用的样式配置
s:{
font: { //字体相关样式
name: '宋体', //字体类型
sz: 11, //字体大小
color: { rgb: '' }, //字体颜色
bold: true, //是否加粗
},
fill: { //背景相关样式
bgColor: { rgb: '' }, //背景色，填充图案背景
fgColor: { rgb: '' }, //前背景色，项目中修改单元格颜色用这项
},
border: [{ //边框相关样式
},{
},{
},{
}],
alignment: { // 对齐方式相关样式
vertical: 'center', //垂直对齐方式
horizontal: 'center', //水平对齐方式
wrapText: true, //自动换行
}
}
//更详细的配置参数如下图，图片来源知乎
复制代码
- 8、其它关于打印的配置或者其它功能本人项目不涉及大家可以自行了解，还有生成blob相关参数，wpot相关参数，大家可以根据代码和需求自行查找资料
六、样式修改及相关封装
- 1、分析项目修改样式规律：这部分处理是为了让代码可以有个合理的格式，我们组织好一个特定的样式配置数据格式，然后再通过函数处理抓换成sheet接受的样式配置信息，这样，本次项目的代码在后续任何项目都可以按照这个数据格式进行配置啦，而要设计数据格式，我们要考虑到所有项目中常用样式修改方案，首先第一点,表格的头部和底部肯定是最常见的修改对象，而且往往是要对其背景色、字体色、字体是否加粗、字体大小进行修改，所以我们封装一个config对象，对象内有merge属性用于设置单元格合并，size属性用于设置行高和列宽（row行高，col列宽），然后再设置个字段myStyle用于存储我们要自定义的样式信息，第二点,对头部做了处理后自然要对底部进行处理，底部处理和头部大同小异，不过要注意底部需要标明行数，否则无法定位底行位置；第三点，针对特定行进行的处理，有时候需要对某一整行或者很多行进行特殊处理，所以需要一个针对特定行修改样式的配置；有行自然有列，第四点，和针对行的修改一样，定义一个属性用于特定列的修改（colCells），第五点，特殊表头的样式处理，表头不一定是统一的样式，所以需要有对对应表头进行处理的配置；第六点，高亮处理，满足条件的单元格可能需要高亮，这里预留一个高亮的配置；暂时的最后一点，通过table_to_sheet的操作合并的单元格可能会失去边框，需要一个补上边框的配置信息。
- 2、以上所说综合起来如下图
- 3、我所做的封装和处理还是很粗糙的，但是基本满足项目的使用了，大家如果有更优的处理方式，欢迎交流讨论，下面的code是我针对我的数据格式处理sheet的逻辑代码，放在生成worksheet之后，生成blob之前。
if (config.merge.length != 0) {
ws['!merges'] = config.merge;
}
ws['!cols'] = config.size.cols;
if (config.myStyle.all) { //作用在所有单元格的样式，必须在最顶层，然后某些特殊样式在后面的操作中覆盖基本样式
Object.keys(ws).forEach((item, index) => {
if (ws[item].t) {
ws[item].s = config.myStyle.all;
}
});
}
if (config.myStyle.headerColor) {
if (config.myStyle.headerLine) {
let line = config.myStyle.headerLine;
let p = /^[A-Z]{1}[A-Z]$/;
Object.keys(ws).forEach((item, index) => {
for (let i = 1; i <= line; i++) {
if (item.replace(i, '').length == 1 || (p.test(item.replace(i, '')))) {
let myStyle = getDefaultStyle();
myStyle.fill.fgColor.rgb = config.myStyle.headerColor;
myStyle.font.color.rgb = config.myStyle.headerFontColor;
ws[item].s = myStyle;
}
}
});
}
}
if (config.myStyle.specialCol) {
config.myStyle.specialCol.forEach((item, index) => {
item.col.forEach((item1, index1) => {
Object.keys(ws).forEach((item2, index2) => {
if (item.expect && item.s) {
if (item2.includes(item1) && !item.expect.includes(item2)) {
ws[item2].s = item.s;
}
}
if (item.t) {
if (item2.includes(item1) && item2.t) {
ws[item2].t = item.t;
}
}
});
});
});
}
if (config.myStyle.bottomColor) {
if (config.myStyle.rowCount) {
Object.keys(ws).forEach((item, index) => {
if (item.indexOf(config.myStyle.rowCount) != -1) {
let myStyle1 = getDefaultStyle();
myStyle1.fill.fgColor.rgb = config.myStyle.bottomColor;
ws[item].s = myStyle1;
}
})
}
}
if (config.myStyle.colCells) {
Object.keys(ws).forEach((item, index) => {
if (item.split('')[0] === config.myStyle.colCells.col && item !== 'C1' && item !== 'C2') {
ws[item].s = config.myStyle.colCells.s;
}
})
}
if (config.myStyle.mergeBorder) { //对导出合并单元格无边框的处理
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"]
let range = config.myStyle.mergeBorder;
range.forEach((item, index) => {
if (item.s.c == item.e.c) { //行相等,横向合并
let star = item.s.r;
let end = item.e.r;
for (let i = star + 1; i <= end; i++) {
ws[arr[i] + (Number(item.s.c) + 1)] = {
s: ws[arr[star] + (Number(item.s.c) + 1)].s
}
}
} else { //列相等，纵向合并
let star = item.s.c;
let end = item.e.c;
for (let i = star + 1; i <= end; i++) {
ws[arr[item.s.r] + (i + 1)] = {
s: ws[arr[item.s.r] + (star + 1)].s
}
}
}
});
}
if (config.myStyle.specialHeader) {
config.myStyle.specialHeader.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
if (item.cells.includes(item1)) {
ws[item1].s.fill = {
fgColor: {
rgb: item.rgb
}
};
if (item.color) {
ws[item1].s.font.color = {
rgb: item.color
};
}
}
});
});
}
if (config.myStyle.heightLightColor) {
Object.keys(ws).forEach((item, index) => {
if (ws[item].t === 's' && ws[item].v && ws[item].v.includes('%') && !item.includes(config.myStyle.rowCount)) {
if (Number(ws[item].v.replace('%', '')) < 100) {
ws[item].s = {
fill: {
fgColor: {
rgb: config.myStyle.heightLightColor
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
auto: 1
}
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
}
}
}
});
}
if (config.myStyle.rowCells) {
config.myStyle.rowCells.row.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
let num = Number(dislodgeLetter(item1));
if (num === item) {
ws[item1].s = config.myStyle.rowCells.s;
}
});
});
}
Object.keys(ws).forEach((item, index) => { //对所有的空数据进行处理，不让其显示null
if (ws[item].t === 's' && !ws[item].v) {
ws[item].v = '-';
}
});
复制代码七、导出数据处理难点
- 1、导出的excel弹窗提示文件错误，如下图：
不慌，出向这样的的提示无非两种原因，一是数据错乱，二是配置合并单元格的信息有误，数据错乱很可能是因为数据类型错误，而合并单元格信息则多是因为起始单元格坐标大于结束单元格坐标，或者是起始坐标和结束坐标一样，总之不是很严重的问题，需要仔细检查样式配置信息，信息无误自然就不会再提示。
- 2、合并单元格：这是我纠结了很久的点，因为部分表格导出没有小计，我的项目导出的是公司内各个部门下的成员信息，也就是需要按部门进行合并，同时合并后每个部门下面有一行分类，对，就是封面图那样，那么怎么才能实现呢，考虑再三，最后我只能在获取到整个部门的人员数据后做一次汇总然后给总数据添加一行，同时用另一个数组存储部门人员数，用于后续的单元格合并计算。以下是我小计的计算code：
const countFormatedData = (formatedData, text) => {
//formatedData是整个部门的数据，text是新添加的汇总行的名称在当前项目是小计或者合计
if (formatedData.length != 0) {
let result1 = {
'1月': 0,
'2月': 0,
'3月': 0,
'4月': 0,
'5月': 0,
'6月': 0,
'7月': 0,
'8月': 0,
'9月': 0,
'10月': 0,
'11月': 0,
'12月': 0,
[T('上期实绩')]: 0,
[T('下期实绩')]: 0,
[T('年度实绩')]: 0,
[T('上期目标')]: 0,
[T('下期目标')]: 0,
[T('年度目标')]: 0,
['共同1月']: 0,
['共同2月']: 0,
['共同3月']: 0,
['共同4月']: 0,
['共同5月']: 0,
['共同6月']: 0,
['共同7月']: 0,
['共同8月']: 0,
['共同9月']: 0,
['共同10月']: 0,
['共同11月']: 0,
['共同12月']: 0,
}
let result = formatedData.reduce((init, next) => {
if (next['部署'] === T('小计')) {
return init;
} else {
return {
'1月': init['1月'] + next['1月'],
'2月': init['2月'] + next['2月'],
'3月': init['3月'] + next['3月'],
'4月': init['4月'] + next['4月'],
'5月': init['5月'] + next['5月'],
'6月': init['6月'] + next['6月'],
'7月': init['7月'] + next['7月'],
'8月': init['8月'] + next['8月'],
'9月': init['9月'] + next['9月'],
'10月': init['10月'] + next['10月'],
'11月': init['11月'] + next['11月'],
'12月': init['12月'] + next['12月'],
[T('上期实绩')]: init[T('上期实绩')] + next[T('上期实绩')],
[T('下期实绩')]: init[T('下期实绩')] + next[T('下期实绩')],
[T('年度实绩')]: init[T('年度实绩')] + next[T('年度实绩')],
[T('上期目标')]: init[T('上期目标')] + next[T('上期目标')],
[T('下期目标')]: init[T('下期目标')] + next[T('下期目标')],
[T('年度目标')]: init[T('年度目标')] + next[T('年度目标')],
['共同1月']: init['共同1月'] + next['共同1月'],
['共同2月']: init['共同2月'] + next['共同2月'],
['共同3月']: init['共同3月'] + next['共同3月'],
['共同4月']: init['共同4月'] + next['共同4月'],
['共同5月']: init['共同5月'] + next['共同5月'],
['共同6月']: init['共同6月'] + next['共同6月'],
['共同7月']: init['共同7月'] + next['共同7月'],
['共同8月']: init['共同8月'] + next['共同8月'],
['共同9月']: init['共同9月'] + next['共同9月'],
['共同10月']: init['共同10月'] + next['共同10月'],
['共同11月']: init['共同11月'] + next['共同11月'],
['共同12月']: init['共同12月'] + next['共同12月'],
}
}
}, result1);
result[T('上期达成率')] = result[T('上期目标')] ? `${(result[T('上期实绩')] / result[T('上期目标')] * 100).toFixed(1)}%` : '0%';
result[T('下期达成率')] = result[T('下期目标')] ? `${(result[T('下期实绩')] / result[T('下期目标')] * 100).toFixed(1)}%` : '0%';
result[T('年度达成率')] = result[T('年度目标')] ? `${(result[T('年度实绩')] / result[T('年度目标')] * 100).toFixed(1)}%` : '0%';
result['NO.'] = text;
result['部署'] = text;
result[T("姓名")] = text;
formatedData.push(result)
}
return formatedData;
}
复制代码利用reduce计算累加是我能想到最好的办法了，还是老样子，欢迎大家探讨更优的写法。
以下是我合并单元格配置的计算逻辑code：
exportData.forEach((item, index) => { //遍历找出合并需要的下标
//小计和合计需要合并，原因请看封面图
if (item[1] === T('总计') || item[1] === T('小计')) {
merge.push({
s: {
r: index,
c: 0
},
e: {
r: index,
c: 2
}
});
if (item[1] === T('小计')) {
//config是我们之前已经介绍了的配置信息
config.myStyle.rowCells.row.push(index + 1);
}
}
});
deptCount.forEach((item, index) => { //这里是重头戏，不进行一定的考量很容易计算错误
if (index == 0) {
merge.push({
s: {
r: 2,
c: 1
},
e: {
r: item + 1,
c: 1
}
});
} else {
let count = 0;
for (let i = 0; i < index; i++) {
count = count + deptCount[i];
}
if (item > 1) {
merge.push({
s: {
r: count + index + 2,
c: 1
},
e: {
r: count + item + index + 1,
c: 1
}
});
}
}
})
复制代码项目中还有一个表格是不需要小计的，那自然比需要小计的简单些，这里就不再介绍了。
- 3、数据去重：我们都知道，一旦涉及到统计，数据去重就不得不考虑，因为任何一个多余的数据，都会引起统计汇总数据的出错，在这里不做更多的细节处理，到时候出错客户投诉可又要被上级批评；而去重一般来说最简单的是数组的去重，但是实际项目中不可能这么简单，往往要涉及到对象数组的去重，而且去重的条件还不一定是完全相等，可能互相包含就要去掉前一个或者后一个，也有可能不去掉一个，而是将二者之间包括二者的所有元素去除，这时候就有点头大了吧。下面是我用到的几种去重方式，欢迎大家参考，更欢迎指出不足。 第一种,将需要去重的推进数组缓存起来，然后再用filter过滤掉数组内的元素：
export function removeDuplicate(data,field) {
let newData = data //去重
let shouldDelete = []; //将所有需要删除的节点放到数组，根据title判断删除节点
for (let i = 0; i < newData.length; i++) {
for (let j = i + 1; j < newData.length; j++) {
if (newData[i][field] == newData[j][field]) {
shouldDelete.push({[field]:newData[i][field],index:i});
}
}
}
console.log(shouldDelete);
if (shouldDelete.length != 0) {
newData = newData.filter((item,index) => {
let result = true;
shouldDelete.forEach((item1) => {
if (item[field] === item1[field]&&index==item1.index) {
result = false;
}
});
return result;
});
}
return newData;
}
复制代码第二种,和第一种不一样的地方在于，它要判断元素的userName字段是否包含，包含的话去掉两个元素之间的元素和前面那个元素，具体看代码：
export function duplicateRemove(flowData) {
let newFlowInfo = flowData //去重
let shouldDelete = []; //将所有需要删除的节点放到数组，根据title判断删除节点
for (let i = 0; i < newFlowInfo.length; i++) {
for (let j = i + 1; j < newFlowInfo.length; j++) {
let name = newFlowInfo[i].userName.split('、').concat(newFlowInfo[j].userName.split('、'));
let newName = Array.from(new Set(name));
if (name.length != newName.length //有重复项
&&
newFlowInfo[i].code && newFlowInfo[i].code !== 'Committee' &&
newFlowInfo[j].code && newFlowInfo[j].code !== 'Committee') {
for (let k = i; k < j; k++) {
shouldDelete.push(newFlowInfo[k]);
}
}
}
}
console.log(shouldDelete);
if (shouldDelete.length != 0) {
newFlowInfo = newFlowInfo.filter((item) => {
let result = true;
shouldDelete.forEach((item1) => {
if (item.title === item1.title) {
result = false;
}
});
return result;
});
}
return newFlowInfo;
}
复制代码第三种，这种去除UPN相同的项，最简洁：
//结果去重
let data = {};
result.data.data = result.data.data.reduce((cur, next) => {
data[next.UPN] ? "" : data[next.UPN] = true && cur.push(next);
return cur;
}, []);
复制代码以上是我在项目中用到的去重方式，当然还有其它方式，这里单独拿几个出来展示一下。
八、总结
excel导出的功能很多前端项目需要用到，奈何市面上很少能符合大部分需求的工具，没办法我们只能自己折腾，所幸折腾来折腾去最终还是满足要求了，当前的处理虽然还很粗糙，不过继续完善相信这次项目用到的东西在以后的需求也可以再次使用，我还得花多点时间处理封装一个更用起来更加便捷的excel导出功能模块，大家有什么建议或者疑问记得评论区见，晚点我会将我书写的excel导出代码和处理好的xlsx-style放到github上供大家使用和完善，今天就到这里啦(^_^)
项目资料：github.com/wannigebang…
更新： 改进后的xlsx-style发布到npm上啦！安装命令npm install xlsx-style-medalsoft
九、更新
最近对功能模块代码做了整理，添加了类型判断以及多表导出，不过因为改动较多 ，代码变得有点复杂了，逻辑肯定还有优化空间，以后有机会再做一次整理吧，先将最新的放上来先
import XLSX from 'xlsx-style';
// npm install xlsx-style-medalsoft
interface IStyle {
font?: any;
fill?: any;
border?: any;
alignment?: any;
}
interface ICell {
r: number;
c: number;
}
interface IMerge {
s: ICell;
e: ICell
}
interface ICol {
wpx?: number;
wch?: number;
}
interface ISize {
cols: ICol[];
rows: any[];
}
interface ISpecialHeader {
cells: string[];
rgb: string;
color?: string;
}
interface ISpecialCol {
col: string[];
rgb: string;
expect: string[];
s: IStyle;
t?: string;
}
interface IRowCells {
row: string[];
s: IStyle;
}
interface IMyStyle {
all?: IStyle;
headerColor?: string;
headerFontColor?: string;
headerLine?: number;
bottomColor?: string;
rowCount?: number;
heightLightColor?: string;
rowCells?: IRowCells;
specialHeader?: ISpecialHeader[];
specialCol?: ISpecialCol[];
mergeBorder?: any[];
}
interface IConfig {
merge?: IMerge[];
size?: ISize;
myStyle?: IMyStyle;
}
/*
* @function 导出excel的方法
* @param data table节点||二维数组
* @param type 导出excel文件名
* @param config 样式配置参数 { all:{all样式的基本格式请参考xlsx-style内xlsx.js文件内的defaultCellStyle}
* merge:[s:{r:开始单元格行坐标,c:开始单元格列坐标},e:{r:结束单元格行坐标,c:结束单元格列坐标}]
* size:{col:[{wpx:800}],row:[{hpx:800}]},
* headerColor: 八位表格头部背景颜色, headerFontColor: 八位表格头部字体颜色,bottomColor:八位表格底部背景色,
* rowCount:表格底部行数, specialHeader: [{cells:[特殊单元格坐标],rgb:特殊单元格背景色}],
* sepcialCol:[{col:[特殊列的列坐标],rgb:特殊列的单元格背景色,expect:[特殊列中不需要修改的单元格坐标],s:{特殊列的单元格样式}}
* }]
*
* 导出流程 table节点|二维数组->worksheet工作表对象->workboo工作簿对象->bolb对象->uri资源链接->a标签下载或者调用navigator API下载
* 每个worksheet都是由!ref、!merge、!col以及属性名为单元格坐标（A1,B2等）值为{v:单元格值,s:单元格样式,t:单元格值类型}的属性组成
*/
export function downLoadExcel(exportElement: [][] | any, fileName: string, config: IConfig|IConfig[], multiSheet?: boolean, sheetNames?: string[]) {
let ws;
let wb = [];
if (multiSheet) {
exportElement.forEach((item, index) => {
wb.push(getSheetWithMyStyle(item, config[index]));
});
} else {
if(!Array.isArray(config)){
ws = getSheetWithMyStyle(exportElement, config);
}
}
console.log(ws, 'worksheet数据');
if (ws) {
downLoad([ws], fileName,sheetNames);
} else {
downLoad(wb, fileName, sheetNames);
}
}
export function downLoad(ws, fileName: string, sheetNames?: string[]) {
var blob = IEsheet2blob(ws, sheetNames);
if (IEVersion() !== 11) { //判断ie版本
openDownloadXLSXDialog(blob, `${fileName}.xlsx`);
} else {
window.navigator.msSaveOrOpenBlob(blob, `${fileName}.xlsx`);
}
}
export function getWorkSheetElement(exportElement: [][] | any) {
let ifIsArray = Array.isArray(exportElement);
let ws = ifIsArray ? XLSX.utils.aoa_to_sheet(exportElement) : XLSX.utils.table_to_sheet(exportElement);
return ws;
}
export function getSheetWithMyStyle(exportElement: [][] | any, config: IConfig, callback?: Function) {
//样式处理函数,返回ws对象，如果要对ws对象进行自定义的修改，可以单独调用此函数获得ws对象进行修改
try {
let ws = getWorkSheetElement(exportElement);
console.log(config, 'excel配置参数');
//根据data类型选择worksheet对象生成方式
if (config.merge) {
ws['!merges'] = config.merge;
}
ws['!cols'] = config.size.cols;
//all样式的基本格式请参考xlsx-style内xlsx.js文件内的defaultCellStyle
if (config.myStyle) {
if (config.myStyle.all) { //作用在所有单元格的样式，必须在最顶层，然后某些特殊样式在后面的操作中覆盖基本样式
Object.keys(ws).forEach((item, index) => {
if (ws[item].t) {
ws[item].s = config.myStyle.all;
}
});
}
if (config.myStyle.headerColor) {
if (config.myStyle.headerLine) {
let line = config.myStyle.headerLine;
let p = /^[A-Z]{1}[A-Z]$/;
Object.keys(ws).forEach((item, index) => {
for (let i = 1; i <= line; i++) {
if (item.replace(i.toString(), '').length == 1 || (p.test(item.replace(i.toString(), '')))) {
let headerStyle = getDefaultStyle();
headerStyle.fill.fgColor.rgb = config.myStyle.headerColor;
headerStyle.font.color.rgb = config.myStyle.headerFontColor;
ws[item].s = headerStyle;
}
}
});
}
}
if (config.myStyle.specialCol) {
config.myStyle.specialCol.forEach((item, index) => {
item.col.forEach((item1, index1) => {
Object.keys(ws).forEach((item2, index2) => {
if (item.expect && item.s) {
if (item2.includes(item1) && !item.expect.includes(item2)) {
ws[item2].s = item.s;
}
}
if (item.t) {
if (item2.includes(item1) && ws[item2].t) {
ws[item2].t = item.t;
}
}
});
});
});
}
if (config.myStyle.bottomColor) {
if (config.myStyle.rowCount) {
Object.keys(ws).forEach((item, index) => {
if (item.indexOf((config.myStyle.rowCount).toString()) != -1) {
let bottomStyle = getDefaultStyle();
bottomStyle.fill.fgColor.rgb = config.myStyle.bottomColor;
ws[item].s = bottomStyle;
}
})
}
}
config.myStyle?.specialHeader?.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
if (item.cells.includes(item1)) {
ws[item1].s.fill = {
fgColor: {
rgb: item.rgb
}
};
if (item.color) {
ws[item1].s.font.color = {
rgb: item.color
};
}
}
});
});
if (config.myStyle.heightLightColor) {
Object.keys(ws).forEach((item, index) => {
if (ws[item].t === 's' && ws[item].v && ws[item].v.includes('%') && !item.includes((config.myStyle.rowCount).toString())) {
if (Number(ws[item].v.replace('%', '')) < 100) {
ws[item].s = {
fill: {
fgColor: {
rgb: config.myStyle.heightLightColor
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
auto: 1
}
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
}
}
}
});
}
config.myStyle?.rowCells?.row.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
let num = Number(dislodgeLetter(item1));
if (num == Number(item)) {
ws[item1].s = config.myStyle.rowCells.s;
}
});
});
if (!Array.isArray(exportElement) && config.myStyle.mergeBorder) { //对导出合并单元格无边框的处理，只针对dom导出，因为只有dom导出会出现合并无边框的情况
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"]
let range = config.myStyle.mergeBorder;
range.forEach((item, index) => {
if (item.s.c == item.e.c) { //行相等,横向合并
let star = item.s.r;
let end = item.e.r;
for (let i = star + 1; i <= end; i++) {
ws[arr[i] + (Number(item.s.c) + 1)] = {
s: ws[arr[star] + (Number(item.s.c) + 1)].s
}
}
} else { //列相等，纵向合并
let star = item.s.c;
let end = item.e.c;
for (let i = star + 1; i <= end; i++) {
ws[arr[item.s.r] + (i + 1)] = {
s: ws[arr[item.s.r] + (star + 1)].s
}
}
}
});
}
}
callback && callback();
Object.keys(ws).forEach((item, index) => { //空数据处理，单元格值为空时不显示null
if (ws[item].t === 's' && !ws[item].v) {
ws[item].v = '-';
}
});
Object.keys(ws).forEach((item, index) => { //空数据处理，单元格值为空时不显示null
if (ws[item].t === 's' && ws[item].v.includes('%')) {
ws[item].v = ws[item].v.includes('.') ? (ws[item].v.replace('%', '').split('.')[1] === '0' ? `${ws[item].v.replace('%', '').split('.')[0]}%` : ws[item].v) : ws[item].v;
}
});
return ws;
} catch (e) {
throw (e);
}
}
function transIndexToLetter(num) { //数字转字母坐标，25->Z ,26-> AA
if (num < 26) {
return String.fromCharCode(num + 65);
} else {
return transIndexToLetter(Math.floor(num / 26) - 1) + transIndexToLetter(num % 26);
}
}
function dislodgeLetter(str) { //去掉字符串中的字母
var result;
var reg = /[a-zA-Z]+/; //[a-zA-Z]表示bai匹配字母，dug表示全局匹配
while (result = str.match(reg)) { //判断str.match(reg)是否没有字母了
str = str.replace(result[0], ''); //替换掉字母 result[0] 是 str.match(reg)匹配到的字母
}
return str;
}
export function sheetToJSON(wb,option?){
return XLSX.utils.sheet_to_json(wb,option);
}
function IEsheet2blob(sheet, sheetName?: string | string[]) {
console.log(sheet,'sheet');
console.log(sheetName,'sheetName');
try {
new Uint8Array([1, 2]).slice(0, 2);
} catch (e) {
//IE或有些浏览器不支持Uint8Array.slice()方法。改成使用Array.slice()方法
Uint8Array.prototype.slice = Array.prototype.slice;
}
sheetName = Array.isArray(sheetName)?(sheetName.length?sheetName:['sheet1']):'sheet1';
var workbook = {
SheetNames: Array.isArray(sheetName) ? sheetName : [sheetName],
Sheets: {}
};
if (Array.isArray(sheetName)) {
sheetName.forEach((item, index) => {
workbook.Sheets[item] = sheet[index];
});
} else { workbook.Sheets[sheetName] = sheet; }
console.log(workbook,'workbook');
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.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;
}
export function sheetToWorkBook(sheet, sheetName?: string | string[]){
console.log(sheet,'sheet');
console.log(sheetName,'sheetName');
try {
new Uint8Array([1, 2]).slice(0, 2);
} catch (e) {
//IE或有些浏览器不支持Uint8Array.slice()方法。改成使用Array.slice()方法
Uint8Array.prototype.slice = Array.prototype.slice;
}
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: Array.isArray(sheetName) ? sheetName : [sheetName],
Sheets: {}
};
if (Array.isArray(sheetName)) {
sheetName.forEach((item, index) => {
workbook.Sheets[item] = sheet[index];
});
} else { workbook.Sheets[sheetName] = sheet; }
console.log(workbook,'workbook');
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
return wbout;
}
function getDefaultStyle() {
let defaultStyle = {
fill: {
fgColor: {
rgb: ''
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
rgb: ''
},
bold: true
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
};
return defaultStyle;
}
function IEVersion() {
var userAgent = navigator.userAgent; //取得浏览器的userAgent字符串
var isIE = userAgent.indexOf("compatible") > -1 && userAgent.indexOf("MSIE") > -1; //判断是否IE<11浏览器
var isEdge = userAgent.indexOf("Edge") > -1 && !isIE; //判断是否IE的Edge浏览器
var isIE11 = userAgent.indexOf('Trident') > -1 && userAgent.indexOf("rv:11.0") > -1;
if (isIE) {
var reIE = new RegExp("MSIE (\\d+\\.\\d+);");
reIE.test(userAgent);
var fIEVersion = parseFloat(RegExp["$1"]);
if (fIEVersion == 7) {
return 7;
} else if (fIEVersion == 8) {
return 8;
} else if (fIEVersion == 9) {
return 9;
} else if (fIEVersion == 10) {
return 10;
} else {
return 6; //IE版本<=7
}
} else if (isEdge) {
return 'edge'; //edge
} else if (isIE11) {
return 11; //IE11
} else {
return -1; //不是ie浏览器
}
}
function openDownloadXLSXDialog(url, saveName: string) {
try {
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);
} catch (e) {
throw (e);
}
}
export function uploadExcel(exportElement: [][] | any, fileName: string, config: IConfig) {
let ws = getSheetWithMyStyle(exportElement, config);
console.log(ws, 'worksheet数据');
downLoad(ws, fileName);
}
export default {
downLoad,
downLoadExcel,
getWorkSheetElement,
getSheetWithMyStyle,
transIndexToLetter,
getDefaultStyle,
sheetToWorkBook,
sheetToJSON,
};