Detailed explanation of the general method of easily exporting Excel on JS front-end

background

In project development, we often encounter situations where we need to export data to Excel files. However, this process can be relatively complex and cumbersome, especially for the case of large amounts of data and complex tabular structures. Therefore, we need a general method that can be flexibly used in various scenarios and realize simple and efficient data export operations

Installation dependencies:

npm install file-saver --save
npm install script-loader --save-dev
npm install xlsx --save

If the project is not using npm, use yarn or pnpm.

Create directory vendor under src

Create two files in the directory

/* eslint-disable */
(function (view) {
    "use strict";
     =  || ;
    if ( && ) {
        try {
            new Blob;
            return;
        } catch (e) { }
    }
    // Internally we use a BlobBuilder implementation to base Blob off of
    // in order to support older browsers that only have BlobBuilder
    var BlobBuilder =  ||  ||  || (function (view) {
        var
            get_class = function (object) {
                return (object).match(/^[object\s(.*)]$/)[1];
            }
            , FakeBlobBuilder = function BlobBuilder() {
                 = [];
            }
            , FakeBlob = function Blob(data, type, encoding) {
                 = data;
                 = ;
                 = type;
                 = encoding;
            }
            , FBB_proto = 
            , FB_proto = 
            , FileReaderSync = 
            , FileException = function (type) {
                 = this[ = type];
            }
            , file_ex_codes = (
                "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
                + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
            ).split(" ")
            , file_ex_code = file_ex_codes.length
            , real_URL =  ||  || view
            , real_create_object_URL = real_URL.createObjectURL
            , real_revoke_object_URL = real_URL.revokeObjectURL
            , URL = real_URL
            , btoa = 
            , atob = 
            , ArrayBuffer = 
            , Uint8Array = view.Uint8Array
            ;
         = FB_proto.fake = true;
        while (file_ex_code--) {
            [file_ex_codes[file_ex_code]] = file_ex_code + 1;
        }
        if (!real_URL.createObjectURL) {
            URL =  = {};
        }
         = function (blob) {
            var
                type = 
                , data_URI_header
                ;
            if (type === null) {
                type = "application/octet-stream";
            }
            if (blob instanceof FakeBlob) {
                data_URI_header = "data:" + type;
                if ( === "base64") {
                    return data_URI_header + ";base64," + ;
                } else if ( === "URI") {
                    return data_URI_header + "," + decodeURIComponent();
                } if (btoa) {
                    return data_URI_header + ";base64," + btoa();
                } else {
                    return data_URI_header + "," + encodeURIComponent();
                }
            } else if (real_create_object_URL) {
                return real_create_object_URL.call(real_URL, blob);
            }
        };
         = function (object_URL) {
            if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
                real_revoke_object_URL.call(real_URL, object_URL);
            }
        };
        FBB_proto.append = function (data/*, endings*/) {
            var bb = ;
            // decode data to a binary string
            if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
                var
                    str = ""
                    , buf = new Uint8Array(data)
                    , i = 0
                    , buf_len = 
                    ;
                for (; i < buf_len; i++) {
                    str += (buf[i]);
                }
                (str);
            } else if (get_class(data) === "Blob" || get_class(data) === "File") {
                if (FileReaderSync) {
                    var fr = new FileReaderSync;
                    ((data));
                } else {
                    // async FileReader won't work as BlobBuilder is sync
                    throw new FileException("NOT_READABLE_ERR");
                }
            } else if (data instanceof FakeBlob) {
                if ( === "base64" && atob) {
                    (atob());
                } else if ( === "URI") {
                    (decodeURIComponent());
                } else if ( === "raw") {
                    ();
                }
            } else {
                if (typeof data !== "string") {
                    data += ""; // convert unsupported types to strings
                }
                // decode UTF-16 to binary string
                (unescape(encodeURIComponent(data)));
            }
        };
        FBB_proto.getBlob = function (type) {
            if (!) {
                type = null;
            }
            return new FakeBlob((""), type, "raw");
        };
        FBB_proto.toString = function () {
            return "[object BlobBuilder]";
        };
        FB_proto.slice = function (start, end, type) {
            var args = ;
            if (args < 3) {
                type = null;
            }
            return new FakeBlob(
                (start, args > 1 ? end : )
                , type
                , 
            );
        };
        FB_proto.toString = function () {
            return "[object Blob]";
        };
        FB_proto.close = function () {
             =  = 0;
        };
        return FakeBlobBuilder;
    }(view));
     = function Blob(blobParts, options) {
        var type = options ? ( || "") : "";
        var builder = new BlobBuilder();
        if (blobParts) {
            for (var i = 0, len = ; i < len; i++) {
                (blobParts[i]);
            }
        }
        return (type);
    };
}(typeof self !== "undefined" && self || typeof window !== "undefined" && window ||  || this));

