How to save .xlsx data to file as a blob

Question

I have a similar question to this question(Javascript: Exporting large text/csv file crashes Google Chrome):

I am trying to save the data created by excelbuilder.js's EB.createFile() function. If I put the file data as the href attribute value of a link, it works. However, when data is big, it crashes Chrome browser. Codes are like this:

//generate a temp <a /> tag
var link = document.createElement("a");
link.href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + encodeURIComponent(data);
link.style = "visibility:hidden";
link.download = fileName;

document.body.appendChild(link);
link.click();
document.body.removeChild(link);

My codes to create the data using excelbuilder.js is like follows:

var artistWorkbook = EB.createWorkbook();
var albumList = artistWorkbook.createWorksheet({name: 'Album List'});

albumList.setData(originalData); 

artistWorkbook.addWorksheet(albumList);

var data = EB.createFile(artistWorkbook);

As suggested by the answer of the similar question (Javascript: Exporting large text/csv file crashes Google Chrome), a blob needs to be created.

My problem is, what is saved in the file isn't a valid Excel file that can be opened by Excel. The codes that I use to save the blob is like this:

var blob = new Blob(
    [data],
    {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,"}
);

// Programatically create a link and click it:
var a = document.createElement("a");
a.href = URL.createObjectURL(blob);
a.download = fileName;
a.click();

If I replace the [data] in the above codes with [Base64.decode(data)], the contents in the file saved looks more like the expected excel data, but still cannot be opened by Excel.

Thanks!

score 43 · Accepted Answer · answered Mar 01 '16 at 02:27

43

I had the same problem as you. It turns out you need to convert the Excel data file to an ArrayBuffer.

var blob = new Blob([s2ab(atob(data))], {
    type: ''
});

href = URL.createObjectURL(blob);

The s2ab (string to array buffer) method (which I got from https://github.com/SheetJS/js-xlsx/blob/master/README.md) is:

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;
}

answered Mar 01 '16 at 02:27

Ron T

545
4
8

7

this doesn't seem to work with raw data (I.e. the excel file returned by a server as-is). In fact "s" returns undefined. any clue? – dragonmnl Jul 06 '17 at 10:33
@Ron T what's atob? – tjvg1991 Mar 02 '19 at 13:14
@tjvg1991: It's the function, window.atob(). What is stands for and what it does is explained here: https://stackoverflow.com/questions/33854103/why-were-javascript-atob-and-btoa-named-like-that – Ron T Mar 04 '19 at 01:19

score 13 · Answer 2 · answered May 28 '18 at 20:07

The answer above is correct. Please be sure that you have a string data in base64 in the data variable without any prefix or stuff like that just raw data.

Here's what I did on the server side (asp.net mvc core):

string path = Path.Combine(folder, fileName);
Byte[] bytes = System.IO.File.ReadAllBytes(path);
string base64 = Convert.ToBase64String(bytes);

On the client side, I did the following code:

const xhr = new XMLHttpRequest();

xhr.open("GET", url);
xhr.setRequestHeader("Content-Type", "text/plain");

xhr.onload = () => {
    var bin = atob(xhr.response);
    var ab = s2ab(bin); // from example above
    var blob = new Blob([ab], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;' });

    var link = document.createElement('a');
    link.href = window.URL.createObjectURL(blob);
    link.download = 'demo.xlsx';

    document.body.appendChild(link);

    link.click();

    document.body.removeChild(link);
};

xhr.send();

And it works perfectly for me.

score 6 · Answer 3 · answered Oct 19 '18 at 23:25

6

This works as of: v0.14.0 of https://github.com/SheetJS/js-xlsx

/* generate array buffer */
var wbout = XLSX.write(wb, {type:"array", bookType:'xlsx'});
/* create data URL */
var url = URL.createObjectURL(new Blob([wbout], {type: 'application/octet-stream'}));
/* trigger download with chrome API */
chrome.downloads.download({ url: url, filename: "testsheet.xlsx", saveAs: true });

answered Oct 19 '18 at 23:25

David Dehghan

14,410
2
84
85

Thank you for this, David. I was trying to simulate uploaded Excel files for testing and this enabled me to get it to work. I had to change type to 'application/binary' but it worked perfectly after that. – Hairgami_Master Jul 11 '19 at 15:39

score 3 · Answer 4 · answered Sep 24 '20 at 15:56

Here's my implementation using the fetch api. The server endpoint sends a stream of bytes and the client receives a byte array and creates a blob out of it. A .xlsx file will then be generated.

return fetch(fullUrlEndpoint, options)
  .then((res) => {
    if (!res.ok) {
      const responseStatusText = res.statusText
      const errorMessage = `${responseStatusText}`
      throw new Error(errorMessage);
    }
    return res.arrayBuffer();
  })
    .then((ab) => {
      // BE endpoint sends a readable stream of bytes
      const byteArray = new Uint8Array(ab);
      const a = window.document.createElement('a');
      a.href = window.URL.createObjectURL(
        new Blob([byteArray], {
          type:
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        }),
      );
      a.download = `${fileName}.XLSX`;
      document.body.appendChild(a);
      a.click();
      document.body.removeChild(a);
    })
    .catch(error => {
      throw new Error('Error occurred:' + error);
    });

Finally, after a whole day of searching and trying different solutions. The only thing that worked with fetch. I guess the point is in Unit8Array witch I have to explore and understand what actually is doing :) — Dach0, Apr 17 '21 at 08:11

score 2 · Answer 5 · answered Sep 27 '18 at 11:21

Solution for me.

Step: 1

<a onclick="exportAsExcel()">Export to excel</a>

Step: 2

I'm using file-saver lib.

Read more: https://www.npmjs.com/package/file-saver

npm i file-saver

Step: 3

let FileSaver = require('file-saver'); // path to file-saver

function exportAsExcel() {
    let dataBlob = '...kAAAAFAAIcmtzaGVldHMvc2hlZXQxLnhtbFBLBQYAAAAACQAJAD8CAADdGAAAAAA='; // If have ; You should be split get blob data only
    this.downloadFile(dataBlob);
}

function downloadFile(blobContent){
    let blob = new Blob([base64toBlob(blobContent, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')], {});
    FileSaver.saveAs(blob, 'report.xlsx');
}

function base64toBlob(base64Data, contentType) {
    contentType = contentType || '';
    let sliceSize = 1024;
    let byteCharacters = atob(base64Data);
    let bytesLength = byteCharacters.length;
    let slicesCount = Math.ceil(bytesLength / sliceSize);
    let byteArrays = new Array(slicesCount);
    for (let sliceIndex = 0; sliceIndex < slicesCount; ++sliceIndex) {
        let begin = sliceIndex * sliceSize;
        let end = Math.min(begin + sliceSize, bytesLength);

        let bytes = new Array(end - begin);
        for (var offset = begin, i = 0; offset < end; ++i, ++offset) {
            bytes[i] = byteCharacters[offset].charCodeAt(0);
        }
        byteArrays[sliceIndex] = new Uint8Array(bytes);
    }
    return new Blob(byteArrays, { type: contentType });
}

Work for me. ^^

score 1 · Answer 6 · answered Jan 25 '16 at 13:36

1

try FileSaver.js library. it might help.

https://github.com/eligrey/FileSaver.js/

answered Jan 25 '16 at 13:36

Vyacheslav

23,112
16
96
174

Thanks for reply, but the result is still the same. – gm2008 Jan 25 '16 at 14:38

score 1 · Answer 7 · answered Dec 25 '20 at 07:08

I've found a solution worked for me:

const handleDownload = async () => {
   const req = await axios({
     method: "get",
     url: `/companies/${company.id}/data`,
     responseType: "blob",
   });
   var blob = new Blob([req.data], {
     type: req.headers["content-type"],
   });
   const link = document.createElement("a");
   link.href = window.URL.createObjectURL(blob);
   link.download = `report_${new Date().getTime()}.xlsx`;
   link.click();
 };

I just point a responseType: "blob"

How to save .xlsx data to file as a blob

7 Answers7

Linked