Nodejs implementation download Excel file

Nodejs as a new language, the report function is not very perfect.

(1). JS-XLSX: Currently Github on the most number of star processing Excel Library, support parsing a variety of format table xlsx/xlsm/xlsb/xls/csv, parsing using pure JS implementation, write need to rely on Nodejs or filesaver The. JS implementation generates a write to Excel that can generate a child table Excel, which is powerful but slightly more difficult to get started with. Do not provide basic settings Excel Table API example cell width, the document is a bit chaotic, not suitable for quick start;

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

(2). NODE-XLSX: Parsing Excel file data and generating Excel files based on node. JS, only supports xlsx format files;

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

(3). Excel-parser: Parsing Excel file data based on node. JS, supporting XLS and xlsx format files, need to rely on python, too heavy is not practical;

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

(4). Excel-export: Based on node. JS data Generation export Excel file, generate file format for xlsx, can set cell width, API easy to get started, unable to generate worksheet Word table, relatively single, basic functions can basically meet;

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

(5). NODE-XLRD: Extracts data from Excel files based on node. JS, supports only XLS format files, does not support xlsx, is a bit outdated, and is commonly used in xlsx format.

Nodejs a few years ago, developers wrote a lot of node-dependent libraries, but most of them are now in a state of non-maintenance.

It is still updated only node-xlsx Excel-export recommended to use, js-xlsx as a chatty base library (although not even now, the biggest problem of this library is the API is very unfriendly, learning curve high) the ability of the project group can be further encapsulated.

This is a simple download of the demo, simply use the Excel-export,

varExpress = require (' Express ');varRouter =Express. Router ();varServer =Express (); Server.use ('/api ', router); varNodeexcel = 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) {varconf ={}; Conf.stylesxmlfile= "Styles.xml"; Conf.name= "MySheet"; Conf.cols=[{caption:' String ', type:' String ', Beforecellwrite:function(row, celldata) {returncelldata.touppercase (); }, Width:300},{Caption:' Date ', type:' Date ', Beforecellwrite:function(){            varOrigindate =NewDate (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",NewDate (2012, 4, 1),false, 2.7182],        ["M&m<> '",NewDate (DATE.UTC (2013, 6, 9)),false, 1.61803],        ["Null Date",NULL,true, 1.414]    ]; varresult =nodeexcel.execute (conf); Res.setheader (' Content-type ', ' application/vnd.openxmlformats '); Res.setheader ("Content-disposition", "attachment; Filename= "+ encodeuricomponent (" Export list ") +". xlsx "); Res.end (Result,' Binary ');}); Router.get ('/exportmultisheetexcel/:id ',function(req, res, next) {varConfs = []; varconf = {}; Conf.cols=[{caption:' String ', type:' String '}, {caption:' Date ', type:' Date '}, {caption:' BOOL ', type:' BOOL '}, {caption:' Number 2 ', type:' Number '        }]; Conf.rows= [[' Hahai ', (NewDate (DATE.UTC (4, 1))). Oadate (),true, 3.14], ["E", (NewDate (4, 1)). Oadate (),false, 2.7182], ["M&m<>", (NewDate (DATE.UTC (6, 9))). Oadate (),false, 1.2], ["null",NULL,NULL,NULL]];  for(vari = 0; I < 3; i++) {conf= Json.parse (json.stringify (conf));//CloneConf.name = ' sheet ' +i;    Confs.push (conf); }    varresult =Nodeexcel.execute (CONFS); Res.setheader (' Content-type ', ' application/vnd.openxmlformats '); Res.setheader ("Content-disposition", "attachment; Filename= "+" report.xlsx "); Res.end (Result,' Binary ');});varServer=server.listen (8080,function() {Console.log (' 8080 ')})

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

Cols can be a column name for caption that sets the column type (the contents of the first row are populated), type is the column data type, Beforecellwrite can logically manipulate the data before it is populated, and width can define the broadband

Rows is a two-bit array that populates the contents of Excel directly by row and column

Name defines sheet.

It's worth noting. Excel-export If you need to define the default format for Excel, you need to refer to an Excel format header, which is defined in Styles.xml, which can be node_modules/example/ Catalog of items copied in Styles.xml

The example uses the root directory, so we need to put it in the root directory, otherwise we will not find this file.

In actual development, sometimes Excel file export to use Chinese, this time to set the next header and formatted Chinese can be

Res.setheader (' Content-type ', ' application/vnd.openxmlformats;charset=utf-8 ');
Res.setheader ("Content-disposition", "attachment; Filename= "+encodeuricomponent (" Export list ") +". xlsx ");

Nodejs implementation download Excel file