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 code

directory

  • 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 code

2. 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 code

3. 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 code

4. 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 code

5. 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 code

6. 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 code

7. 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 code

8. 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 code

9. 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 code

10. 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 code

11. 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 code

12. 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 code

13. 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 code

Export 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 code

15. 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 code

parameter

The details are on my Github github.com/Zheng-Chang…

Feel good please give a star, thank you ~~