前端表格基于数据源导出Excel,支持定制化

1,774 阅读12分钟

将前端 table 导出 excel , 基于 exceljs + file-saver , 适用于所有表格 基于 数据源的定制化导出 github地址:github.com/Zheng-Chang… 觉的好用请给个star,谢谢~~

npm install table-excel
import { ElMapExportTable } from "table-excel";

目录

1. 导出正常表格到 Excel

导出正常表格-2.png

正常按表格格式书写 column 和 data, dataIndex 为对应数据源的字段

注意:column 中的 key 默认为 title,可通过 columnKey 设置

# code
// 点击导出触发的函数
handleExport() {
      const instance = new ElMapExportTable(
        { column, data },
        { progress: progress => console.log(progress) }// 进度条回调
      );
      instance.download("导出正常表格案例");
}

# column
const column = [
        { title: "日期", dataIndex: "date" }, // title为excel列名称,dataIndex为当前列对应的数据源字段
        { title: "姓名", dataIndex: "name" },
        { title: "地址", dataIndex: "address" },
];

# data
const data = [
    	{
          date: "2016-05-02",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1518 弄",
        },
        {
          date: "2016-05-04",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1517 弄",
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1519 弄",
        },
        {
          date: "2016-05-03",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1516 弄",
        },
]

2. 导出表头合并到 Excel

导出表头合并表格-2.png

表头的合并,将对应的列设置成相应的树形结构

注意:树形结构默认的孩子字段为 children,可通过 childrenKey 设置

# code
// 点击导出触发的函数
handleExport() {
      const instance = new ElMapExportTable(
        { column, data },
        { progress: progress => console.log(progress) }// 进度条回调
      );
      instance.download("导出表头合并的表格案例");
}

# column
const column = [
    { title: "日期", dataIndex: "date" },
    {
        title: "配送信息",
        children: [
            { title: "姓名", dataIndex: "name" },
            {
                title: "地址",
                children: [
                    { title: "省份", dataIndex: "province" },
                    { title: "市区", dataIndex: "city" },
                    { title: "地址", dataIndex: "address" },
                    { title: "邮编", dataIndex: "zip" },
                ],
            },
        ],
    },
];

