单元格状态允许你根据所选单元格的状态,对其应用不同的样式。 这对于需要实时反馈的应用来说特别有用,比如在订单列表中的行上悬停时显示更多信息,突出显示用户需要输入数据的单元格,或者让用户知道单元格数据不正确。
如果两个或更多的状态相交,最后设置的样式会优先显示,并与其他样式结合。
单元格状态的优先级是: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="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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>修改以下属性,然后点击“应用单元格状态”按钮来应用这些更改。</p>
<div>
<label>
<h4>范围:</h4>
</label>
<div class="row-content">
<span>工作表中选中的范围将用作单元格状态范围。</span>
</div>
</div>
<div class="row">
<label>
<h4>样式:</h4>
</label>
<div class="row-content">
<select class="row-select" id="style-list"></select>
</div>
</div>
<div class="row">
<label>
<h4>单元格状态类型:</h4>
</label>
<div class="row-content">
<select id="cell-states-type" class="row-select">
<option value="1">悬停</option>
<option value="2">无效</option>
<option value="8">编辑</option>
<option value="4">只读</option>
<option value="16">活动</option>
<option value="32">选择</option>
<option value="64">已修改</option>
<option value="128">无效公式</option>
</select>
<div id="style-list-dialog">
</div>
</div>
</div>
<div class="row">
<label>
<h4>保护信息:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="protectSheet"> 保护工作表</label>
<label class="protect-info"><input type="button" id="lockRange" value=" 锁定范围单元格"></label>
</div>
</div>
<div class="row">
<label>
<h4>工作簿选项:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="allowInvalidFormula"> 允许无效公式</label>
</div>
</div>
<div class="row">
<label>
<h4> 数据验证:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="number" id="data-validation-from"
placeholder="数据验证值1">
</label>
<label class="protect-info">
<select id="data-validation-op" class="row-select">
<option value="6" selected="">介于</option>
<option value="7">不介于</option>
<option value="0">等于</option>
<option value="1">不等于</option>
<option value="2">大于</option>
<option value="4">小于</option>
<option value="3">大于或等于</option>
<option value="5">小于或等于</option>
</select>
</label>
<label class="protect-info"><input type="number" id="data-validation-to"
placeholder="数据验证值2">
</label>
<label class="protect-info"><input type="button" id="data-validation-apply"
value="添加验证"></label>
</div>
</div>
<div class="row">
<label> <span></span></label>
<div class="row-content">
<input type="button" value="应用单元格状态" 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;
}