行状态和列状态

概述本 Demo 展示了 SpreadJS 的行状态和列状态功能。Demo 创建了 8 个工作表，分别演示了 8 种不同的状态类型：悬停、编辑、活跃、选中、脏值、插入、无效和非法公式。每个工作表都包含了三个表格，分别演示行方向、列方向和双向（同时应用行和列）的状态规则效果。实现思路创建 8 个工作表，每个工作表演示一种状态类型每个工作表加载示例数据并创建三个表格区域使用 addRowStateRule 和 addColumnStateRule 为不同区域添加状态规则为不同状态配置不同的背景颜色样式对于 invalid 状态，设置数据验证规则对于 invalidFormula 状态，启用允许非法公式选项代码解析为行方向添加悬停状态规则这段代码为指定范围的行添加了悬停状态规则。当鼠标悬停在任意单元格上时，该单元格所在的整行都会应用 style 中定义的背景颜色。为列方向添加悬停状态规则这段代码为指定范围的列添加了悬停状态规则。当鼠标悬停时，整列都会被高亮显示。同时应用行和列方向的状态规则当同时应用行和列方向的状态规则时，鼠标悬停的单元格所在的行和列都会被高亮，形成十字交叉效果。为不同区域设置不同样式这段代码为不同的区域设置了不同的样式，当鼠标悬停时，不同列会显示不同的背景颜色。设置数据验证规则（用于 invalid 状态）为 Country 列设置列表验证器，当用户输入无效值时，该行会被标记为 invalid 状态。运行效果 hover 工作表：鼠标悬停在单元格上时，整行或整列会高亮显示 edit 工作表：双击编辑单元格时，整行或整列会高亮显示 active 工作表：单元格获得焦点时，整行或整列会高亮显示 selected 工作表：选中单元格时，整行或整列会高亮显示，支持 Ctrl 多选 dirty 工作表：修改单元格值或插入行后，整行或整列会标记为脏值状态 inserted 工作表：插入行后，新增的行会被高亮显示 invalid 工作表：数据验证失败时，整行或整列会被标记为无效状态 invalidFormula 工作表：输入非法公式（如 =SUM()）时，整行或整列会被高亮显示每个工作表都包含三个表格，分别演示行方向、列方向和双向的效果。 API 参考 addRowStateRule 方法 state：状态类型，使用 GC.Spread.Sheets.RowColumnStates 枚举 style：匹配状态时应用的样式，可以是单个样式或样式数组 ranges：应用规则的范围数组，每个元素为 GC.Spread.Sheets.Range 对象 addColumnStateRule 方法参数与 addRowStateRule 相同，但应用于列方向。 RowColumnStates 枚举可用的状态类型： hover：悬停状态 edit：编辑状态 active：活跃/焦点状态 selected：选中状态 dirty：脏值状态（值被修改） inserted：插入状态 invalid：无效状态（数据验证失败） invalidFormula：非法公式状态

