Sheetjs: how client download xlsx file

[nvcken]

My node.js code
var file = fs.readFileSync('./report/output/out.xlsx', 'binary');
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-Disposition', "attachment; filename=" + "out.xlsx")
return res.end(file, 'binary');
How javascript client download this file ? I have try many way but get open file corrupt
Thanks for help

[SheetJSDev]

Where are you using the library in the code sample? If you are generating out.xlsx using this library, can you manually verify that the generated file is not corrupted (download through some other means like FTP or SCP)

[SheetJSDev]

@mani95lisa Did you face a corruption issue when sending xlsx files using express? Any thoughts?

[notatestuser]

I faced a corruption issue when sending the written xlsx as a buffer out through express, but encoding as base64 solved it:

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

[SheetJSDev]

@notatestuser Can you check if passing a buffer works?

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

[notatestuser]

Yes I tried that before resorting to using base64 (as I'm aware of the huge inefficiency in that approach) but, no, it didn't work. I'll switch it back and gather more information.

[notatestuser]

So with buffer output I just get the unable to read file malarkey in excel. There appears to be some strange subtle differences in the files and if hexdumps mean anything to you I'll be able to get the headers of good and bad files to compare tomorrow.

[SheetJSDev]

@notatestuser can you confirm that you are passing a buffer to res.end? Add a line

console.log(Buffer.isBuffer(wbbuf))

just before res.end

[notatestuser]

I'm actually getting a string. I am checking right after the call to write.

[SheetJSDev]

@notatestuser are you passing type:'buffer' to XLSX.write? That should always give a buffer:

$ node -pe "var XLSX = require('xlsx'); XLSX.write(XLSX.readFile('AutoFilter.xlsx'),{type:'buffer'})"
<Buffer 50 4b 03 04 0a 00 00 00 00 00 06 78 3d 45 a5 ab ea b3 11 02 00 00 11 02 00 00 11 00 00 00 64 6f 63 50 72 6f 70 73 2f 63 6f 72 65 2e 78 6d 6c 3c 3f 78 6d ...>

[notatestuser]

When I do that I get a buffer but not in my app.

Don't worry, it's clearly something weird and hopefully only specific to our use case. When I have a chance to go on a debugging expedition I'll update you.

[nvcken]

@notatestuser , @SheetJSDev
Could you please check my client code, I have change my server code as your post

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

then my client code (angular receive data by $http post request)

var a         = document.createElement('a');
a.href        = 'data:attachment/xlsx,' + encodeURI(data);
a.target      = '_blank';
a.download    = 'out.xlsx';
console.log(a);
document.body.appendChild(a);
a.click();

p/s: the excel file generated on server open fine, the excel downloaded on client open corrupt

[SheetJSDev]

@nvcken in your web browser, can you directly hit the route? e.g. if the route /foo/bar sends the file in the response, can you just go to that route in your browser, download the file, and check if it is valid? If it is valid, then there must be an issue in the client code.

[nvcken]

yes, issue in the client code
@SheetJSDev
my client code below
var a = document.createElement('a');
a.href = 'data:attachment/xlsx,' + encodeURI(data);
a.target = '_blank';
a.download = 'out.xlsx';
console.log(a);
document.body.appendChild(a);
a.click();

[SheetJSDev]

@nvcken Have you looked into FileSaver.js? It provides a saveAs function that the write demos use. https://github.com/eligrey/FileSaver.js is the project.

For example, http://sheetjs.com/demos/writexlsx.html does:

var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});

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;
}
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx")

If angular isn't messing with the raw binary data, I assume something like this could work

[nvcken]

@SheetJSDev
I had take a look FileSaver.js.
Then I try to impelemnt download file binary from server.
Could you please tell me what I do wrong on client code?

[nvcken]

seem I found the way.

[SheetJSDev]

@nvcken what ended up working for you?

[nvcken]

@SheetJSDev
I have try blob download / base64 data url , two solution work fine on chrome ff, but not work on safari 7.
FileSaver.js not work on safari 7 too, I have issue posted on that repo but not found solution or I miss it
I temporary end up download by usual url /foo/bar with header content-disposition

p/s: is there any plan to implement API of styling cells ( or keep style of template excel file when output another file from that template) for js-xlsx ?

