Node.JS Excel-Export

Nice little module that is assisting when creating excel exports from datasets. It takes normal array-of-objects dataset plus a json report specification and builds excel(.xlsx) file. It supports styling and re-formating of the data on the fly. Check the example usage for more information.

Installation

npm install node-excel-export

Usage

• Check here, for more styling

constexcel=require('node-excel-export');// You can define styles as json objectconststyles={headerDark: {fill: {fgColor: {rgb: 'FF000000'}},font: {color: {rgb: 'FFFFFFFF'},sz: 14,bold: true,underline: true}},cellPink: {fill: {fgColor: {rgb: 'FFFFCCFF'}}},cellGreen: {fill: {fgColor: {rgb: 'FF00FF00'}}}};//Array of objects representing heading rows (very top)constheading=[[{value: 'a1',style: styles.headerDark},{value: 'b1',style: styles.headerDark},{value: 'c1',style: styles.headerDark}],['a2','b2','c2']// <-- It can be only values];//Here you specify the export structureconstspecification={customer_name: {// <- the key should match the actual data keydisplayName: 'Customer',// <- Here you specify the column headerheaderStyle: styles.headerDark,// <- Header stylecellStyle: function(value,row){// <- style renderer function// if the status is 1 then color in green else color in red// Notice how we use another cell value to style the current onereturn(row.status_id==1) ? styles.cellGreen : {fill: {fgColor: {rgb: 'FFFF0000'}}};// <- Inline cell style is possible },width: 120// <- width in pixels},status_id: {displayName: 'Status',headerStyle: styles.headerDark,cellFormat: function(value,row){// <- Renderer function, you can access also any row.propertyreturn(value==1) ? 'Active' : 'Inactive';},width: '10'// <- width in chars (when the number is passed as string)},note: {displayName: 'Description',headerStyle: styles.headerDark,cellStyle: styles.cellPink,// <- Cell stylewidth: 220// <- width in pixels}}// The data set should have the following shape (Array of Objects)// The order of the keys is irrelevant, it is also irrelevant if the// dataset contains more fields as the report is build based on the// specification provided above. But you should have all the fields// that are listed in the report specificationconstdataset=[{customer_name: 'IBM',status_id: 1,note: 'some note',misc: 'not shown'},{customer_name: 'HP',status_id: 0,note: 'some note'},{customer_name: 'MS',status_id: 0,note: 'some note',misc: 'not shown'}]// Define an array of merges. 1-1 = A:1// The merges are independent of the data.// A merge will overwrite all data _not_ in the top-left cell.constmerges=[{start: {row: 1,column: 1},end: {row: 1,column: 10}},{start: {row: 2,column: 1},end: {row: 2,column: 5}},{start: {row: 2,column: 6},end: {row: 2,column: 10}}]// Create the excel report.// This function will return Bufferconstreport=excel.buildExport([// <- Notice that this is an array. Pass multiple sheets to create multi sheet report{name: 'Report',// <- Specify sheet name (optional)heading: heading,// <- Raw heading array (optional)merges: merges,// <- Merge cell rangesspecification: specification,// <- Report specificationdata: dataset// <-- Report data}]);// You can then return this straightres.attachment('report.xlsx');// This is sails.js specific (in general you need to set headers)returnres.send(report);// OR you can save this buffer to the disk by creating a file.

Contributors

Contributor	Contribution
@jbogatay	Allow null values
@frenchbread	Example update
@fhemberger	Undefined header style
@zeg-io Tony Archer	Cell Merging
@martin-podlubny	Number and Dates custom formatting