When writing hyperlink cell & save the wb, cell created as simple text #156

New Issue

2015-01-01T16:37:15Z

MayaGi commented

2015-01-01 16:37:15 +00:00

(Migrated from github.com)

Hi,
When creating the workbook from scratch, creating a hyperlink text according to the docs, cell is created as a simple text.

here is the code:

/* writing feature test -- look for TEST: in comments */
    /* vim: set ts=2: */

    function Hyperlink() {
        this.target = "";
        this.display = "";
    }

    var x = new Hyperlink();
    x.target = "http://localhost:9980/HEXA3_FA/index.html#/fm/s/unassociated_failures";
    x.display = "link";

    /* original data */
    var data = [
        [1, 2, 3],
        [true, false, null, "sheetjs"],
        ["foo", "bar", new Date("2014-02-19T14:30Z"), "0.3"],
        ["baz", null, x]
    ];

    var ws_name = "SheetJS";

    var wscols = [
        { wch: 6 },
        { wch: 7 },
        { wch: 10 },
        { wch: 20 }
    ];


    console.log("Sheet Name: " + ws_name);
    console.log("Data: "); for (var i = 0; i != data.length; ++i) console.log(data[i]);
    console.log("Columns :"); for (i = 0; i != wscols.length; ++i) console.log(wscols[i]);



    /* require XLSX */
    if (typeof XLSX === "undefined") { try { XLSX = require('./'); } catch (e) { XLSX = require('../'); } }

    /* dummy workbook constructor */
    function Workbook() {
        if (!(this instanceof Workbook)) return new Workbook();
        this.SheetNames = [];
        this.Sheets = {};
    }
    var wb = new Workbook();


    /* TODO: date1904 logic */
    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);
    }

    /* convert an array of arrays in JS to a CSF spreadsheet */
    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 });

                /* TEST: proper cell types and value handling */
                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 if (cell.v instanceof Hyperlink) {
                    cell.l = { Target: cell.v.target, tooltip: cell.v.display },
                    cell.v = cell.v.display;
                }
                else cell.t = 's';
                ws[cell_ref] = cell;
            }
        }

        /* TEST: proper range */
        if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
        return ws;
    }
    var ws = sheet_from_array_of_arrays(data);

    /* TEST: add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    /* TEST: column widths */
    ws['!cols'] = wscols;

    /* write file */
    //XLSX.writeFile(wb, 'sheetjs.xlsx');
    var wbout = XLSX.write(wb, { bookType: "xlsx", bookSST: true, type: 'binary' });
    console.log("wbout: " + wbout);
    var s2ab = function(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;
    };

    var saveAs = saveAs || (typeof navigator !== "undefined" && navigator.msSaveOrOpenBlob && navigator.msSaveOrOpenBlob.bind(navigator)) || (function (h) { if (typeof navigator !== "undefined" && /MSIE [1-9]\./.test(navigator.userAgent)) { return } var r = h.document, l = function () { return h.URL || h.webkitURL || h }, e = h.URL || h.webkitURL || h, n = r.createElementNS("http://www.w3.org/1999/xhtml", "a"), g = !h.externalHost && "download" in n, j = function (t) { var s = r.createEvent("MouseEvents"); s.initMouseEvent("click", true, false, h, 0, 0, 0, 0, 0, false, false, false, false, 0, null); t.dispatchEvent(s) }, o = h.webkitRequestFileSystem, p = h.requestFileSystem || o || h.mozRequestFileSystem, m = function (s) { (h.setImmediate || h.setTimeout)(function () { throw s }, 0) }, c = "application/octet-stream", k = 0, b = [], i = function () { var t = b.length; while (t--) { var s = b[t]; if (typeof s === "string") { e.revokeObjectURL(s) } else { s.remove() } } b.length = 0 }, q = function (t, s, w) { s = [].concat(s); var v = s.length; while (v--) { var x = t["on" + s[v]]; if (typeof x === "function") { try { x.call(t, w || t) } catch (u) { m(u) } } } }, f = function (t, v) { var w = this, C = t.type, F = false, y, x, s = function () { var G = l().createObjectURL(t); b.push(G); return G }, B = function () { q(w, "writestart progress write writeend".split(" ")) }, E = function () { if (F || !y) { y = s(t) } if (x) { x.location.href = y } else { if (navigator.userAgent.match(/7\.[\d\s\.]+Safari/) && typeof window.FileReader !== "undefined" && t.size <= 1024 * 1024 * 150) { var G = new window.FileReader(); G.readAsDataURL(t); G.onloadend = function () { var H = r.createElement("iframe"); H.src = G.result; H.style.display = "none"; r.body.appendChild(H); B(); return }; w.readyState = w.DONE; w.savedAs = w.SAVEDASUNKNOWN; return } else { window.open(y, "_blank"); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; B(); return } } }, A = function (G) { return function () { if (w.readyState !== w.DONE) { return G.apply(this, arguments) } } }, z = { create: true, exclusive: false }, D; w.readyState = w.INIT; if (!v) { v = "download" } if (g) { y = s(t); r = h.document; n = r.createElementNS("http://www.w3.org/1999/xhtml", "a"); n.href = y; n.download = v; var u = r.createEvent("MouseEvents"); u.initMouseEvent("click", true, false, h, 0, 0, 0, 0, 0, false, false, false, false, 0, null); n.dispatchEvent(u); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; B(); return } if (h.chrome && C && C !== c) { D = t.slice || t.webkitSlice; t = D.call(t, 0, t.size, c); F = true } if (o && v !== "download") { v += ".download" } if (C === c || o) { x = h } if (!p) { E(); return } k += t.size; p(h.TEMPORARY, k, A(function (G) { G.root.getDirectory("saved", z, A(function (H) { var I = function () { H.getFile(v, z, A(function (J) { J.createWriter(A(function (K) { K.onwriteend = function (L) { x.location.href = J.toURL(); b.push(J); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; q(w, "writeend", L) }; K.onerror = function () { var L = K.error; if (L.code !== L.ABORT_ERR) { E() } }; "writestart progress write abort".split(" ").forEach(function (L) { K["on" + L] = w["on" + L] }); K.write(t); w.abort = function () { K.abort(); w.readyState = w.DONE; w.savedAs = w.FAILED }; w.readyState = w.WRITING }), E) }), E) }; H.getFile(v, { create: false }, A(function (J) { J.remove(); I() }), A(function (J) { if (J.code === J.NOT_FOUND_ERR) { I() } else { E() } })) }), E) }), E) }, d = f.prototype, a = function (s, t) { return new f(s, t) }; d.abort = function () { var s = this; s.readyState = s.DONE; s.savedAs = s.FAILED; q(s, "abort") }; d.readyState = d.INIT = 0; d.WRITING = 1; d.DONE = 2; d.FAILED = -1; d.SAVEDASBLOB = 1; d.SAVEDASURI = 2; d.SAVEDASUNKNOWN = 3; d.error = d.onwritestart = d.onprogress = d.onwrite = d.onabort = d.onerror = d.onwriteend = null; h.addEventListener("unload", i, false); a.unload = function () { i(); h.removeEventListener("unload", i, false) }; return a }(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content));
    saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), 'sheetjs.xlsx');

