sheetjs/sheetjs
38
Fork 38
Code Issues 193 Pull Requests 12 Wiki Activity

Incorrect values when parsing certain formulas exported from excel #372

New Issue
Closed
opened 2016-02-24 18:31:51 +00:00 by HuFlungDu · 8 comments
HuFlungDu commented 2016-02-24 18:31:51 +00:00 (Migrated from github.com)
Copy Link

I'm using js-xlsx to parse xlsx sheets containing time data for a website. Occasionally, the time data comes out wrong. In the particular case I am seeing, the formula calculates 72:00:00 in excel, but when I import it, it gets calculated as 48:00:00. However, if I open the sheet in libreoffice and save it, it will import correctly. Looking at the raw data for the two different sheets, it appears the the value it's giving me for the one saved in excel is 2.9999999996 (thereabouts), while the one it's giving me from libreoffice is just 3. I assume those values mean days, and that it's rounding the 2.9999999996 value down to 2 days, hence 48 hours. There seem to be fairly specific conditions to cause this to happen, and I've attached an example sheet where this happens. If you change the values too much (but if they still add up to 72 hours) it will sometimes actually work, sometimes not, which makes this feel like a floating point precision problem in excel.

Again, sheet is attached. You can test the problem by uploading the sheet to http://oss.sheetjs.com/js-xlsx and just checking the last value.

broken_numbers.xlsx

I'm using js-xlsx to parse xlsx sheets containing time data for a website. Occasionally, the time data comes out wrong. In the particular case I am seeing, the formula calculates 72:00:00 in excel, but when I import it, it gets calculated as 48:00:00. However, if I open the sheet in libreoffice and save it, it will import correctly. Looking at the raw data for the two different sheets, it appears the the value it's giving me for the one saved in excel is 2.9999999996 (thereabouts), while the one it's giving me from libreoffice is just 3. I assume those values mean days, and that it's rounding the 2.9999999996 value down to 2 days, hence 48 hours. There seem to be fairly specific conditions to cause this to happen, and I've attached an example sheet where this happens. If you change the values too much (but if they still add up to 72 hours) it will sometimes actually work, sometimes not, which makes this feel like a floating point precision problem in excel. Again, sheet is attached. You can test the problem by uploading the sheet to http://oss.sheetjs.com/js-xlsx and just checking the last value. [broken_numbers.xlsx](https://github.com/SheetJS/js-xlsx/files/145183/broken_numbers.xlsx)
SheetJSDev commented 2017-05-16 20:07:07 +00:00 (Migrated from github.com)
Copy Link

Thanks @HuFlungDu !

So the issue boiled down to an incomplete propagation of a rounding, which was fixed in SSF 0.9.3 and will be rolled into the next release.

Thanks @HuFlungDu ! So the issue boiled down to an incomplete propagation of a rounding, which was fixed in SSF 0.9.3 and will be rolled into the next release.
HuFlungDu commented 2017-05-17 17:45:50 +00:00 (Migrated from github.com)
Copy Link

This issue was not completely fixed. It appears the commit to update to ssf 0.9.3 in package.json exists, but the actual distribution code still uses 0.9.2, so if you go to test the sheet I attached at the js-xlsx test site, you'll still see the problem.

Thanks for working to get this fixed!

This issue was not completely fixed. It appears the commit to update to ssf 0.9.3 in package.json exists, but the actual distribution code still uses 0.9.2, so if you go to test the sheet I attached at the js-xlsx test site, you'll still see the problem. Thanks for working to get this fixed!
SheetJSDev commented 2017-05-17 17:50:05 +00:00 (Migrated from github.com)
Copy Link

@HuFlungDu we're going to cut the next version soon, a few other things were messed up in this release too (somehow added the typescript reference in the package.json but didn't include the actual definitions in the npm module).

To confirm that we actually addressed the issue in the underlying module, http://oss.sheetjs.com/ssf/ shows:

screen shot 2017-05-17 at 13 49 44
@HuFlungDu we're going to cut the next version soon, a few other things were messed up in this release too (somehow added the typescript reference in the package.json but didn't include the actual definitions in the npm module). To confirm that we actually addressed the issue in the underlying module, http://oss.sheetjs.com/ssf/ shows: <img width="286" alt="screen shot 2017-05-17 at 13 49 44" src="https://cloud.githubusercontent.com/assets/6070939/26168337/b5b821b8-3b07-11e7-9d10-d16ccfd42916.png">
HuFlungDu commented 2017-05-17 17:54:26 +00:00 (Migrated from github.com)
Copy Link

Thanks. I assumed it actually got fixed, just figured I should let you know I saw that issue. I'll test the next release once it gets released and hopefully be able to roll it out to my site shortly thereafter.

Thanks again.

Thanks. I assumed it actually got fixed, just figured I should let you know I saw that issue. I'll test the next release once it gets released and hopefully be able to roll it out to my site shortly thereafter. Thanks again.
SheetJSDev commented 2017-05-17 18:09:38 +00:00 (Migrated from github.com)
Copy Link

Ok this time I checked :)

screen shot 2017-05-17 at 14 07 42

It'll show up in NPM once the CI tests pass.

Let us know about any other issues you come across! These reports help a lot

Ok this time I checked :) <img width="298" alt="screen shot 2017-05-17 at 14 07 42" src="https://cloud.githubusercontent.com/assets/6070939/26169024/3d2d8168-3b0a-11e7-8ed2-1b76738a8a0c.png"> It'll show up in NPM once the CI tests pass. Let us know about any other issues you come across! These reports help a lot
HuFlungDu commented 2017-05-18 17:03:59 +00:00 (Migrated from github.com)
Copy Link

Yep, this release works great! The only issue now is that it does not appear that a 0.10.3 tag was created on the repo, so I can't add the new version to my bower file unless I use the commit id.

Yep, this release works great! The only issue now is that it does not appear that a 0.10.3 tag was created on the repo, so I can't add the new version to my bower file unless I use the commit id.
SheetJSDev commented 2017-05-18 17:05:42 +00:00 (Migrated from github.com)
Copy Link

good catch! the tag is visible locally but wasn't pushed, give it a few moments to propagate.

good catch! the tag is visible locally but wasn't pushed, give it a few moments to propagate.
HuFlungDu commented 2017-05-18 19:19:17 +00:00 (Migrated from github.com)
Copy Link

Alright, rolled out and working great. Thanks again for the support.

Alright, rolled out and working great. Thanks again for the support.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels   
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 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
Milestone
Clear milestone
No items
No Milestone
Assignees
Clear assignees
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#372
No description provided.
Delete Branch "%!s()"

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?