Changing cell number format to 'text' doesn't work for .xls #987

New Issue

2018-02-07T23:36:20Z

scwood commented

2018-02-07 23:36:20 +00:00

(Migrated from github.com)

Here's a fiddle:

var data = [
  {a: '01234', b: '01234'},
  {a: '01234', b: '01234'},
];

var worksheet = XLSX.utils.json_to_sheet(data);

var range = XLSX.utils.decode_range(worksheet['!ref']);
for (var r = range.s.r; r <= range.e.r; r++) {
  for (var c = range.s.c; c <= range.e.c; c++) {
    var cellName = XLSX.utils.encode_cell({c: c, r: r});
    worksheet[cellName].z = '@';
  }
}

var workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, 'sheet1');

var wbout = XLSX.write(workbook, {type: 'array', bookType: 'xls'});
saveAs(new Blob([wbout], {type: 'application/octet-stream'}), 'test.xls');

All the cells should be formatted as 'Text' however opening the file in Excel they're formatted as 'General'.

If I instead change the above code to write the file as .xlsx I get the correct cell formatting. End goal here is to preserve leading zeros in numeric fields, but we need the compatibility of .xls.

Is this an expected limitation?

Here's a fiddle: https://jsfiddle.net/jq0cr4gu/ ```js var data = [ {a: '01234', b: '01234'}, {a: '01234', b: '01234'}, ]; var worksheet = XLSX.utils.json_to_sheet(data); var range = XLSX.utils.decode_range(worksheet['!ref']); for (var r = range.s.r; r <= range.e.r; r++) { for (var c = range.s.c; c <= range.e.c; c++) { var cellName = XLSX.utils.encode_cell({c: c, r: r}); worksheet[cellName].z = '@'; } } var workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, 'sheet1'); var wbout = XLSX.write(workbook, {type: 'array', bookType: 'xls'}); saveAs(new Blob([wbout], {type: 'application/octet-stream'}), 'test.xls'); ``` All the cells should be formatted as 'Text' however opening the file in Excel they're formatted as 'General'. If I instead change the above code to write the file as .xlsx I get the correct cell formatting. End goal here is to preserve leading zeros in numeric fields, but we need the compatibility of .xls. Is this an expected limitation?

SheetJSDev commented

2018-02-08 03:51:37 +00:00

(Migrated from github.com)

The BIFF2/5/8 XLS formats are not currently persisting the number formats. You can still pass numbers as text and the values will be stored as text.

Note: Passing @ as the format won't preserve the leading zeroes. If you are passing a raw number, you actually need a format that mandates leading zeroes like 0000000000. The reason it "seems to work" for both XLS and XLSX is that you were actually passing a text string. Here's a fiddle showing the difference between passing text and numeric values: https://jsfiddle.net/oq7Lo3m0/

The BIFF2/5/8 XLS formats are not currently persisting the number formats. You can still pass numbers as text and the values will be stored as text. Note: Passing `@` as the format won't preserve the leading zeroes. If you are passing a raw number, you actually need a format that mandates leading zeroes like `0000000000`. The reason it "seems to work" for both XLS and XLSX is that you were actually passing a text string. Here's a fiddle showing the difference between passing text and numeric values: https://jsfiddle.net/oq7Lo3m0/

❤️ 1

scwood commented

2018-02-08 04:48:25 +00:00

(Migrated from github.com)

I see that the leading zeros are preserved initially, but as soon as you edit the number the leading zeros are stripped.

So do I understand correctly that unless we want to bump up to a more modern workbook type we're out of luck?

I see that the leading zeros are preserved initially, but as soon as you edit the number the leading zeros are stripped. So do I understand correctly that unless we want to bump up to a more modern workbook type we're out of luck?

scwood commented

2018-02-08 20:51:08 +00:00

(Migrated from github.com)

Wow, appreciate the quick turnaround. We grabbed the latest version and it's working great. Thank you 👍

Sign in to join this conversation.