项目中使用xlsx遇到的问题(导出须知，表头自定义，字体颜色)

声明：本人只是一个还有很多坑需要踩的新的不能再新的菜鸟。通过查资料加上自己浅薄的认知得以解决问题，不足的地方希望各位大佬多多指点！

首先我们知道，使用xlsx能够实现导出功能，但是如果遇到特殊的导出内容怎么实现呢，例如表头自定义，加重字体等。

一、下面是需要实现的效果图

可以看到，表头的标题颜色需要使用红色标明，行高和列宽也是另外设置的，还有导入须知的合并单元格。

二、解决方案：

通过百度结合自身理解能力，尝试了以下两种方案

1. xlsx+xlsx-style

可以实现部分样式的导出，在开发过程中发现xlsx-style不能实现合并单元格或者设置列宽等一些重要属性，并且xlsx-style目前没有持续迭代，因此尝试过该方案之后放弃了。

2. xlsx+xlsx-js-style

这种方案能够成功实现想要的效果。

使用版本：

安装命令：

npm/cnpm install  xlsx + @latest/ 指定版本号

npm/cnpm install  xlsx-js-style + @latest/ 指定版本号

三、代码

文件名：excel.js

/*
封装导出excel的方法
*/
import * as XLSX from 'xlsx';
import XLSXS from 'xlsx-js-style';
import { saveAs } from 'file-saver';
import XSU from './xlsx-styleUtils';
// 自动列宽计算
const AutoWidth = (ws, arr) => {
  // 最小列宽
  let minWch = 10;
  // 有地址信息时使用的最小列宽
  let addressInfoMinWch = 50;
  // 设置worksheet每列的最大宽度
  const colWidth = arr.map(row =>
    row.map(val => {
      // 判断是否为null/undefined
      if (val == null) {
        return { wch: minWch };
      } else if (val.toString().charCodeAt(0) > 255) {
        // 判断是否为中文
        if (val.indexOf('地址') > 0) {
          return { wch: addressInfoMinWch + val.toString().length * 2 };
        } else {
          return { wch: minWch + val.toString().length * 2 };
        }
      } else {
        return { wch: minWch + val.toString().length };
      }
    })
  );
  // 以第一行为初始值
  const 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;
};

// 数组转换成JSON
const formatJSON = (key, data) => {
  return data.map(v =>
    key.map(i => {
      return v[i];
    })
  );
};

// 字符串转ArrayBuffer
const s2ab = s => {
  const buf = new ArrayBuffer(s.length);
  const view = new Uint8Array(buf);
  for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
  return buf;
};

// 导出EXCEL表格
export const exportDataToExcel = ({
  importNotice, //导入须知
  headerKey, // 表头名数组
  requiredHeader, // 必填项的表头名数组
  requiredColor = 'FFFF0000', //必填项的表头字体颜色(默认为红色)
  data, // 需要导出数据的数组
  fileName, // 导出文件名
} = {}) => {
  // 创建Workbook对象
  const wb = XLSX.utils.book_new();
  const arr = formatJSON(headerKey, data);
  fileName = fileName || 'excel-list';

  if (headerKey?.length) arr.unshift(headerKey);
  if (importNotice?.length) arr.unshift(importNotice);
  // 将数组数据转换为worksheet
  const ws = XLSX.utils.aoa_to_sheet(arr);

  if (importNotice?.length) {
    arr.shift();
    AutoWidth(ws, arr);
    const rows = importNotice.map(mi => mi.length)[0];
    ws['!rows'] = [{ hpx: rows * 0.75 }];
  } else {
    AutoWidth(ws, arr);
  }
  // 向Workbook对象中追加worksheet和fileName
  XLSX.utils.book_append_sheet(wb, ws, fileName);

  // 调用样式处理方法
  const wbStyle = utilsTest(wb, importNotice, requiredHeader, requiredColor);

  // 生成EXCEL的配置项
  // 这里调用XLSXS 来调整excel样式
  const wbout = XLSXS.write(wbStyle, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary',
  });
  // 浏览器下载
  saveAs(
    new Blob([s2ab(wbout)], {
      type: 'application/octet-stream',
    }),
    `${fileName}.xlsx`
  );
};