# data
const data = [
        {
          date: "2016-05-03",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-02",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-04",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-08",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-06",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-07",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
]

3. 导出表体合并到 Excel

导出表体合并表格-2.png

表体的合并,指定 spanMethod函数,该函数接收 4 个参数,返回值为对象格式,可写参数为 rowspan、colspan

row:当前行数据

column:当前列数据

rowIndex:当前行索引

columnIndex:当前列索引

# code
// 点击导出触发的函数
handleExport() {
      const instance = new ElMapExportTable(
        {
            column,
            data,
            spanMethod: ({ row, column, rowIndex, columnIndex }) => {
            if (columnIndex === 0) {
              if (rowIndex % 2 === 0) {
                return {
                  rowspan: 2,
                  colspan: 1,
                };
              }
            }
          },
        },
        {
          progress: progress => console.log(progress),
        }
      );
      instance.download("导出表体合并案例");
}

# column
const column = [
        { title: "ID", dataIndex: "id" },
        { title: "姓名", dataIndex: "name" },
        { title: "数值1(元)", dataIndex: "amount1" },
        { title: "数值2(元)", dataIndex: "amount2" },
        { title: "数值3(元)", dataIndex: "amount3" },
];

# data
const data = [
        {
          id: "12987122",
          name: "王小虎1",
          amount1: "234",
          amount2: "3.2",
          amount3: 10,
        },
        {
          id: "12987123",
          name: "王小虎2",
          amount1: "165",
          amount2: "4.43",
          amount3: 12,
        },
        {
          id: "12987124",
          name: "王小虎3",
          amount1: "324",
          amount2: "1.9",
          amount3: 9,
        },
        {
          id: "12987125",
          name: "王小虎4",
          amount1: "621",
          amount2: "2.2",
          amount3: 17,
        },
        {
          id: "12987126",
          name: "王小虎5",
          amount1: "539",
          amount2: "4.1",
          amount3: 15,
        },
];

4. 导出混合合并到 Excel

导出混合表格到Excel.png

混合合并,需要结合表头合并 + 表体合并即可

# code
// 点击导出触发的函数
handleExport() {
      const instance = new ElMapExportTable(
        {
            column,
            data,
            spanMethod: ({ rowIndex, columnIndex }) => {
            if (columnIndex === 0 && rowIndex === 0) {
              return {
                rowspan: 2,
                colspan: 2,
              };
            }
            if (rowIndex === 2 && columnIndex === 2) {
              return {
                rowspan: 1,
                colspan: 3,
              };
            }
            if (rowIndex === 0 && columnIndex === 4) {
              return {
                rowspan: 2,
                colspan: 1,
              };
            }
            if (rowIndex === 6 && columnIndex === 0) {
              return {
                rowspan: 1,
                colspan: 6,
              };
            }
          },
        },
        {progress: progress => console.log(progress)}
      );
      instance.download("导出正常表格案例");
}

# column
const column = [
        { title: "姓名", dataIndex: "name" },
        { title: "年龄", dataIndex: "age" },
        {
          title: "配送信息",
          children: [
            {
              title: "地址",
              children: [
                { title: "省份", dataIndex: "province" },
                { title: "市区", dataIndex: "city" },
                { title: "地址", dataIndex: "address" },
                { title: "邮编", dataIndex: "zip" },
              ],
            },
          ],
        },
];

# data
const data = [
        {
          date: "2016-05-03",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-03",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-03",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-08",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-08",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-07",
          name: "王小虎",
          age: 20,
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
]

5. 导出树形表格到 Excel

导出树形表格.png

支持树形数据的导出,当数据中有 children 字段时会标记为树形的 Excel 如果不需要或配置为其他字段可以用 childrenKey 进行配置

可以通过 indentSize 以控制每一层的缩进宽度

必须添加 treeNode:true 字段

默认会取第一列的字段作为 树形结构展示,如果是其他列可以用 treeField 字段来指定列名

注意:树形结构表体区域不支持合并、表头可以自定义合并

# code
// 点击导出触发的函数
handleExport() {
    const instance = new ElMapExportTable(
        {
            column,
            data,
            treeNode:true,
            treeField: "name",
        },
        {
            progress: progress => console.log(progress),
            indentSize: 1, // 默认是1,可自行调整
        }
    );
    instance.download("导出树形表格案例");
}

# column
const column = const column = [
        { title: "ID", dataIndex: "id" },
        { title: "日期", dataIndex: "date" },
        { title: "姓名", dataIndex: "name" },
        { title: "地址", dataIndex: "address" },
];

# data
const data = [
        {
          id: "1",
          date: "2016-05-02",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1518 弄",
          children: [
            {
              id: "1-1",
              date: "2016-05-02",
              name: "王小虎-1",
              address: "上海市普陀区金沙江路 1518 弄",
            },
            {
              id: "1-2",
              date: "2016-05-02",
              name: "王小虎-2",
              address: "上海市普陀区金沙江路 1518 弄",
              children: [
                {
                  id: "1-2-1",
                  date: "2016-05-02",
                  name: "王小虎-1",
                  address: "上海市普陀区金沙江路 1518 弄",
                },
                {
                  id: "1-2-2",
                  date: "2016-05-02",
                  name: "王小虎-2",
                  address: "上海市普陀区金沙江路 1518 弄",
                },
                {
                  id: "1-2-3",
                  date: "2016-05-02",
                  name: "王小虎-2",
                  address: "上海市普陀区金沙江路 1518 弄",
                },
              ],
            },
          ],
        },
        {
          id: "2",
          date: "2016-05-04",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1517 弄",
        },
        {
          id: "3",
          date: "2016-05-01",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1519 弄",
          children: [
            {
              id: "3-1",
              date: "2016-05-02",
              name: "王小虎-1",
              address: "上海市普陀区金沙江路 1518 弄",
            },
            {
              id: "3-2",
              date: "2016-05-02",
              name: "王小虎-2",
              address: "上海市普陀区金沙江路 1518 弄",
            },
            {
              id: "3-3",
              date: "2016-05-02",
              name: "王小虎-2",
              address: "上海市普陀区金沙江路 1518 弄",
            },
          ],
        },
        {
          id: "4",
          date: "2016-05-03",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1516 弄",
        },
];

6. 导出图片到 Excel

导出图片到Excel.png

dataIndex 对应的数据源设置成数组结构即可

另外对于图片的样式(目前只支持设置图片宽高)提供了 setImageStyle 函数 ,参数格式为对象,包含 data (数据源)、rowIndex (当前行索引)、columnIndex (当前列索引)、type (标识当前是表头还是表体)**

注意:数组中每一项都为 图片 url 路径,错误路径会导致请求失败,保证图片路径和当前项目是同源,否则会导致跨域

# code
// 点击导出触发的函数
handleExport() {
	const instance = new ElMapExportTable(
        {
          column,
          data: this.tableData,
          setImageStyle: ({ data, rowIndex, columnIndex, type }) => {
            return {
              width: 100,
              height: 100,
            };
          },
        },
        { progress: val => console.log(val) }
      );
      instance.download("导出图片到Excel案例");
}

# column
const column = [
        { title: "日期", dataIndex: "date" },
        { title: "姓名", dataIndex: "name" },
        { title: "图片", dataIndex: "images" },
        { title: "地址", dataIndex: "address" },
      ];

# data
const data = [
        {
          date: "2016-05-02",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1518 弄",
          images: [
            "/assets/插入数据到Excel尾部.png",
            "/assets/插入数据到Excel尾部.png",
            "/assets/插入数据到Excel尾部.png",
          ],
        },
        {
          date: "2016-05-04",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1517 弄",
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1519 弄",
        },
        {
          date: "2016-05-03",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1516 弄",
        },
]

7. 设置 Excel 的列样式

设置Excel的列样式.png

提供 setColumnStyle 函数, 该函数接收一个参数,参数格式为对象,包含 columnIndex (当前列索引)

返回值为对象,具体可写的所有样式参考github.com/exceljs/exc…

# code
// 点击导出触发的函数内
handleExport(){
  const instance = new ElMapExportTable(
     {
         column,
         data,
         setColumnStyle({ columnIndex }) {
             if (columnIndex === 2) {
                 return { width: 40, style: { font: { bold: true } } };
             }
         },
     },
     { progress: progress => console.log(progress) }
 );
  instance.download("设置Excel的列样式");
}

8. 设置 Excel 的行样式

设置Excel的行样式.png

提供 setRowStyle 函数

该函数接收一个参数,参数格式为对象,包含 data (数据源)、rowIndex (当前行索引)、columnIndex (当前列索引)、type (标识当前是表头还是表体)

返回值为对象,具体可写的所有样式参考github.com/exceljs/exc…

# code
// 点击导出触发的函数内
handleExport(){
  const instance = new ElMapExportTable(
     {
         column,
         data,
         setRowStyle({ data, columnIndex, rowIndex, type }) {
            console.log({ data, columnIndex, rowIndex, type });
            if (type === "main") {
              return {
                height: 40,
              };
            }
          },
     },
     { progress: progress => console.log(progress) }
 );
  instance.download("设置Excel的行样式");
}

9. 设置 Execl 的单元格样式

设置Excel的单元格样式.png

提供 setCellStyle 函数

该函数接收一个参数,参数格式为对象,包含 data (数据源)、rowIndex (当前行索引)、columnIndex (当前列索引)、type (标识当前是表头还是表体)

返回值为对象,具体可写的所有样式参考github.com/exceljs/exc…

// 点击导出触发的函数内
handleExport(){
  const instance = new ElMapExportTable(
     {
         column,
         data,
         setCellStyle({ data, columnIndex, rowIndex, type }) {
            console.log({ data, columnIndex, rowIndex, type });
            if (type === "main" && columnIndex === 2) {
              return {
                font: {
                  size: 16, // 字体大小
                  bold: true, // 字体加粗
                  italic: true, // 字体倾斜
                  color: { argb: "FFFF0000" }, // 字体颜色
                },
                // fill: {
                //   type: "pattern",
                //   pattern: "solid",
                //   fgColor: { argb: "FF0000FF" }, // 填充背景颜色
                // },
              };
            }
          },
     },
     { progress: progress => console.log(progress) }
 );
  instance.download("设置Excel的单元格样式");
}

10. 自定义 Excel 单元格格式

自定义单元格格式-1.png

自定义单元格格式-2.png

提供 setCellFormat 函数

该函数接收一个参数,参数格式为对象,包含 data (数据源)、rowIndex (当前行索引)、columnIndex (当前列索引)、type (标识当前是表头还是表体)

返回值为对象,具体可写的所有样式参考github.com/exceljs/exc…

# code
// 点击导出触发的函数内
handleExport(){
    const instance = new ElMapExportTable(
        {
            column,
            data,
            setCellFormat: ({ data, rowIndex, columnIndex, type }) => {
                if (type === "header" && rowIndex === 0 && columnIndex === 0) {
                    return {
                        text: "我是超链接",
                        hyperlink: "http://www.chengxiaohui.com",
                        tooltip: "小郑同学的开发路",
                    };
                }
                if (rowIndex === 1 && columnIndex === 0) {
                    return {
                        numFmt: "yyyy-mm-dd",
                    };
                }
            },
        },
        { progress => console.log(progress) }
    );
    instance.download("自定义Excel单元格格式");
}

11. 设置 Excel-Sheet 样式

设置Excel-Sheet样式.png

提供 setSheetStyle 函数

该函数接收一个参数,参数格式为对象,包含 sheetIndex (当前 sheet 索引)

返回值为对象,具体可写的所有样式参考github.com/exceljs/exc…

# code
// 点击导出触发的函数内
handleExport(){
    const instance = new ElMapExportTable(
        {
            column,
            data,
            sheetName: "~~~ 我有名字了 ~~~", // sheet名称
            setSheetStyle: ({ sheetIndex }) => {
                console.log(sheetIndex, "sheetIndex");
                return {
                    properties: { tabColor: { argb: "FFC0000" } }, // 创建带有红色标签颜色的工作表
                    views: [
                        {
                            state: "frozen",
                            xSplit: 1, // 固定1列(同表格固定列)
                            ySplit: 1, // 固定1行(同表格固定行)
                        },
                    ],
                };
            },
        },
        { progress => console.log(progress) }
    );
    instance.download("设置Excel-Sheet样式");
}

12. 临时插入 Excel 头部数据

插入数据到Excel头部.png

提供 setInsertHeader 函数

该函数接收一个参数,参数格式为对象,包含 sheetIndex (当前 sheet 索引)

返回值为对象,对象中可以写 cells (单元格信息及样式)、columnStyle (列样式)、rowStyle (行样式)

# code
// 点击导出触发的函数内
handleExport(){
    const instance = new ElMapExportTable(
        {
            column,
            data,
            setInsertHeader: ({ sheetIndex }) => {
                console.log(sheetIndex);
                return {
                    cells: [
                        {
                            row: 0,
                            col: 0,
                            rowspan: 2, // 占2行
                            colspan: 3, // 占3列
                            text: "我是插入到Excel头部的信息",
                        },
                        {
                            row: 2,
                            col: 0,
                            rowspan: 3,
                            colspan: 3,
                            text: "我也是插入到Excel头部的信息",
                            style: {
                                font: {
                                    size: 16, // 字体大小
                                    bold: true, // 字体加粗
                                    italic: true, // 字体倾斜
                                    color: { argb: "FFFF0000" }, // 字体颜色
                                },
                            },
                        },
                    ],
                };
            },
        },
        { progress => console.log(progress) }
    );
    instance.download("临时插入Excel头部数据");
}

13. 临时插入 Excel 尾部数据

插入数据到Excel尾部.png

setInsertHeader 同配置

提供 setInsertFooter 函数

该函数接收一个参数,参数格式为对象,包含 sheetIndex (当前 sheet 索引)

返回值为对象,对象中可以写 cells (单元格信息及样式)、columnStyle (列样式)、rowStyle (行样式)

# code
// 点击导出触发的函数内
handleExport(){
    const instance = new ElMapExportTable(
        {
            column,
            data,
            setInsertFooter: ({ sheetIndex }) => {
                console.log(sheetIndex);
                return {
                    cells: [
                        {
                            row: 0,
                            col: 0,
                            rowspan: 2, // 占2行
                            colspan: 3, // 占3列
                            text: "我是插入到Excel尾部的信息",
                        },
                        {
                            row: 2,
                            col: 0,
                            rowspan: 3,
                            colspan: 3,
                            text: "我也是插入到Excel尾部的信息",
                            style: {
                                font: {
                                    size: 16, // 字体大小
                                    bold: true, // 字体加粗
                                    italic: true, // 字体倾斜
                                    color: { argb: "FFFF0000" }, // 字体颜色
                                },
                            },
                        },
                    ],
                };
            },
        },
        { progress => console.log(progress) }
    );
    instance.download("临时插入数据到Excel尾部");
}

14. 导出多个 Sheet 到 Excel

导出多个sheet-1.png

导出多个sheet-2.png

传递为一个数组即可,数组中每一项都为一个Sheet,Sheet 的所有配置同之前一样

# code
// 点击导出触发的函数内
handleExport(){
    const instance = new ElMapExportTable(
        [
            { column: column1, data: data1, sheetName: "我是Sheet1" },
            { column: column2, data: data2, sheetName: "我是Sheet2" },
        ],
        { progress: this.handlePercentage }
    );
    instance.download("导出多个Sheet到Excel");
}

15. 导出大数据量表格到 Excel

导出大数据量表格.png

10W 条数据 耗时 4s 左右,不同电脑不同速度

# code
const instance = new ElMapExportTable(
    { column, data },
    { progress: val => console.log(val) }
);
instance.download("导出大数据量表格到Excel");

参数

具体细节在我的github上github.com/Zheng-Chang…

觉得不错请给个star,谢谢~~