Writing to Excel workbook using javascript and xlsx library gives error

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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!
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
Sounds like fun. Post the code you are running in your console so I can recreate the problem.
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb
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[R].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[R][C] };
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],[true, false, null, "sheetjs"],["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[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx")

Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
I will take a loot at this tomorrow.
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb
Ok, Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
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
})();
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb
Thanks for the reply!

I tried the suggestions you provided, however, I still come across the same error.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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).
(Edited)
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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.
Photo of Rizu

Rizu

  • 210 Points 100 badge 2x thumb
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],[true, false, null, "sheetjs"],["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[R].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[R][C] };
			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[i] = s.charCodeAt(i) & 0xFF;
	return buf;
}
})();
(Edited)