Formatting Cells

You can format cell values using standard number, date time, and custom formats. You can also set the culture to "ja-jp" or "en-us".

Numbers are formatted based on the current culture. The default culture is English. You can set the culture to "ja-jp" or "en-us". You can specify the culture for the widget or individual cells. The cell culture does not change the widget culture.

SpreadJS supports common fraction formats, such as "# ?/?", "# ??/??", "# ?/4", and "#,## ?/?". You can convert a negative value to a fraction. SpreadJS also supports the local ID "$-411" and DBNumber.

The custom format mode supports the combo, conditional, and forecolor formatters. The "(", ")", and "*" expressions are not supported.

You can also create a custom formatter with the CustomFormatterBase class.

For more information on standard numeric format strings and number format codes, refer to http://msdn.microsoft.com/en-us/library/dwhawy9k.aspx.

Using Code

This example formats cell values.

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("[>2]0.0;[<-3]0.0;zero;"); activeSheet.setValue(1, 0, 3);

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("[>2]0.0;[<-3]0.0;zero;");
activeSheet.setValue(1, 0, 3);

Using Code

This example formats a cell value as a fraction.

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

Using Code

This example formats cell values using standard formatters.

JavaScript	Copy Code
var dvalue= 12345.6789; var nvalue = 12345; activeSheet.setColumnWidth(0, 100); activeSheet.setColumnWidth(1, 100); activeSheet.setColumnWidth(2, 100); activeSheet.setValue(0,0,dvalue); activeSheet.getCell(0, 0).formatter(new GcSpread.Sheets.GeneralFormatter("c3", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.getCell(0, 1).formatter(new GcSpread.Sheets.GeneralFormatter("E", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(0,1,dvalue); activeSheet.getCell(0, 2).formatter(new GcSpread.Sheets.GeneralFormatter("f", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(0,2,dvalue); activeSheet.getCell(1, 0).formatter(new GcSpread.Sheets.GeneralFormatter("N", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(1,0,dvalue); activeSheet.getCell(1, 1).formatter(new GcSpread.Sheets.GeneralFormatter("g2", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(1,1,dvalue); activeSheet.getCell(1, 2).formatter(new GcSpread.Sheets.GeneralFormatter("p", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(1,2,dvalue); activeSheet.getCell(2, 0).formatter(new GcSpread.Sheets.GeneralFormatter("r", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.getCell(2, 0).value(Math.PI); activeSheet.getCell(2, 1).formatter(new GcSpread.Sheets.GeneralFormatter("x", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(2, 1,nvalue); activeSheet.getCell(2, 2).formatter(new GcSpread.Sheets.GeneralFormatter("D8", GcSpread.Sheets.FormatMode.StandardNumericMode)); activeSheet.setValue(2,2,nvalue);

JavaScript

Copy Code

var dvalue= 12345.6789;
var nvalue = 12345;
activeSheet.setColumnWidth(0, 100);
activeSheet.setColumnWidth(1, 100);
activeSheet.setColumnWidth(2, 100);
activeSheet.setValue(0,0,dvalue);
activeSheet.getCell(0, 0).formatter(new GcSpread.Sheets.GeneralFormatter("c3", GcSpread.Sheets.FormatMode.StandardNumericMode));

activeSheet.getCell(0, 1).formatter(new GcSpread.Sheets.GeneralFormatter("E", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(0,1,dvalue);

activeSheet.getCell(0, 2).formatter(new GcSpread.Sheets.GeneralFormatter("f", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(0,2,dvalue);

activeSheet.getCell(1, 0).formatter(new GcSpread.Sheets.GeneralFormatter("N", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(1,0,dvalue);

activeSheet.getCell(1, 1).formatter(new GcSpread.Sheets.GeneralFormatter("g2", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(1,1,dvalue);

activeSheet.getCell(1, 2).formatter(new GcSpread.Sheets.GeneralFormatter("p", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(1,2,dvalue);

activeSheet.getCell(2, 0).formatter(new GcSpread.Sheets.GeneralFormatter("r", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.getCell(2, 0).value(Math.PI);

activeSheet.getCell(2, 1).formatter(new GcSpread.Sheets.GeneralFormatter("x", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(2, 1,nvalue);

activeSheet.getCell(2, 2).formatter(new GcSpread.Sheets.GeneralFormatter("D8", GcSpread.Sheets.FormatMode.StandardNumericMode));
activeSheet.setValue(2,2,nvalue);

Using Code

This example formats a cell value using locale ID "$-411".

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

Using Code

This example formats a cell value using DBNumber.

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

Using Code

This example sets the culture for the widget.

JavaScript	Copy Code
//widget setting GcSpread.Sheets.Culture("ja-jp");

Using Code

This 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 GcSpread.Sheets.GeneralFormatter("yyyy/MM/dd dddd", 0, "ja-jp")); activeSheet.getCell(0,1).formatter(new GcSpread.Sheets.GeneralFormatter("yyyy/MM/dd dddd", 0, "en-us"));