xlsx.js设置Excel样式

article/2025/7/27 7:13:59

在这里插入图片描述
需求是这样的：
东西太多了，合并单元格，行高，列宽，边框样式，字体样式，合计啥的，直接上代码吧

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 < keyList.length; i += 2) {mastertableList.push([keyList[i], valueList[i] ? valueList[i] : '', '', '', keyList[i + 1] ? keyList[i + 1] : '', valueList[i + 1] ? valueList[i + 1] : ''])}let newTableData = [...mastertableList, [], ...data]//这个新数组是主表+字表let wb = new Workbook(),ws = sheet_from_array_of_arrays2(newTableData, 1);//得到单元格对象if (autoWidth) {									  //设置所有格属性/*设置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 < colWidth.length; i++) {for (let j = 0; j < colWidth[i].length; j++) {if (result[j]['wch'] < colWidth[i][j]['wch']) {result[j]['wch'] = colWidth[i][j]['wch'];}}}ws['!cols'] = result;}ws["A1"] = { 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 < R) range.e.r = R;if (range.e.c < C) range.e.c = C;let cell = {v: data[R][C]};if (cell.v === null) continue;let cell_ref = XLSX.utils.encode_cell({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 < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);return ws;
}