SpreadJS 15
Features / Cells / Cell Format / Basic Format
In This Topic
    Basic Format
    In This Topic

    You can format cell values using standard number, date time, custom formats, and various other format options.

    Use the setFormatter method to set a formatter string to a cell, and use the getFormatter method to get the cell formatter.

    You can also create a custom formatter with the FormatterBase class which supports the combo, conditional, and forecolor formatters.  The "(", ")", and "*" expressions are not supported.

    Standard Values Formatting

    You can get the cell, column, or row by using the formatter method to get and set the formatter for the cell.

    The following code example shows how to format cell values with different format styles.

    JavaScript
    Copy Code
    var dvalue= 12345.6789;
    activeSheet.setValue(0,0,dvalue);
    activeSheet.getCell(0, 1).formatter("M");
    activeSheet.setValue(0, 1, new Date(2011, 2, 9));
    activeSheet.getCell(0, 2).formatter("m");
    activeSheet.setValue(0, 2, new Date(2012, 10, 15));
    activeSheet.getCell(0, 3).formatter("General");
    activeSheet.setValue(0, 3, new Date(2012, 10, 15));
    activeSheet.getCell(0, 4).formatter("#.#%");
    activeSheet.setValue(0, 4, 1);
    activeSheet.getCell(1, 0).formatter('[<0](0.0);[>0]0.0;"zero";@');
    activeSheet.setValue(1, 0, 3);
    

    Fraction Formatting

    SpreadJS supports common fraction formats, such as "# ?/?", "# ??/??", "# ?/4", and "#,## ?/?". You can convert a negative value to a fraction.

    The following code example formats a cell value as a fraction.

    JavaScript
    Copy Code
    var dvalue= 123.89;
    activeSheet.setValue(0,0,dvalue);
    activeSheet.getCell(0, 0).formatter("# ??/??");
    

    DB Number Formatting

    SpreadJS also supports the local ID "$-411" and DBNumber.

    The following code example formats a cell value using locale ID "$-411".

    JavaScript
    Copy Code
    var dvalue= 123.89;
    activeSheet.setValue(0,0,dvalue);
    activeSheet.getCell(0,0).formatter("[$-411]dddd");
    

    The following code example formats a cell value using DBNumber.

    JavaScript
    Copy Code
    var dvalue= 123.89;
    activeSheet.setValue(0,0,dvalue);
    activeSheet.getCell(0,0).formatter("[DBNum2][$-411]General");
    

    Culture Formatting

    You can also set the culture to "ja-jp" or "en-us". Specify the culture for the widget or individual cells.

    The following code example sets the culture for the widget.

    JavaScript
    Copy Code
    //widget setting
    GC.Spread.Common.CultureManager.culture("ja-jp");
    

    Numbers are formatted based on the current culture. The default culture is English. The cell culture does not change the widget culture.

    The following code example sets the culture for the cell.

    JavaScript
    Copy Code
    //cell setting
    //Input date string "2014/01/07" in cell[0,0] and cell[0,1]
    activeSheet.getCell(0,0).formatter(new GC.Spread.Formatter.GeneralFormatter("yyyy/MM/dd dddd", "ja-jp"));
    activeSheet.getCell(0,1).formatter(new GC.Spread.Formatter.GeneralFormatter("yyyy/MM/dd dddd", "en-us"));
    

    Color Formatting

    The color format affects the foreColor and supports color string names as well as color index, ranging from color 1 to color 56.

    Color Index Formatting

    The following code example shows how to set color formatting by index to change the color of the cells according to the value range.

    JavaScript
    Copy Code
    activeSheet.getRange(2,2,1,6).formatter("[color44][<300]0.0;[color3][>400]0.0;[color45]0.0")
    activeSheet.getCell(2,2).value(100);
    activeSheet.getCell(2,3).value(200);
    activeSheet.getCell(2,4).value(300);
    activeSheet.getCell(2,5).value(400);
    activeSheet.getCell(2,6).value(500);
    activeSheet.getCell(2,7).value(600);
    
    Note: Using the color index in a formula does not have any color effect in Excel export.