Hi, When creating the workbook from scratch, creating a hyperlink text according to the docs, cell is created as a simple text. here is the code: ``` /* writing feature test -- look for TEST: in comments */ /* vim: set ts=2: */ function Hyperlink() { this.target = ""; this.display = ""; } var x = new Hyperlink(); x.target = "http://localhost:9980/HEXA3_FA/index.html#/fm/s/unassociated_failures"; x.display = "link"; /* original data */ var data = [ [1, 2, 3], [true, false, null, "sheetjs"], ["foo", "bar", new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, x] ]; var ws_name = "SheetJS"; var wscols = [ { wch: 6 }, { wch: 7 }, { wch: 10 }, { wch: 20 } ]; console.log("Sheet Name: " + ws_name); console.log("Data: "); for (var i = 0; i != data.length; ++i) console.log(data[i]); console.log("Columns :"); for (i = 0; i != wscols.length; ++i) console.log(wscols[i]); /* require XLSX */ if (typeof XLSX === "undefined") { try { XLSX = require('./'); } catch (e) { XLSX = require('../'); } } /* dummy workbook constructor */ function Workbook() { if (!(this instanceof Workbook)) return new Workbook(); this.SheetNames = []; this.Sheets = {}; } var wb = new Workbook(); /* TODO: date1904 logic */ 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); } /* convert an array of arrays in JS to a CSF spreadsheet */ 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 }); /* TEST: proper cell types and value handling */ 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 if (cell.v instanceof Hyperlink) { cell.l = { Target: cell.v.target, tooltip: cell.v.display }, cell.v = cell.v.display; } else cell.t = 's'; ws[cell_ref] = cell; } } /* TEST: proper range */ if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; } var ws = sheet_from_array_of_arrays(data); /* TEST: add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; /* TEST: column widths */ ws['!cols'] = wscols; /* write file */ //XLSX.writeFile(wb, 'sheetjs.xlsx'); var wbout = XLSX.write(wb, { bookType: "xlsx", bookSST: true, type: 'binary' }); console.log("wbout: " + wbout); var s2ab = function(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; }; var saveAs = saveAs || (typeof navigator !== "undefined" && navigator.msSaveOrOpenBlob && navigator.msSaveOrOpenBlob.bind(navigator)) || (function (h) { if (typeof navigator !== "undefined" && /MSIE [1-9]\./.test(navigator.userAgent)) { return } var r = h.document, l = function () { return h.URL || h.webkitURL || h }, e = h.URL || h.webkitURL || h, n = r.createElementNS("http://www.w3.org/1999/xhtml", "a"), g = !h.externalHost && "download" in n, j = function (t) { var s = r.createEvent("MouseEvents"); s.initMouseEvent("click", true, false, h, 0, 0, 0, 0, 0, false, false, false, false, 0, null); t.dispatchEvent(s) }, o = h.webkitRequestFileSystem, p = h.requestFileSystem || o || h.mozRequestFileSystem, m = function (s) { (h.setImmediate || h.setTimeout)(function () { throw s }, 0) }, c = "application/octet-stream", k = 0, b = [], i = function () { var t = b.length; while (t--) { var s = b[t]; if (typeof s === "string") { e.revokeObjectURL(s) } else { s.remove() } } b.length = 0 }, q = function (t, s, w) { s = [].concat(s); var v = s.length; while (v--) { var x = t["on" + s[v]]; if (typeof x === "function") { try { x.call(t, w || t) } catch (u) { m(u) } } } }, f = function (t, v) { var w = this, C = t.type, F = false, y, x, s = function () { var G = l().createObjectURL(t); b.push(G); return G }, B = function () { q(w, "writestart progress write writeend".split(" ")) }, E = function () { if (F || !y) { y = s(t) } if (x) { x.location.href = y } else { if (navigator.userAgent.match(/7\.[\d\s\.]+Safari/) && typeof window.FileReader !== "undefined" && t.size <= 1024 * 1024 * 150) { var G = new window.FileReader(); G.readAsDataURL(t); G.onloadend = function () { var H = r.createElement("iframe"); H.src = G.result; H.style.display = "none"; r.body.appendChild(H); B(); return }; w.readyState = w.DONE; w.savedAs = w.SAVEDASUNKNOWN; return } else { window.open(y, "_blank"); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; B(); return } } }, A = function (G) { return function () { if (w.readyState !== w.DONE) { return G.apply(this, arguments) } } }, z = { create: true, exclusive: false }, D; w.readyState = w.INIT; if (!v) { v = "download" } if (g) { y = s(t); r = h.document; n = r.createElementNS("http://www.w3.org/1999/xhtml", "a"); n.href = y; n.download = v; var u = r.createEvent("MouseEvents"); u.initMouseEvent("click", true, false, h, 0, 0, 0, 0, 0, false, false, false, false, 0, null); n.dispatchEvent(u); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; B(); return } if (h.chrome && C && C !== c) { D = t.slice || t.webkitSlice; t = D.call(t, 0, t.size, c); F = true } if (o && v !== "download") { v += ".download" } if (C === c || o) { x = h } if (!p) { E(); return } k += t.size; p(h.TEMPORARY, k, A(function (G) { G.root.getDirectory("saved", z, A(function (H) { var I = function () { H.getFile(v, z, A(function (J) { J.createWriter(A(function (K) { K.onwriteend = function (L) { x.location.href = J.toURL(); b.push(J); w.readyState = w.DONE; w.savedAs = w.SAVEDASBLOB; q(w, "writeend", L) }; K.onerror = function () { var L = K.error; if (L.code !== L.ABORT_ERR) { E() } }; "writestart progress write abort".split(" ").forEach(function (L) { K["on" + L] = w["on" + L] }); K.write(t); w.abort = function () { K.abort(); w.readyState = w.DONE; w.savedAs = w.FAILED }; w.readyState = w.WRITING }), E) }), E) }; H.getFile(v, { create: false }, A(function (J) { J.remove(); I() }), A(function (J) { if (J.code === J.NOT_FOUND_ERR) { I() } else { E() } })) }), E) }), E) }, d = f.prototype, a = function (s, t) { return new f(s, t) }; d.abort = function () { var s = this; s.readyState = s.DONE; s.savedAs = s.FAILED; q(s, "abort") }; d.readyState = d.INIT = 0; d.WRITING = 1; d.DONE = 2; d.FAILED = -1; d.SAVEDASBLOB = 1; d.SAVEDASURI = 2; d.SAVEDASUNKNOWN = 3; d.error = d.onwritestart = d.onprogress = d.onwrite = d.onabort = d.onerror = d.onwriteend = null; h.addEventListener("unload", i, false); a.unload = function () { i(); h.removeEventListener("unload", i, false) }; return a }(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content)); saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), 'sheetjs.xlsx'); ```

