Guriddo » Topic: Excel export Rounding values

Excel export Rounding values

Guriddo
excel export
Excel export Rounding values

Tagged: excel export

This topic has 3 replies, 2 voices, and was last updated 6 years, 5 months ago by tony.

Creator

Topic
July 7, 2017 at 9:04 pm #126255

cgatling
Participant

We are using Jqgrid version 5.2.1, we are having an issue with data values from the grid (numbers) round up/down to the nearest 10<sup>th</sup> in the exported excel document. Below is an example.

In the grid we have the following number 4000000000000333 but when the data is exported to excel the value is changed to 4000000000000330 in the excel view. Can anyone suggests a way to fix this issue
Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies
July 10, 2017 at 12:02 pm #126259

tony
Keymaster

Hello,

This is not a export problem, but rather Excel one. The maximum digits that can be accepted in excel is 15 after that everthing is rouned.

More on this problem you can found here

To solve the problem one possible solution is to set the number like the example

i.e as string as recommended in the most solutions.

Kind Regards

July 10, 2017 at 10:09 pm #126263

cgatling
Participant

Hi Tony,

The issue is not that my value is not already a string but that the the export to excel code is treating my number as a ‘number’ instead of a string. Basically when the value in exported the cell is being marked as a ‘number’ cell instead of an ‘inlineStr’.

To fix this i propose the follow change update the regex check to insure values that are larger than the IEEE 15 digits are treated as strings as to not have them be truncated in the exported file.

http://jsfiddle.net/cgatling/2xwctngx/1/

July 11, 2017 at 10:33 am #126268

tony
Keymaster

@cgatling,

First of all thank you for finding this problem and supply a fix for it – much appreciated.

The only that stopped me to apply a fix is that the exported file is read correct in Linux Office Packages – like LibreOffice and OpenOffice, but despite this fact, I have made the fix suggested from you, since the most users using excel files origin from Microsoft Excel.

I have refine your fix, since in case when the user use a custom formatter which return number the logic does not accept it. This can be easy seen if you replace in your code:

var v = "4000000000000333"; ...

1
2

var v = "4000000000000333";
...

with

var v = 4000000000000333; ...

1
2

var v = 4000000000000333;
...

The fix is in GitHub and you can check it.

Thank you again.

Kind Regards,
Author

Replies