define Cell Data Format #2171

New Issue

sheetjs · 2020-11-10T13:07:30Z

akoskm commented

2020-11-10 13:07:30 +00:00

(Migrated from github.com)

Is there a way to enforce Cell Data Format for empty cells?

I generate the sheet with:

const ws = XLSX.utils.aoa_to_sheet([['column 1', 'column 2', 'column 3']]);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'SheetJS');
const contents = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

however, after downloading the sheet and opening it in Numbers the Data Format is automatic.
I want to enforce this to be text.

Is there a way to enforce Cell Data Format for empty cells? I generate the sheet with: ``` const ws = XLSX.utils.aoa_to_sheet([['column 1', 'column 2', 'column 3']]); const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, 'SheetJS'); const contents = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' }); ``` however, after downloading the sheet and opening it in Numbers the Data Format is automatic. I want to enforce this to be text. ![image](https://user-images.githubusercontent.com/3111627/98677705-ac06f580-235d-11eb-969e-e4d58b77a241.png)

👍 2

SheetJSDev commented

2020-11-11 04:47:57 +00:00

(Migrated from github.com)

That is probably an issue with Numbers XLSX import codec. To be sure, can you construct something Numbers would accept? To do this:

after reading the documentation, do something to make Numbers treat the default format as Text
save as XLSX and confirm that reopening the file shows that the default format is text
either email the file to us (hello@sheetjs.com) or post it to this issue (you should be able to click-drag XLSX files into the text box in the GH website)

That is probably an issue with Numbers XLSX import codec. To be sure, can you construct something Numbers would accept? To do this: 1) after reading the documentation, do something to make Numbers treat the default format as Text 2) save as XLSX and confirm that reopening the file shows that the default format is text 3) either email the file to us (hello@sheetjs.com) or post it to this issue (you should be able to click-drag XLSX files into the text box in the GH website)

leodavinci1 commented

2021-04-05 13:51:35 +00:00

(Migrated from github.com)

@akoskm did you find a solution for this? I am having the same issue here, both Excel and Numbers softwares consider all cells the 'Automatic' type...

akoskm commented

2021-04-05 14:49:40 +00:00

(Migrated from github.com)

@leodavinci1 I did not find a solution to this. I was able to verify that in Excel you can save sheets with pre-set cell types that on opening keep their types.

z695101385 commented

2021-07-19 05:35:14 +00:00

(Migrated from github.com)

same question, mark

plotka commented

2022-01-04 17:12:20 +00:00

(Migrated from github.com)

Same question, did anyone find a solution in the meanwhile?

lakca commented

2022-04-28 10:03:49 +00:00

(Migrated from github.com)

Same question...

SheetJSDev commented

2022-06-11 21:08:01 +00:00

(Migrated from github.com)

Samples:

Types.numbers.zip (rename to Types.numbers)
Types.xls
Types.xlsx

To find the formats, run in NodeJS:

> require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].A3.z
'm/d/yy h:mm AM/PM'
> require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].B3.z
'General'
> require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].C3.z
'@'

So forcing "Text" seems to be a matter of setting the cell number format to Text (@ in Excel).

This normally would be

const ws = XLSX.utils.aoa_to_sheet([
	['Date', 'Number', 'Text'],
	[{t: "z", z: "m/d/yy h:mm AM/PM"}, {t:"z", z: "General"}, {t: "z", z: "@"}]
]);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'SheetJS');
XLSX.writeFile(wb, "issue2171.xlsx", { sheetStubs: true });

However there is a logical bug in the cell filtering. Feel free to submit a PR:

