Saturday 23 August 2014

export database data to excel file in node.js with express

Standard
today i will tell you a small script that are so useful.
we have saw that maximum user want to database data store in local machine by excel,csv or other file format so today i will give a code that will create a excel file in user m/c.
ok first you need to install excel-export module in node application so goto folder from terminal where you want to create your app and type bellow command:

$sudo npm install excel-export

next create a get method where you want write excel export code and then copy bellow code in this method:



get('excel',function(req,res){
    var nodeExcel=require('excel-export');
    var dateFormat = require('dateformat');
    var conf={}
    conf.cols=[{
            caption:'Sl.',
            type:'number',
            width:3
        },
        {
            caption:'Job',
            type:'string',
            width:50
        },
        {
            caption:'Date',
            type:'string',
            width:15
        }
        ];
    req.getConnection(function(err,connection){
        var query=connection.query("select * from todo where user_id="+req.session.id,function(err,rows){
            arr=[];
            for(i=0;i<rows.length;i++){
                job=rows[i].job;
                a=[i+1,job,(dateFormat(rows[i].add_date*1000, "dd/mm/yyyy"))];
                arr.push(a);
                }
                conf.rows=arr;
    var result=nodeExcel.execute(conf);
    res.setHeader('Content-Type','application/vnd.openxmlformates');
    res.setHeader("Content-Disposition","attachment;filename="+"todo.xlsx");
    res.end(result,'binary');
            });
    });
});


here i have create excel file where user can export all todo list job in excel file like this:

so you have a new trick.

how you will parse a excel data i will tell you later.

enjoy