/* eslint-disable */
import {saveAs} from 'file-saver'
import * as XLSX from 'xlsx'
import XLSXS from "xlsx-style"
require('script-loader!file-saver');
require('./Blob');//This is your addressrequire('script-loader!xlsx/dist/');
/**
  *
  * @param {*} table
  * @returns
  * Convert HTML tables to 2D arrays
  * Functions support processing of cell merging, row and column spans, and conversion of specific data types in tables
  */
function generateArray(table) {
  let out = [];
  let rows = ('tr');
  let ranges = [];
  for (let R = 0; R &lt; ; ++R) {
    let outRow = [];
    let row = rows[R];
    let columns = ('td');
    for (let C = 0; C &lt; ; ++C) {
      let cell = columns[C];
      let colspan = ('colspan');
      let rowspan = ('rowspan');
      let cellValue = ;
      if (cellValue !== "" &amp;&amp; cellValue == +cellValue) cellValue = +cellValue;
      //Skip ranges
      (function (range) {
        if (R &gt;=  &amp;&amp; R &lt;=  &amp;&amp;  &gt;=  &amp;&amp;  &lt;= 
          .c) {
          for (let i = 0; i &lt;=  - ; ++i) (null);
        }
      });
      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ({
          s: {
            r: R,
            c: 
          },
          e: {
            r: R + rowspan - 1,
            c:  + colspan - 1
          }
        });
      }
      ;
      //Handle Value
      (cellValue !== "" ? cellValue : null);
      //Handle Colspan
      if (colspan)
        for (let k = 0; k &lt; colspan - 1; ++k) (null);
    }
    (outRow);
  }
  return [out, ranges];
};
function datenum(v, date1904) {
  if (date1904) v += 1462;
  let epoch = (v);
  return (epoch - new Date((1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
/**
  *
  * @param {*} data
  * @param {*} opts
  * @returns
  * JavaScript function for converting 2D arrays into Excel worksheet objects
  * Use the library-provided tooling methods to encode cell references and worksheet scopes to generate worksheet data that can be processed by Excel.
  * Supports converting some data types to data types supported by Excel, such as converting date objects to numeric types and setting corresponding formats.
  */
function sheet_from_array_of_arrays(data, opts) {
  let ws = {};
  let range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  };
  for (let R = 0; R != ; ++R) {
    for (let C = 0; C != data[R].length; ++C) {
      if ( &gt; R)  = R;
      if ( &gt; C)  = C;
      if ( &lt; R)  = R;
      if ( &lt; C)  = C;
      let cell = {
        v: data[R][C]
      };
      if ( == null) continue;
      let cell_ref = .encode_cell({
        c: C,
        r: R
      });
      if (typeof  === 'number')  = 'n';
      else if (typeof  === 'boolean')  = 'b';
      else if ( instanceof Date) {
         = 'n';
         = ._table[14];
         = datenum();
      } else  = 's';
      ws[cell_ref] = cell;
    }
  }
  if ( &lt; 10000000) ws['!ref'] = .encode_range(range);
  return ws;
}
function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
   = [];
   = {};
}
function s2ab(s) {
  let buf = new ArrayBuffer();
  let view = new Uint8Array(buf);
  for (let i = 0; i != ; ++i) view[i] = (i) &amp; 0xFF;
  return buf;
}
export function export_table_to_excel(id) {
  let theTable = (id);
  let oo = generateArray(theTable);
  let ranges = oo[1];
  /* original data */
  let data = oo[0];
  let ws_name = "SheetJS";
  let wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);
  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;
  /* add worksheet to workbook */
  (ws_name);
  [ws_name] = ws;
  let wbout = (wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), "")
}
export function export_json_to_excelhb({
                                         multiHeader = [], // The first row of table header                                         multiHeader2 = [], // The second row of table header                                         header = [], // The third row header                                         data,//The data passed                                         filename, //file name                                         merges = [], // Merge                                         autoWidth = true,// Used to set the column width                                         bookType = 'xlsx'
                                       } = {}) {
  /* original data */
  filename = filename || 'List';
  data = [...data]
  for (let i =  - 1; i &gt; -1; i--) {
    (header[i])
  }
  for (let i =  - 1; i &gt; -1; i--) {
    (multiHeader2[i])
  }
  for (let i =  - 1; i &gt; -1; i--) {
    (multiHeader[i])
  }
  let ws_name = "SheetJS";
  let wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);
  // Set the cell public style  let borderAll = { //Outside box line of cell    top: {
      style: 'thin',
    },
    bottom: {
      style: 'thin',
    },
    left: {
      style: 'thin',
    },
    right: {
      style: 'thin',
    }
  };
  for (let key in ws) {
    // Cell common style settings    if (ws[key] instanceof Object) {
      ws[key].s = {
        border: borderAll,
        alignment: {
          horizontal: 'center', //Align horizontally centered          vertical: 'center',//Center vertically          wrapText: 1,// Automatic line wrap        },
        // fill: { // Background color        //     fgColor: { rgb: 'dbf3f2' }
        // },
        font: {
          sz: 10,//Style style and color settings of fonts in cells          color: {
            rgb: '000000'
          }
        },
        bold: true,
        numFmt: 0
      }
    }
    //Add a style to a specific grid (with '1', that is, the first line title), the same is true for the following    // if ((/[^0-9]/ig, '') === '1') {
    //     ws[key].s = {
    //         ...ws[key].s,
    // fill: { // Background color    //             fgColor: { rgb: 'd4e6fd' }
    //         },
    // font: {//Overwrite font    // name: 'isoline',    //             sz: 10,
    //             // bold: true
    //         },
    //     }
    // }
    if (key === 'A1') {
      ws[key].s = {
        ...ws[key].s,
        fill: { //Background color          fgColor: {rgb: 'd4e6fd'}
        },
      }
    }
    // if (key === 'B2' || key === 'C2' || key === 'D2' || key === 'E2' || key === 'F2' || key === 'G2') {
    //     ws[key].s = {
    //         ...ws[key].s,
    // fill: { // Background color    //             fgColor: { rgb: 'fbedd7' }
    //         }
    //     }
    // }
  }
  if ( &gt; 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    (item =&gt; {
      ws['!merges'].push(.decode_range(
        .encode_cell() + ':' + .encode_cell()
      ));
      // Set the cell style      ws[.encode_cell()].s = ;
    });
  }
  // ws['I2'] = ws['H2'] = ws['G2'] = ws['F2'] = ws['E2'] = ws['D2'] = ws['C2'] = ws['B2'] = ws['A2']//Style settings for cells in the second row (if it is the first row of merge, it is 1)  // if ( &gt; 0) {
  //     if (!ws['!merges']) ws['!merges'] = [];
  //     (item =&gt; {
  //         (item);
  //         ws['!merges'].push(.decode_range(item))
  //     })
  // }
  if (autoWidth) {
    let colWidths = [];
    // Calculate all cell widths of each column    // traverse the line first    ((row) =&gt; {
      // Column number      let index = 0
      // traverse columns      for (const key in row) {
        if (colWidths[index] == null) colWidths[index] = []
        switch (typeof row[key]) {
          case 'string':
          case 'number':
          case 'boolean':
            colWidths[index].push(getCellWidth(row[key]))
            break
          case 'object':
          case 'function':
            colWidths[index].push(0)
            break
        }
        index++
      }
    })
    ws['!cols'] = [];
    // Settings of the third row header    ((widths, index) =&gt; {
      // Calculate the width of the column header      // (getCellWidth(header[index]))
      // Set the maximum value to column width      ws['!cols'].push({
        wch: (...widths)
      })
    })
  }
  /* add worksheet to workbook */
  (ws_name);
  [ws_name] = ws;
  let wbout = (wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}
