How to implement the function of downloading Excel files in NodeJs?

NodeJsHow to implement the function of downloading Excel files? The following article will introduce it to you. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

[Video tutorial recommendation: node js tutorial]

As a new language, nodejs’ reporting function is not very complete. .

(1).js-xlsx: Currently the library with the largest number of stars on Github for processing Excel. It supports parsing multiple format tables XLSX / XLSM / XLSB / XLS / CSV. The parsing is implemented in pure js and written You need to rely on nodejs or FileSaver .js to generate and write Excel. You can generate sub-table Excel, which is powerful, but it is slightly difficult to get started. It does not provide basic setting of Excel table api example cell width, the document is a bit messy and not suitable for getting started quickly;

https://github.com/SheetJS/js-xlsx

(2). node-xlsx: parses excel file data and generates excel files based on Node.js, only supports xlsx format files;

https://github.com/mgcrea/node-xlsx

(3 ).excel-parser: parses excel file data based on Node.js, supports xls and xlsx format files, needs to rely on python, is too heavy and not practical;

https://github.com/leftshifters/excel- parser

(4).excel-export: Generate and export data to excel files based on Node.js. The generated file format is xlsx. You can set the cell width. The API is easy to use. It cannot generate worksheet words. It is relatively simple. , the basic functions can be basically satisfied;

https://github.com/functionscope/Node-Excel-Export

(5).node-xlrd: based on node.js from excel file Extracting data only supports xls format files. It does not support xlsx. It is a bit outdated and the commonly used format is XLSX.

In the years when nodejs first came out, developers wrote a lot of node dependency libraries, but most of them are now unmaintained.

Only node-xlsx and excel-export are still being continuously updated. js-xlsx is a large and comprehensive basic library (although it is no longer available now. The biggest problem with this library is that the API is very Unfriendly, high learning curve) A capable project team can further encapsulate,.

This article is a simple downloaded DEMO, simply use excel-export,

var express = require('express');var router = express.Router();var server =  express();

server.use('/api', router); 
var nodeExcel = require('excel-export');
 
const disableLayout ={layout: false};

 router.get('/test', function(req, res, next) {
     res.json({
         code:200
     })
 }) 
// disable interface layout.hbs  user config layout: falserouter.get('/exportExcel/:id', function(req, res, next) {    var conf ={};
    conf.stylesXmlFile = "styles.xml";
    conf.name = "mysheet";
    conf.cols = [{
        caption:'string',
        type:'string',
        beforeCellWrite:function(row, cellData){            return cellData.toUpperCase();
        },
        width:300
    },{
        caption:'date',
        type:'date',
        beforeCellWrite:function(){            var originDate = new Date(Date.UTC(1899,11,30));            return function(row, cellData, eOpt){
                console.log((cellData - originDate));                if (eOpt.rowNum%2){
                    eOpt.styleIndex = 1;
                }                else{
                    eOpt.styleIndex = 2;
                }                if (cellData === null){
                    eOpt.cellType = 'string';                    return 'N/A';
                } else
                    return (cellData - originDate) / (24 * 60 * 60 * 1000);
            }
        }()
    },{
        caption:'bool',
        type:'bool'
    },{
        caption:'number',
        type:'number'
    }];
    conf.rows = [
        ['pi', '2013-12-5', true, 3.14],
        ["e", new Date(2012, 4, 1), false, 2.7182],
        ["M&M<>'", new Date(Date.UTC(2013, 6, 9)), false, 1.61803],
        ["null date", null, true, 1.414]
    ];    var result = nodeExcel.execute(conf);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats');
    res.setHeader("Content-Disposition", "attachment; filename=" + encodeURIComponent("导出列表")+".xlsx");
    res.end(result, 'binary');
});
 
router.get('/exportmultisheetExcel/:id', function(req, res, next) {    var confs = [];    var conf = {};
    conf.cols = [{
        caption: 'string',
        type: 'string'
    },
        {
            caption: 'date',
            type: 'date'
        },
        {
            caption: 'bool',
            type: 'bool'
        },
        {
            caption: 'number 2',
            type: 'number'
        }];
    conf.rows = [['hahai', (new Date(Date.UTC(2013, 4, 1))).oaDate(), true, 3.14], ["e", (new Date(2012, 4, 1)).oaDate(), false, 2.7182], ["M&M<>'", (new Date(Date.UTC(2013, 6, 9))).oaDate(), false, 1.2], ["null", null, null, null]];    for (var i = 0; i < 3; i++) {
        conf = JSON.parse(JSON.stringify(conf));   //clone
        conf.name = 'sheet'+i;
        confs.push(conf);
    }    var result = nodeExcel.execute(confs);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats');
    res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
    res.end(result, 'binary');
});var server=server.listen(8080,function(){
    console.log('8080')
})

3.excel-export provides 4 types of data formats, numbers, time, true and false , the default string

cols can set the column type caption to the column name (the content of the first row will be filled in), type is the column data type, beforeCellWrite can perform logical processing on the data before filling, and width can Define wideband

rows as a two-digit array, fill the excel content directly in rows and columns

name defines the name of the sheet

It is worth noting that if excel-export needs to be defined The default format of excel needs to reference an excel format header. This header is defined in styles.xml. This file can be copied in the corresponding directory of the project in node_modules/example/styles.xml

The example uses the root directory, so we need to put it in the root directory, otherwise it will report that the file cannot be found.

In actual development, sometimes excel files need to be exported in Chinese. At this time, you need to set the header and format the Chinese language.

res.setHeader('Content-Type', ' application/vnd.openxmlformats;charset=utf-8');
res.setHeader("Content-Disposition", "attachment; filename=" encodeURIComponent("Export List") ".xlsx");

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of How to implement the function of downloading Excel files in NodeJs?. For more information, please follow other related articles on the PHP Chinese website!