Table With Conditional Formats and Data Validation

Spread supports automatically adding any existing conditional formatting and data validation rules to newly added table rows or columns.

In the following example, tables columns B:F have data validation rules. Column F also has conditional formatting rules. Click in cell F11 (value ‘13’) and press the Tab key. A new row will be added. You can follow the rules in the above rows to see this in action. For example, in B12, type ‘5’. This is not a valid value so it will be highlighted. Type ‘10’ in F12 to highlight is using the conditional formatting rules.

If the existing table has conditional formatting and/or data validation rules and the newly added table row or column intersects or is adjacent to the range of the conditional formatting/data validation, the rules will automatically be applied to the newly added data when one the following actions are done: table insert rows table insert columns table auto expand table resize
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { var sheet1 = spread.getSheet(0); sheet1.getCell(0, 0).wordWrap(true); sheet1.setRowHeight(0, 100); sheet1.setColumnWidth(0, 200); sheet1.setValue(0, 0, "Conditional format/Data validation range in table will expand when table rows changed or columns changed."); var table = sheet1.tables.add("Table1", 2, 1, 9, 5, null); sheet1.getCell(0, 0).text('REMOVE TEXT AND SHRINK ROW HEIGHT TO NORMAL'); sheet1.getCell(2, 1).text("Name"); sheet1.getCell(2, 2).text("Value"); sheet1.getCell(2, 3).text("T/F"); sheet1.getCell(2, 4).text("AW"); sheet1.getCell(2, 5).text("T"); for (var i = 1; i <= 8; i++) { sheet1.getCell(2 + i, 1).value(i + 1); sheet1.getCell(2 + i, 2).value(i + 2); sheet1.getCell(2 + i, 3).value(i + 3); sheet1.getCell(2 + i, 4).value(i + 4); sheet1.getCell(2 + i, 5).value(i + 5); } var spreadNS = GC.Spread.Sheets; var cfs = sheet1.conditionalFormats; var style = new spreadNS.Style(); style.backColor = '#CCFFCC'; var cvRule = cfs.addCellValueRule( spreadNS.ConditionalFormatting.ComparisonOperators.greaterThan, 8, 0, style, [new GC.Spread.Sheets.Range(3, 5, 8, 1)]); spread.options.highlightInvalidData = true; sheet1.getRange(3, 1, 8, 5).validator(spreadNS.DataValidation.createNumberValidator(spreadNS.ConditionalFormatting.ComparisonOperators.notBetween, 4, 6, false)); } function _getElementById(id) { return document.getElementById(id); }
<!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" class="sample-spreadsheets"></div> </div> </div></body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width:100%; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }