Error when writing a .xlsb if cell value is a number #2795
Labels
No Label
DBF
Dates
Defined Names
Features
Formula
HTML
Images
Infrastructure
Integration
International
ODS
Operations
Performance
PivotTables
Pro
Protection
Read Bug
SSF
SYLK
Style
Write Bug
good first issue
No Milestone
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: sheetjs/sheetjs#2795
Loading…
Reference in New Issue
No description provided.
Delete Branch "%!s(<nil>)"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Currently using community version
xlsx-0.18.11
, I can generate my data as .xlsx, but when I tried to change to .xlsb I got the following error:I had to dig a bit, but here is what I noticed:
XLSX.utils.sheet_add_aoa
, the content of the aoa cells are directly put in the sheet cells'v
value property (unless object, which is merged){ "v": true, "t": "s", "z": "@" }
{ "v": 123456, "t": "s", "z": "@" }
(I kept my custom formatting in the examples in case this is relevant, but I don't believe it should be)
No problem when generating a regular .xlsx though. I also did not test on other formats than .xlsx and .xlsb. Or with non-integer numbers.
https://docs.sheetjs.com/docs/api/utilities#array-of-arrays-input needs to clarify the logic:
{v: "abc", t: "s"}
){v: 123, t: "n"}
){ "v": 123456, "t": "s", "z": "@" }
->{ "v": 123456, "t": "s", "z": "@" }
).{ "v": 123456, "t": "s", "z": "@" }
does not make sense. EitherA) the cell should be numeric
{ "v": 123456, "t": "n", "z": "@" }
B) the value should be a string
{ "v": "123456", "t": "s", "z": "@" }
.The XLSX export "works" thanks to JS string conversions.
It is still a bug in the XLSB writer. There are a few parts that need to be fixed in
bits/68_wsbin.js
..
We'd need to decide how to stringify values. Feedback appreciated :)
For numbers, there are a few approaches:
String(cell.v)
cell.w
)BrtShortRk
orBrtCellRk
orBrtShortReal
orBrtCellReal
)For booleans:
true
/false
stringsTRUE
/FALSE
values.XLSB does not have a native date cell type, so the options are
Oh, good to know!
I had overwritten the
t
andz
parameter for custom formatting after the aoa input of the number value, with"t": "s"
because this value was actually supposed to be a string and not a number (bad leftover in database), that's why my error case is unusual!But yes, it would be better to at least generate a clear error message on the cause if stringifying is not yet implemented.
I'm not that familiar with Excel/ExcelB formats, and I don't have requirements impacted by these, so please understand this feedback is not expert level. :)
For numbers: I would go with either 1 (simple) or 3 (exact)
For booleans: 2 (Excel value over JS value, in the case of xlsb) or 3
For date: 1 or 3
I'm not sure how these choices would impact the user experience, so instead I would say to find which remains closest to the original when generating the file, then reading the generated file with the XLSX package. And maybe check how Excel chooses to do these types too.
You could also add writing options to choose behaviour other than default, but this is already getting complex. :)
Probably should have explained the choices with an example. Suppose we were talking about a cell like:
If this were marked as a numeric cell (
t: "n"
) there would be no ambiguity -- it's a number. If the value were a string (v: "123456"
) there would be no ambiguity -- it's a string. But when you mark a cell as a string and do not pass a string, the library has to decide how to handle that value. The choices are equivalent to:'123456
in the formula bar'123,456
in the formula bar123456
and assigning the format#,##0
They map to cells B2/C2/D2 in the sample: issue2795.xlsx -- which cell best reflects the intention? Same question applies to boolean (row 3) and date (row 4)