Writing files with adobe extendscript

[grefel]

I get js-xlsx to read XLSX Files as shown in the demo https://github.com/SheetJS/js-xlsx/tree/master/demos/extendscript. Im struggling with writing a workbook to disk.

Adding the following lines to https://github.com/SheetJS/js-xlsx/blob/master/demos/extendscript/test.jsx

var binaryString = XLSX.write(workbook, {bookType:'xlsx', bookSST:true, type: 'binary'});
var outFile = File (Folder.desktop + "/test.xlsx");
outFile.encoding = "BINARY";
outFile.open( "w" );
outFile.write (binaryString);
outFile.close ();

brings the not so usable error

Trying to hunt this down I added

#include "../../jszip.js";
#include "../../xlsx.flow.js";

instead of xlsx.core.min.js

This results in some minor Regex Escaping Bugs of Extendscript I fixed here grefel@24d00a0.

But the resulting File ist not a usable ZIP-Arhive:

Any help would be greatly appreciated.

This is related to #603

[SheetJSDev]

Summary:

• some regular expressions had unescaped / in character classes (you already noticed these)
• some unparenthesized boolean logic, where extend script was parsing the result differently (you identified a few, but there were a few others)
• JSZip utf8 name conversion had unparenthesized bit operations. (this is the root of the current problem)
• ExtendScript seems to have trouble with array named keys (affects XLS and XLSB)

For XLSX conversion the following fixes resolved the problem:

