导出ecxel表格的通用方法 JS

import * as ExcelJs from 'exceljs';
import { Workbook } from 'exceljs';
// 导出ecxel表格的通用方法
interface ExportExcelColumn {
  key: string;
  header: string;
  width: number;
}
type ExportExcelDatasource = { [key: string]: any };

const exportExcel: ({
  name,
  columns,
  datasource
}: {
  name: string;
  columns: ExportExcelColumn[];
  datasource: ExportExcelDatasource[];
}) => void = (params) => {
  const { name, columns, datasource } = params;

  // 设置文件名字
  const time = moment().format('YYYYMMDDHHmmss');

  const exportName = name + '_' + time + '.xlsx';
  // 创建工作簿
  const workbook = new ExcelJs.Workbook();
  // 添加sheet
  const worksheet = workbook.addWorksheet('sheet');
  // 设置 sheet 的默认行高
  worksheet.properties.defaultRowHeight = 20;
  // 设置列
  worksheet.columns = columns as any;
  // 给表头添加背景色。因为表头是第一行，可以通过 getRow(1) 来获取表头这一行
  const headerRow = worksheet.getRow(1);
  // 直接给这一行设置背景色
  // headerRow.fill = {
  //   type: 'pattern',
  //   pattern: 'solid',
  //   fgColor: {argb: 'dff8ff'},
  // }
  // 通过 cell 设置样式，更精准
  headerRow.eachCell((cell, colNum) => {
    // 设置背景色
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: '99ccff' }
    };
    // 设置字体
    cell.font = {
      bold: true,
      // italic: true,
      size: 11
      // name: '微软雅黑',
      // color: {argb: 'ff0000'},
    };
    // 设置对齐方式
    cell.alignment = {
      vertical: 'middle',
      horizontal: 'center',
      wrapText: false
    };
    // 设置边框
    cell.border = { bottom: { style: 'thin' }, right: { style: 'thin' } };
  });
  // 添加行
  const rows = worksheet.addRows(datasource); //按照testNumber正序排序后下载
  // 设置内容行的样式
  rows?.forEach((row) => {
    row.eachCell((cell, colNum) => {
      // 设置背景色
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'ffff99' }
      };
      // 设置字体
      cell.font = {
        size: 11
        // name: '微软雅黑',
      };
      // 设置对齐方式
      cell.alignment = {
        vertical: 'middle',
        horizontal: 'center',
        wrapText: false
      };
      // 设置边框
      cell.border = { bottom: { style: 'thin' }, right: { style: 'thin' } };
    });
  });
  // 导出excel
  saveWorkbook(workbook, exportName);
};

// 前端导出excel文件的处理函数
export const saveWorkbook = (workbook: Workbook, fileName: string) => {
  const saveAs = (fileName: any, content: any) => {
    const link = document.createElement('a');
    const blob = new Blob([content], {
      type: 'application/vnd.ms-excel;charset=utf-8;',
    });
    link.download = fileName;
    link.href = URL.createObjectURL(blob);
    link.click();
  };

  workbook.xlsx.writeBuffer().then((buffer) => {
    saveAs(fileName, buffer);
  });
};