Forum Discussion

RizuGulati's avatar
RizuGulati
Qrew Cadet
9 years ago

Writing to Excel workbook using javascript and xlsx library gives error

I am trying to write to an Excel workbook from QuickBase using script and xlsx.core.min.js library. For testing purpose, the code is executing from a snippet in chrome debugger with the xlsx library saved in QB. Upon execution, the following error is displayed in the Chrome console:

"Uncaught TypeError: jszip is not a constructor
at write_zip (eval at <anonymous> (jquery-1.7.2.min.js:2), <anonymous>:15:1607)    at write_zip_type (eval at <anonymous> (jquery-1.7.2.min.js:2), <anonymous>:15:4439)
    at Object.writeSync [as write] (eval at <anonymous> (jquery-1.7.2.min.js:2), <anonymous>:15:5832)
"
I have tried to include the jszip library as well, however, the result is still the same.

Alternatively, when I run the same code on google.com (library still referenced in QB) using chrome debugger, it works fine.

Kindly let me know how to resolve this.

Thanks!
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    Sounds like fun. Post the code you are running in your console so I can recreate the problem.
  • RizuGulati's avatar
    RizuGulati
    Qrew Cadet
    9 years ago
    This is taken from the test script provided in xlsx library. Here is the code:

    //Instead of using cdn as shown below, my code links to pages in QB 
    $.getScript("https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.6/xlsx.core.min.js")
    $.getScript("https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.3/FileSaver.js")

    function datenum(v, date1904) { if(date1904) v+=1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    }
     
    function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
    for(var R = 0; R != data.length; ++R) {
    for(var C = 0; C != data.length; ++C) {
    if(range.s.r > R) range.s.r = R;
    if(range.s.c > C) range.s.c = C;
    if(range.e.r < R) range.e.r = R;
    if(range.e.c < C) range.e.c = C;
    var cell = {v: data };
    if(cell.v == null) continue;
    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

    if(typeof cell.v === 'number') cell.t = 'n';
    else if(typeof cell.v === 'boolean') cell.t = 'b';
    else if(cell.v instanceof Date) {
    cell.t = 'n'; cell.z = XLSX.SSF._table[14];
    cell.v = datenum(cell.v);
    }
    else cell.t = 's';

    ws[cell_ref] = cell;
    }
    }
    if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
    }
     
    /* original data */
    var data = [[1,2,3],,["foo","bar",new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, "qux"]]
    var ws_name = "SheetJS";
     
    function Workbook() {
    if(!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
    }
     
    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
     
    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;
    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 = s.charCodeAt(i) & 0xFF;
    return buf;
    }
    saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx")

    Thanks!
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    Sorry for the delay but I am having trouble keeping up with the flood of messages from the forum.

    I did not fully debug this but the first thing I see wrong is that you are not allowing the two JavaScript files to load before proceeding with the remainder of your code. If you continue to use jQuery to load the two external libraries (versus using a module loader such as requirejs) you need to hold off running the remainder of the script till after the two libraries have fully loaded.

    Try this:
    var urlXLSX = "https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.6/xlsx.core.min.js";
    var urlFileSaver = "https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.3/FileSaver.js";
    $.when($.getScript(urlXLSX), $.getScript(urlXLSX))
      .then(function() {
        //your code here
      });
    And, also all of your code should be wrapped in a closure to prevent your variables from potentially conflicting with QuickBase's global variables:
    (function(){
      //your code here
    })();
  • RizuGulati's avatar
    RizuGulati
    Qrew Cadet
    9 years ago
    Thanks for the reply!

    I tried the suggestions you provided, however, I still come across the same error.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      9 years ago
      Are you including jszip?

      https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js

      >when I run the same code on google.com (library still referenced in QB) using chrome debugger, it works fine.

      What is the URL of the example "on google.com" that works?

      Most likely you are either (a) missing a library, (b) loading an old version or (c) are loading libraries in the wrong order (due to dependencies).
    • RizuGulati's avatar
      RizuGulati
      Qrew Cadet
      9 years ago
      It works now. Thanks for your help!

      So, I had an older version of xlsx library and jszip need to be loaded before the xlsx library which was causing issues.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      9 years ago
      Most users don't understand that you can manipulate Excel and Zip files with JavaScript today. You can help nudge them in the right direction by posting the code you used to sort out the demo. Thanks in advance.
  • RizuGulati's avatar
    RizuGulati
    Qrew Cadet
    9 years ago
    Following is my updated code for the demo:
    (function(){ var urlJSZip ="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"; var urlXLSX ="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.9.2/xlsx.core.min.js"; var urlFileSaver ="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.3/FileSaver.js";  $.when($.getScript(urlJSZip), $.getScript(urlXLSX), $.getScript(urlFileSaver)).then(function() {
       /* original data */  var data = [[1,2,3],,["foo","bar",new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, "qux"]]  var ws_name = "SheetJS";    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);     /* add worksheet to workbook */  wb.SheetNames.push(ws_name);  wb.Sheets[ws_name] = ws;  var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});      saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx") });  function datenum(v, date1904) {  if(date1904) v+=1462;  var epoch = Date.parse(v);  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000); }   function sheet_from_array_of_arrays(data, opts) {  var ws = {};  var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};  for(var R = 0; R != data.length; ++R) {   for(var C = 0; C != data.length; ++C) {    if(range.s.r > R) range.s.r = R;    if(range.s.c > C) range.s.c = C;    if(range.e.r < R) range.e.r = R;    if(range.e.c < C) range.e.c = C;    var cell = {v: data };    if(cell.v == null) continue;    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});        if(typeof cell.v === 'number') cell.t = 'n';    else if(typeof cell.v === 'boolean') cell.t = 'b';    else if(cell.v instanceof Date) {     cell.t = 'n'; cell.z = XLSX.SSF._table[14];     cell.v = datenum(cell.v);    }    else cell.t = 's';        ws[cell_ref] = cell;   }  }  if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);  return ws; }    function Workbook() {  if(!(this instanceof Workbook)) return new Workbook();  this.SheetNames = [];  this.Sheets = {}; }    function s2ab(s) {  var buf = new ArrayBuffer(s.length);  var view = new Uint8Array(buf);  for (var i=0; i!=s.length; ++i) view = s.charCodeAt(i) & 0xFF;  return buf; } })();