Number and Date Format

While working with SpreadJS, users can choose to display numbers and dates like Excel for the two CellTypes - Text and ComboBox. The data in the cells is displayed differently based on the width of the columns.

This feature is particularly useful when users need to deal with spreadsheets containing hundreds or thousands of columns with different widths. The data is displayed dynamically according to the space available in the column.

Example

For instance - If a user types "123456.654321 in a cell, the data in the cell will be displayed differently depending upon the column width as shown in the image below -
123456.654321 -> 123456.65432 -> 123456.6543 -> 123456.654 -> 123456.65 -> 123456.7 -> 123457 -> 1E+05 -> ####

For displaying numbers, the following rules are applied :

When the width of the column is not sufficient to display the integer part, the scientific notation will be used. [For example - refer to the cell B1 in the above image].
When the column width is such that it can include the integer part but the decimal part cannot be placed; the decimal will be rounded up [For example - refer to the cell C1 in the above image].
When the shortest scientific notation is not enough [For example - refer to the cell C1 in the above image], the number will be displayed as "####" where the length of "#" depends on the column width.

For displaying dates, the following rule is applied :

When the text length of the date is bigger then the column width, the cell will be displayed as "######" where the length of "#" depends on the column width.

Using Code

Refer to the following example code in order to display dates and numbers according to the column width.

JavaScript	Copy Code
// Displaying Numbers var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); var sheet = spread.getActiveSheet(); sheet.suspendPaint(); var width = [35, 45, 54, 66, 71, 80, 88, 95, 114]; for (var c = 0; c < 9; c++) { sheet.setValue(0, c, 123456.654321); sheet.setColumnWidth(c, width[c]); } sheet.resumePaint(); // Displaying Dates var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); var sheet = spread.getActiveSheet(); sheet.suspendPaint(); var date = new Date(2018, 9, 7); sheet.setValue(0, 0, date); sheet.setFormatter(0, 0, 'yyyy-mm-dd'); sheet.setColumnWidth(0, 80); sheet.resumePaint();

JavaScript

Copy Code

// Displaying Numbers

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
var width = [35, 45, 54, 66, 71, 80, 88, 95, 114];

for (var c = 0; c < 9; c++)
{
sheet.setValue(0, c, 123456.654321);
sheet.setColumnWidth(c, width[c]);
}
sheet.resumePaint();

// Displaying Dates

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
var date = new Date(2018, 9, 7);
sheet.setValue(0, 0, date);
sheet.setFormatter(0, 0, 'yyyy-mm-dd');
sheet.setColumnWidth(0, 80);
sheet.resumePaint();

Note: The following limitations should be kept in mind while formatting dates and numbers in SpreadJS -

Setting the TextIndent property will impact the number and date format in the cells. For example - when the column width is 60 px and the text Indent is set to 40 px, then only 20 px (60 px-40 px) will be left to set the number or data. But for displaying "#", we will have the column width of "60 px" to set "####".
When shrinkToFit property is set as Boolean true, the Number and Date Format will not affected.
When the cell format and the cell value is a number, SpreadJS will not change the value but will only show "###" in case the length of text is bigger then the column width.
Date and Number display will not support the cell-overflow feature.