Export front-end table to Excel, based on Exceljs + file-saver, applicable to all tables based on data source custom export Github address: github.com/Zheng-Chang… Please give me a star, thank you ~~
npm install table-excel
import { ElMapExportTable } from "table-excel";
Copy the codedirectory
- Export the normal table to Excel
- Export table headers to Excel
- Export table body to Excel
- Export blend merge to Excel
- Export the tree table to Excel
- Export images to Excel
- Set Excel column styles
- Set Excel line styles
- Set the Excel cell style
- Custom Excel cell format
- Set the Excel Sheet style
- Temporarily insert Excel header data
- Temporarily insert Excel tail data
- Export multiple sheets to Excel
- Export large data table to Excel
1. Export the normal table to Excel
Normally, column and data are written in table format. DataIndex is the field of the corresponding data source
Note: The key in column is title by default and can be set by columnKey
# code
// Click export to trigger the function
handleExport() {
const instance = new ElMapExportTable(
{ column, data },
{ progress: progress= > console.log(progress) }// Progress bar callback
);
instance.download("Exporting normal Forms");
}
# column
const column = [
{ title: "Date".dataIndex: "date" }, // Title is the excel column name and dataIndex is the data source field corresponding to the current column
{ title: "Name".dataIndex: "name" },
{ title: "Address".dataIndex: "address"},]; # dataconst data = [
{
date: "2016-05-02".name: "Wang Xiaohu".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {date: "2016-05-04".name: "Wang Xiaohu".address: Lane 1517, Jinshajiang Road, Putuo District, Shanghai}, {date: "2016-05-01".name: "Wang Xiaohu".address: Lane 1519, Jinshajiang Road, Putuo District, Shanghai}, {date: "2016-05-03".name: "Wang Xiaohu".address: Lane 1516, Jinshajiang Road, Putuo District, Shanghai,},]Copy the code2. Export the table header to Excel
Table header merge, set the corresponding column into the corresponding tree structure
Note: The default child field for the tree structure is children, which can be set by childrenKey
# code
// Click export to trigger the function
handleExport() {
const instance = new ElMapExportTable(
{ column, data },
{ progress: progress= > console.log(progress) }// Progress bar callback
);
instance.download("Exporting table header merge case");
}
# column
const column = [
{ title: "Date".dataIndex: "date" },
{
title: "Delivery information".children: [{title: "Name".dataIndex: "name" },
{
title: "Address".children: [{title: "Province".dataIndex: "province" },
{ title: "Downtown".dataIndex: "city" },
{ title: "Address".dataIndex: "address" },
{ title: "Zip code".dataIndex: "zip"},],},],},]; # dataconst data = [
{
date: "2016-05-03".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-02".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-04".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-01".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-08".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-06".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-07".name: "Wang Xiaohu".province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333,},]Copy the code3. Export the table body to Excel
Table body merge, specifying spanMethod function, which takes four arguments, returns values in object format, and writable arguments rowSPAN, colSPAN
Row: indicates the current row data
Column: indicates the current column data
RowIndex: index of the current row
ColumnIndex: index of the current column
# code
// Click export to trigger the function
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("Export table body merge case");
}
# column
const column = [
{ title: "ID".dataIndex: "id" },
{ title: "Name".dataIndex: "name" },
{ title: "Number 1 (yuan)".dataIndex: "amount1" },
{ title: Value 2 (yuan).dataIndex: "amount2" },
{ title: Number 3 (yuan).dataIndex: "amount3"},]; # dataconst data = [
{
id: "12987122".name: "Wang Xiaohu 1".amount1: "234".amount2: "3.2".amount3: 10}, {id: "12987123".name: "Wang Xiaohu 2".amount1: "165".amount2: "4.43".amount3: 12}, {id: "12987124".name: "Wang Xiaohu 3".amount1: "324".amount2: "1.9".amount3: 9}, {id: "12987125".name: "Wang Xiaohu 4".amount1: "621".amount2: "2.2".amount3: 17}, {id: "12987126".name: "Wang Xiaohu 5".amount1: "539".amount2: "4.1".amount3: 15,},];Copy the code4. Export mixes and merge into Excel
Mixed merge, need to combine table header merge + table body merge can be
# code
// Click export to trigger the function
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("Exporting normal Forms");
}
# column
const column = [
{ title: "Name".dataIndex: "name" },
{ title: "Age".dataIndex: "age" },
{
title: "Delivery information".children: [{title: "Address".children: [{title: "Province".dataIndex: "province" },
{ title: "Downtown".dataIndex: "city" },
{ title: "Address".dataIndex: "address" },
{ title: "Zip code".dataIndex: "zip"},],},],},]; # dataconst data = [
{
date: "2016-05-03".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-03".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-03".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-01".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-08".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-08".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333}, {date: "2016-05-07".name: "Wang Xiaohu".age: 20.province: "Shanghai".city: "Putuo District".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.zip: 200333,},]Copy the code5. Export the tree table to Excel
Support for exporting tree data. Excel is marked as a tree when the children field is in the data and can be configured with childrenKey if not required or configured for other fields
You can control the indentation width of each layer by indentSize
The treeNode: True field must be added
By default, columns in the first column are displayed as a tree structure, while other columns can be named using the treeField field
Note: tree structure table body region does not support merge, table header can be customized merge
# code
// Click export to trigger the function
handleExport() {
const instance = new ElMapExportTable(
{
column,
data,
treeNode:true.treeField: "name"}, {progress: progress= > console.log(progress),
indentSize: 1.// The default value is 1}); instance.download("Exporting tree tables");
}
# column
const column = const column = [
{ title: "ID".dataIndex: "id" },
{ title: "Date".dataIndex: "date" },
{ title: "Name".dataIndex: "name" },
{ title: "Address".dataIndex: "address"},]; # dataconst data = [
{
id: "1".date: "2016-05-02".name: "Wang Xiaohu".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.children: [{id: "1-1".date: "2016-05-02".name: "Wang Xiaohu-1".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {id: "1-2".date: "2016-05-02".name: "Wang Xiaohu-2".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.children: [{id: "1-2-1".date: "2016-05-02".name: "Wang Xiaohu-1".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {id: "1-2-2".date: "2016-05-02".name: "Wang Xiaohu-2".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {id: "1-2-3".date: "2016-05-02".name: "Wang Xiaohu-2".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai,},],},],}, {id: "2".date: "2016-05-04".name: "Wang Xiaohu".address: Lane 1517, Jinshajiang Road, Putuo District, Shanghai}, {id: "3".date: "2016-05-01".name: "Wang Xiaohu".address: Lane 1519, Jinshajiang Road, Putuo District, Shanghai.children: [{id: "3-1".date: "2016-05-02".name: "Wang Xiaohu-1".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {id: "3-2".date: "2016-05-02".name: "Wang Xiaohu-2".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai}, {id: "3-3".date: "2016-05-02".name: "Wang Xiaohu-2".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai,},],}, {id: "4".date: "2016-05-03".name: "Wang Xiaohu".address: Lane 1516, Jinshajiang Road, Putuo District, Shanghai,},];Copy the code6. Export the image to Excel
Set the dataIndex data source to an array structure
In addition, the setImageStyle function is provided for the image style (currently, only the image width and height can be set). The parameter format is an object, including data (data source), rowIndex (current rowIndex), columnIndex (current columnIndex), and type (Identifies the current table header or body)**
Note: each item in the array is the image URL path. Incorrect path will cause the request to fail. Ensure that the image path is the same as the current project, otherwise it will cause cross-domain
# code
// Click export to trigger the function
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("Export image to Excel case");
}
# column
const column = [
{ title: "Date".dataIndex: "date" },
{ title: "Name".dataIndex: "name" },
{ title: "Image".dataIndex: "images" },
{ title: "Address".dataIndex: "address"},]; # dataconst data = [
{
date: "2016-05-02".name: "Wang Xiaohu".address: Lane 1518, Jinshajiang Road, Putuo District, Shanghai.images: [
"/assets/ Insert data into the end of Excel. PNG"."/assets/ Insert data into the end of Excel. PNG"."/assets/ Insert data into the end of Excel. PNG",]}, {date: "2016-05-04".name: "Wang Xiaohu".address: Lane 1517, Jinshajiang Road, Putuo District, Shanghai}, {date: "2016-05-01".name: "Wang Xiaohu".address: Lane 1519, Jinshajiang Road, Putuo District, Shanghai}, {date: "2016-05-03".name: "Wang Xiaohu".address: Lane 1516, Jinshajiang Road, Putuo District, Shanghai,},]Copy the code7. Set Excel column styles
Provides the setColumnStyle function, which takes a parameter in the format of an object containing columnIndex (the current columnIndex)
The return value is an object. See github.com/exceljs/exc…
# code
// Click export to trigger the function
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("Set Excel column styles");
}
Copy the code8. Set Excel line styles
Provides the setRowStyle function
This function takes a single argument in the format of an object, including data (data source), rowIndex (current rowIndex), columnIndex (current columnIndex), and type (identifying the current head or body of the table).
The return value is an object. See github.com/exceljs/exc…
# code
// Click export to trigger the function
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("Set Excel line styles");
}
Copy the code9. Set the cell style for Execl
Provide the setCellStyle function
This function takes a single argument in the format of an object, including data (data source), rowIndex (current rowIndex), columnIndex (current columnIndex), and type (identifying the current head or body of the table).
The return value is an object. See github.com/exceljs/exc…
// Click export to trigger the function
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.// Font size
bold: true.// Make the font bold
italic: true.// Font slant
color: { argb: "FFFF0000" }, // Font color
},
// fill: {
// type: "pattern",
// pattern: "solid",
// fgColor: {arGB: "FF0000FF"}, // Fill the background color
// },}; }}}, {progress: progress= > console.log(progress) }
);
instance.download("Set Excel cell styles");
}
Copy the code10. Customize Excel cell formats
Provides the setCellFormat function
This function takes a single argument in the format of an object, including data (data source), rowIndex (current rowIndex), columnIndex (current columnIndex), and type (identifying the current head or body of the table).
The return value is an object. See github.com/exceljs/exc…
# code
// Click export to trigger the function
handleExport(){
const instance = new ElMapExportTable(
{
column,
data,
setCellFormat: ({ data, rowIndex, columnIndex, type }) = > {
if (type === "header" && rowIndex === 0 && columnIndex === 0) {
return {
text: "I'm a hyperlink".hyperlink: "http://www.chengxiaohui.com".tooltip: "Xiao Zheng's Development Road"}; }if (rowIndex === 1 && columnIndex === 0) {
return {
numFmt: "yyyy-mm-dd"}; }}}, {progress= > console.log(progress) }
);
instance.download("Custom Excel cell format");
}
Copy the code11. Set the Excel Sheet style
Provide the setSheetStyle function
This function takes an argument in the form of an object, including sheetIndex.
The return value is an object. See github.com/exceljs/exc…
# code
// Click export to trigger the function
handleExport(){
const instance = new ElMapExportTable(
{
column,
data,
sheetName: "I have a name."./ / the name of the sheet
setSheetStyle: ({ sheetIndex }) = > {
console.log(sheetIndex, "sheetIndex");
return {
properties: { tabColor: { argb: "FFC0000"}},// Create a worksheet with a red label color
views: [{state: "frozen".xSplit: 1.// select * from table1;
ySplit: 1.// select * from table;},]}; }, {},progress= > console.log(progress) }
);
instance.download("Set the Excel Sheet style");
}
Copy the code12. Temporarily insert Excel header data
Provide the setInsertHeader function
This function takes an argument in the form of an object, including sheetIndex.
The return value is an object that can write cells (cell information and style), columnStyle (columnStyle), or rowStyle (rowStyle)
# code
// Click export to trigger the function
handleExport(){
const instance = new ElMapExportTable(
{
column,
data,
setInsertHeader: ({ sheetIndex }) = > {
console.log(sheetIndex);
return {
cells: [{row: 0.col: 0.rowspan: 2./ / 2 rows
colspan: 3./ / 3 columns
text: "I'm the information inserted into the Excel header."}, {row: 2.col: 0.rowspan: 3.colspan: 3.text: "I'm also plugging into the Excel header.".style: {
font: {
size: 16.// Font size
bold: true.// Make the font bold
italic: true.// Font slant
color: { argb: "FFFF0000" }, // Font color},},},],}; }, {},progress= > console.log(progress) }
);
instance.download("Temporarily insert Excel header data");
}
Copy the code13. Temporarily insert Excel tail data
The same configuration as Set Server Ader
Provide the setInsertFooter function
This function takes an argument in the form of an object, including sheetIndex.
The return value is an object that can write cells (cell information and style), columnStyle (columnStyle), or rowStyle (rowStyle)
# code
// Click export to trigger the function
handleExport(){
const instance = new ElMapExportTable(
{
column,
data,
setInsertFooter: ({ sheetIndex }) = > {
console.log(sheetIndex);
return {
cells: [{row: 0.col: 0.rowspan: 2./ / 2 rows
colspan: 3./ / 3 columns
text: "I'm the information that I inserted at the end of Excel."}, {row: 2.col: 0.rowspan: 3.colspan: 3.text: "I'm also plugging in the information at the end of Excel.".style: {
font: {
size: 16.// Font size
bold: true.// Make the font bold
italic: true.// Font slant
color: { argb: "FFFF0000" }, // Font color},},},],}; }, {},progress= > console.log(progress) }
);
instance.download("Temporarily insert data to the end of Excel");
}
Copy the codeExport multiple sheets to Excel
Pass it to an array, where each entry is a Sheet, and all the configuration of the Sheet is the same as before
# code
// Click export to trigger the function
handleExport(){
const instance = new ElMapExportTable(
[
{ column: column1, data: data1, sheetName: "I am Sheet1" },
{ column: column2, data: data2, sheetName: "I am Sheet2"{},],progress: this.handlePercentage }
);
instance.download(Export multiple sheets to Excel);
}
Copy the code15. Export large data table to Excel
10W data takes about 4 seconds. Different computers have different speeds
# code
const instance = new ElMapExportTable(
{ column, data },
{ progress: val= > console.log(val) }
);
instance.download("Export large data table to Excel");
Copy the codeparameter
The details are on my Github github.com/Zheng-Chang…
Feel good please give a star, thank you ~~