define Cell Data Format

[akoskm]

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.

[SheetJSDev]

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 ([email protected]) or post it to this issue (you should be able to click-drag XLSX files into the text box in the GH website)

[leodavinci1]

@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]

@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]

same question, mark

[plotka]

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

[lakca]

Same question...

[SheetJSDev]

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) {

[laurelgr]

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]

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]

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.

[SheetJSDev]

This was released and the example works with the latest version