Spread is designed to follow Excel’s design as closely as possible. Excel and SpreadJS have the same maximum number precision limit of 15 digits in a cell. For example, the value 123456789123456 contains the maximum quantity of digits (15) in a cell. Large whole numbers like this will be converted into exponential format.
If you have a whole value over 15 digits and do not want it to display as an exponent, there are a few ways you can format the cell(s). Using the Designer Component, you can format the cell(s) as “Number” with 0 decimal spaces:
You can also format the cell(s) as “Custom” with type “0”, which displays whole numbers:
You can also achieve these same results using the formatter Method on the desired cell(s)/range(s), setting the value to “0”. Here is an example using getCell:
var testval = 123456789123456; //16-digit value
activeSheet.setValue(0,0,testval);
activeSheet.getCell(0, 0).formatter("0"); //set whole number format
activeSheet.setColumnWidth(0, 160); //You may want to increase column width
Alternatively, you can also create your own custom number format that will only be compatible with Spread. For more information, see our demo here: /spreadjs/demos/features/cells/formatter/custom-formatter#demo_source_name
The above methods will display values over 15 digits in whole number format, as opposed to the default exponential format.
Now that we can see the whole number values of cells, what happens if we go past the 15-digit number precision limit? If we take the value 123456789123456 and add a 16th digit to the end (for this example we will use 7) to make 1234567891234567, we can insert this new 16-digit value into a cell and see that the displayed cell value changes.
In this case, the “67” at the end of the value is rounded up to “70”. The original value still remains, however. In Excel, you may notice that the “67” is actually rounded down to “60”. Aside from rounding up or down, this limitation is identical between Excel and SpreadJS.
Tye Glenz