export function getCellWidth(value) {
  if (value == null) {
    return 10
  } else if (().charCodeAt(0) &gt; 255) {
    // Determine whether it contains Chinese    let length = ().length * 2
    if (length &gt; 60) {
      length = length - 40
      //The width here can be set by yourself. Set wrapText in the front: 1 You can wrap lines in the cell    }
    return length
  } else {
    return ().length * 1.2
  }
}

import {saveAs} from 'file-saver'
import * as XLSX from 'xlsx'
import XLSXS from "xlsx-style"
`file-saver`Importing in the library`saveAs`function，Used to save files。
`xlsx`Importing in the library所有的功能，And take it as`XLSX`Object reference。
`xlsx-style`Library import supports styles，And name it`XLSXS`。

require('script-loader!file-saver'); 
require('./Blob');//This is your addressrequire('script-loader!xlsx/dist/');
This part of the code uses`require`and`script-loader`Come to loadand引入所需的脚本document。
in，pass`script-loader!file-saver`Come to load`file-saver`Scripts required by the library，
`require('./Blob')`Used to load custom``document，
`script-loader!xlsx/dist/`For loading`xlsx`The core script of the library。

The specific paths and file names in the above code snippets may vary depending on your project structure, and you need to adjust them according to the actual situation.

Through the above steps, you can use it in your projectsaveAs()Functions save files locally and useXLSXandXLSXSObjects perform processing and operation of Excel files.

Code implementation

There are many files on the Internet, which are subtle and basically the same according to the individual project, but there are a lot of data charts and various types of tables in the project. Without a general method of encapsulation, the number of code duplication will increase and will not be easy to maintain.

Therefore, I made a general method based on Export2Excel, the code is as follows:

Create a vendor/ file

// Export Excel general methodasync function exportExcel(multiHeader, multiHeader2, filterVal, tableData, tabulationTitle, indexNumber) {
  // Export title  const EXPORT_FILENAME = tabulationTitle;
  // Cell style 1 dbf3f2  const style1 = {
    fill: {patternType: 'solid', fgColor: {rgb: 'dbf3f2'}},
    border: {top: {style: 'thin'}, left: {style: 'thin'}, bottom: {style: 'thin'}, right: {style: 'thin'}},
    alignment: {horizontal: 'center', vertical: 'center', wrapText: 1}
  };
  // Cell style 2 fbedd7  const style2 = {
    fill: {patternType: 'solid', fgColor: {rgb: 'fbedd7'}},
    border: {top: {style: 'thin'}, left: {style: 'thin'}, bottom: {style: 'thin'}, right: {style: 'thin'}},
    alignment: {horizontal: 'center', vertical: 'center', wrapText: 1}
  };
  // Combine cells of all headers  let headerList = [];
  let titleList = [];
  let styleNumber = 1; // Initially 1 level header style judgment  let headerStyle = style2;
  let rowIndex = 1;
  let colIndex = 1;
  let isOdd = true;
  let count = 0;
  const indexNumber2 = indexNumber + 1
  // First-level header merge and color assignment  if (multiHeader[0] &amp;&amp; multiHeader[0].length &gt; 0) {
    multiHeader[0].forEach((group, index) =&gt; {
      if (rowIndex &lt; multiHeader[0].length) {
        ({s: {r: 0, c: rowIndex}, e: {r: 0, c: rowIndex + indexNumber}, style: headerStyle});
        rowIndex += indexNumber2;
      }
      // Modify the style value      if (styleNumber === 1) {
        styleNumber = 2 // Modify the style value        headerStyle = style1
      } else {
        styleNumber = 1 // Modify the style value        headerStyle = style2
      }
    });
  }
  if (multiHeader2[0] &amp;&amp; multiHeader2[0].length &gt; 0) {
    multiHeader2[0].forEach((group, index) =&gt; {
      if (colIndex &lt; multiHeader2[0].length) {
        ({s: {r: 1, c: colIndex}, e: {r: 1, c: colIndex}, style: isOdd ? style2 : style1});
        colIndex += 1;
        count++;
      }
      if (count % indexNumber2 === 0) {
        isOdd = !isOdd;
      }
    });
  }
  // Combine cells of all headers  const merges = [
    // Merge A1 and A2 and set background color 1    {
      s: {r: 0, c: 0},
      e: {r: 1, c: 0},
      style: {
        fill: {patternType: 'solid', fgColor: {rgb: 'd4e6fd'}},
        border: {top: {style: 'thin'}, left: {style: 'thin'}, bottom: {style: 'thin'}, right: {style: 'thin'}},
        alignment: {horizontal: 'center', vertical: 'center', wrapText: 1}
      }
    },
    ...headerList,
    ...titleList // Secondary table header  ];
  const data = ((v) =&gt; ((j) =&gt; v[j]));
  await import('/src/vendor/').then((excel) =&gt; {
    excel.export_json_to_excelhb({
      multiHeader, // Here is the header of the first row      multiHeader2, // Here is the header of the second row      data,
      filename: EXPORT_FILENAME,
      merges
    });
  });
}
export default {exportExcel}

