Basic Format

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.

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.

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

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.

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.

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

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

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

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.