You can create a style and set properties for it using code such as the following:
Then you can set the style to a cell, row, column or a range:
The style in each of the different levels has a different precedence, as follows: cell > row > column.
SpreadJS allows you to set a name for a style, and add this named style to the sheet's named styles collection. This makes the style more convenient to use and manage.
You can set a name for a style and add this style to the named style collection of the sheet or the Spread component.
After the named style has been added to the named styles collection, you can get the style by its name or set style name directly:
If a named style will not be used, you can remove it from the named styles collection:
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import { SpreadSheets, Worksheet} from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
const spreadNS = GC.Spread.Sheets;
const data = [
["Month", "Payment", "Principal", "Interest", "Balance"],
[1, 8750.00, 8333.34, 416.67, 91666.67],
[2, 8715.28, 8333.34, 381.94, 83333.33],
[3, 8680.56, 8333.34, 347.22, 75000.00],
[4, 8645.83, 8333.34, 312.50, 66666.67],
[5, 8611.11, 8333.34, 277.78, 58333.33],
[6, 8576.39, 8333.34, 243.06, 50000.00],
[7, 8541.67, 8333.34, 208.33, 41666.67],
[8, 8506.94, 8333.34, 173.61, 33333.33],
[9, 8472.22, 8333.34, 138.89, 25000.00],
[10, 8437.50, 8333.34, 104.17, 16666.67],
[11, 8402.78, 8333.34, 69.44, 8333.33],
[12, 8368.06, 8333.34, 34.72, 0.00],
["Total", 102708.33, 100000.00, 2708.33]
];
export function AppFunc() {
const [styleInfo, setStyleInfo] = React.useState({
rowIndex: 0,
columnIndex: 0,
backgroundColor: '',
actualBackgroundColor: ''
});
const initSpread = (spread) => {
const sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.name("Basic Usage");
const cellStyle = new spreadNS.Style();
cellStyle.backColor = "#f7a711";
const rowStyle = new spreadNS.Style();
rowStyle.backColor = "#82bc00";
const columnStyle = new spreadNS.Style();
columnStyle.backColor = "#cccccc";
sheet.setText(4, 4, 'cell style', spreadNS.SheetArea.viewport);
sheet.setStyle(4, 4, cellStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(4, -1, rowStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(9, -1, rowStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(-1, 4, columnStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(-1, 6, columnStyle, spreadNS.SheetArea.viewport);
cellStyle.name = 'style1';
rowStyle.name = 'style2';
columnStyle.name = 'style3';
sheet.addNamedStyle(cellStyle);
sheet.addNamedStyle(rowStyle);
sheet.addNamedStyle(columnStyle);
sheet.setText(1, 0, 'style1', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 0, sheet.getNamedStyle('style1'), spreadNS.SheetArea.viewport);
sheet.setText(1, 1, 'style2', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 1, sheet.getNamedStyle('style2'), spreadNS.SheetArea.viewport);
sheet.setText(1, 2, 'style3', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 2, sheet.getNamedStyle('style3'), spreadNS.SheetArea.viewport);
const style5 = new spreadNS.Style();
style5.backColor = "red";
style5.isVerticalText = 'true';
style5.textIndent = 5;
const style6 = new spreadNS.Style();
style6.backColor = "green";
style6.isVerticalText = 'true';
style6.wordWrap = 'true';
const style7 = new spreadNS.Style();
style7.backColor = "yellow";
style7.isVerticalText = 'true';
style7.shrinkToFit = 'true';
sheet.resumePaint();
sheet.bind(GC.Spread.Sheets.Events.SelectionChanged, (eventName, args) => {
const { row: rowIndex, col: columnIndex } = args.newSelections[0];
const style = sheet.getStyle(rowIndex, columnIndex);
const actualStyle = sheet.getActualStyle(rowIndex, columnIndex);
const backgroundColor = getStyleColor(style);
const actualBackgroundColor = getStyleColor(actualStyle);
setStyleInfo({ rowIndex, columnIndex, backgroundColor, actualBackgroundColor });
})
let sheet1 = spread.getSheet(1);
sheet1.suspendPaint();
sheet1.name("Loan");
sheet1.setColumnCount(5);
for (let i = 1; i < 5; i++) {
sheet1.setColumnWidth(i, 100);
}
sheet1.setValue(0, 0, "Principal: $100,000.00");
sheet1.setValue(1, 0, "Interest rate: 5.00%");
sheet1.setValue(2, 0, "Payment interval: Monthly");
sheet1.setValue(3, 0, "Number of payments: 12");
sheet1.setArray(5, 0, data);
let summaryStyle = new GC.Spread.Sheets.Style();
summaryStyle.backColor = "#D9EAD3";
sheet1.getRange(0, 0, 4, 3).setStyle(summaryStyle);
let headStyle = new GC.Spread.Sheets.Style();
headStyle.font = "bold 12px sans-serif";
headStyle.backColor = "#cccccc";
let contentStyle = new GC.Spread.Sheets.Style();
contentStyle.backColor = "#81b100";
sheet1.getRange("A6:E6").setStyle(headStyle);
sheet1.getRange(5, 0, 14, 5).hAlign(GC.Spread.Sheets.HorizontalAlign.center)
for (let r = 6; r < 18; r++) {
if (r % 2 == 0) {
sheet1.setStyle(r,-1,contentStyle);
}
}
let currencyStyle = new GC.Spread.Sheets.Style();
currencyStyle.formatter = "[$$-409]#,##0.00";
currencyStyle.name = "currency";
sheet1.addNamedStyle(currencyStyle);
sheet1.getRange(6, 1, 13, 4).setStyleName("currency");
sheet1.getRange(18,0,1,5).font("bold 12px sans-serif")
sheet1.resumePaint();
sheet1.bind(GC.Spread.Sheets.Events.SelectionChanged, (eventName, args) => {
const { row: rowIndex, col: columnIndex } = args.newSelections[0];
const style = sheet1.getStyle(rowIndex, columnIndex);
const actualStyle = sheet1.getActualStyle(rowIndex, columnIndex);
const backgroundColor = getStyleColor(style);
const actualBackgroundColor = getStyleColor(actualStyle);
setStyleInfo({ rowIndex, columnIndex, backgroundColor, actualBackgroundColor });
})
};
const getStyleColor = (style) => {
let color = '';
if(style && style.backColor) {
color = style.backColor;
}
return color;
};
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={initSpread}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel panelinfo={styleInfo} />
</div>);
}
function Panel(props) {
const [panelInfo, setPanelInfo] = React.useState(props.panelinfo);
React.useEffect(() => {
if (props.panelinfo !== panelInfo) {
setPanelInfo(props.panelinfo);
}
}, [props.panelinfo]);
return (
<div class="options-container">
<p style={{ padding: '2px 10px'}}>Spread Styles are useful for consolidating visual properties that are use in a lot of different cells.</p>
<p style={{ padding: '2px 10px', backgroundColor: '#F4F8EB'}}>Select cell C2, E2, E5 or E10 then any other cell to see difference between the getStyle and getActualStyle api.</p>
<label id="getStyleLabel" for="getStyle">sheet.getStyle({ panelInfo.rowIndex }, { panelInfo.columnIndex })</label>
<input id="getStyle" type="text" disabled="disabled" style={{ backgroundColor: panelInfo.backgroundColor }} />
<label id="getActualStyleLabel" for="getActualStyle">sheet.getActualStyle({ panelInfo.rowIndex }, { panelInfo.columnIndex })</label>
<input id="getActualStyle" type="text" disabled="disabled" style={{ backgroundColor: panelInfo.actualBackgroundColor }} />
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet} from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
const spreadNS = GC.Spread.Sheets;
const Component = React.Component;
const data = [
["Month", "Payment", "Principal", "Interest", "Balance"],
[1, 8750.00, 8333.34, 416.67, 91666.67],
[2, 8715.28, 8333.34, 381.94, 83333.33],
[3, 8680.56, 8333.34, 347.22, 75000.00],
[4, 8645.83, 8333.34, 312.50, 66666.67],
[5, 8611.11, 8333.34, 277.78, 58333.33],
[6, 8576.39, 8333.34, 243.06, 50000.00],
[7, 8541.67, 8333.34, 208.33, 41666.67],
[8, 8506.94, 8333.34, 173.61, 33333.33],
[9, 8472.22, 8333.34, 138.89, 25000.00],
[10, 8437.50, 8333.34, 104.17, 16666.67],
[11, 8402.78, 8333.34, 69.44, 8333.33],
[12, 8368.06, 8333.34, 34.72, 0.00],
["Total", 102708.33, 100000.00, 2708.33]
];
export class App extends Component {
constructor(props) {
super(props);
this.state = {
rowIndex: 0,
columnIndex: 0,
backgroundColor: '',
actualBackgroundColor: ''
};
}
render() {
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel panelinfo={this.state} />
</div>);
}
initSpread(spread) {
const sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.name("Basic Usage");
const cellStyle = new spreadNS.Style();
cellStyle.backColor = "#f7a711";
const rowStyle = new spreadNS.Style();
rowStyle.backColor = "#82bc00";
const columnStyle = new spreadNS.Style();
columnStyle.backColor = "#cccccc";
sheet.setText(4, 4, 'cell style', spreadNS.SheetArea.viewport);
sheet.setStyle(4, 4, cellStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(4, -1, rowStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(9, -1, rowStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(-1, 4, columnStyle, spreadNS.SheetArea.viewport);
sheet.setStyle(-1, 6, columnStyle, spreadNS.SheetArea.viewport);
cellStyle.name = 'style1';
rowStyle.name = 'style2';
columnStyle.name = 'style3';
sheet.addNamedStyle(cellStyle);
sheet.addNamedStyle(rowStyle);
sheet.addNamedStyle(columnStyle);
sheet.setText(1, 0, 'style1', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 0, sheet.getNamedStyle('style1'), spreadNS.SheetArea.viewport);
sheet.setText(1, 1, 'style2', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 1, sheet.getNamedStyle('style2'), spreadNS.SheetArea.viewport);
sheet.setText(1, 2, 'style3', spreadNS.SheetArea.viewport);
sheet.setStyle(1, 2, sheet.getNamedStyle('style3'), spreadNS.SheetArea.viewport);
const style5 = new spreadNS.Style();
style5.backColor = "red";
style5.isVerticalText = 'true';
style5.textIndent = 5;
const style6 = new spreadNS.Style();
style6.backColor = "green";
style6.isVerticalText = 'true';
style6.wordWrap = 'true';
const style7 = new spreadNS.Style();
style7.backColor = "yellow";
style7.isVerticalText = 'true';
style7.shrinkToFit = 'true';
sheet.resumePaint();
sheet.bind(GC.Spread.Sheets.Events.SelectionChanged, (eventName, args) => {
const { row: rowIndex, col: columnIndex } = args.newSelections[0];
const style = sheet.getStyle(rowIndex, columnIndex);
const actualStyle = sheet.getActualStyle(rowIndex, columnIndex);
const backgroundColor = this.getStyleColor(style);
const actualBackgroundColor = this.getStyleColor(actualStyle);
this.setState({ rowIndex, columnIndex, backgroundColor, actualBackgroundColor });
})
let sheet1 = spread.getSheet(1);
sheet1.suspendPaint();
sheet1.name("Loan");
sheet1.setColumnCount(5);
for (let i = 1; i < 5; i++) {
sheet1.setColumnWidth(i, 100);
}
sheet1.setValue(0, 0, "Principal: $100,000.00");
sheet1.setValue(1, 0, "Interest rate: 5.00%");
sheet1.setValue(2, 0, "Payment interval: Monthly");
sheet1.setValue(3, 0, "Number of payments: 12");
sheet1.setArray(5, 0, data);
let summaryStyle = new GC.Spread.Sheets.Style();
summaryStyle.backColor = "#D9EAD3";
sheet1.getRange(0, 0, 4, 3).setStyle(summaryStyle);
let headStyle = new GC.Spread.Sheets.Style();
headStyle.font = "bold 12px sans-serif";
headStyle.backColor = "#cccccc";
let contentStyle = new GC.Spread.Sheets.Style();
contentStyle.backColor = "#81b100";
sheet1.getRange("A6:E6").setStyle(headStyle);
sheet1.getRange(5, 0, 14, 5).hAlign(GC.Spread.Sheets.HorizontalAlign.center)
for (let r = 6; r < 18; r++) {
if (r % 2 == 0) {
sheet1.setStyle(r,-1,contentStyle);
}
}
let currencyStyle = new GC.Spread.Sheets.Style();
currencyStyle.formatter = "[$$-409]#,##0.00";
currencyStyle.name = "currency";
sheet1.addNamedStyle(currencyStyle);
sheet1.getRange(6, 1, 13, 4).setStyleName("currency");
sheet1.getRange(18,0,1,5).font("bold 12px sans-serif")
sheet1.resumePaint();
sheet1.bind(GC.Spread.Sheets.Events.SelectionChanged, (eventName, args) => {
const { row: rowIndex, col: columnIndex } = args.newSelections[0];
const style = sheet1.getStyle(rowIndex, columnIndex);
const actualStyle = sheet1.getActualStyle(rowIndex, columnIndex);
const backgroundColor = this.getStyleColor(style);
const actualBackgroundColor = this.getStyleColor(actualStyle);
this.setState({ rowIndex, columnIndex, backgroundColor, actualBackgroundColor });
})
}
getStyleColor(style) {
let color = '';
if(style && style.backColor) {
color = style.backColor;
}
return color;
}
}
class Panel extends Component {
constructor(props) {
super(props);
this.state = this.props.panelinfo;
}
componentWillReceiveProps(nextProps) {
this.setState(nextProps.panelinfo);
}
render() {
return (
<div class="options-container">
<p style={{ padding: '2px 10px'}}>Spread Styles are useful for consolidating visual properties that are use in a lot of different cells.</p>
<p style={{ padding: '2px 10px', backgroundColor: '#F4F8EB'}}>Select cell C2, E2, E5 or E10 then any other cell to see difference between the getStyle and getActualStyle api.</p>
<label id="getStyleLabel" for="getStyle">sheet.getStyle({ this.state.rowIndex }, { this.state.columnIndex })</label>
<input id="getStyle" type="text" disabled="disabled" style={{ backgroundColor: this.state.backgroundColor }} />
<label id="getActualStyleLabel" for="getActualStyle">sheet.getActualStyle({ this.state.rowIndex }, { this.state.columnIndex })</label>
<input id="getActualStyle" type="text" disabled="disabled" style={{ backgroundColor: this.state.actualBackgroundColor }} />
</div>
)
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
textarea {
width: 100%;
height: 80px;
padding: 6px 12px;
box-sizing: border-box;
}
.sample-options {
z-index: 1000;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