[lukelafountaine]

I encountered the same issue. Using the base64 type and File-Saver, I was able to get it to work. This post was very helpful with it: http://stackoverflow.com/questions/16245767/creating-a-blob-from-a-base64-string-in-javascript in case anyone else is stuck.

[ak4wrapp]

@nvcken nvcken Do you have any sample Code for downloading excel export using Safari?

[BryanYang]

@nvcken so what ended up working for you? could you share your code ?

[SheetJSDev]

@ak4wrapp @BryanYang @lukelafountaine @nvcken @notatestuser Safari downloads are problematic, see the relevant filesaver.js issue for a longer discussion. The best approach if you need to support generation in safari is to punt to the server.

http://sheetjs.com/demos/table.html demonstrates generating and downloading files using filesaver.js as well as downloadify.js, manually tested back to IE6.

The actual code that generates the file runs in Safari, which you should be able to verify by roundtripping in the browser:

var outfile = XLSX.write(wb, {type:'binary'});
var new_wb = XLSX.read(outfile, {type:'binary'});

[Chipintoza]

Please Look at:
https://stackoverflow.com/a/34552682/5086654

Could it because you are using connect-livereload plugin? The plugin seems cause corrupted binary files being transferred. I've encountered the same, and solved it by adding 'ignore' when initiate connect-livereload plugin.

app.use(require('connect-livereload')({
ignore:['.xls', '.xlsx']
}));
See this post for detail: https://github.com/intesso/connect-livereload/issues/39

[Terry-Su]

Just solved this problem.
Client:

const url = '/excel'

$.post(url, function (data) {
    const blob = new Blob([new Uint8Array(data.data)])
    download(blob, 'out.xlsx')
})

Server:

const express = require('express')
const app = express()
const FS = require('fs')

const buffer = FS.readFileSync('./origin.xlsx')

app.use(express.static('./'))

app.post('/excel', function (req, res) {
    res.json(buffer)
})

app.listen(3000)

Having tried lots of possible answers on the internet, but they both seemed not work. So i created a simple client and server testing case and found the answer finally.
(Attachment: download.js)

[DyuldinKS]

@Terry-Su, thanks a lot.
Maybe it's obvious, but I spent a few hours searching for an error. The key detail is to send buffer as json. If you are using res.send instead of res.json you will receive broken data.

In addition, short example with the generation of a xlsx on the server:

// generate empty workbook
const wb = xlsx.utils.book_new();
const table = [['a', 'b', 'c'], [1, 2, 3]]
const ws = xlsx.utils.aoa_to_sheet(table);
xlsx.utils.book_append_sheet(wb, ws, 'test');

// write options
const wopts = { bookType: 'xlsx', bookSST: false, type: 'buffer' };
const buffer = xlsx.write(wb, wopts);
res.json(buffer);

[bscaspar]

For those still struggling - I ended up going with the response provided by @lukelafountaine . There are a few gotchas, so providing the full example here. This post ended up being helpful.

server:

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

  res.setHeader('Content-Type', 'application/octet-stream');

  var dataArray = [{
    "id": 0,
    "species": "elk",
    "sex": "male"
  },{
    "id": 1,
    "species": "moose",
    "sex": "female"
  }];

  var wb = XLSX.utils.book_new();
  var ws = XLSX.utils.json_to_sheet(dataArray);
  XLSX.utils.book_append_sheet(wb, ws, "TestWB.xlsx");

  var wbopts = {
    type: 'base64',
    bookType: "xlsx",
    bookSST: false
  }

  var wbout = XLSX.write(wb, wbopts);

  console.log(wbout);

  res.send(wbout);
})

client:

    axios.get('http://localhost:3000', 
    {
      responseType: 'text',
      headers: { 'Content-Type': 'application/octet-stream'}
    }).then((res) => {
      var byteCharacters = atob(res.data);
      var byteNumbers = new Array(byteCharacters.length);
      for (var i = 0; i < byteCharacters.length; i++) {
        byteNumbers[i] = byteCharacters.charCodeAt(i);
      }
      var byteArray = new Uint8Array(byteNumbers);
      var blob = new Blob([byteArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});

      fileSaver.saveAs(blob, 'xlsx.xlsx');
    })