[最简单]exceljs 纯前端导出Excel,带样式,保护锁定

/**
 * exceljs方法封装处理
 * Copyright (c) 2024 sanghx
 */

import ExcelJS from "exceljs";

/**
 * 导出Excel表格
 * @param {Array} columns 表头 [{title:"姓名",dataIndex:"name"}]
 * @param {Array} tableData  表数据 [{name:'张三'}]
 * @param {String} name 表名称
 * @param {Array} colWidth 列宽
 */

const exportExcel = async ({
  tableData = [],
  columns = [],
  name = "",
  colWidth = [],
}) => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sheet");

  worksheet.getRow(1).getCell(1).value = name
  worksheet.mergeCells(1, 1, 1, columns.length)
  worksheet.getRow(1).getCell(1).alignment = {
    horizontal: "center",// 水平居中
    vertical: 'middle', // 垂直居中
  }
  worksheet.getRow(1).getCell(1).font = { bold: true,size: 14 };



  //保护工作表
  worksheet.protect("111", {
    objects: true,
    scenarios: false,
    selectLockedCells: true,
    selectUnlockedCells: true,
    formatCells: true,
    formatColumns: true,
    formatRows: true,
    insertColumns: false,
    insertRows: false,
    insertHyperlinks: false,
    deleteColumns: false,
    deleteRows: false,
    sort: false,
    autoFilter: false,
    pivotTables: false,
  });

  // 表头
  const headerRow = worksheet.addRow(columns.map((column) => column.title));

  // 表头样式
  headerRow.eachCell((cell) => {
    cell.font = { bold: true,size: 12 };
    cell.alignment = {
       horizontal: "center",// 水平居中
       vertical: 'middle', // 垂直居中
      };
  });

  // 数据
  tableData.forEach((rowData) => {
    worksheet.addRow(columns.map((column) => rowData[column.dataIndex] || ""));
  });

  worksheet.getRow(1).height = 30;
  worksheet.getRow(2).height = 30;

  // 添加边框
  for (let num = 0; num < worksheet.lastRow._number; num++) {
    // 循环出每一行
    for (let index = 0; index < columns.length; index++) {
      // 循环出每一个单元格
      worksheet.getRow(num + 1).getCell(index + 1).border = {
        // 为单元格添加边框
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    }
  }

  // 列宽
  if (colWidth.length) {
    colWidth.forEach((width, index) => {
      worksheet.getColumn(index + 1).width = width || 20;
    });
  } else {
    for (let index = 0; index < columns.length; index++) {
      worksheet.getColumn(index + 1).width = columns[index].title.length * 2 + 10;
    }
  }


  // 生成Excel文件
  const buffer = await workbook.xlsx.writeBuffer();
  saveExcelFile(buffer, name);
};

// 下载Excel文件
const saveExcelFile = (buffer, name) => {
  const blob = new Blob([buffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  });
  const url = window.URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.setAttribute("download", `${name}.xlsx`);
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
  window.URL.revokeObjectURL(url);
};

export default exportExcel;