SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage Data / Cell Format / Number and Date Format
In This Topic
    Number and Date Format
    In This Topic

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

    Number format

    For displaying numbers, the following rules are applied :

    For displaying dates, the following rule is applied :

    Date format     

    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();
    

    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.