How to get data from xlsx as percentage ? #608
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#608
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?
Is it possible to get data from (xlsx) cell that formatted as percentage ?
I've tried and I got numbers divided by 100.
xlsx file 12.23%
readed data 0.12
Result required 12.23 or 12.23%
thanks in advance.
Can you share a file?
If you are reading from a file, the formatted text should be in the
w
field of the cell (thev
field stores the raw numeric value)file.xlsx
result:
0.01,0.22,-0,-0,0.25,-0,0.224,0.132,0.1755,0.704,,,,,,,,,,,,,,,,,,,,
@mjdb3d The extendscript support is relatively new and we haven't stress tested every feature. Thanks for reporting, we will look into this
@SheetJSDev you’re welcome.
I hope it will be fixed soon.
Thank you.
This seems to be part of a larger problem (with apparently all excel readers) where the type information is available (I've read the source code), but for some unknown reason the data is abstracted away from the end-user and ignored for output. For example, for this spreadsheet in xlsx:
I'm expecting one of these 2 outputs from
sheet_to_json
:The actual result is:
This also results in data-loss for
sheet_to_csv
.Here we are checking the primary type
t
:but there is not any checking of the secondary type 's' (currency, percent, etc..)
@InstanceOfMichael can you share a file? I just threw together a sample file:
608.xlsx
The CSV and JSON does exactly what's expected. This is from http://oss.sheetjs.com/js-xlsx/ (just drag and drop into the box):
This is from node:
The problem covered in this issue is extendscript not playing nice with the formatting functions. We've run into issues in the past with regular expressions, for example https://github.com/davidchambers/Base64.js/issues/31#issue-216606310 , but if you are using the web browser or node you should get the correct formatted data or raw data.
using
{ raw: false }
, I am now getting this from my imaginary spreadsheet:Which is good enough for my purposes. Thank you!
That still looks wrong. Can you share the worksheet object (
JSON.stringify(worksheet)
)? If the cell has formatted text (the cell object for40
has aw
key with value"$40 "
) you should see the same text value in the output.https://gist.github.com/InstanceOfMichael/220f5eb3b74ff3770c4d309ccf8be16f
@InstanceOfMichael thanks for sharing! I can reproduce, it's a bug in the number formatter.
At a high level, Excel doesn't distinguish between "currency" and "percent" and "date" and other cell types. There is a number cell type, and the number format dictates the interpretation.
If you want to see the number format, you can set
cellNF:true
in the options object. Then every cell will have az
key corresponding to the raw number format. If you look at the end of the output you'll see:Those are the formats that LibreOffice stored. Normally Excel would store a format like
$#,##0.00
for the currency.http://oss.sheetjs.com/ssf/ lets you test the formats:
using Excel's normal currency:
Using the LO currency format:
@mjdb3d @firas3d thanks for reporting! Finally figured out the issue, and it's a really really strange ExtendScript bug! Consider the following expression:
In JS this is interpreted as
In ExtendScript Toolkit (you can test in the JavaScript console) and Photoshop/Illustrator the expression is interpreted as
The formatter library uses a complex expression of the same form:
Since we use similar expressions throughout the codebases we have to make some more changes, but based on some simple tests I'm fairly confident that is the origin of the problem
@SheetJSDev Still getting the same issue in the
0.15.6
version. Any idea what to do? @mjdb3d @InstanceOfMichaelHi @praveen-wal @mjdb3d @InstanceOfMichael , have you got any solution for this?