👍 4

kenneth-hao commented

2015-06-04 11:25:01 +00:00

(Migrated from github.com)

@SheetJSDev
I also met this problem. when I am writing hyperlink into Workbook, it's not working.

@SheetJSDev I also met this problem. when I am writing hyperlink into Workbook, it's not working.

dr-dimitru commented

2015-11-28 23:38:41 +00:00

(Migrated from github.com)

+1

turengege commented

2016-01-05 06:28:55 +00:00

(Migrated from github.com)

+1

token97 commented

2016-02-10 13:20:25 +00:00

(Migrated from github.com)

+1
same problem.
does anyone solve it ?

+1 same problem. does anyone solve it ?

guillaumervls commented

2016-03-15 16:13:42 +00:00

(Migrated from github.com)

👍

:+1:

RawSanj commented

2016-05-03 11:21:48 +00:00

(Migrated from github.com)

Looks like js-xlsx doesn't support adding Hyperlinks in exported excel. 😞

Looks like js-xlsx doesn't support adding Hyperlinks in exported excel. :disappointed:

martin-liu commented

2016-05-13 00:25:25 +00:00

(Migrated from github.com)

+1

👎 1

danielbdias commented

2016-05-16 19:49:58 +00:00

(Migrated from github.com)

+1

👎 1