The code given above is an example of exporting a common method for Excel. Let's gradually analyze the key logic:

First, in the function parameters, we pass in some necessary parameters, such as multi-level header (multiHeaderandmultiHeader2), data filtering column (filterVal), table data (tableData), table title (tabulationTitle) and index number (indexNumber). These parameters are used as input to the function to generate an Excel file.
Next, we define some constants, such as export file name (EXPORT_FILENAME) and two cell styles of different styles (style1andstyle2). These styles can be used to beautify the exported Excel tables, making them more readable and professional.
After the header merge and style setting, we useimportThe syntax is introduced withlibrary file. This library file is a core tool used to implement data export to Excel, we can call itexport_json_to_excelhbMethod to implement the export function.
Finally, we call the sorted data, file name to be exported, header style and cell merging information as parameters.export_json_to_excelhbMethod implements the export operation.

Export Example

Introduce the global

import Excel from './vendor/exportExcel'
.$Excel = Excel;

// Call the export Excel common methodasync exportExcelDemo() {
  let multiHeader = []; // The header of the first row  let multiHeader2 = [] // The header of the second row  let filterVal = [] // Corresponding fields  multiHeader = [[, 'Game Hall', '', '', 'Game Hall', '', '', 'Internet cafe', '', '',]]
  multiHeader2 = [['', , , 'Year-on-year', , , 'Year-on-year', , , 'Year-on-year',]];
  filterVal = ['areaName',
                    'sumValue03', 'momPerCent', 'yoyPerCent',
                    'sumValue032', 'momPerCent2', 'yoyPerCent2',
                    'sumValue033', 'momPerCent3', 'yoyPerCent3',
                ];
  const tabulationTitle = 'Export Table'; // Export title  const indexNumber =  === 'day' ? 6 : 7 //Adjust according to business needs, the required field data is also different  await this.$(multiHeader, multiHeader2, filterVal, tableData, tabulationTitle,indexNumber);
}
// Call example();

Application scenarios

This general export Excel method is suitable for a variety of front-end development scenarios, such as:

In a data visualization project, export the chart data to an Excel file for further analysis and processing.
In the data management system, the query data is exported into Excel tables, which is convenient for users to use and share offline.
In the e-commerce platform, product information, sales reports, etc. are exported into Excel files to facilitate merchants to conduct business analysis and statistics.

Summarize

Through this general export Excel method, we can easily handle various data export requirements and can adapt to different table structures and data volumes. Just simply configure parameters to generate beautiful, easy to read and process Excel files.

I hope this article can be helpful to everyone's data export needs in front-end development.

The above is a detailed explanation of the general methods of JS front-end to easily export Excel. For more information on JS general methods of exporting Excel, please pay attention to my other related articles!