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

Closed
opened 2018-02-07 23:36:20 +00:00 by scwood · 3 comments
scwood commented 2018-02-07 23:36:20 +00:00 (Migrated from github.com)

Here's a fiddle:

https://jsfiddle.net/jq0cr4gu/

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/
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 👍

Wow, appreciate the quick turnaround. We grabbed the latest version and it's working great. Thank you 👍
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#987
No description provided.