table_to_sheet/book transforms blank cells to 0 #779

New Issue

2017-08-16T15:22:58Z

ghost commented

2017-08-16 15:22:58 +00:00

(Migrated from github.com)

This also includes cells filled with only white spaces (e.g. ' ').

the-spyke commented

2017-08-17 08:41:38 +00:00

(Migrated from github.com)

Yeah, I even have this code as temporary solution:

// Fix empty values parsed as number 0 by SheetJS just in case
if (cell.v === 0 && cell.w === "" && cell.t === "n") {
    cell.v = "";
    cell.t = "s";
}

Yeah, I even have this code as temporary solution: ``` // Fix empty values parsed as number 0 by SheetJS just in case if (cell.v === 0 && cell.w === "" && cell.t === "n") { cell.v = ""; cell.t = "s"; } ```

SheetJSDev commented

2017-08-18 18:15:01 +00:00

(Migrated from github.com)

Put the wrong number in the commit message :/ commit 2ea9c95839 resolves the issue by skipping blank cells. There are new tests for the DOM methods (using JSDOM in nodejs and the standard facilities in the browser)

Put the wrong number in the commit message :/ commit 2ea9c95839ddf439873d652a27e88e6ed0535013 resolves the issue by skipping blank cells. There are new tests for the DOM methods (using JSDOM in nodejs and the standard facilities in the browser)

ghost commented

2017-08-20 12:47:23 +00:00

(Migrated from github.com)

Thanks for the quick fix, I appreciate it!

danxfisher commented

2017-09-27 20:56:59 +00:00

(Migrated from github.com)

@domxa Did this work for you in 0.11.4? I am still seeing 0's where there should be blanks or white space.

SheetJSDev commented

2017-09-27 21:06:19 +00:00

(Migrated from github.com)

@danxfisher can you share a fiddle? https://jsfiddle.net/0w8b1qry/ appears to work

Fiddle Code (click to show)

/* external references:
 - https://unpkg.com/xlsx/dist/xlsx.full.min.js
 - https://rawgit.com/eligrey/FileSaver.js/master/FileSaver.js

HTML:
<table id="tableau">
<tr><td>The next cell is blank:</td><td></td><td>The previous cell is blank</td></tr>
</table>

<pre id="out"></pre>
*/

var wb = XLSX.utils.table_to_book(document.getElementById('tableau'));

document.getElementById('out').innerText = JSON.stringify(wb.Sheets.Sheet1, 2, 2);

/* write workbook (use type 'binary') */
var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});

/* generate a download */
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"}), "sheetjs.xlsx");

@danxfisher can you share a fiddle? https://jsfiddle.net/0w8b1qry/ appears to work <details><summary>Fiddle Code (click to show)</summary> ```js /* external references: - https://unpkg.com/xlsx/dist/xlsx.full.min.js - https://rawgit.com/eligrey/FileSaver.js/master/FileSaver.js HTML: <table id="tableau"> <tr><td>The next cell is blank:</td><td></td><td>The previous cell is blank</td></tr> </table> <pre id="out"></pre> */ var wb = XLSX.utils.table_to_book(document.getElementById('tableau')); document.getElementById('out').innerText = JSON.stringify(wb.Sheets.Sheet1, 2, 2); /* write workbook (use type 'binary') */ var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'}); /* generate a download */ 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"}), "sheetjs.xlsx"); ``` </details>

danxfisher commented

2017-09-27 21:27:49 +00:00

(Migrated from github.com)

@SheetJSDev Ah, I see what the issue is. If the formatting of the table isn't <td></td> on the same line but rather like this:

<td>

</td>

Then it will return 0.

Fiddle: https://jsfiddle.net/b3asc8jm/

@SheetJSDev Ah, I see what the issue is. If the formatting of the table isn't `<td></td>` on the same line but rather like this: ``` <td> </td> ``` Then it will return 0. Fiddle: https://jsfiddle.net/b3asc8jm/

danxfisher commented

2017-09-28 20:11:47 +00:00

(Migrated from github.com)

@SheetJSDev Sorry to reply to this again but just wanted to clarify: Is my above comment by design or a bug? If it's a bug, I'll create a separate issue so that this one can remain closed. Otherwise I'll try and find a workaround for my tables being generated by EJS. Thanks!

SheetJSDev commented

2017-09-28 20:17:26 +00:00

(Migrated from github.com)

it's a bug, the uncertainty surrounded inconsistencies in how you get text data from a cell. We're testing a new technique that we should be able to push out tonight. No need to raise a new issue, although you can if you feel like :)

danxfisher commented

2017-09-28 21:22:02 +00:00

(Migrated from github.com)

If there's no need, then there's no need! I am honestly baffled by the response time from the team--thanks for continuing to make everyone's life easier at such an incredible pace.

SheetJSDev commented

2017-09-30 16:09:13 +00:00

(Migrated from github.com)

@danxfisher we suffer for the art :)

👍 1

Sign in to join this conversation.