Cell States allow you to apply different styles to selected cells depending on their states. This can be particularly useful for applications that require real-time feedback, such as showing more information when hovering over rows in an order list, highlighting cells that users need to enter data into, or letting users know when cell data is incorrect.
If two or more states intersect, the last set style takes priority and will combine with the other styles.
The priority of cell states are: edit > hover > active > selected > invalid formula > dirty > invalid > readonly;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
spread.suspendPaint();
var sheet = spread.getActiveSheet();
sheet.setDataSource(dataSource);
sheet.defaults.rowHeight = 30;
sheet.defaults.colWidth = 120;
initSpread(spread);
spread.resumePaint();
};
function initSpread(spread) {
addNameStyles(spread);
initPage(spread);
bindEvent(spread);
}
function getStyleNames(spread) {
var styles = spread.getNamedStyles();
return styles.map(function (style) {
return style.name;
});
}
function initPage(spread) {
//init style list
var $styleList = document.getElementById("style-list");
var styleListHtml = '';
var names = getStyleNames(spread);
names.forEach(function (name) {
styleListHtml += '<option>' + name + '</option>'
});
$styleList.innerHTML = styleListHtml;
}
function addNameStyles(spread) {
var temp;
for (var i = 0, len = nameStyles.length; i < len; i++) {
temp = new GC.Spread.Sheets.Style();
temp.fromJSON(nameStyles[i]);
spread.addNamedStyle(temp);
}
}
function bindEvent(spread) {
var btn = document.getElementById("apply-cell-state");
btn.addEventListener("click", function () {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
var styleName = document.getElementById("style-list").value;
var style;
if (styleName) {
style = spread.getNamedStyle(styleName);
}
var cellStateType = parseInt(document.getElementById("cell-states-type").value, 10);
if (range && cellStateType !== undefined && style) {
sheet.cellStates.add(range, cellStateType, style);
}
});
var protectSheetBtn = document.getElementById("protectSheet");
protectSheetBtn.addEventListener("change", function () {
var checked = !!protectSheetBtn.checked;
var sheet = spread.getActiveSheet();
sheet.options.isProtected = checked;
});
var lockRangeBtn = document.getElementById("lockRange");
lockRangeBtn.addEventListener("click", function () {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
sheet.getRange(range.row, range.col, range.rowCount, range.colCount).locked(true);
});
var validationBtn = document.getElementById("data-validation-apply");
validationBtn.addEventListener("click", function () {
var value1 = parseInt(document.getElementById("data-validation-from").value, 10);
var value2 = parseInt(document.getElementById("data-validation-to").value, 10);
var op = parseInt(document.getElementById("data-validation-op").value);
if (value1 !== undefined && value2 !== undefined) {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(op, value1, value2);
sheet.setDataValidator(range.row, range.col, range.rowCount, range.colCount, dv);
}
});
var invalidFormulaCheckbox = document.getElementById("allowInvalidFormula");
invalidFormulaCheckbox.addEventListener("change", function () {
var checked = !!invalidFormulaCheckbox.checked;
spread.options.allowInvalidFormula = checked;
var sheet = spread.getActiveSheet();
changeInvalidFormulaCellState(sheet, !checked);
sheet.recalcAll();
})
}
function changeInvalidFormulaCellState (sheet, isRemove) {
var wholeRange = new GC.Spread.Sheets.Range(0, 0, sheet.getRowCount(), sheet.getColumnCount());
if (isRemove) {
sheet.cellStates.remove(wholeRange, GC.Spread.Sheets.CellStatesType.invalidFormula, GC.Spread.Sheets.SheetArea.viewport);
return;
}
var style = new GC.Spread.Sheets.Style();
var borderTop = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderBottom = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderLeft = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderRight = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
style.borderTop = borderTop;
style.borderBottom = borderBottom;
style.borderLeft = borderLeft;
style.borderRight = borderRight;
sheet.cellStates.add(wholeRange, GC.Spread.Sheets.CellStatesType.invalidFormula, style, GC.Spread.Sheets.SheetArea.viewport);
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script
src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/namestyle.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p>Change the properties below then press the Apply Cell States button to apply these changes.</p>
<div >
<label> <h4>Range:</h4></label>
<div class="row-content">
<span>The selected range in sheet will be used as the cell states range.</span>
</div>
</div>
<div class="row">
<label> <h4>Style:</h4></label>
<div class="row-content">
<select class="row-select" id="style-list">
<option>20% - Accent1</option>
<option>20% - Accent2</option>
<option>20% - Accent3</option>
<option>20% - Accent4</option>
<option>20% - Accent5</option>
<option>20% - Accent6</option>
<option>40% - Accent1</option>
<option>40% - Accent2</option>
<option>40% - Accent3</option>
<option>40% - Accent4</option>
<option>40% - Accent5</option>
<option>40% - Accent6</option>
<option>60% - Accent1</option>
<option>60% - Accent2</option>
<option>60% - Accent3</option>
<option>60% - Accent4</option>
<option>60% - Accent5</option>
<option>60% - Accent6</option>
<option>Accent1</option>
<option>Accent2</option>
<option>Accent3</option>
<option>Accent4</option>
<option>Accent5</option>
<option>Accent6</option>
<option>Bad</option>
<option>Calculation</option>
<option>Check Cell</option>
<option>Comma</option>
<option>Comma [0]</option>
<option>Currency</option>
<option>Currency [0]</option>
<option>Explanatory Text</option>
<option>Good</option>
<option>Heading 1</option>
<option>Heading 2</option>
<option>Heading 3</option>
<option>Heading 4</option>
<option>Input</option>
<option>Linked Cell</option>
<option>Neutral</option>
<option>Normal</option>
<option>Note</option>
<option>Output</option>
<option>Percent</option>
<option>Title</option>
<option>Total</option>
<option>Warning Text</option>
</select>
</div>
</div>
<div class="row">
<label> <h4>Cell States Type:</h4></label>
<div class="row-content">
<select id="cell-states-type" class="row-select">
<option value="1">hover</option>
<option value="2">invalid</option>
<option value="8">edit</option>
<option value="4">readonly</option>
<option value="16">active</option>
<option value="32">select</option>
<option value="64">dirty</option>
<option value="128">invalid formula</option>
</select>
<div id="style-list-dialog">
</div>
</div>
</div>
<div class="row">
<label> <h4>Protect Info:</h4></label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="protectSheet"> protect
sheet</label>
<label class="protect-info"><input type="button" id="lockRange" value=" lock range cell"></label>
</div>
</div>
<div class="row">
<label> <h4>Workbook Option:</h4></label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="allowInvalidFormula"> allow invalid formula</label>
</div>
</div>
<div class="row">
<label> <h4> Data validation:</h4></label>
<div class="row-content">
<label class="protect-info"><input type="number" id="data-validation-from" placeholder="Data Validation value1">
</label>
<label class="protect-info">
<select id="data-validation-op" class="row-select">
<option value="6" selected="">Between</option>
<option value="7">NotBetween</option>
<option value="0">EqualTo</option>
<option value="1">NotEqualTo</option>
<option value="2">GreaterThan</option>
<option value="4">LessThan</option>
<option value="3">GreaterThanOrEqualTo</option>
<option value="5">LessThanOrEqualTo</option>
</select>
</label>
<label class="protect-info"><input type="number" id="data-validation-to" placeholder="Data Validation value2">
</label>
<label class="protect-info"><input type="button" id="data-validation-apply"
value="add validation"></label>
</div>
</div>
<div class="row">
<label> <span></span></label>
<div class="row-content">
<input type="button" value="Apply Cell States" id="apply-cell-state">
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 300px);
height: 100%;
overflow: hidden;
float: left;
box-sizing: border-box;
}
.options-container {
float: right;
width: 300px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
padding-bottom: 8px;
}
label {
padding-bottom: 4px;
display: block;
}
input {
width: 100%;
padding: 4px 8px;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
h4{
margin: 0;
}
.row{
line-height: 30px;
}
.row > label{
width: 150px;
text-align: left;
padding: 0 5px;
}
.row-select{
width: 240px;
margin-left: 20px;
}
.protect-info{
width: 100%;
display: inline-block;
margin: 5px 0;
}
.protect-info input[type="checkbox"]{
width: auto;
}
.protect-info input[type="number"] , .protect-info input[type="text"], .protect-info input[type="button"]{
width: 240px;
margin-left: 20px;
}
.protect-info input[type="button"]{
height: 30px;
line-height: 30px;
}
#apply-cell-state {
font-weight: bold;
margin-top: 15px;
}
