Incorrect values when parsing certain formulas exported from excel #372

New Issue

2016-02-24T18:31:51Z

HuFlungDu commented

2016-02-24 18:31:51 +00:00

(Migrated from github.com)

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)

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)

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)

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

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

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)

Ok this time I checked :)

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)

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)

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)

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

Sign in to join this conversation.