Three scenarios of realizing excel export function

• The back end generates excel and encapsulates it, and directly returns the file address to the front end through the interface. After getting the file address, the front end creates a tag through js, assigns the file address to the href attribute of a tag, calls the click method of a tag, and finally removes the a tag

  const link = document.createElement('a')
  link.download = fileName // Assignment file name
  link.style.display = 'none'
  link.href = path
  document.body.appendChild(link)
  link.click()
  document.body.removeChild(link)
  

• The back end generates excel, but the interface returns the binary stream that generates excel

  const content = res.blob // Get the file stream first
  const blob = new Blob([content], {type: 'application/octet-stream'}) // Create a blob instance to convert the file stream into a class file object
  const url = URL.createObjectURL(blob) // The operation behind the generated file download url is the same as the first type. Generate a tag and click
  

• The front end generates excel, and the back end only provides data filled in Excel

  The following will describe in detail the principle of buried point export, how the front end generates excel and how to fill in data

Principle of buried point derivation

Embedded point export is generally used for personalized templates. Tables with regular data can directly use the xlsx populate library to fill data and export through traversal, which will not be repeated here.

According to the principle of custom template, the embedded points are embedded in the general template

The above name and employee number are self-contained in the template, #name# and #jobNum# are our buried points. The format of buried points here can be customized, which is mainly used to capture the cells to be filled through regular capture# The attribute name in the number is also user-defined, which is used to find the corresponding attribute value in the data source

After embedding the points, a template for parsing is built. Next, we parse the file through the xlsx populate library. After parsing, we traverse to obtain the cells in the content area and the values in the cells. At this time, the points we buried before come into use. We can capture the cells of the buried points through regular expressions, read their attributes, and then go to the data source to find the corresponding values of the attributes, Then replace the original buried point with the found value. Finally, export the updated excel form through xlsx populate.

realization

Here we need to declare some tools we need to use in advance. Firstly, it must be the xlsx populate library. Secondly, I use the get method in the lodash library to search the value of the specified field in the data source. Finally, I use the file Saver library to download the generated excel table. You can go to the official websites of these libraries to download packages first.

// Import tool 
import XLSX from "xlsx-populate";
import { saveAs } from "file-saver";
import _ from "lodash";

It should be mentioned here that the templates we buried earlier can generally be placed in the static resource directory of the project, public/static. Moreover, when the browser uses the xlsx populate library, it can only use the api fromDataAsync to parse the file. The official example is like this

// The first step is to define a method to parse the template
const getExcelTemplate = url => {
    return new Promise(resolve => {
        const req = new XMLHttpRequest();
        req.open("GET", url, true);
        req.responseType = "arraybuffer";
        req.onreadystatechange = async () => {
            if (req.readyState === 4 && req.status === 200) {
                // Here, you can parse the template and resolve the parsed data
                const workbook = await XLSX.fromDataAsync(req.response);
                resolve(workbook);
            }
        };
        req.send();
    });
};

// Step 2: fill in data as the data source
async function fill(data) {
    // Get the parsed template url first, which is the path where you save the template
    const BOOK = await getExcelTemplate("/static/performance-temp/probation-temp.xlsx");

    // Get sheet
    const sheet = BOOK.sheet("Sheet1");
    for (let row = 3; row < 29; row++) 
        // Here, the traversal range of row and col is determined according to the content range of their own template
        for (let col = 1; col <= 16; col++) {
            // Gets the value of each cell
            const val = sheet.row(row).cell(col).value();

            const rule = /^#.+#$/;
            if (rule.test(val)) {
                    // The buried point cell is obtained here
                    // Find the value we want to fill in from the data source, and pay attention to removing the special symbols added by our previous buried points
                	const target = _.get(data, val.replace(/#/g,''))
                    // fill
                    sheet.row(row).cell(col).value(target);
            }
        }
    }
	return BOOK
}

// The last step is to download the filled excel
const blob = await fill(data).outputAsync();
saveAs(blob, `fileName.xlsx`);