将el-table表格导出excel，且给每个单元格加边框，并将不合格的数据标红

el-table样式

导出的excel样式

实现代码

import { utils } from 'xlsx'; //! 免费版的xlsx不支持样式设置，需要使用xlsx-style-vite
import * as XLSX_STYLE from 'xlsx-style-vite';
import FileSaver from 'file-saver';

/**
 * @description 获取字符串的宽度
 */
function getStringWidth(text, fontSize) {
  const span = document.createElement('span');
  //. 设置 span 元素的字体和样式
  span.style.font = 'bold';
  if (fontSize) {
    span.style.fontSize = fontSize + 'px';
  }
  span.style.visibility = 'hidden';
  span.style.whiteSpace = 'pre';
  span.innerText = text;
  document.body.appendChild(span);
  const width = span.offsetWidth;
  document.body.removeChild(span);
  return width;
}

/**
 * @description 导出
 * @param {*} elTableRef el-table 的引用
 * @param {*} filename 导出文件名
 * @param {*} badDataLocation:{R:[C1,C2]} 不合格数据位置,用以标红
 * @param {*} headRowSpan 表头行数
 * @param {*} isTopHead 是否有额外的描述表头
 * @param {*} boldFirstCol 第一列字体加粗
 */
export function exportByElTable(elTableRef, option) {
  if (!elTableRef) return;

  const { filename, badDataLocation = {}, headRowSpan = 1, isTopHead = false, boldFirstCol = false } = option;

  const { $el } = elTableRef;

  const wb = utils.book_new();

  //` 获取表格的Dom对象
  //#region
  const headerDOM = $el.querySelector('.el-table .el-table__header-wrapper .el-table__header');
  const bodyDOM = $el.querySelector('.el-table .el-table__body-wrapper .el-table__body');

  let element = document.createElement('div');
  if (headerDOM) {
    element.appendChild(headerDOM.cloneNode(true));
  }
  element.appendChild(bodyDOM.cloneNode(true));
  const sheet = utils.table_to_sheet(element, { raw: true });
  //#endregion

  //`样式
  //#region
  //. 按列遍历每个单元格，给所有单元格加边框，并设置宽度为内容宽度
  const colWidths = [];
  /*
  ///range是一个对象，包含了一个表格的范围
  ///{s:{c:0,r:0}
  ///{e:{c:maxColIndex,r:maxRowIndex}}
  */
  const range = utils.decode_range(sheet['!ref']);
  const borderStyle = { style: 'thin', color: { rgb: '000000' } };
  const getBorderStyleObj = (direction, cell) => {
    const obj = cell || { s: { border: {} } };
    obj.s.border[direction] = borderStyle;
    return obj;
  };
  for (let C = range.s.c; C <= range.e.c; ++C) {
    let maxWidth = 60; /// 设置一个默认宽度
    for (let R = range.s.r; R <= range.e.r; ++R) {
      const cellRef = utils.encode_cell({ r: R, c: C });
      const cell = sheet[cellRef];
      //. 空单元格补边
      if (!cell?.t) {
        //! 有额外的描述表头时加下边框
        if (isTopHead && R == 0 && C > 0) {
          sheet[cellRef] = getBorderStyleObj('bottom', sheet[cellRef]);
        }

        /// 最后一行，补下边
        if (R == range.e.r) {
          sheet[cellRef] = getBorderStyleObj('bottom', sheet[cellRef]);
        } else if (R > 0) {
          /// 上边行为空，下边行不为空，补上边
          const topCellRef = utils.encode_cell({ r: R - 1, c: C });
          const topCell = sheet[topCellRef];
          const bottomCellRef = utils.encode_cell({ r: R + 1, c: C });
          const bottomCell = sheet[bottomCellRef];
          if (!topCell?.t && bottomCell?.t) {
            sheet[cellRef] = getBorderStyleObj('top', sheet[cellRef]);
          }
        }
        /// 最后一列，补右边
        if (C == range.e.c) {
          sheet[cellRef] = getBorderStyleObj('right', sheet[cellRef]);
        } else if (C > 0) {
          /// 左边列为空，右边列不为空，补左边
          const leftCellRef = utils.encode_cell({ r: R, c: C - 1 });
          const leftCell = sheet[leftCellRef];
          const rightCellRef = utils.encode_cell({ r: R, c: C + 1 });
          const rightCell = sheet[rightCellRef];
          if (!leftCell?.t && rightCell?.t) {
            sheet[cellRef] = getBorderStyleObj('left', sheet[cellRef]);
          }
        }
        continue;
      }

      //. 样式设置
      //#region
      const cellStyle = {
        border: {
          top: borderStyle,
          bottom: borderStyle,
          left: borderStyle,
          right: borderStyle,
        },
        alignment: { horizontal: 'center', vertical: 'center' },
      };
      /// 第一列字体加粗
      if (C == 0 && boldFirstCol) {
        cellStyle.font = { bold: true };
      }
      /// 如果是表头，字体加粗
      if (R < headRowSpan) {
        cellStyle.font = { bold: true };
      }
      if (isTopHead && R == 0) {
        cellStyle.font = { bold: true, sz: 18 };
      }
      /// 判断是否为不合格数据，不合格的字体为红色
      if (badDataLocation[R] && badDataLocation[R].includes(C)) {
        cellStyle.font = { color: { rgb: 'FF0000' } };
      }

      cell.s = cellStyle;
      //#endregion

      /// 计算单元格内容的最大宽度
      if (cell.v) {
        //! 有额外的描述表头时不算其内容宽度
        if (isTopHead && R == 0) {
          continue;
        }
        const cellValue = cell.v.toString();
        let fontSize = C == range.e.c ? 16 : 14;
        maxWidth = Math.max(maxWidth, getStringWidth(cellValue, fontSize));
      }
    }
    colWidths.push({ wpx: maxWidth });
  }
  sheet['!cols'] = colWidths;
  //#endregion

  utils.book_append_sheet(wb, sheet);
  element = null;
  //` 导出工作表，String2ArrayBuffer
  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;
  };
  const writeOption = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary',
    cellStyles: true,
  };
  const wbout = XLSX_STYLE.write(wb, writeOption);
  try {
    FileSaver.saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream;charset=utf-8"' }), filename);
  } catch (e) {
    if (typeof console !== 'undefined') {
    }
  }
}