jonahbron commented

2016-06-29 22:06:57 +00:00

(Migrated from github.com)

👍

:+1:

lamebrain commented

2016-07-22 23:51:24 +00:00

(Migrated from github.com)

👍

rohithshenoyg commented

2016-09-15 18:39:31 +00:00

(Migrated from github.com)

@SheetJSDev any chance this was solved?

SergiiBilyk commented

2016-12-29 11:44:52 +00:00

(Migrated from github.com)

+1

gangadharjannu commented

2017-03-10 07:25:32 +00:00

(Migrated from github.com)

Has anyone able to find the fix or workaround for this issue ?

dr-dimitru commented

2017-03-10 11:26:01 +00:00

(Migrated from github.com)

@guillaumervls check this SO thread

On our end we moved to CSV where is it possible

@guillaumervls check [this SO thread](http://stackoverflow.com/questions/32485449/xlxs-js-how-does-the-l-cell-hyperlink-object-option-work#comment55771059_32485449) On our end we moved to CSV where is it possible

👍 2

SheetJSDev commented

2017-03-31 01:01:01 +00:00

(Migrated from github.com)

We've pushed support for XLSX/XLSM/XLSB/XLML. The BIFF2 format didn't support hyperlinks. If there is enough interest we'll look into ODS hyperlinks.

👍 9

mrcates commented

2019-05-13 20:50:26 +00:00

(Migrated from github.com)

Hi @SheetJSDev

Is there any reason XLS isn't on the supported list? I'm assuming there must be some technical limitation or something that stands in the way?

Is there any chance that XLS support may be added at some point?

Hi @SheetJSDev Is there any reason XLS isn't on the supported list? I'm assuming there must be some technical limitation or something that stands in the way? Is there any chance that XLS support may be added at some point?

SheetJSDev commented

2019-05-14 09:08:19 +00:00

(Migrated from github.com)

@rcates1 it is currently supported in BIFF8 XLS (used in versions starting from Excel 97), https://jsfiddle.net/sheetjs/24nxpek8/ is a simple test.

ayush17 commented

2020-08-08 02:50:14 +00:00

(Migrated from github.com)

@rcates1 it is currently supported in BIFF8 XLS (used in versions starting from Excel 97), https://jsfiddle.net/sheetjs/24nxpek8/ is a simple test.

@SheetJSDev The hyperlinks are not highlighted in MS OFFICE EXCEL. While it is highlighted in LiberOffice

> @rcates1 it is currently supported in BIFF8 XLS (used in versions starting from Excel 97), https://jsfiddle.net/sheetjs/24nxpek8/ is a simple test. @SheetJSDev The hyperlinks are not highlighted in MS OFFICE EXCEL. While it is highlighted in LiberOffice

Sign in to join this conversation.