Node XLSX for write excel file

2
votes

I'm trying to use exceljs to create a excel file of mongodb collection. This is what i'm trying to do This is my mongodb collecton

{
  "id": "01",
  "make": "toyota",
  "year": [
    2005,
    2006,
    2007,
    2008,
    2009,
    2010
   ],
  "model": "fortuner",
  "type": "a"
} 
{

"id": "02",
"make": "toyota",
"year": [
    2005,
    2006,
    2007,
    2008,
    2009,
    2010
   ],
"model": "land cruiser 200",
"type": "b"
} 

{
"id": "03",
"make": "toyota",
"year": [
    2005,
    2006,
    2007,
    2008,
    2009,
    2010
],
"model": "land cruiser 200",
"type": "e"
}

,and i want to create excel file from this collection

ID Make Year Model Type

01 toyota 2005-2010 a xxxx

02 nissan 2000-2006 b xxxx

In XLSX official documentation they said to use

Writing Workbooks

nodejs write to file:

/* output format determined by filename */
XLSX.writeFile(workbook, 'out.xlsx');
/* at this point, out.xlsx is a file that you can distribute */
write to binary string (using FileSaver.js):
/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
var wopts = { bookType:'xlsx', bookSST:false, type:'binary' };

var wbout = XLSX.write(workbook,wopts);

function s2ab(s) {
 var buf = new ArrayBuffer(s.length);
 var view = new Uint8Array(buf);
 for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
 return buf;
}

/* the saveAs call downloads a file on the local machine */
saveAs(new Blob([s2ab(wbout)],{type:""}), "test.xlsx")

i can't get good idea about how to use this. Does anyone know how to do this ?

2
node.jsexcelmongodb

2 Answers

1
votes

Look this code:

            const reader = require('xlsx');

            const json = [
                {
                    id: 1,
                    color: 'red',
                    number: 75
                },
                {
                    id: 2,
                    color: 'blue',
                    number: 62
                },
                {
                    id: 3,
                    color: 'yellow',
                    number: 93
                },
            ];

            let workBook = reader.utils.book_new();
            const workSheet = reader.utils.json_to_sheet(json);
            reader.utils.book_append_sheet(workBook, workSheet, `response`);
            let exportFileName = `response.xls`;
            reader.writeFile(workBook, exportFileName);

I found the article here: https://buildcoding.com/useful-npm-libraries-to-create-excel-files-in-node-js/#3_xlsx

0
votes

Use excel-export . Here is code snippet.

var nodeExcel = require('excel-export');

app.get('/downloadExcel',function(req,res){
    var configuration = {};
    configuration.cols = [
    {
        caption: 'ID',
        type: 'String',
        width: 20
    },{
        caption: 'Make',
        type: 'String',
        width:20
    },{
        caption: 'Year',
        type: 'String',
        width: 20
    },{
        caption: 'Model',
        type: 'String',
        width: 20
    },{
        caption: 'Type',
        type: 'String',
        width: 20
    }];

    configuration.rows  = [
        ["01", "toyota" ,"2005-2010", "a", "xxxx"],
        ["02", "nissan" ,"2005-2010", "b", "xxxx"]
        ];
    var result = nodeExcel.execute(configuration);
    res.setHeader('Content-Type','application/vnd.openxmlformates');
    res.setHeader("Content-Disposition","attachment;filename="+"file_name.xlsx");
    res.end(result,'binary'); 
});