xlxs.js how does the "l" cell hyperlink object option work

Question

Im currently using the following library to make excel documents https://github.com/SheetJS/js-xlsx/blob/master/README.md

right now two of my cells look like this

ws[XLSX.utils.encode_cell({c: 0, r: 1})] = {v: "Report Url", s: {font : {sz : "11", bold : true}}}
ws[XLSX.utils.encode_cell({c: 1, r: 1})] = {v: self.url, s: {font : {sz : "11"}}}

Which yield a row with: "Report Url" | ::really long ugly url::

The Documentation says there is an "l" option But gives no documentation as to how to use it. In README:

Cell object:

'::l:: cell hyperlink object (.Target holds link, .tooltip is tooltip)'

Does anyone have an exp with it, I'd like the excel to have a row with just one column that says "report url" and it would be a clickable link

All the things I tried that failed:

ws[XLSX.utils.encode_cell({c: 0, r: 1})] = {l: self.url, s: {font : {sz : "11", bold : true}}}
ws[XLSX.utils.encode_cell({c: 0, r: 1})] = {v: "url", l: self.url, s: {font : {sz : "11", bold : true}}}
ws[XLSX.utils.encode_cell({c: 0, r: 1})] = {v: Target,l: {Target :self.url}, s: {font : {sz : "11", bold : true}}}
ws[XLSX.utils.encode_cell({c: 0, r: 1})] = {l: {Target :self.url}, s: {font : {sz : "11", bold : true}}}

Any Ideas?

Answer 1

It is currently not implemented. See open issue.

An other approach would be to use a hyperlink formula like this

ws[XLSX.utils.encode_cell({
    c: 0,
    r: 0
})] = {
    f: '=HYPERLINK("http://www.google.com","Google")'
};

to achieve the same thing as soon as the preventing issue is fixed. This patch and the other one sadly didn't work for me.

Answer 2

I had a similar issue, wanting to link all rows in column one (except the first that is the header) with a custom link. What I did was the following (json is my array for the excel file while links is an array containig the link for each row):

const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
for (let i = 1; i < json.length + 1; i++) {
    worksheet[XLSX.utils.encode_cell({
      c: 1,
      r: i
    })].l = { Target: links[i-1] };
}

That had as a result an excel file with the second column having internal custom links