xlsx.js设置Excel样式

爱被打了一巴掌 2023-06-09 15:30 34阅读 0赞

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

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;
    }

大概就这些。。。希望对你有帮助。。。嗯  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_1_color_FFFFFF_t_10]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_1_color_FFFFFF_t_70]: /images/20230531/49a42327013744a5947b34f035cadbb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_1_color_FFFFFF_t_10]: /images/20230531/0b38567e1ed3493b9a7a8f57d9061bad.png