How to download created excel file in node.js using exceljs

Question

I am using exceljs module for creating excel file. The problem is it is neither getting created nor getting saved in the path.

var excel = require('exceljs');
var options = {
    filename: './streamed-workbook.xlsx',
    useStyles: true,
    useSharedStrings: true
};

var workbook = new Excel.stream.xlsx.WorkbookWriter(options);
var sheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 },
    { header: 'D.O.B.', key: 'DOB', width: 10 }
];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
worksheet.commit();

workbook.commit().then(function(){
    console.log('xls file is written.');
});

But when I run the code nothing happens. The excel is not created. What am I missing here?

*********************** Edit ************************** Made the following changes to my code but still its not working.

        var Excel = require('exceljs');
        var workbook = new Excel.Workbook();
        var worksheet = workbook.addWorksheet('My Sheet');
        worksheet.columns = [
            { header: 'Id', key: 'id', width: 10 },
            { header: 'Name', key: 'name', width: 32 },
            { header: 'D.O.B.', key: 'DOB', width: 10 }
        ];
        worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
        worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
        workbook.commit();
        workbook.xlsx.writeFile('./temp.xlsx').then(function() {
            // done
            console.log('file is written');
        });

where did you use this code either controller or schema page @codeinprogess — Vinoth
– Vinoth, Commented Jun 6, 2017 at 9:10
I don't know what you mean by "schema page", this code is in my router's js file — codeinprogress
– codeinprogress, Commented Jun 7, 2017 at 8:53
do you have any sample for your excel download @codeinprogress — Vinoth
– Vinoth, Commented Jun 7, 2017 at 10:07
do i have to use your code in my controller code or how to call your code in server page @code in progress — Vinoth
– Vinoth, Commented Jun 7, 2017 at 11:48

Danielle · Accepted Answer · 2017-10-11 15:53:56Z

29

In order to send the excel workbook to the client you can:

Using async await:

async function sendWorkbook(workbook, response) { 
    var fileName = 'FileName.xlsx';

    response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

     await workbook.xlsx.write(response);

    response.end();
}

Using promise:

function sendWorkbook(workbook, response) { 
    var fileName = 'FileName.xlsx';

    response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

    workbook.xlsx.write(response).then(function(){
        response.end();
    });
}

answered Oct 11, 2017 at 15:53

Danielle

90010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kirubel Over a year ago

The file is not being downloaded but in the response there's this unreadable content with random characters. Do you have any idea why this method in not working for me?

Randi Pratama Over a year ago

@Kirubel if you use postman, you can click Save Response dropdown and choose save to a file. Based on the content type that set to the header, let's say excel format, it will saved as an excel file.

Rohit Vyas Over a year ago

I am getting a corrupted excel file.

gandalivs · Accepted Answer · 2019-06-11 02:14:07Z

8

get answer from this link https://github.com/exceljs/exceljs/issues/37

    router.get('/createExcel', function (req, res, next) {
    var workbook = new Excel.Workbook();

    workbook.creator = 'Me';
    workbook.lastModifiedBy = 'Her';
    workbook.created = new Date(1985, 8, 30);
    workbook.modified = new Date();
    workbook.lastPrinted = new Date(2016, 9, 27);
    workbook.properties.date1904 = true;

    workbook.views = [
        {
            x: 0, y: 0, width: 10000, height: 20000,
            firstSheet: 0, activeTab: 1, visibility: 'visible'
        }
    ];
    var worksheet = workbook.addWorksheet('My Sheet');
    worksheet.columns = [
        { header: 'Id', key: 'id', width: 10 },
        { header: 'Name', key: 'name', width: 32 },
        { header: 'D.O.B.', key: 'dob', width: 10, outlineLevel: 1, type: 'date', formulae: [new Date(2016, 0, 1)] }
    ];

    worksheet.addRow({ id: 1, name: 'John Doe', dob: new Date(1970, 1, 1) });
    worksheet.addRow({ id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7) });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
    workbook.xlsx.write(res)
        .then(function (data) {
            res.end();
            console.log('File write done........');
        });
 }

answered Jun 11, 2019 at 2:14

gandalivs

4035 silver badges5 bronze badges

2 Comments

Ameer Dheyaa Over a year ago

your answer is the best actually

user3491125 Over a year ago

The file that this generates is corrupted, any idea why ?

codeinprogress · Accepted Answer · 2016-09-25 10:15:46Z

7

So I figured out that I was getting an error because of the workbook.commit(). I removed the commit and everything started working like a charm. Here is the entire working code of creating and downloading an excel file:

Note: I am using an npm module called tempfile to create a temporary file path for the created excel file. This file path is then automatically removed. Hope this helps.

