React + exceljs 导出Excel文件

1.前端导出 excel 文件基本步骤

1.1 引入

import Excel from "exceljs";

1.2 初始化工作簿

const workbook = new Excel.Workbook();

1.3 设置工作簿属性

workbook.creator = "admin";
workbook.lastModifiedBy = "admin";
workbook.created = new Date();
workbook.modified = new Date();

1.4 设置表头

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];

1.5 添加表格数据

const row = sheet.getRow(1);
  row.eachCell((cell, rowNumber) => {
    sheet.getColumn(rowNumber).alignment = {
      vertical: "middle",
      horizontal: "center",
    };
  });

1.6 导出表格

// 将表格数据转为二进制
workbook.xlsx.writeBuffer().then((buffer) => {
    writeFile(`${execlTitle}.xlsx`, buffer);
});
      
// 将二进制转为Excel并下载
const writeFile = (fileName, content) => {
    let a = document.createElement("a");
    let blob = new Blob([content], { type: "text/plain" });

    a.download = fileName;
    a.href = URL.createObjectURL(blob);

    a.click();
};

2. excel导出组件完整代码

import React, { useState, useEffect } from "react";
import { Button } from "antd";
import "antd/dist/antd.css";
import Excel from "exceljs";

/**
  text?: string // 下载按钮内文字
  icon?: string // 按钮 icon
  size?: string // 按钮尺寸
  type?: string // 按钮类型
  execlTitle?: string // 导出execl文件名
  tableColumns: [] // 表头
  selectedUrl: string // 接口地址url
 */

const TableDownload = ({
  size = "default",
  text = "导出",
  type = "default",
  icon = "download",
  selectedUrl,
  execlTitle = "表格数据",
}) => {
  const [isLoading, setLoading] = useState(false);
  const [tableRows, setTableData] = useState([]);
  const [tableColumns, setTableColumns] = useState([]);

  useEffect(() => {
    setLoading(true);
    fetch(selectedUrl)
      .then((response) => response.json())
      .then(({ data }) => {
        setTableColumns(data && data.columns);
        setTableData(data && data.rows);
        setTimeout(() => {
          setLoading(false);
        }, 2000);
      });
  }, [selectedUrl]);

  // 执行下载表格
  const fetchTableDatas = () => {
    // 初始化 创建工作簿
    const workbook = new Excel.Workbook();
    // 设置工作簿属性
    workbook.creator = "admin";
    workbook.lastModifiedBy = "admin";
    workbook.created = new Date();
    workbook.modified = new Date();

    // 添加工作表
    let sheet = workbook.addWorksheet("sheet");

    let columns = [];
    // 表头格式化
    tableColumns.map((item) => {
      columns.push({
        header: item.title,
        key: item.key || item.dataIndex,
        width: parseInt(item.width / 6, 10) || 40,
      });
      return true;
    });

    // 添加表头
    sheet.columns = columns;

    if (Array.isArray(tableRows)) {
      // 添加表格数据
      sheet.addRows(tableRows);

      // 设置每一列样式 居中
      const row = sheet.getRow(1);
      row.eachCell((cell, rowNumber) => {
        sheet.getColumn(rowNumber).alignment = {
          vertical: "middle",
          horizontal: "center",
        };
      });

      // 将表格数据转为二进制
      workbook.xlsx.writeBuffer().then((buffer) => {
        writeFile(`${execlTitle}.xlsx`, buffer);
      });
    } else {
      alert("下载失败");
    }
  };

  // 将二进制转为Excel并下载
  const writeFile = (fileName, content) => {
    let a = document.createElement("a");
    let blob = new Blob([content], { type: "text/plain" });

    a.download = fileName;
    a.href = URL.createObjectURL(blob);

    a.click();
  };

  return (
    <div style={{ padding: 10, margin: 10, border: "1px solid red" }}>
      <Button
        type={type}
        icon={icon || ""}
        size={size}
        loading={isLoading}
        onClick={fetchTableDatas}
      >
        {isLoading ? "正在导出" : text}
      </Button>
    </div>
  );
};

export default TableDownload;

3. 组件使用

import TableDownload from "./TableDownload";

<TableDownload
  text="导出"
  execlTitle="表格数据"
  selectedUrl="/api/tableData.json"
  icon="download"
/>

其中，接口请求的tableData.json数据为：

{
  "data": {
    "total": 3,
    "columns": [
      {
        "dataIndex": "name",
        "title": "姓名"
      },
      {
        "dataIndex": "age",
        "title": "年龄"
      }
    ],
    "rows": [
      {
        "id": 1,
        "name": "tom",
        "age": "18"
      },
      {
        "id": 2,
        "name": "jim",
        "age": "25"
      },
      {
        "id": 3,
        "name": "tim",
        "age": "25"
      }
    ]
  }
}