diff --git a/bits/67_wsxml.js b/bits/67_wsxml.js
--- a/bits/67_wsxml.js
+++ b/bits/67_wsxml.js
@@ -258,7 +258,7 @@ function write_ws_xml_sheetviews(ws, opts, idx, wb)/*:string*/ {
 
 function write_ws_xml_cell(cell/*:Cell*/, ref, ws, opts/*::, idx, wb*/)/*:string*/ {
        if(cell.c) ws['!comments'].push([ref, cell.c]);
-       if(cell.v === undefined && typeof cell.f !== "string" || cell.t === 'z' && !cell.f) return "";
+       if((cell.v === undefined || cell.t === "z" && !(opts||{}).sheetStubs) && typeof cell.f !== "string" && typeof cell.z == "undefined") return "";
        var vv = "";
        var oldt = cell.t, oldv = cell.v;
        if(cell.t !== "z") switch(cell.t) {

Samples: - [Types.numbers.zip](https://github.com/SheetJS/sheetjs/files/8884704/Types.numbers.zip) (rename to Types.numbers) - [Types.xls](https://github.com/SheetJS/sheetjs/files/8884700/Types.xls) - [Types.xlsx](https://github.com/SheetJS/sheetjs/files/8884701/Types.xlsx) To find the formats, run in NodeJS: ```js > require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].A3.z 'm/d/yy h:mm AM/PM' > require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].B3.z 'General' > require("xlsx").readFile("Types.xlsx", {cellNF: true, sheetStubs: true}).Sheets["Sheet 1"].C3.z '@' ``` So forcing "Text" seems to be a matter of setting the cell number format to Text (`@` in Excel). This normally would be ```js const ws = XLSX.utils.aoa_to_sheet([ ['Date', 'Number', 'Text'], [{t: "z", z: "m/d/yy h:mm AM/PM"}, {t:"z", z: "General"}, {t: "z", z: "@"}] ]); const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, 'SheetJS'); XLSX.writeFile(wb, "issue2171.xlsx", { sheetStubs: true }); ``` However there is a logical bug in the cell filtering. Feel free to submit a PR: ```diff diff --git a/bits/67_wsxml.js b/bits/67_wsxml.js --- a/bits/67_wsxml.js +++ b/bits/67_wsxml.js @@ -258,7 +258,7 @@ function write_ws_xml_sheetviews(ws, opts, idx, wb)/*:string*/ { function write_ws_xml_cell(cell/*:Cell*/, ref, ws, opts/*::, idx, wb*/)/*:string*/ { if(cell.c) ws['!comments'].push([ref, cell.c]); - if(cell.v === undefined && typeof cell.f !== "string" || cell.t === 'z' && !cell.f) return ""; + if((cell.v === undefined || cell.t === "z" && !(opts||{}).sheetStubs) && typeof cell.f !== "string" && typeof cell.z == "undefined") return ""; var vv = ""; var oldt = cell.t, oldv = cell.v; if(cell.t !== "z") switch(cell.t) { ```

👀 1

laurelgr commented

2022-07-18 03:08:19 +00:00

(Migrated from github.com)

Nice to know I wasn't going crazy.

Workaround: have cell stub with empty string value v and cell type t of text to pass the filter, apply formatting to z:

cell = { v: '', t: 's', z: '@' }

@SheetJSDev the line you show is already fixed in commit warn on codepage override in mini build [ci skip], but there hasn't been a new release for 4 months. When can we expect the next release please? :)

Nice to know I wasn't going crazy. Workaround: have cell stub with empty string value `v` and cell type `t` of text to pass the filter, apply formatting to `z`: ``` cell = { v: '', t: 's', z: '@' } ``` @SheetJSDev the line you show is already fixed in commit `warn on codepage override in mini build [ci skip]`, but there hasn't been a new release for 4 months. When can we expect the next release please? :)

SheetJSDev commented

2022-07-18 17:05:02 +00:00

(Migrated from github.com)

https://docs.sheetjs.com/docs/installation/ 0.18.9 (which does not include the commit) was pushed on Jun 9. We are aiming for 0.19.0 with some much-needed date changes, but could release a 0.18.10 if Numbers is significant enough to be prioritized

laurelgr commented

2022-07-19 02:22:25 +00:00

(Migrated from github.com)

Ah, npm website's latest version is still 0.18.5, I didn't notice you changed the installation to provide package yourself. 😅
Thanks for the heads up, I have a workaround so I'll wait for next release.

Ah, npm website's latest version is still 0.18.5, I didn't notice you changed the installation to provide package yourself. 😅 Thanks for the heads up, I have a workaround so I'll wait for next release.

sheetjs commented

2023-01-03 02:51:00 +00:00

This patch was rolled out.

To be clear, Numbers maps the @ number format to a cell with type Text.

The number format can be applied by setting the z property of the cell object:

// set cell C2 number format
if(ws["!data"]) ws["!data"][1][2].z = "@";
else ws["C2"].z = "@";

When using a method like aoa_to_sheet or sheet_add_aoa, a cell object can be passed:

const ws = XLSX.utils.aoa_to_sheet([
	['Text'], // A1 = "Text"
	[{t: "z", z: "@"}] // A2 will be marked as cell type Text
]);

This patch was rolled out. To be clear, Numbers maps the `@` number format to a cell with type Text. The number format can be applied by setting the `z` property of the cell object: ```js // set cell C2 number format if(ws["!data"]) ws["!data"][1][2].z = "@"; else ws["C2"].z = "@"; ``` When using a method like `aoa_to_sheet` or `sheet_add_aoa`, a cell object can be passed: ```js const ws = XLSX.utils.aoa_to_sheet([ ['Text'], // A1 = "Text" [{t: "z", z: "@"}] // A2 will be marked as cell type Text ]); ```

sheetjs closed this issue

2023-01-03 02:51:00 +00:00

Sign in to join this conversation.