// 表格样式编辑
const utilsTest = (wb, importNotice, requiredHeader, requiredColor) => {
  const excelContent = wb.Sheets[wb.SheetNames[0]];

  XSU.setAlignmentWrapTextAll(wb, wb.SheetNames[0], true);
  XSU.setFontTypeAll(wb, wb.SheetNames[0], '等线');
  XSU.setAlignmentVerticalAll(wb, wb.SheetNames[0], 'center');
  XSU.setFontSizeAll(wb, wb.SheetNames[0], 12);

  for (let key in excelContent) {
    if (key.indexOf('!') < 0) {
      // 处理导入须知的表格样式
      if (importNotice?.length && key == 'A1') {
        const colMerges = XSU.getMaxCol(wb, wb.SheetNames[0]);
        const merges = [{ s: { c: 0, r: 0 }, e: { c: colMerges - 1, r: 0 } }];
        XSU.mergeCellsByObj(wb, wb.SheetNames[0], merges);
        XSU.setAlignmentHorizontal(wb, wb.SheetNames[0], key, 'left');
      } else {
        XSU.setAlignmentHorizontal(wb, wb.SheetNames[0], key, 'center');
      }
      // 处理必填项表格头数组的字体颜色
      if (requiredHeader?.length && requiredHeader.includes(excelContent[key].v)) {
        XSU.setFontColorRGB(wb, wb.SheetNames[0], key, requiredColor);
      }
      XSU.setBorderStyles(wb, wb.SheetNames[0], key, XSU.borderAll);
    }
  }
  return wb;
};

// 从Excel文件中获取表格头
const getHeaderRow = sheet => {
  const headers = [];
  // 将 A1:G8 这种字符串转换为行列对象
  const range = XLSX.utils.decode_range(sheet['!ref']);
  let C;
  const R = range.s.r;
  // 从第一列开始，遍历范围中的每一列
  for (C = range.s.c; C <= range.e.c; ++C) {
    // 将行列对象转换为 A1 这种字符串
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
    // 用默认值替换
    let hdr = 'UNKNOWN ' + C;
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
    headers.push(hdr);
  }
  return headers;
};

// 读取Excel文件
export const readDataFromExcel = (data, type, outputType) => {
  // 读取Excel文件并保存到Workbook对象
  const workbook = XLSX.read(data, { type: type });
  const firstSheetName = workbook.SheetNames[0]; // 取第一张表
  // 获取Workbook对象的worksheet
  const worksheet = workbook.Sheets[firstSheetName];
  const header = getHeaderRow(worksheet);
  // 将worksheet转化成数组
  const results = XLSX.utils.sheet_to_json(worksheet, outputType || null);
  return { header, results };
};

export default {
  exportDataToExcel,
  readDataFromExcel,
};

文件名：xlsx-styleUtils.js

/*
 对xlsx-js-style方法进行二次封装 方便调用以导出带样式Excel
*/
import * as XLSX from 'xlsx';
let XSU;

