Named cell reference causes the string "undefined" to be output in the ODS file #1717

dandv · 2020-01-06T05:13:52Z

const XLSX = require('xlsx');

const wb = XLSX.utils.book_new();

const ws = XLSX.utils.json_to_sheet([]);

XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');

ws.A1 = { t: 's', f: 'square' };

XLSX.writeFile(wb, 'formula.ods');

The resulting .ODS file contains:

<table:table-cell table:formula="of:=square" office:value-type="string"><text:p>undefined</text:p></table:table-cell>

Not sure what <text:p> is (probably a placeholder?), but it should simply be omitted in this case.

Adding a v: '' to the cell object outputs an empty <text:p></text:p>.

<table:table-cell table:formula="of:=square" office:value-type="string"><text:p></text:p></table:table-cell>

That, again, should be omitted, or optimized to <text:p/>.

SheetJSDev · 2020-01-07T04:37:56Z

This is actually an issue with a string cell that has no value. The fix is pretty straightforward (feel free to submit a PR): https://github.com/SheetJS/sheetjs/blob/master/bits/81_writeods.js#L81

-						textp = cell.v;
+						textp = cell.v == null ? "" : cell.v;


        fix: SheetJS#1717

I saw the issue was raised and a SheetJS Dev said the fix was simple and involved dealing with defined and undefined variables. They wrote a line using a ternary conditional to assign the variable textp to the value of cell.v if it's defined, or an empty string if it is not defined. SheetJS Dev Code: textp = cell.v == null ? "" : cell.v; Suggesting using the line: textp = cell.v || ""; This does the same thing and assigns textp to cell.v if it is defined or an empty string if it is not. (Note any feedback would be greatly appreciated, this is my first try at contributing to open source).


        Did suggested fix to handle possible null return of text.v listed in …

…issue SheetJS#1717

SheetJSDev added the good first issue label Jan 13, 2020

geoffrymichael added a commit to geoffrymichael/sheetjs that referenced this issue Feb 2, 2020

Did suggested fix to handle possible null return of text.v listed in …

fa144cb

…issue SheetJS#1717

geoffrymichael mentioned this issue Feb 2, 2020

Did suggested fix to handle possible null return of text.v listed in … #1730

Open

SheetJS / sheetjs

Named cell reference causes the string "undefined" to be output in the ODS file #1717

Named cell reference causes the string "undefined" to be output in the ODS file #1717

dandv commented Jan 6, 2020

SheetJSDev commented Jan 7, 2020

SheetJS / sheetjs

Sponsor SheetJS/sheetjs

Join GitHub today

Named cell reference causes the string "undefined" to be output in the ODS file #1717

Named cell reference causes the string "undefined" to be output in the ODS file #1717

Comments

dandv commented Jan 6, 2020

SheetJSDev commented Jan 7, 2020