window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
};
var suppliersData = [
["Id","ContactName","CompanyName","Country","phone"],
[4,"Yoshi Nagase","Tokyo Traders","Japan","(03) 3555-5011"],
[5,"Antonio del Valle Saavedra","Cooperativa de Quesos 'Las Cabras'","Spain","(98) 598 76 54"],
[6,"Mayumi Ohno","Mayumi's","Japan","(06) 431-7877"],
[7,"Ian Devling","Pavlova Ltd.","Australia","(03) 444-2343"],
[8,"Peter Wilson","Specialty Biscuits Ltd.","UK","(161) 555-4448"],
[9,"Lars Peterson","PB Knäckebröd AB","Sweden","031-987 65 43"],
[10,"Carlos Diaz","Refrescos Americanas LTDA","Brazil","(11) 555 4640"],
[11,"Petra Winkler","Heli Süßwaren GmbH & Co. KG","Germany","(010) 9984510"],
[2,"Shelley Burke","New Orleans Cajun Delights","USA","(100) 555-4822"]
];
var ordersData = [
["Id","CustomerId","ShipVia","shipName","Freight"],
[10271,"SPLIR",2,"Split Rail Beer & Ale",4.54],
[10266,"WARTH",3,"Wartian Herkku",25.73],
[10279,"LEHMS",2,"Lehmanns Marktstand",25.83],
[10292,"TRADH",2,"Tradiçao Hipermercados",1.35],
[10295,"VINET",2,"Vins et alcools Chevalier",1.15],
[10313,"QUICK",2,"QUICK-Stop",1.96],
[10317,"LONEP",1,"Lonesome Pine Restaurant",12.69],
[10322,"PERIC",3,"Pericles Comidas clásicas",0.4],
[10320,"WARTH",3,"Wartian Herkku",34.57]
];
var productsData = [
["Id","UnitPrice","ProductName","UnitsInStock","QuantityPerUnit","Discontinued"],
[4,22,"Chef Anton's Cajun Seasoning",53,"48 - 6 oz jars",true],
[5,21.35,"Chef Anton's Gumbo Mix",0,"36 boxes",true],
[6,25,"Grandma's Boysenberry Spread",120,"12 - 8 oz jars",false],
[7,30,"Uncle Bob's Organic Dried Pears",15,"12 - 1 lb pkgs.",false],
[8,40,"Northwoods Cranberry Sauce",6,"12 - 12 oz jars",false],
[9,97,"Mishi Kobe Niku",29,"18 - 500 g pkgs.",true],
[10,31,"Ikura",31,"12 - 200 ml jars",false],
[2,19,"Chang",17,"24 - 12 oz bottles",false],
[3,10,"Aniseed Syrup",13,"12 - 550 ml bottles",false],
[11,21,"Queso Cabrales 2",22,"1 kg pkg.",false],
[12,38,"Queso Manchego La Pastora",86,"10 - 500 g pkgs.",false],
[13,6,"Konbu",24,"2 kg box",false],
[14,23.25,"Tofu",35,"40 - 100 g pkgs.",false],
[15,15.5,"Genen Shouyu",39,"24 - 250 ml bottles",false],
[1,18,"Chai",39,"10 boxes x 20 bags",false],
[16,17.45,"Pavlova",29,"32 - 500 g boxes",false],
[17,39,"Alice Mutton",0,"20 - 1 kg tins",true],
[18,62.5,"Carnarvon Tigers",42,"16 kg pkg.",false],
[19,9.2,"Teatime Chocolate Biscuits",25,"10 boxes x 12 pieces",false],
[20,81,"Sir Rodney's Marmalade",40,"30 gift boxes",false],
[24,4.5,"Guaraná Fantástica",20,"12 - 355 ml cans",true],
];
function initSpread(spread) {
spread.setSheetCount(8);
initHoverStateSheet(spread);
initEditStateSheet(spread);
initActiveStateSheet(spread);
initSelectedStateSheet(spread);
initDirtyStateSheet(spread);
initInsertedStateSheet(spread);
initInvalidStateSheet(spread);
initInvalidFormulaStateSheet(spread);
}
function initHoverStateSheet(spread) {
var sheet = spread.getSheet(0);
sheet.name("hover");
var description = "Use you mouse to hover the table cells.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var style1 = new GC.Spread.Sheets.Style("rgb(240, 67, 54)", "rgb(255, 255, 255)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable0", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.hover, [style, style1, style], [new GC.Spread.Sheets.Range(4, 1, 9, 2), new GC.Spread.Sheets.Range(4, 3, 9, 1), new GC.Spread.Sheets.Range(4, 4, 9, 2)]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable1", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.hover, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable2", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.hover, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.hover, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}
function initEditStateSheet(spread) {
var sheet = spread.getSheet(1);
sheet.name("edit");
var description = "Double click the table cells or input something directly to edit it.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable3", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.edit, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable4", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.edit, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable5", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.edit, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.edit, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}
function initActiveStateSheet(spread) {
var sheet = spread.getSheet(2);
sheet.name("active");
var description = "Focus table cells to active it.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable6", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.active, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable7", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.active, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable8", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.active, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.active, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}
function initSelectedStateSheet(spread) {
var sheet = spread.getSheet(3);
sheet.name("selected");
var description = "Select table cells, you could also use ctrl to multiple select or deselect.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable9", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.selected, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable10", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.selected, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable11", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.selected, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.selected, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}
function initDirtyStateSheet(spread) {
var sheet = spread.getSheet(4);
sheet.name("dirty");
var description = "Edit table cells value or insert rows to make them dirty.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable12", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.dirty, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable13", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.dirty, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable14", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.dirty, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.dirty, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.clearPendingChanges();
sheet.resumePaint();
}
function initInsertedStateSheet(spread) {
var sheet = spread.getSheet(5);
sheet.name("inserted");
var description = "Insert rows, not support the inserted column.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable15", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.inserted, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable16", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.inserted, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable17", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.inserted, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.inserted, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.clearPendingChanges();
sheet.resumePaint();
}
function initInvalidStateSheet(spread) {
spread.options.highlightInvalidData = true;
var sheet = spread.getSheet(6);
sheet.name("invalid");
var description = "Edit table cells value to make them invalid.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator('Japan, Spain, Australia, UK, Sweden, Brazil, Germany, USA');
dv1.inputTitle('Please choose a country:');
sheet.setDataValidator(rowRange.row, 4, rowRange.rowCount, 1, dv1);
var dv2 = new GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 1, 3);
sheet.setDataValidator(columnRange.row, 3, columnRange.rowCount, 1, dv2);
var dv3 = new GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 0, 120);
sheet.setDataValidator(bothRange.row, 10, bothRange.rowCount, 1, dv3);
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction(The validator is at the 'Country' column.)").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable18", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.invalid, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction(The validator is at the 'ShipVia' column. The valid number is between 1 ~ 3)").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable19", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.invalid, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction(The validator is at the 'UnitsInStock' column. The valid number is between 0 ~ 120)").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable20", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.invalid, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.invalid, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}
function initInvalidFormulaStateSheet(spread) {
spread.options.allowInvalidFormula = true;
var sheet = spread.getSheet(7);
sheet.name("invalid formula");
var description = "Edit table cells value to invalid formula, such as '=SUM()'.";
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style("rgb(205, 228, 153)");
var rowRange = new GC.Spread.Sheets.Range(4, 1, 9, 5);
var columnRange = new GC.Spread.Sheets.Range(16, 1, 9, 5);
var bothRange = new GC.Spread.Sheets.Range(4, 7, 21, 6);
sheet.suspendPaint();
sheet.getCell(1, 1).value(description).foreColor("red");
sheet.getCell(rowRange.row - 2, rowRange.col).value("Row Direction").font("bold 14.6667px Calibri")
sheet.tables.add("rowTable21", rowRange.row - 1, rowRange.col, rowRange.rowCount + 1, rowRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(rowRange.row -1, rowRange.col, suppliersData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.invalidFormula, style, [rowRange]);
sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri")
sheet.tables.add("columnTable22", columnRange.row - 1, columnRange.col, columnRange.rowCount + 1, columnRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(columnRange.row - 1, columnRange.col, ordersData);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.invalidFormula, style, [columnRange]);
sheet.getCell(bothRange.row - 2, bothRange.col).value("Both Direction").font("bold 14.6667px Calibri")
sheet.tables.add("bothTable23", bothRange.row - 1, bothRange.col, bothRange.rowCount + 1, bothRange.colCount, GC.Spread.Sheets.Tables.TableThemes.light1);
sheet.setArray(bothRange.row - 1, bothRange.col, productsData);
cfs.addRowStateRule(GC.Spread.Sheets.RowColumnStates.invalidFormula, style, [bothRange]);
cfs.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.invalidFormula, style, [bothRange]);
sheet.autoFitColumn(2);
sheet.autoFitColumn(3);
sheet.autoFitColumn(4);
sheet.autoFitColumn(5);
sheet.autoFitColumn(9);
sheet.autoFitColumn(11);
sheet.autoFitColumn(12);
sheet.resumePaint();
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<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="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" style="width: 100%; height: 100%"></div>
</div>
</body>
</html>

.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}

SpreadJS 在线示例