- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
-
Cells
- Work with Cells
- Cell Types
- Cell Format
- Cell States
- Cell Range
- Get Dirty Status
- Cell Buttons
- Cell Dropdowns
- Cell Comments
- Hyperlink
- Auto Merge Cells
- AutoFit
- Auto Fill Data
- Mask Input
- Shrink to Fit
- Cell Overflow
- Ellipsis or Tips for Cell Overflow
- Cell Alignment and Indentation
- Cell Padding and Label Styles
- Cell Span
- Cell Tags
-
Styles
- Cell Decoration
- Cell Colors
- Borders, Gridlines and Diagonal Lines
- Quote Prefix
- Insert Cut or Copied Cells
- Rotate Text In Cells
- Text Direction
- Text Decoration
- Rich Text
- Wrap Text
- Drag and Drop
- Drag and Merge
- Edit Mode Always
- Watermark
- Display Zero as Blank
- IME Mode
- Pattern Fill and Gradient Fill
- Paste Options
- Data Binding
- TableSheet
- GanttSheet
- ReportSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Form Controls
- Floating Objects
- Barcodes
- Charts
- Sparklines
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
Styles
You can create a style in SpreadJS that can be applied to a cell, cell range, rows, columns, or a sheet using the Style object. You can also create your own named style that uses a Style object. The style can contain settings such as borders, colors, and fonts.
The cell style is a composite of settings that are applied based on priority. The style in the cell has the highest priority. The style of the row the cell is in is next, then the column the cell is in, and then the default style of the sheet.
Style objects can be assigned using the setStyle and setStyleName methods for cells and cell ranges. You can also specify an entire row or entire column by using the -1 value.
Set Style for a Cell
You can create a style and apply it to a cell in a spreadsheet using the setStyle method.
For example, you can define a style consisting of red background color and a different style of line border. Apply the style to worksheet cells as shown in the image below.
The following code sample uses the setStyle method to assign a style to a cell.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.borderLeft = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderTop = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderRight = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderBottom = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
activeSheet.setStyle(1,1,style,GC.Spread.Sheets.SheetArea.viewport);
//row
//activeSheet.setStyle(1,-1,style,GC.Spread.Sheets.SheetArea.viewport);
//column
//activeSheet.setStyle(-1,2,style,GC.Spread.Sheets.SheetArea.viewport);Set Default Style
You can specify a default style for the cells in a spreadsheet by using the setDefaultStyle method. All the cells follow the same style as defined by the setDefaultStyle method.
The following code sample uses the setDefaultStyle method.
//setDefaultStyle
activeSheet.setRowCount(5, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setColumnCount(5, GC.Spread.Sheets.SheetArea.viewport);
//Set the default styles.
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.backColor = "LemonChiffon";
defaultStyle.foreColor = "Red";
defaultStyle.formatter = "0.00";
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
defaultStyle.borderLeft = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);
defaultStyle.borderTop = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);
defaultStyle.borderRight = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);
defaultStyle.borderBottom = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);
activeSheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
var rowCount = activeSheet.getRowCount();
var colCount = activeSheet.getColumnCount();
for(var i = 0; i < rowCount; i++){
for(var j = 0; j < colCount; j++){
activeSheet.setValue(i, j, i+j, GC.Spread.Sheets.SheetArea.viewport);
}
}Set Style for a Range
You can specify a style and apply it to a cell range using the CellRange's setStyle method. For example, the following image shows two different styles set on the cell range.
The following code sample shows how to set styles in a cell range.
// use setStyle method on cellRange
let style1 = new GC.Spread.Sheets.Style();
style1.formatter = "#,##0.00";
style1.backColor = "rgb(124,200,208)";
style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style1.font = "bold 12px sans-serif";
style1.borderLeft = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style1.borderTop = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style1.borderRight = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style1.borderBottom = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
activeSheet.getRange("I2:J2").setStyle(style1);
let style2 = new GC.Spread.Sheets.Style();
style2.backColor = "rgb(218, 241, 243)";
style2.borderLeft = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style2.borderTop = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style2.borderRight = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
style2.borderBottom = new GC.Spread.Sheets.LineBorder("gray", GC.Spread.Sheets.LineStyle.double);
activeSheet.getRange("I3:J7").setStyle(style2);Set Named Style
You can create your own named style and add it to the sheet or the spread with the addNamedStyle method. You can change the style settings or remove the named style. Use the setStyleName method from Worksheet or CellRange class to use the style on a cell or a cell range. Use -1 to specify an entire row or entire column.
Named styles are useful if a style is used many times or in many cells. Use a named style with a JSON data source or Excel import and export since less data is used.
The following code sample uses named styles to set styles for the table header and the table data.
// use setStyleName method on cell
let styleCell = new GC.Spread.Sheets.Style();
styleCell.name = "header_style";
styleCell.font = "bold 14px sans-serif";
styleCell.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
styleCell.backColor = "rgb(255, 225, 0)";
styleCell.borderLeft = new GC.Spread.Sheets.LineBorder("purple", GC.Spread.Sheets.LineStyle.hair);
styleCell.borderTop = new GC.Spread.Sheets.LineBorder("purple", GC.Spread.Sheets.LineStyle.hair);
styleCell.borderRight = new GC.Spread.Sheets.LineBorder("purple", GC.Spread.Sheets.LineStyle.hair);
styleCell.borderBottom = new GC.Spread.Sheets.LineBorder("purple", GC.Spread.Sheets.LineStyle.hair);
activeSheet.addNamedStyle(styleCell);
activeSheet.setStyleName(1, 1, "header_style");
activeSheet.setStyleName(1, 2, "header_style");
// use setStyleName method on cell range
let style = new GC.Spread.Sheets.Style();
style.name = "score_style";
style.font = "italic 12px sans-serif";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.backColor = "rgb(255,149,126)";
style.borderLeft = new GC.Spread.Sheets.LineBorder("red", GC.Spread.Sheets.LineStyle.hair);
style.borderTop = new GC.Spread.Sheets.LineBorder("red", GC.Spread.Sheets.LineStyle.hair);
style.borderRight = new GC.Spread.Sheets.LineBorder("red", GC.Spread.Sheets.LineStyle.hair);
style.borderBottom = new GC.Spread.Sheets.LineBorder("red", GC.Spread.Sheets.LineStyle.hair);
activeSheet.addNamedStyle(style);
activeSheet.getRange("B3:C8").setStyleName("score_style");You can also set apply flags that help to handle the effect of different properties in the defined named styles. This gives the option to set more than one named style to a cell without affecting each other's formatting options.
The following table shows all the available apply flags available in GC.Spread.Sheets.Style and the properties affected for namedStyle:
Apply Flag | Properties Affected |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
The default value of apply flags behave equally as being true. These properties will be saved in exported JSON if the value is defined for namedStyle.
For example, you can add two different named styles to a cell where "Font style" controls the font properties whereas "Align style" controls the alignment properties. The following code sample shows how to achieve the same.
var style = new GC.Spread.Sheets.Style();
style.name = "Font style";
style.font = "Verdana";
style.backColor = "red";
style.formatter = "yyyy/mm/dd";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.applyFill = false;
style.applyAlignment = false;
spread.addNamedStyle(style);
var style2 = new GC.Spread.Sheets.Style();
style2.name = "Align style";
style2.font = "Calibri";
style2.backColor = "yellow";
style2.formatter = "dd/mm/yy";
style2.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
style2.wordWrap = false;
style2.applyFont = false;
style2.applyNumberFormat = false;
spread.addNamedStyle(style2);
//do something
activeSheet.setStyleName(0, 1, "Font style");
activeSheet.setStyleName(0, 1, "Align style");
//another usage
activeSheet.setStyle(0, 0, "Font style");
activeSheet.setStyle(0, 0, "Align style");