记录前端导出表格

一、涉及插件及版本

“file-saver”: “^2.0.5”,
“exceljs”: “^4.3.0”,

二、主方法

简单做了处理

// 配置参考文档 https://github.com/exceljs/exceljs/blob/master/README_zh.md

import { saveAs } from "file-saver";
const Excel = require("exceljs");

interface Columns {
  header: string;
  key: string;
  width?: number;
  style?: object;
}

interface CellValue {
  cell: string; // 单元格
  value: any; // 值
}

interface ExcelConfig {
  mergeCells?: string[]; // 合并单元格
  cellsValue?: CellValue[]; // 设置对应单元格值
  outConfig?: object; // 导出配置
}

const exportJsToExcel = (
  columns: Columns[], // 列数据
  rows: any[], // 行数据
  filename = "工作簿", // 导出文件名
  excelConfig?: ExcelConfig // 配置项
): void => {
  const { mergeCells, cellsValue, outConfig } = excelConfig || {};
  const workbook = new Excel.Workbook();
  workbook.creator = "admin";
  workbook.lastModifiedBy = "admin";
  workbook.created = new Date();
  workbook.modified = new Date();
  const title = filename;
  // 在加载时强制工作簿计算属性
  workbook.calcProperties.fullCalcOnLoad = true;

  const worksheet = workbook.addWorksheet(title, {
    views: [{ state: "frozen", xSplit: 0, ySplit: 2 }], // 冻结列与行
    // 打印配置 pageSetup
    pageSetup: {
      showGridLines: true,
      orientation: "landscape",
    },
    ...outConfig,
  });

  worksheet.columns = columns;
  // 先添加列数据↑↑↑ 再操作列
  columns.forEach((item, i) => {
    worksheet.getColumn(i + 1).alignment = {
      vertical: "middle", // 垂直对齐
      horizontal: "center", // 水平对齐
      wrapText: true, // 自动换行 识别符\n
    };
  });

  // 合并单元格
  const mergeCellsHandle = (cellsList: string[]) => {
    cellsList.forEach((item) => {
      worksheet.mergeCells(item);
    });
  };
  if (Array.isArray(mergeCells)) {
    mergeCellsHandle(mergeCells);
  }

  // 设置对应表格值
  const setCellsValue = (cellsKeyValues: CellValue[]) => {
    cellsKeyValues.forEach((item) => {
      worksheet.getCell(item.cell).value = item.value;
    });
  };
  if (Array.isArray(cellsValue)) {
    setCellsValue(cellsValue);
  }

  // 设置特定的行高
  worksheet.getRow("1").height = 24;
  worksheet.getRow("2").height = 24;

  // 第一行第二行字体加粗
  worksheet.getRow("1").font = {
    bold: true,
  };
  worksheet.getRow("2").font = {
    bold: true,
  };

  worksheet.addRows(rows); // 尽量往后放，避免操作单元格导致数据展示出错

  workbook.xlsx.writeBuffer().then((buffer: any) => {
    // const blob = new Blob([buffer], {
    //   type:
    //     "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    // });
    // const url = window.URL.createObjectURL(blob);
    // const a = document.createElement("a");
    // document.body.appendChild(a);
    // a.setAttribute("style", "display: none");
    // a.href = url;
    // a.download = "考勤表.xlsx";
    // a.click();
    // window.URL.revokeObjectURL(url);
    // a.remove();

    saveAs.saveAs(
      new Blob([buffer], { type: "application/octet-stream" }),
      `${filename}.xlsx`
    );
  });
};
export default exportJsToExcel;

三、使用

配置常量数据用作导出

// 表格头部信息 会因为合并错乱 需要手动配置cellsValue
const columns = [
  { header: "渠道名称", key: "id", width: 20 },
  { header: "渠道号", key: "name", width: 32 },
  { header: "目标", key: "target", width: 20 },
  {
    header: "开户成本",
    key: "cost",
    width: 20,
    style: {
      numFmt: "#,##0",
    },
  },
  { header: "佣金分成", key: "commision", width: 20 },
];

// 展示的每行数据 对象key对应上面的key
const rows = [
  {
    id: "移动",
    name: "10086",
    target: 100,
    cost: 10000,
    commision: "12.5%",
  },
  {
    id: "联通",
    name: "10010",
    target: 200,
    cost: 1443234,
    commision: "12.5%",
  },
  {
    id: "电信",
    name: "10000",
    target: 300,
    cost: 1003223,
    commision: "12.5%",
  },
];

// 要合并的单元格 
const mergeCells = ["A1:A2", "B1:B2", "C1:E1"];

// 为合并后的某些单元格单独赋值
const cellsValue = [
  {
    cell: "C1",
    value: "基础成本",
  },
  {
    cell: "C2",
    value: "目标",
  },
  {
    cell: "D2",
    value: "开户成本",
  },
  {
    cell: "E2",
    value: "佣金分成",
  },
];


// 使用
exportJsToExcel(columns, rows, "开户成本", {
  mergeCells,
  cellsValue,
});