try {
        var workbook = new Excel.Workbook();
        var worksheet = workbook.addWorksheet('My Sheet');

        worksheet.columns = [
            { header: 'Id', key: 'id', width: 10 },
            { header: 'Name', key: 'name', width: 32 },
            { header: 'D.O.B.', key: 'DOB', width: 10 }
        ];
        worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
        worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});

        var tempFilePath = tempfile('.xlsx');
        workbook.xlsx.writeFile(tempFilePath).then(function() {
            console.log('file is written');
            res.sendFile(tempFilePath, function(err){
                console.log('---------- error downloading file: ' + err);
            });
        });
    } catch(err) {
        console.log('OOOOOOO this is the error: ' + err);
    }

answered Sep 25, 2016 at 10:15

codeinprogress

3,5218 gold badges56 silver badges76 bronze badges

10 Comments

Vinoth Over a year ago

how to call this code from controller.js side. can you tell @codein progress

codeinprogress Over a year ago

In my case its a router.get() request. so router.get('/download', function(req, res){...the above code});

Vinoth Over a year ago

can you check my code. @Codeinprogress stackoverflow.com/questions/44369082/… I have done like this

codeinprogress Over a year ago

It looks like you are using a different excel module for node.js. This is the one I am using: npmjs.com/package/exceljs

Vinoth Over a year ago

Hi i am new to AngularJS. I am getting router is not defined error and where should i use this code. var excel = require('exceljs'); var options = { filename: './streamed-workbook.xlsx', useStyles: true, useSharedStrings: true };

|

philipeachille · Accepted Answer · 2019-09-04 09:06:15Z

This code snippet is using node.js with the excel4node and express modules in order to convert JSON data to an Excel file and send it to the client, using Javascript.

const xl = require('excel4node');
const express = require('express');
const app = express();

var json = [{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]

const createSheet = () => {

  return new Promise(resolve => {

// setup workbook and sheet
var wb = new xl.Workbook();

var ws = wb.addWorksheet('Sheet');

// Add a title row

ws.cell(1, 1)
  .string('Vehicle')

ws.cell(1, 2)
  .string('Date')

ws.cell(1, 3)
  .string('Location')

ws.cell(1, 4)
  .string('Speed')

// add data from json

for (let i = 0; i < json.length; i++) {

  let row = i + 2

  ws.cell(row, 1)
    .string(json[i].Vehicle)

  ws.cell(row, 2)
    .date(json[i].Date)

  ws.cell(row, 3)
    .string(json[i].Location)

  ws.cell(row, 4)
    .number(json[i].Speed)
}

resolve( wb )

  })
}

app.get('/excel', function (req, res) {

  createSheet().then( file => {
    file.write('ExcelFile.xlsx', res);
  })

});

app.listen(3040, function () {
  console.log('Excel app listening on port 3040');
});

Princess · Accepted Answer · 2016-07-19 12:21:02Z

0

There is a mistake in your Code.

You have declared your My Sheet with one variable and using a different variable in your entire code.

var sheet = workbook.addWorksheet('My Sheet');

worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10 } ];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});

worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});

worksheet.commit();

Change variable Sheet to Worksheet. Like the below code

var worksheet = workbook.addWorksheet('My Sheet');

worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10 } ];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});

worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});

worksheet.commit();

This should fix your issue. Thanks

edited Jul 19, 2016 at 12:21

answered Jul 14, 2016 at 12:41

Princess

362 bronze badges

2 Comments

codeinprogress Over a year ago

made the above edited changes but its still not working.

codeinprogress Over a year ago

The code I put under the edit is the code i am working with. I wrote a sample based on their documentation and it did not work.

Karthik · Accepted Answer · 2016-08-31 06:39:23Z

0

var excel = require("exceljs");
var workbook1 = new excel.Workbook();
workbook1.creator = 'Me';
workbook1.lastModifiedBy = 'Me';
workbook1.created = new Date();
workbook1.modified = new Date();
var sheet1 = workbook1.addWorksheet('Sheet1');
var reHeader=['FirstName','LastName','Other Name'];
var reColumns=[
    {header:'FirstName',key:'firstname'},
    {header:'LastName',key:'lastname'},
    {header:'Other Name',key:'othername'}
];
sheet1.columns = reColumns;
workbook1.xlsx.writeFile("./uploads/error.xlsx").then(function() {
    console.log("xlsx file is written.");
});

This creates xlsx file in uploads folder.

answered Aug 31, 2016 at 6:39

Karthik

1292 silver badges8 bronze badges

2 Comments

codeinprogress Over a year ago

Ok, but is there a way to download it as soon as it gets created? I want the users to click a button > the excel file is generated > a download/save option is given to them.

codeinprogress Over a year ago

I have found the solution for downloading the created file. Please see: stackoverflow.com/questions/39667773/…

Collectives™ on Stack Overflow

How to download created excel file in node.js using exceljs

6 Answers 6

3 Comments

2 Comments

10 Comments

Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

2 Comments

10 Comments

Comments

2 Comments

2 Comments

Linked

Related