Skip to main content
Question

Writing to Excel workbook using javascript and xlsx library gives error

  • March 1, 2017
  • 0 replies
  • 252 views
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!
      This topic has been closed for replies.

      • anomDiebolt
      • Registered
      • March 1, 2017
      Sounds like fun. Post the code you are running in your console so I can recreate the problem.
      • RizuGulati
      • Author
      • Registered
      • March 1, 2017
      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
      • Registered
      • March 2, 2017
      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
      • Author
      • Registered
      • March 3, 2017
      Thanks for the reply!

      I tried the suggestions you provided, however, I still come across the same error.
      • RizuGulati
      • Author
      • Registered
      • March 15, 2017
      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; } })();
      Terms & ConditionsAccessibility statement