Row States and Column States

The row state and column state is an extended version of the cell state, giving states to entire rows and columns.

We support 8 kinds of states in the row or column direction, and you can try them in different sheets in this demo. States Hover state Hover row: hovering over a row Hover column: hovering of a column Invalid state Invalid row: if one cell is invalid, then its row is invalid state Invalid column: if one cell is invalid, then its column is invalid state Edit state Edit row: when editing a cell, then its row is edit state Edit column: when editing a cell, then its column is edit state Active state Active row: the active row Active column: the active column Selected state Selected row: when one cell is selected, then its row is selected state Selected column: when one cell is selected, then its column is selected state Dirty state Dirty row: if one cell is dirty, then its row is dirty state Dirty column: if one cell is dirty, then its column is dirty state Inserted state Inserted row: the inserted row You could find them in GC.Spread.Sheets.RowColumnStates. Add State Rule The row state and column state is based on conditional formatting, so you could use addRowStateRule or addColumnStateRule: Or you can also create a new StateRule, then use addRule. Here is some sample code:
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(7); initHoverStateSheet(spread); initEditStateSheet(spread); initActiveStateSheet(spread); initSelectedStateSheet(spread); initDirtyStateSheet(spread); initInsertedStateSheet(spread); initInvalidStateSheet(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 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("rowTable", 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, [rowRange]); sheet.getCell(columnRange.row - 2, columnRange.col).value("Column Direction").font("bold 14.6667px Calibri") sheet.tables.add("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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("rowTable", 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("columnTable", 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("bothTable", 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(); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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="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; }