|
 需求是这样的： 东西太多了，合并单元格，行高，列宽，边框样式，字体样式，合计啥的，直接上代码吧
export function exportExce({
header, //子表数据头信息
data, //子表数据
masterTable, //主表数据
foot, //页脚
filename, //文件名
autoWidth = true,
bookType = 'xlsx'
} = {}) {
filename = filename || 'excel'
data = [...data]
data.unshift(header); //子表添加头信息
data.push(foot) //添加页脚（不一定非要在这填，可以后边追加）
data.push([""]) //加一行空，不知为啥最后一行老是不展示，出此下策，如果没这个问题的话可以去掉
let ws_name = "SheetJS";
//处理主表内容
let keyList = Object.keys(masterTable)
let valueList = Object.values(masterTable)
let mastertableList = []
for (let i = 0; i //设置所有格属性
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val === null) {
return {
'wch': 10
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*以第一行为初始值*/
let result = colWidth[0];
for (let i = 1; i
if (result[j]['wch'] t: "s", v: filename }; //添加表格头信息
ws["A1"].s = { //为某个单元格设置单独样式
font: {
name: '宋体',
sz: 24,
bold: true,
color: { rgb: "FFFFAA00" }
},
alignment: { horizontal: "center", vertical: "center", wrap_text: true },
fill: { bgcolor: { rgb: 'ffff00' } }
}
//需要合并单元格的数组处理
let mergeclumns = []
mastertableList.forEach((element, index) => {
mergeclumns.push({
s: {//s为开始
c: 1,//开始列
r: index + 1//开始取值范围
},
e: {//e结束
c: 3,//结束列
r: index + 1//结束范围
}
})
mergeclumns.push({
s: {
c: 5,
r: index + 1
},
e: {
c: 7,
r: index + 1
}
})
});
ws["!merges"] = [{
s: {
c: 0,
r: 0
},
e: {
c: ws['!cols'].length - 1,
r: 0
}
}, ...mergeclumns,
{
s: {
c: 0,
r: mastertableList.length + 1
},
e: {
c: ws['!cols'].length - 1,
r: mastertableList.length + 1
}
},
{
s: {
c: 0,
r: newTableData.length-1
},
e: {
c: ws['!cols'].length - 1,
r: newTableData.length-1
}
}];
ws['!cols'] = [{wch: 10}] //还是自定义样式：第一列列宽
ws['!rows'] = [{hpx: 25}] //还是自定义样式：第一行行高
wb.SheetNames.push(ws_name); //文件名
wb.Sheets[ws_name] = ws; //单元格内容
let wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
处理表格数据函数：
/**
* 参数： data：需要转换的参数
* topLength：距离顶部的单元
*/
function sheet_from_array_of_arrays2(data, topLength, opts) {
let ws = {};
let 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
c: C,
r: R + topLength
});
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;
}
}
if (range.s.c |