Conditional Formatting and Data Validation

TableSheet supports data-bound conditional formatting, data validation, and column styles to make it easy to quickly highlight important data.

TableSheet custom views support conditional formatting, data validation, and column styles by specifying the conditionalFormat, validator and style of the column info when it is created. This is the sample code.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.options.highlightInvalidData = true; //init a data manager var baseApiUrl = getBaseApiUrl(); var dataManager = spread.dataManager(); //add product table var productTable = dataManager.addTable("productTable", { remote: { read: { url: baseApiUrl + "/Product" } } }); //init a table sheet var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; //hide new row sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); //bind a view to the table sheet var numericStyle = {}; numericStyle.formatter = "$ 0.00"; var formulaRule = { ruleType: "formulaRule", formula: "@>=50", style: { font:"bold 12pt Calibri", backColor: "#F7D3BA", foreColor :"#F09478" } }; var positiveNumberValidator = { type: "formula", formula: '@<50', inputTitle: 'Data validation:', inputMessage: 'Enter a number smaller than 50.', highlightStyle: { type: 'icon', color: "#F09478", position: 'outsideRight', } }; var myView = productTable.addView("myView", [ { value: "Id", caption: "ID", width: 46}, { value: "ProductName", caption: "Name", width: 250 }, { value: "QuantityPerUnit", caption: "Quantity Per Unit", width: 140}, { value: "UnitPrice", caption: "Unit Price", width: 140, conditionalFormats: [formulaRule], validator: positiveNumberValidator, style: numericStyle }, { value: "UnitsInStock", caption: "Units In Stock", width: 140}, { value: "UnitsOnOrder", caption: "Units On Order", width: 140}, { value: "Discontinued", width: 120, style:{formatter:"[green]✔;;[red]✘"}} ]); myView.fetch().then(function () { sheet.setDataView(myView); }); spread.resumePaint(); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!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"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.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 id="optionContainer" class="optionContainer"> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }