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.

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); var spreadNS = GC.Spread.Sheets; var table = sheet1.tables.add("Table1", 2, 1, 9, 5, spreadNS.Tables.TableThemes["medium4"]); 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); } sheet1.setColumnWidth(1, 100); sheet1.setColumnWidth(2, 100); sheet1.setColumnWidth(3, 100); sheet1.setColumnWidth(4, 100); sheet1.setColumnWidth(5, 100); var cfs = sheet1.conditionalFormats; var style = new spreadNS.Style(); style.backColor = '#F4F8EB'; style.foreColor = '#82bc00'; //apply style for numbers greater than 8 var cvRule = cfs.addCellValueRule( spreadNS.ConditionalFormatting.ComparisonOperators.greaterThan, 8, 0, style, [new GC.Spread.Sheets.Range(3, 5, 8, 1)]); spread.options.highlightInvalidData = true; //Numbers between 4,6 are invalid 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/@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="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"> <div class="option-row"> <label>Table's columns B:F have data validation rules. Column F also has conditional formatting rules. </label> <label>Click in cell F11 (value ‘13’) and press the Tab key. A new row will be added. </label> <label><b>You can follow the rules in the above rows to see this in action. </b></label> <label>For example, in B12, type ‘5’. This is not a valid value so it will be highlighted. </label> <label>Type ‘10’ in F12 to highlight is using the conditional formatting rules.</label> </div> </div> </div> </div></body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } label { display: block; margin-bottom: 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }