Generate Excel Report in Nodejs

Posted By : Vivek Joshi | 30-Nov-2017

nodejs

For creating Excel file reports in node js following steps are required:

Step 1:- For generating Excel report in node js we are using an excel4node module in node js.

Step 2:- Download the excel4node module using npm

npm install excel4node

Step 3:- Implement and use an excel4node module in the code following the basic step for creating excel file.

var excel = require('excel4node');

var workbook = new excel.Workbook();

var worksheet = workbook.addWorksheet('Sheet 1');
var worksheet2 = workbook.addWorksheet('Sheet 2');

worksheet.cell(1,1).string('content for display');

workbook.write('report.xlsx');

In the following above code new excel.Workbook(); will create a workbook for excel and the addWorksheet method add worksheets into the workbook.

worksheet's cell method indicates the particular cell in the sheet with (row X column ) pair

There is lot of other methods to add string, number, formula and all

Step 4:- You can add Excel file into the response of HTTP request as the following code.

var express = require('express');
var app = express();
var excel = require('excel4node');

var workbook = new excel.Workbook();

var worksheet = workbook.addWorksheet('report 1');
var worksheet2 = workbook.addWorksheet('report 2');

worksheet.cell(2,1).string('string');

app.get('/', function (req, res) {
    workbook.write('report.xlsx', res);
});
app.listen(3000, function () {
  console.log('Example app listening on port 3000!');
});
}

Step 5:- there are some more useful methods which are used for formatting and add some complex data into the excel file.

var excel = require('excel4node');

var workbook = new excel.Workbook();

var worksheet = workbook.addWorksheet('Report 1');
var worksheet2 = workbook.addWorksheet('Report 2');

var style = wb.createStyle({
	font: {
		color: '#FF0800',
		size: 12
	},
	numberFormat: '$#,##0.00; ($#,##0.00); -'
});


worksheet.cell(2,1).string('string').style(style);

var complexString = [
    'default font String\n',
    {
        bold: true,
        underline: true,
        italic: true,
        color: 'FF0000',
        size: 18,
        name: 'Courier',
        value: 'Hello..'
    },
    ' World!!!',
    {
        color: '000000',
        underline: false,
        name: 'Arial',
        vertAlign: 'subscript'
    },
    ' All',
    ' these',
    ' strings',
    ' are',
    ' black subsript,',
    {
        color: '0000FF',
        value: '\nbut',
        vertAlign: 'baseline'
    },
    ' now are blue'
];
worksheet.cell(4, 1).string(complexString);
worksheet.cell(5, 1).string('simple string').style({ font: {name: 'Helvetica'} });


workbook.write('report.xlsx');