将前端 table 导出 excel , 基于 exceljs + file-saver , 适用于所有表格 基于 数据源的定制化导出 github地址:github.com/Zheng-Chang… 觉的好用请给个star,谢谢~~
npm install table-excel
import { ElMapExportTable } from "table-excel";
目录
- 导出正常表格到 Excel
- 导出表头合并到 Excel
- 导出表体合并到 Excel
- 导出混合合并到 Excel
- 导出树形表格到 Excel
- 导出图片到 Excel
- 设置 Excel 的列样式
- 设置 Excel 的行样式
- 设置 Excel 的单元格样式
- 自定义 Excel 单元格格式
- 设置 Excel-Sheet 样式
- 临时插入 Excel 头部数据
- 临时插入 Excel 尾部数据
- 导出多个 Sheet 到 Excel
- 导出大数据量表格到 Excel
1. 导出正常表格到 Excel
正常按表格格式书写 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
表头的合并,将对应的列设置成相应的树形结构
注意:树形结构默认的孩子字段为 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
表体的合并,指定 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
混合合并,需要结合表头合并 + 表体合并即可
# 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
支持树形数据的导出,当数据中有 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
将 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 的列样式
提供 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 的行样式
提供 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 的单元格样式
提供 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 单元格格式
提供 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 样式
提供 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 头部数据
提供 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 尾部数据
和 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,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
10W 条数据 耗时 4s 左右,不同电脑不同速度
# code
const instance = new ElMapExportTable(
{ column, data },
{ progress: val => console.log(val) }
);
instance.download("导出大数据量表格到Excel");
参数
具体细节在我的github上github.com/Zheng-Chang…
觉得不错请给个star,谢谢~~