XSU = {
  //字符串转字符流
  s2ab: function (s) {
    let buf = new ArrayBuffer(s.length);
    let view = new Uint8Array(buf);
    for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  },

  //初始化
  init: function (workBook, sheetName, cell) {
    if (!workBook.Sheets[sheetName][cell].s) {
      workBook.Sheets[sheetName][cell].s = {};
    }
  },

  init1: function (workBook, sheetName, cell, attr) {
    this.init(workBook, sheetName, cell);
    if (!workBook.Sheets[sheetName][cell].s[attr]) {
      workBook.Sheets[sheetName][cell].s[attr] = {};
    }
  },

  init2: function (workBook, sheetName, cell, attr1, attr2) {
    this.init(workBook, sheetName, cell);
    this.init1(workBook, sheetName, cell, attr1);
    if (!workBook.Sheets[sheetName][cell].s[attr1][attr2]) {
      workBook.Sheets[sheetName][cell].s[attr1][attr2] = {};
    }
  },

  //根据ref的单元格范围新建范围内所有单元格,不存在的单元格置为空值,已存在的不处理
  initAllCell: function (workBook, sheetName) {
    let ref = workBook.Sheets[sheetName]['!ref'].split(':');
    let startCell = ref[0];
    let endCell = ref[1];
    let sc = XLSX.utils.decode_cell(startCell).c;
    let sr = XLSX.utils.decode_cell(startCell).r;
    let ec = XLSX.utils.decode_cell(endCell).c;
    let er = XLSX.utils.decode_cell(endCell).r;
    let isExist;
    for (let c = sc; c <= ec; c++) {
      //初始化所有单元格
      for (let r = sr; r <= er; r++) {
        let temp = XLSX.utils.encode_cell({
          c: c,
          r: r,
        });
        isExist = false;
        for (let cell in workBook.Sheets[sheetName]) {
          if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
            if (temp == cell) {
              isExist = true;
              break;
            }
          }
        }
        if (!isExist) {
          //单元格不存在则新建单元格
          XLSX.utils.sheet_add_aoa(workBook.Sheets[sheetName], [['']], {
            origin: temp,
          });
        }
      }
    }
  },

  //单元格合并 startCell=A1 endCell=B5
  mergeCells: function (workBook, sheetName, startCell, endCell) {
    /*var sc = startCell.substr(0, 1).charCodeAt(0) - 65;
		var sr = startCell.substr(1);
		sr = parseInt(sr) - 1;
		var ec = endCell.substr(0, 1).charCodeAt(0) - 65;
		var er = endCell.substr(1)
		er = parseInt(er) - 1;*/

    let sc = XLSX.utils.decode_cell(startCell).c;
    let sr = XLSX.utils.decode_cell(startCell).r;
    let ec = XLSX.utils.decode_cell(endCell).c;
    let er = XLSX.utils.decode_cell(endCell).r;

    let merges = [
      {
        s: {
          //s start 始单元格
          c: sc, //cols 开始列
          r: sr, //rows 开始行
        },
        e: {
          //e end  末单元格
          c: ec, //cols 结束列
          r: er, //rows 结束行
        },
      },
    ];
    if (!workBook.Sheets[sheetName]['!merges']) {
      workBook.Sheets[sheetName]['!merges'] = merges;
    } else {
      workBook.Sheets[sheetName]['!merges'] = workBook.Sheets[sheetName]['!merges'].concat(merges);
    }

    return workBook;
  },

  //merges=[{s: {c: 0, r: 0},e: {c: 3, r: 0}}]
  mergeCellsByObj: function (workBook, sheetName, merges) {
    if (workBook.Sheets[sheetName]['!merges']) {
      workBook.Sheets[sheetName]['!merges'] = workBook.Sheets[sheetName]['!merges'].concat(merges);
    } else {
      workBook.Sheets[sheetName]['!merges'] = merges;
    }

    return workBook;
  },

  //设置每列列宽,单位px cols= [{wpx: 45}, {wpx: 165}, {wpx: 45}, {wpx: 45}]
  setColWidth: function (workBook, sheetName, cols) {
    workBook.Sheets[sheetName]['!cols'] = cols;
    return workBook;
  },

  setCellStyle: function (workBook, sheetName, cell, styles) {
    workBook.Sheets[sheetName][cell].s = styles;
    return workBook;
  },

  /*Fill*/

  //填充颜色 fill={"bgColor": {"indexed": 64},"fgColor": {"rgb": "FFFFFF00"}}
  setFillStyles: function (workBook, sheetName, cell, styles) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.fill = styles;
    return workBook;
  },

  setFillStylesAll: function (workBook, sheetName, styles) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillStyles(workBook, sheetName, cell, styles);
      }
    }
  },

  //patternType="solid" or "none"”
  setFillPatternType: function (workBook, sheetName, cell, patternType) {
    this.init1(workBook, sheetName, cell, 'fill');
    workBook.Sheets[sheetName][cell].s.fill.patternType = patternType;
    return workBook;
  },

  setFillPatternTypeAll: function (workBook, sheetName, patternType) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillPatternType(workBook, sheetName, cell, patternType);
      }
    }
  },

  //前景颜色(单元格颜色) rgb
  //COLOR_SPEC属性值:{ auto: 1}指定自动值,{ rgb: "FFFFAA00" }指定16进制的ARGB,{ theme: "1", tint: "-0.25"}指定主题颜色和色调的整数索引（默认值为0）,{ indexed: 64} 默认值 fill.bgColor
  setFillFgColor: function (workBook, sheetName, cell, COLOR_SPEC) {
    this.init1(workBook, sheetName, cell, 'fill');
    workBook.Sheets[sheetName][cell].s.fill.fgColor = COLOR_SPEC;
    return workBook;
  },

  setFillFgColorAll: function (workBook, sheetName, COLOR_SPEC) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillFgColor(workBook, sheetName, cell, COLOR_SPEC);
      }
    }
  },

  //使用RGB值设置颜色
  setFillFgColorRGB: function (workBook, sheetName, cell, rgb) {
    this.init2(workBook, sheetName, cell, 'fill', 'fgColor');
    workBook.Sheets[sheetName][cell].s.fill.fgColor.rgb = rgb;
    return workBook;
  },

  setFillFgColorRGBAll: function (workBook, sheetName, rgb) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillFgColorRGB(workBook, sheetName, cell, rgb);
      }
    }
  },

  //单元格背景颜色（貌似没用）
  setFillBgColor: function (workBook, sheetName, cell, COLOR_SPEC) {
    this.init1(workBook, sheetName, cell, 'fill');
    workBook.Sheets[sheetName][cell].s.fill.bgColor = COLOR_SPEC;
    return workBook;
  },

  setFillBgColorAll: function (workBook, sheetName, COLOR_SPEC) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillBgColor(workBook, sheetName, cell, COLOR_SPEC);
      }
    }
  },

  //单元格背景颜色
  setFillBgColorRGB: function (workBook, sheetName, cell, rgb) {
    this.init2(workBook, sheetName, cell, 'fill', 'bgColor');
    workBook.Sheets[sheetName][cell].s.fill.bgColor.rgb = rgb;
    return workBook;
  },

  setFillBgColorRGBAll: function (workBook, sheetName, rgb) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFillBgColorRGB(workBook, sheetName, cell, rgb);
      }
    }
  },

  /*Font*/

  //字体风格，可一次性在styles中设置所有font风格
  setFontStyles: function (workBook, sheetName, cell, styles) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.font = styles;
    return workBook;
  },

  setFontStylesAll: function (workBook, sheetName, styles) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontStyles(workBook, sheetName, cell, styles);
      }
    });
  },

  //字体 type="Calibri"
  setFontType: function (workBook, sheetName, cell, type) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.name = type;
    return workBook;
  },

  setFontTypeAll: function (workBook, sheetName, type) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontType(workBook, sheetName, cell, type);
      }
    });
  },

  //字体大小
  setFontSize: function (workBook, sheetName, cell, size) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.sz = size;
    return workBook;
  },

  setFontSizeAll: function (workBook, sheetName, size) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontSize(workBook, sheetName, cell, size);
      }
    });
  },

  //字体颜色 COLOR_SPEC
  setFontColor: function (workBook, sheetName, cell, COLOR_SPEC) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.color = COLOR_SPEC;
    //workBook.Sheets[sheetName][cell].s.font.color.rgb = rgb;
    return workBook;
  },

  setFontColorAll: function (workBook, sheetName, COLOR_SPEC) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontColor(workBook, sheetName, cell, COLOR_SPEC);
      }
    }
  },

  //字体颜色RGB
  setFontColorRGB: function (workBook, sheetName, cell, rgb) {
    this.init2(workBook, sheetName, cell, 'font', 'color');
    workBook.Sheets[sheetName][cell].s.font.color.rgb = rgb;
    return workBook;
  },

  setFontColorRGBAll: function (workBook, sheetName, rgb) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontColorRGB(workBook, sheetName, cell, rgb);
      }
    }
  },

  //是否粗体 boolean isBold
  setFontBold: function (workBook, sheetName, cell, isBold) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.bold = isBold;
    return workBook;
  },

  setFontBoldAll: function (workBook, sheetName, isBold) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontBold(workBook, sheetName, cell, isBold);
      }
    }
  },

  //设置某列为粗体
  setFontBoldOfCols: function (workBook, sheetName, isBold, col) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        if (cell.substr(0, 1) == col) {
          this.setFontBold(workBook, sheetName, cell, isBold);
        }
      }
    }
  },

  //设置某行为粗体
  setFontBoldOfRows: function (workBook, sheetName, isBold, row) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        if (cell.substr(1) == row) {
          this.setFontBold(workBook, sheetName, cell, isBold);
        }
      }
    }
  },

  //是否下划线 boolean isUnderline
  setFontUnderline: function (workBook, sheetName, cell, isUnderline) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.underline = isUnderline;
    return workBook;
  },

  setFontUnderlineAll: function (workBook, sheetName, isUnderline) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontUnderline(workBook, sheetName, cell, isUnderline);
      }
    }
  },

  //是否斜体 boolean isItalic
  setFontItalic: function (workBook, sheetName, cell, isItalic) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.italic = isItalic;
    return workBook;
  },

  setFontItalicAll: function (workBook, sheetName, isItalic) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontItalic(workBook, sheetName, cell, isItalic);
      }
    }
  },

  //是否删除线 boolean isStrike
  setFontStrike: function (workBook, sheetName, cell, isStrike) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.strike = isStrike;
    return workBook;
  },

  setFontStrikeAll: function (workBook, sheetName, isStrike) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontStrike(workBook, sheetName, cell, isStrike);
      }
    }
  },

  //是否outline boolean isOutline
  setFontOutline: function (workBook, sheetName, cell, isOutline) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.outline = isOutline;
    return workBook;
  },

  setFontOutlineAll: function (workBook, sheetName, isOutline) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontOutline(workBook, sheetName, cell, isOutline);
      }
    }
  },

  //是否阴影 boolean isShadow
  setFontShadow: function (workBook, sheetName, cell, isShadow) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.shadow = isShadow;
    return workBook;
  },

  setFontShadowAll: function (workBook, sheetName, isShadow) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontShadow(workBook, sheetName, cell, isShadow);
      }
    }
  },

  //是否vertAlign boolean isVertAlign
  setFontVertAlign: function (workBook, sheetName, cell, isVertAlign) {
    this.init1(workBook, sheetName, cell, 'font');
    workBook.Sheets[sheetName][cell].s.font.vertAlign = isVertAlign;
    return workBook;
  },

  setFontVertAlignAll: function (workBook, sheetName, isVertAlign) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontVertAlign(workBook, sheetName, cell, isVertAlign);
      }
    }
  },

  /*numFmt*/

  setNumFmt: function (workBook, sheetName, cell, numFmt) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.numFmt = numFmt;
    return workBook;
  },

  setNumFmtAll: function (workBook, sheetName, numFmt) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setNumFmt(workBook, sheetName, cell, numFmt);
      }
    }
  },

  /*Alignment*/

  //文本对齐 alignment={vertical:top,horizontal:top,}
  setAlignmentStyles: function (workBook, sheetName, cell, styles) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.alignment = styles;
    return workBook;
  },

  setAlignmentStylesAll: function (workBook, sheetName, styles) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentStyles(workBook, sheetName, cell, styles);
      }
    });
  },

  //文本垂直对齐 vertical	="bottom" or "center" or "top"
  setAlignmentVertical: function (workBook, sheetName, cell, vertical) {
    this.init1(workBook, sheetName, cell, 'alignment');
    workBook.Sheets[sheetName][cell].s.alignment.vertical = vertical;
    return workBook;
  },

  setAlignmentVerticalAll: function (workBook, sheetName, vertical) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentVertical(workBook, sheetName, cell, vertical);
      }
    });
  },

  //文本水平对齐 "bottom" or "center" or "top"
  setAlignmentHorizontal: function (workBook, sheetName, cell, horizontal) {
    this.init1(workBook, sheetName, cell, 'alignment');
    workBook.Sheets[sheetName][cell].s.alignment.horizontal = horizontal;
    return workBook;
  },

  setAlignmentHorizontalAll: function (workBook, sheetName, horizontal) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentHorizontal(workBook, sheetName, cell, horizontal);
      }
    });
  },

  //自动换行
  setAlignmentWrapText: function (workBook, sheetName, cell, isWrapText) {
    this.init1(workBook, sheetName, cell, 'alignment');
    workBook.Sheets[sheetName][cell].s.alignment.wrapText = isWrapText;
    return workBook;
  },

  setAlignmentWrapTextAll: function (workBook, sheetName, isWrapText) {
    Object.keys(workBook.Sheets[sheetName]).forEach(cell => {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentWrapText(workBook, sheetName, cell, isWrapText);
      }
    });
  },

  setAlignmentReadingOrder: function (workBook, sheetName, cell, readingOrder) {
    this.init1(workBook, sheetName, cell, 'alignment');
    workBook.Sheets[sheetName][cell].s.alignment.readingOrder = readingOrder;
    return workBook;
  },

  setAlignmentReadingOrderAll: function (workBook, sheetName, readingOrder) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentReadingOrder(workBook, sheetName, cell, readingOrder);
      }
    }
  },

  //文本旋转角度 0-180，255 is special, aligned vertically
  setAlignmentTextRotation: function (workBook, sheetName, cell, textRotation) {
    this.init1(workBook, sheetName, cell, 'alignment');
    workBook.Sheets[sheetName][cell].s.alignment.textRotation = textRotation;
    return workBook;
  },

  setAlignmentTextRotationAll: function (workBook, sheetName, textRotation) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setAlignmentTextRotation(workBook, sheetName, cell, textRotation);
      }
    }
  },

  /*Border*/

  //单元格四周边框默认样式
  borderAll: {
    top: {
      style: 'thin',
    },
    bottom: {
      style: 'thin',
    },
    left: {
      style: 'thin',
    },
    right: {
      style: 'thin',
    },
  },

  //边框默样式：细线黑色
  defaultBorderStyle: {
    style: 'thin',
  },

  //边框 styles={top:{ style:"thin",color:"FFFFAA00"},bottom:{},...}
  setBorderStyles: function (workBook, sheetName, cell, styles) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.border = styles;
    return workBook;
  },

  setBorderStylesAll: function (workBook, sheetName, styles) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderStyles(workBook, sheetName, cell, styles);
      }
    }
  },

  //设置单元格上下左右边框默认样式
  setBorderDefault: function (workBook, sheetName, cell) {
    this.init(workBook, sheetName, cell);
    workBook.Sheets[sheetName][cell].s.border = this.borderAll;
    return workBook;
  },

  //设置所有单元默认格边框
  setBorderDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderDefault(workBook, sheetName, cell);
      }
    }
  },

  //上边框
  setBorderTop: function (workBook, sheetName, cell, top) {
    this.init(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.top = top;
    return workBook;
  },

  setBorderTopAll: function (workBook, sheetName, top) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderTop(workBook, sheetName, cell, top);
      }
    }
  },

  //上边框默样式
  setBorderTopDefault: function (workBook, sheetName, cell) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.top = this.defaultBorderStyle;
    return workBook;
  },

  setBorderTopDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderTopDefault(workBook, sheetName, cell);
      }
    }
  },

  //下边框
  setBorderBottom: function (workBook, sheetName, cell, bottom) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.bottom = bottom;
    return workBook;
  },

  setBorderBottomAll: function (workBook, sheetName, bottom) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderBottom(workBook, sheetName, cell, bottom);
      }
    }
  },

  //下边框默样式
  setBorderBottomDefault: function (workBook, sheetName, cell) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.bottom = this.defaultBorderStyle;
    return workBook;
  },

  setBorderBottomDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderBottomDefault(workBook, sheetName, cell);
      }
    }
  },

  //左边框
  setBorderLeft: function (workBook, sheetName, cell, left) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.left = left;
    return workBook;
  },

  setBorderLeftAll: function (workBook, sheetName, left) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderLeft(workBook, sheetName, cell, left);
      }
    }
  },

  setBorderLeftDefault: function (workBook, sheetName, cell) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.left = this.defaultBorderStyle;
    return workBook;
  },

  setBorderLeftDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderLeftDefault(workBook, sheetName, cell);
      }
    }
  },

  //右边框
  setBorderRight: function (workBook, sheetName, cell, right) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.right = right;
    return workBook;
  },

  setBorderRightAll: function (workBook, sheetName, right) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderRight(workBook, sheetName, cell, right);
      }
    }
  },
  setBorderRightDefault: function (workBook, sheetName, cell) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.right = this.defaultBorderStyle;
    return workBook;
  },

  setBorderRightDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderRightDefault(workBook, sheetName, cell);
      }
    }
  },

  //对角线
  setBorderDiagonal: function (workBook, sheetName, cell, diagonal) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.diagonal = diagonal;
    return workBook;
  },

  setBorderDiagonalAll: function (workBook, sheetName, diagonal) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderDiagonal(workBook, sheetName, cell, diagonal);
      }
    }
  },

  setBorderDiagonalDefault: function (workBook, sheetName, cell) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.diagonal = this.defaultBorderStyle;
    return workBook;
  },

  setBorderDiagonalDefaultAll: function (workBook, sheetName) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderDiagonalDefault(workBook, sheetName, cell);
      }
    }
  },

  setBorderDiagonalUp: function (workBook, sheetName, cell, isDiagonalUp) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.diagonalUp = isDiagonalUp;
    return workBook;
  },

  setBorderDiagonalUpAll: function (workBook, sheetName, isDiagonalUp) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderDiagonalUp(workBook, sheetName, cell, isDiagonalUp);
      }
    }
  },

  setBorderDiagonalDown: function (workBook, sheetName, cell, isDiagonalDown) {
    this.init1(workBook, sheetName, cell, 'border');
    workBook.Sheets[sheetName][cell].s.border.diagonalDown = isDiagonalDown;
    return workBook;
  },

  setBorderDiagonalDownAll: function (workBook, sheetName, isDiagonalDown) {
    this.initAllCell(workBook, sheetName);
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setBorderDiagonalDown(workBook, sheetName, cell, isDiagonalDown);
      }
    }
  },

  //默认样式，多单元格设置样式

  //设置所有单元格字体样式
  setFgColorStylesAll: function (workBook, sheetName, fontType, fontColor, fontSize) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        this.setFontType(workBook, sheetName, cell, fontType);
        this.setFontColorRGB(workBook, sheetName, cell, fontColor);
        this.setFontSize(workBook, sheetName, cell, fontSize);
      }
    }
  },

  //设置第一行标题自定义样式
  setTitleStyles: function (workBook, sheetName, fgColor, fontColor, alignment, isBold, fontSize) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        let row = cell.substr(1);
        if (row == '1') {
          this.setFillFgColorRGB(workBook, sheetName, cell, fgColor);
          this.setFontColor(workBook, sheetName, cell, fontColor);
          this.setAlignmentHorizontal(workBook, sheetName, cell, alignment);
          this.setFontBold(workBook, sheetName, cell, isBold);
          this.setFontSize(workBook, sheetName, cell, fontSize);
        }
      }
    }
  },

  //设置第一行标题默认样式
  setTitleStylesDefault: function (workBook, sheetName) {
    for (let cell in workBook.Sheets[sheetName]) {
      let row = cell.substr(1);
      if (row == '1') {
        //setFillFgColorRGB(workBook, sheetName, cell, 'FFFF00');
        this.setAlignmentHorizontal(workBook, sheetName, cell, 'center');
        this.setFontBold(workBook, sheetName, cell, true);
        this.setFontSize(workBook, sheetName, cell, '20');
      }
    }
  },

  //设置双数行背景色灰色，便于阅读
  setEvenRowColorGrey: function (workBook, sheetName) {
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        let row = parseInt(cell.substr(1));
        if (row % 2 == 0) {
          this.setFillFgColorRGB(workBook, sheetName, cell, 'DCDCDC');
        }
      }
    }
  },

  //合并同一列中内容一样的相邻行
  mergeSameColCells: function (workBook, sheetName, col) {
    let cells = [];
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        if (cell.substr(0, 1) == col) {
          cells.push(cell); //获得该列单元格数组，升序
        }
      }
    }
    for (let i = 0; i < cells.length - 1; ) {
      for (let j = i + 1; j < cells.length; j++) {
        //内容一样且不为空则合并
        if (
          workBook.Sheets[sheetName][cells[i]].v == workBook.Sheets[sheetName][cells[j]].v &&
          workBook.Sheets[sheetName][cells[i]].v != ''
        ) {
          this.mergeCells(workBook, sheetName, cells[i], cells[j]);
          if (j == cells.length - 1) {
            i = j;
          }
        } else {
          //当且仅当相邻的两个cell值相同时才合并
          i = j;
          break;
        }
      }
    }
  },

  //合并同一行中内容一样的相邻列
  mergeSameRowCells: function (workBook, sheetName, row) {
    let cells = [];
    for (let cell in workBook.Sheets[sheetName]) {
      if (cell != '!cols' && cell != '!merges' && cell != '!ref') {
        if (cell.substr(1) == row) {
          cells.push(cell); //获得该列单元格数组，升序
        }
      }
    }
    for (let i = 0; i < cells.length - 1; ) {
      for (let j = i + 1; j < cells.length; j++) {
        if (workBook.Sheets[sheetName][cells[i]].v == workBook.Sheets[sheetName][cells[j]].v) {
          this.mergeCells(workBook, sheetName, cells[i], cells[j]);
          if (j == cells.length - 1) {
            i = j;
          }
        } else {
          //当且仅当相邻的两个cell值相同时才合并
          i = j;
          break;
        }
      }
    }
  },

  //  当前表格最大行数 返回数字类型
  getMaxRow: function (workBook, sheetName) {
    let length = 0;
    for (let ever in workBook.Sheets[sheetName]) {
      let temp = parseInt(ever.substr(1));
      if (temp > length) {
        length = temp;
      }
    }
    return length;
  },

  /*当前表格最大列数
  返回类型： 例如最大列是 C
  1. 返回所在列在数字排序中的值(C对应3) 3 number
  2. 直接返回'C'
  默认返回number类型
   */
  getMaxCol: function (workBook, sheetName, returnType = 'number') {
    let length = 'A';
    for (let ever in workBook.Sheets[sheetName]) {
      let temp = ever.substr(0, 1);
      if (temp > length) {
        length = temp;
      }
    }
    length = returnType == 'number' ? length.charCodeAt(0) - 64 : length;
    return length;
  },
};

export default XSU;

参考文章：xlsx 导出导入excel，xlsx-style 修改excel样式_安装了xslx-style,excel还是无法添加样式_唐十八_wei的博客-CSDN博客