diff --git a/bits/40_harb.js b/bits/40_harb.js
index bb7b0db..b8ff220 100644
--- a/bits/40_harb.js
+++ b/bits/40_harb.js
@@ -460,7 +460,7 @@ var SYLK = (function() {
 			for(var C = r.s.c; C <= r.e.c; ++C) {
 				var coord = encode_cell({r:R,c:C});
 				cell = dense ? (ws[R]||[])[C]: ws[coord];
-				if(!cell || cell.v == null && (!cell.f || cell.F)) continue;
+				if(!cell || (cell.v == null && (!cell.f || cell.F))) continue;
 				o.push(write_ws_cell_sylk(cell, ws, R, C, opts));
 			}
 		}
diff --git a/bits/59_vba.js b/bits/59_vba.js
index 6ffe2e3..3f69886 100644
--- a/bits/59_vba.js
+++ b/bits/59_vba.js
@@ -3,7 +3,7 @@ function make_vba_xls(cfb/*:CFBContainer*/) {
 	var newcfb = CFB.utils.cfb_new({root:"R"});
 	cfb.FullPaths.forEach(function(p, i) {
 		if(p.slice(-1) === "/" || !p.match(/_VBA_PROJECT_CUR/)) return;
-		var newpath = p.replace(/^[^/]*/,"R").replace(/\/_VBA_PROJECT_CUR\u0000*/, "");
+		var newpath = p.replace(/^[^\/]*/,"R").replace(/\/_VBA_PROJECT_CUR\u0000*/, "");
 		CFB.utils.cfb_add(newcfb, newpath, cfb.FileIndex[i].content);
 	});
 	return CFB.write(newcfb);
@@ -12,7 +12,7 @@ function make_vba_xls(cfb/*:CFBContainer*/) {
 function fill_vba_xls(cfb/*:CFBContainer*/, vba/*:CFBContainer*/)/*:void*/ {
 	vba.FullPaths.forEach(function(p, i) {
 		if(i == 0) return;
-		var newpath = p.replace(/[^/]*[/]/, "/_VBA_PROJECT_CUR/");
+		var newpath = p.replace(/[^\/]*[\/]/, "/_VBA_PROJECT_CUR/");
 		if(newpath.slice(-1) !== "/") CFB.utils.cfb_add(cfb, newpath, vba.FileIndex[i].content);
 	});
 }
diff --git a/bits/67_wsxml.js b/bits/67_wsxml.js
index fdbc5fc..1337c38 100644
--- a/bits/67_wsxml.js
+++ b/bits/67_wsxml.js
@@ -429,7 +429,7 @@ function write_ws_xml_data(ws/*:Worksheet*/, opts, idx/*:number*/, wb/*:Workbook
 			if(_cell === undefined) continue;
 			if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
 		}
-		if(r.length > 0 || rows && rows[R]) {
+		if(r.length > 0 || (rows && rows[R])) {
 			params = ({r:rr}/*:any*/);
 			if(rows && rows[R]) {
 				row = rows[R];
diff --git a/bits/75_xlml.js b/bits/75_xlml.js
index 11c4218..a428536 100644
--- a/bits/75_xlml.js
+++ b/bits/75_xlml.js
@@ -1013,7 +1013,7 @@ function write_ws_xlml_comment(comments/*:Array<any>*/)/*:string*/ {
 	}).join("");
 }
 function write_ws_xlml_cell(cell, ref/*:string*/, ws, opts, idx/*:number*/, wb, addr)/*:string*/{
-	if(!cell || cell.v == undefined && cell.f == undefined) return "";
+	if(!cell || (cell.v == undefined && cell.f == undefined)) return "";
 
 	var attr = {};
 	if(cell.f) attr["ss:Formula"] = "=" + escapexml(a1_to_rc(cell.f, addr));
diff --git a/jszip.js b/jszip.js
index 21da89f..948d9de 100644
--- a/jszip.js
+++ b/jszip.js
@@ -1624,14 +1624,14 @@ var string2buf = function (str) {
     // count binary size
     for (m_pos = 0; m_pos < str_len; m_pos++) {
         c = str.charCodeAt(m_pos);
-        if ((c & 0xfc00) === 0xd800 && (m_pos+1 < str_len)) {
+        if (((c & 0xfc00) === 0xd800) && (m_pos+1 < str_len)) {
             c2 = str.charCodeAt(m_pos+1);
             if ((c2 & 0xfc00) === 0xdc00) {
                 c = 0x10000 + ((c - 0xd800) << 10) + (c2 - 0xdc00);
                 m_pos++;
             }
         }
-        buf_len += c < 0x80 ? 1 : c < 0x800 ? 2 : c < 0x10000 ? 3 : 4;
+        buf_len += (c < 0x80) ? 1 : ((c < 0x800) ? 2 : ((c < 0x10000) ? 3 : 4));
     }
 
     // allocate buffer
@@ -1661,13 +1661,13 @@ var string2buf = function (str) {
         } else if (c < 0x10000) {
             /* three bytes */
             buf[i++] = 0xE0 | (c >>> 12);
-            buf[i++] = 0x80 | (c >>> 6 & 0x3f);
+            buf[i++] = 0x80 | ((c >>> 6) & 0x3f);
             buf[i++] = 0x80 | (c & 0x3f);
         } else {
             /* four bytes */
             buf[i++] = 0xf0 | (c >>> 18);
-            buf[i++] = 0x80 | (c >>> 12 & 0x3f);
-            buf[i++] = 0x80 | (c >>> 6 & 0x3f);
+            buf[i++] = 0x80 | ((c >>> 12) & 0x3f);
+            buf[i++] = 0x80 | ((c >>> 6) & 0x3f);
             buf[i++] = 0x80 | (c & 0x3f);
         }
     }

I am not quite sure why the minified version is causing issues, but it looks related to where the newline appears in a switch block. Might have to change how newlines are inserted.

You can test with the following script:

var thisFile = new File($.fileName);  
var basePath = thisFile.path;  

#include "shim.js";
#include "jszip.js";
#include "xlsx.js";

var filename = "/sheetjs.xlsx";

/* Read file from disk */
var infile = File(basePath+filename);
infile.open("r");
infile.encoding = "binary";
var data = infile.read();

/* Parse file */
var workbook = XLSX.read(data, {type:"binary"});

/* Display first worksheet */
var first_sheet_name = workbook.SheetNames[0], first_worksheet = workbook.Sheets[first_sheet_name];
var data = XLSX.utils.sheet_to_json(first_worksheet, {header:1});
alert(data);

var outfmts = [

  ["xlml",  "test.xml.xls"],
  ["fods",  "test.fods"],
  ["csv",   "test.csv"],
  ["txt",   "test.txt"],
  ["slk",   "test.slk"],
  ["eth",   "test.eth"],
  ["htm",   "test.htm"],
  ["dif",   "test.dif"],
  ["ods",   "test.ods"],
/*
  ["xlsb",  "test.xlsb"],
  ["biff8", "test.biff8.xls"],
  ["biff5", "test.biff5.xls"],
  ["biff2", "test.biff2.xls"],
*/
  ["xlsx",  "test.xlsx"]
];
for(var i = 0; i < outfmts.length; ++i) {
  alert(outfmts[i][0]);
  /* Generate new file */
  var wbout = XLSX.write(workbook, {bookType:outfmts[i][0], bookSST:true, type:'binary', cellDates:true});

  /* Roundtrip and Display first worksheet */
  var wb = XLSX.read(wbout, {type:"binary"});
  var f_sheet_name = wb.SheetNames[0], f_worksheet = wb.Sheets[f_sheet_name];
  var data = XLSX.utils.sheet_to_json(f_worksheet, {header:1, cellDates:true});
  alert(data);

  /* Write file to disk */
  var outFile = File(basePath + "/" + outfmts[i][1]);
  outFile.open("w");
  outFile.encoding = "binary";
  outFile.write(wbout);
  outFile.close();
}

(As you probably saw, the current tests only try reading from XLSX, so none of the write code paths and none of the non-XLSX read paths have been tested in ExtendScript. We'll close this issue once the full roundtrip tests pass for all of the supported formats)

[grefel]

Wow, that was quick. Thanks for looking into it.

Your suggested patch works for me.

Textformats are looking great. XLSX and ODS are working as well!

Regarding the minified version: I encounter these sort of problems often in ExtendScript and minified JS. I think most ExtendScript users are happy without a minified version, beacuse these scripts are not delivered throught HTTP rather saved into the Scripts Folder of the application on a harddisk.

[SheetJSDev]

There's a new xlsx.extendscript.js amalgamation which includes everything. readFile and writeFile now support the ExtendScript environment. See the demo for more details.

[grefel]

This is great. Thank you!