Number and Date Format

Users can choose set numbers and date formats in the worksheet. SpreadJS provides two modes, Mask and Overflow to help users choose the preferred behavior.

By default, the users can mask the data in the cells based on the width of the columns. This is known as the Mask mode. Users can also unmask the data in the cells and let it overflow into adjacent cells using the Overflow mode.

Each mode is described in detail below.

Mask Mode

The mask mode 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.

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 -> ####

Rules Applied while Displaying Numbers

The following rules are applied for displaying numbers:

• 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 below 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 below image].

• When the shortest scientific notation is not enough [For example - refer to the cell A1 in the below image], the number will be displayed as "####" where the length of "#" depends on the column width.

Rules Applied while Displaying Dates

The following rule is applied for displaying dates:

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

The following code sample shows how to display dates and numbers according to the column width.

// 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.

Overflow Mode

SpreadJS automatically displays "####", scientific notation, and rounded off integer values, when the column is not wide enough to display the entire number. However, you can change this default SpreadJS behavior to prevent the cell content from getting masked by using the NumbersFitMode workbook option and setting its value to 1.

This NumbersFitMode option will help you to display the entire number or date by allowing the cell content to overflow into the adjacent cells when the number or date length is bigger than the column width. This option is also applicable to the dates as well.

Refer to the following example to allow the dates and numbers to overflow when their length exceed the column width in a cell.

//set numbersFitMode option to 1
spread.options.numbersFitMode = 1;

//set date
var d = new Date();
activeSheet.setValue(1, 3, new Date(2011, 11, 11));

//set number
activeSheet.getCell(1, 1, GC.Spread.Sheets.SheetArea.viewport).text("12345678901239");