Excel builder crashes on large workbook fix

Problem:

When exporting an Excel file in client side javascript code, a normal approach is to use the excel builder (excel-builder/excel-builder) and save in this way:

var workbook = Builder.createWorkbook()  
...
...#add data into workbook
...
var file = Builder.createFile(workbook);  
var url = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + file;  
var link = document.createElement('a');  
link.href = url;  
link.target = '_blank';  
link.click();  

But with this approach, a large workbook would crash the application, because a large workbook translates into a extremely long base64 string.

And here's the workaround:

  • convert the workbook file into blob first
  • use the hashed url from blob to download instead

The same steps now look like:

var workbook = Builder.createWorkbook()  
...
...#add data into workbook
...
var file = Builder.createFile(workbook);  
var blob = base64toBlob(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64")  
var url = URL.createObjectURL(blob);  
var link = document.createElement('a');  
link.href = url;  
link.target = '_blank';  
link.click();  

where the function base64toBlob(adapted from this stackoverflow answer) looks like:

function base64toBlob(base64Data, contentType) {  
  contentType = contentType || '';
  var sliceSize = 1024;
  var byteCharacters = atob(base64Data);
  var bytesLength = byteCharacters.length;
  var slicesCount = Math.ceil(bytesLength / sliceSize);
  var byteArrays = new Array(slicesCount);

  for (var sliceIndex = 0; sliceIndex < slicesCount; ++sliceIndex) {
    var begin = sliceIndex * sliceSize;
    var end = Math.min(begin + sliceSize, bytesLength);
    var 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 });
}

not sure what the size limit is for this approach, but it's been tested to work up to ~10Mb of workbook size!

comments powered by Disqus