XLSX.utils.sheet_to_html #1239

New Issue

2018-08-25T11:57:39Z

dolphinotaku commented

2018-08-25 11:57:39 +00:00

(Migrated from github.com)

merged columns display incorrect in html table
convert excel line break to   for html table

- merged columns display incorrect in html table - convert excel line break to ` ` for html table ![js xlsx](https://user-images.githubusercontent.com/5440966/44618032-f9900880-a8a0-11e8-9a25-c9d02056d860.PNG)

SheetJSDev commented

2018-08-25 14:22:37 +00:00

(Migrated from github.com)

Can you share the original XLSX file?

dolphinotaku commented

2018-08-25 15:36:18 +00:00

(Migrated from github.com)

bankUpdateLog.xlsx

[bankUpdateLog.xlsx](https://github.com/SheetJS/js-xlsx/files/2320831/bankUpdateLog.xlsx)

SheetJSDev commented

2018-08-25 15:59:29 +00:00

(Migrated from github.com)

There are two independent issues:

newlines are translated to the html character encoding rather than BR. Fix is straightforward and applied to escapehtml in bits/22_xmlutils.js:

--- a/bits/22_xmlutils.js
+++ b/bits/22_xmlutils.js
@@ -66,7 +66,7 @@ function escapexmltag(text/*:string*/)/*:string*/{ return escapexml(text).replac
 var htmlcharegex = /[\u0000-\u001f]/g;
 function escapehtml(text/*:string*/)/*:string*/{
        var s = text + '';
-       return s.replace(decregex, function(y) { return rencoding[y]; }).replace(htmlcharegex,function(s) { return "&#x" + ("000"+s.charCodeAt(0).toString(16)).slice(-4) + ";"; });
+       return s.replace(decregex, function(y) { return rencoding[y]; }).replace(/\n/g, "<br/>").replace(htmlcharegex,function(s) { return "&#x" + ("000"+s.charCodeAt(0).toString(16)).slice(-4) + ";"; });
 }
 
 function escapexlml(text/*:string*/)/*:string*/{

the file has a stub cell with colspan 3. The HTML writer currently writes a single-cell hardcoded expression for stubs. Fix is applied to bits/79_html.js:

--- a/bits/79_html.js
+++ b/bits/79_html.js
@@ -70,13 +70,12 @@ var HTML_ = (function() {
                        if(RS < 0) continue;
                        var coord = encode_cell({r:R,c:C});
                        var cell = o.dense ? (ws[R]||[])[C] : ws[coord];
-                       if(!cell || cell.v == null) { oo.push(nullcell); continue; }
-                       /* TODO: html entities */
-                       var w = cell.h || escapexml(cell.w || (format_cell(cell), cell.w) || "");
                        var sp = {};
                        if(RS > 1) sp.rowspan = RS;
                        if(CS > 1) sp.colspan = CS;
-                       sp.t = cell.t;
+                       /* TODO: html entities */
+                       var w = (cell && cell.v != null) && (cell.h || escapehtml(cell.w || (format_cell(cell), cell.w) || "")) || "";
+                       sp.t = cell && cell.t || 'z';
                        if(o.editable) w = '<span contenteditable="true">' + w + '</span>';
                        sp.id = "sjs-" + coord;
                        oo.push(writextag('td', w, sp));

Feel free to submit those changes as a PR.

There are two independent issues: 1) newlines are translated to the html character encoding rather than BR. Fix is straightforward and applied to `escapehtml` in `bits/22_xmlutils.js`: ```diff --- a/bits/22_xmlutils.js +++ b/bits/22_xmlutils.js @@ -66,7 +66,7 @@ function escapexmltag(text/*:string*/)/*:string*/{ return escapexml(text).replac var htmlcharegex = /[\u0000-\u001f]/g; function escapehtml(text/*:string*/)/*:string*/{ var s = text + ''; - return s.replace(decregex, function(y) { return rencoding[y]; }).replace(htmlcharegex,function(s) { return "&#x" + ("000"+s.charCodeAt(0).toString(16)).slice(-4) + ";"; }); + return s.replace(decregex, function(y) { return rencoding[y]; }).replace(/\n/g, " ").replace(htmlcharegex,function(s) { return "&#x" + ("000"+s.charCodeAt(0).toString(16)).slice(-4) + ";"; }); } function escapexlml(text/*:string*/)/*:string*/{ ``` 2) the file has a stub cell with colspan 3. The HTML writer currently writes a single-cell hardcoded expression for stubs. Fix is applied to `bits/79_html.js`: ```diff --- a/bits/79_html.js +++ b/bits/79_html.js @@ -70,13 +70,12 @@ var HTML_ = (function() { if(RS < 0) continue; var coord = encode_cell({r:R,c:C}); var cell = o.dense ? (ws[R]||[])[C] : ws[coord]; - if(!cell || cell.v == null) { oo.push(nullcell); continue; } - /* TODO: html entities */ - var w = cell.h || escapexml(cell.w || (format_cell(cell), cell.w) || ""); var sp = {}; if(RS > 1) sp.rowspan = RS; if(CS > 1) sp.colspan = CS; - sp.t = cell.t; + /* TODO: html entities */ + var w = (cell && cell.v != null) && (cell.h || escapehtml(cell.w || (format_cell(cell), cell.w) || "")) || ""; + sp.t = cell && cell.t || 'z'; if(o.editable) w = '' + w + ''; sp.id = "sjs-" + coord; oo.push(writextag('td', w, sp)); ``` Feel free to submit those changes as a PR.

emanresuwenaretne commented

2021-10-05 22:25:07 +00:00

(Migrated from github.com)

Is there an example of writing to HTML from JSON object? I fetch the JSON object by query returned from SQL Server database.

Sign in to join this conversation.