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]✘", hAlign: GC.Spread.Sheets.HorizontalAlign.center }}
]);
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/@mescius/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/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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;
}
