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. Code 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 code 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 code 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 code with [Base64.decode(data)], the contents in the file saved looks more like the expected excel data, but still cannot be opened by Excel.

Melindamelinde

Answer 1

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

Inflammable

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? – Funnelform

@Ron T what's atob? – Swine

@tjvg1991: It's the function, window.atob(). What is stands for and what it does is explained here: #33854603 – Inflammable

Make sure your return file content is already in base64 encoded string for this to work. It depends on your server buffer object creation logic. ``` – Glossitis

Answer 2

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.

Vine

Answer 3

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"

Colostomy

Thank you! works great with laravel Maatwebsite\Excel and axios. – Stanislaus

I'm producing a file with SheetJs in the backend, storing it in S3, getting the signed URL in the frontend, downloading the file and finally changing it its name to download by the user and this solved my problem. – Virg

Wonderful solution mate. I was using getSignedIUrl from S3 and then just downloading it with Axios get, but I didnt specify respond type Blob. Adding that fixed my issue! – Nucleus

Answer 4

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

Eventual

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. – Mogador

thanks, i was looking for XLSX library. – Bestride

Answer 5

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

Nates

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 :) – Cuzco

Answer 6

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. ^^

Marseillaise

Answer 7

try FileSaver.js library. it might help.

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

Serle

Thanks for reply, but the result is still the same. – Melindamelinde

Answer 8

This answer depends on both the frontend and backend having a compatible return object, so giving both frontend & backend logic. Make sure backend return data is base64 encoded for the following logic to work.

// Backend code written in nodejs to generate XLS from CSV
import * as XLSX from 'xlsx';

export const convertCsvToExcelBuffer = (csvString: string) => {
  const arrayOfArrayCsv = csvString.split("\n").map((row: string) => {
    return row.split(",")
  });
  const wb = XLSX.utils.book_new();
  const newWs = XLSX.utils.aoa_to_sheet(arrayOfArrayCsv);
  XLSX.utils.book_append_sheet(wb, newWs);
  const rawExcel = XLSX.write(wb, { type: 'base64' })
  // set res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') 
  // to include content type information to frontend. 
  return rawExcel
}

//frontend logic to get the backend response and download file. 

// function from Ron T's answer which gets a string from ArrayBuffer. 
const 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;
};

const downloadExcelInBrowser = ()=>{
  const excelFileData = await backendCall();
  const decodedFileData = atob(excelFileData.data);
  const arrayBufferContent = s2ab(decodedFileData); // from example above
  const blob = new Blob([arrayBufferContent], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;' });
  var URL = window.URL || window.webkitURL;
  var downloadUrl = URL.createObjectURL(fileBlob);
  if (filename) {
    // use HTML5 a[download] attribute to specify filename
    var a = document.createElement('a');
    // safari doesn't support this yet
    if (typeof a.download === 'undefined') {
      window.location.href = downloadUrl;
    } else {
      a.href = downloadUrl;
      a.download = 'test.xlsx';
      document.body.appendChild(a);
      a.click();
    }
  } else {
    window.location.href = downloadUrl;
  }
}

Glossitis

atob is not working – Dvina

which browser are you using? – Glossitis

Answer 9

if you are using typescript then here is a working example of how to convert array to xlsx and download it.

const fileName = "CustomersTemplate";
    const fileExtension = ".xlsx";
    const fullFileName = fileName.concat(fileExtension);

    const workBook : WorkBook = utils.book_new();

    const content : WorkSheet = utils.json_to_sheet([{"column 1": "data", "column 2": "data"}]);

    utils.book_append_sheet(workBook, content, fileName);

    const buffer : any = writeFile(workBook, fullFileName);

    const data : Blob = new Blob(buffer, { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;" });

    const url = URL.createObjectURL(data); //some browser may use window.URL
    
    const a = document.createElement("a");
    document.body.appendChild(a);
    a.href = url;
    a.download = fullFileName;
    a.click();
    
    setTimeout(() => {
      window.URL.revokeObjectURL(url);
      document.body.removeChild(a);
    }, 0);

Durman