Corrupt xlsx file when downloading from express js, but work fine on file system. #366

New Issue

2016-02-06T12:36:48Z

TejasCMehta commented

2016-02-06 12:36:48 +00:00

(Migrated from github.com)

Hi,

I am trying to create export to xlsx and csv functionality in MEAN stack below is my code


var export_type = req.params.type || 'csv';
    var title = req.params.title || 'category' ;

    if(export_type === 'xlsx') {

        /* original data */
//var data = [[1,2,3],[true, false, null, "sheetjs"],["foo","bar","0.3"], ["baz", null, "qux"]]
var data = JSON.parse(req.body.data)
var ws_name = "Categories";

/* require XLSX */
var XLSX = require('xlsx')

/* set up workbook objects -- some of these will not be required in the future */
var wb = {}
wb.Sheets = {};
wb.Props = {};
wb.SSF = {};
wb.SheetNames = [];

/* create worksheet: */
var ws = {}

/* the range object is used to keep track of the range of the sheet */
var range = {s: {c:0, r:0}, e: {c:0, r:0 }};

/* Iterate through each element in the structure */
for(var R = 0; R != data.length; ++R) {
  if(range.e.r < R) range.e.r = R;
  for(var C = 0; C != data[R].length; ++C) {
    if(range.e.c < C) range.e.c = C;

    /* create cell object: .v is the actual data */
    var cell = { v: data[R][C] };
    if(cell.v == null) continue;

    /* create the correct cell reference */
    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

    /* determine the cell type */
    if(typeof cell.v === 'number') cell.t = 'n';
    else if(typeof cell.v === 'boolean') cell.t = 'b';
    else cell.t = 's';

    /* add to structure */
    ws[cell_ref] = cell;
  }
}
ws['!ref'] = XLSX.utils.encode_range(range);

/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;

/* write file */

// XLSX.writeFile(wb, 'test11.xlsx');
// res.download('test11.xlsx');

var wbbuf = XLSX.write(wb, {
    type: 'base64'
});
res.writeHead(200, [['Content-Type',  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']]);
res.end( new Buffer(wbbuf, 'base64') );

When I use only XLSX.writeFile(wb, 'test11.xlsx'); it creates file and that is getting opened in MS OFFICE 2013 but when I write res.download('test11.xlsx'); after writeFile line then its getting downloaded but on the open its show file is corrupt message.

Please guide me! Any working code with expressjs will be great!

Thanks,
TM

Hi, I am trying to create export to xlsx and csv functionality in MEAN stack below is my code ``` var export_type = req.params.type || 'csv'; var title = req.params.title || 'category' ; if(export_type === 'xlsx') { /* original data */ //var data = [[1,2,3],[true, false, null, "sheetjs"],["foo","bar","0.3"], ["baz", null, "qux"]] var data = JSON.parse(req.body.data) var ws_name = "Categories"; /* require XLSX */ var XLSX = require('xlsx') /* set up workbook objects -- some of these will not be required in the future */ var wb = {} wb.Sheets = {}; wb.Props = {}; wb.SSF = {}; wb.SheetNames = []; /* create worksheet: */ var ws = {} /* the range object is used to keep track of the range of the sheet */ var range = {s: {c:0, r:0}, e: {c:0, r:0 }}; /* Iterate through each element in the structure */ for(var R = 0; R != data.length; ++R) { if(range.e.r < R) range.e.r = R; for(var C = 0; C != data[R].length; ++C) { if(range.e.c < C) range.e.c = C; /* create cell object: .v is the actual data */ var cell = { v: data[R][C] }; if(cell.v == null) continue; /* create the correct cell reference */ var cell_ref = XLSX.utils.encode_cell({c:C,r:R}); /* determine the cell type */ if(typeof cell.v === 'number') cell.t = 'n'; else if(typeof cell.v === 'boolean') cell.t = 'b'; else cell.t = 's'; /* add to structure */ ws[cell_ref] = cell; } } ws['!ref'] = XLSX.utils.encode_range(range); /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; /* write file */ // XLSX.writeFile(wb, 'test11.xlsx'); // res.download('test11.xlsx'); var wbbuf = XLSX.write(wb, { type: 'base64' }); res.writeHead(200, [['Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']]); res.end( new Buffer(wbbuf, 'base64') ); ``` When I use only `XLSX.writeFile(wb, 'test11.xlsx');` it creates file and that is getting opened in MS OFFICE 2013 but when I write `res.download('test11.xlsx');` after writeFile line then its getting downloaded but on the open its show file is corrupt message. Please guide me! Any working code with expressjs will be great! Thanks, TM

pietersv commented

2016-03-05 04:28:02 +00:00

(Migrated from github.com)

Perhaps try using buffer rather than base64? The following works for me in Express 3 and 4:

var fileName = "Categories.xlsx";
res.setHeader('Content-disposition', 'attachment; filename=' + fileName);
res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
var wbout = XLSX.write(workbook.finalize(), { bookType: 'xlsx', type: 'buffer'});
 res.send(new Buffer(wbout));

Perhaps try using `buffer` rather than `base64`? The following works for me in Express 3 and 4: ``` js var fileName = "Categories.xlsx"; res.setHeader('Content-disposition', 'attachment; filename=' + fileName); res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); var wbout = XLSX.write(workbook.finalize(), { bookType: 'xlsx', type: 'buffer'}); res.send(new Buffer(wbout)); ```

👍 7 🎉 2

TejasCMehta commented

2016-05-26 17:33:22 +00:00

(Migrated from github.com)

can you please share with that export to xlsx function, so I can use in my code. I am still not getting it working. I am calling NodeJS api from angularjs.

reviewher commented

2017-03-25 05:11:58 +00:00

(Migrated from github.com)

@TejasCMehta are you trying to generate the XLSX file in the client or in the server?

diegoazh commented

2021-02-02 01:16:02 +00:00

(Migrated from github.com)

var fileName = "Categories.xlsx";
res.setHeader('Content-disposition', 'attachment; filename=' + fileName);
res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
var wbout = XLSX.write(workbook.finalize(), { bookType: 'xlsx', type: 'buffer'});
 res.send(new Buffer(wbout));

I used a similar version of this code, but with a little modifications

var fileName = "Categories.xlsx";
res.setHeader('Content-disposition', `attachment; filename=${fileName}`);
res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
var wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer'});
res.send(Buffer.from(wbout));

> ```js > var fileName = "Categories.xlsx"; > res.setHeader('Content-disposition', 'attachment; filename=' + fileName); > res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); > var wbout = XLSX.write(workbook.finalize(), { bookType: 'xlsx', type: 'buffer'}); > res.send(new Buffer(wbout)); > ``` I used a similar version of this code, but with a little modifications ```js var fileName = "Categories.xlsx"; res.setHeader('Content-disposition', `attachment; filename=${fileName}`); res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); var wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer'}); res.send(Buffer.from(wbout)); ```

👍 1

Sign in to join this conversation.