TableSheet custom views support conditional formatting, data validation, and column styles by specifying the conditionalFormats, validator and style of the column info when it is created.
The "Product Conditional Format" sheet demonstrates a TableSheet with formula-based conditional formatting and data validation on the Unit Price column.
The "Sparkline Rule" sheet showcases the new Sparkline Conditional Format Rule feature in a TableSheet. Two sparkline rule types are demonstrated using the conditionalFormats column property with ruleType: "sparklineRule":
Bullet Sparkline Rule: Visualizes each region's actual revenue against company-wide benchmarks (target, good, bad thresholds), making it easy to assess regional performance at a glance.
Lollipop Variance Sparkline Rule: Displays the percentage variance between target and actual revenue for each region, with green indicating above-target and orange indicating below-target performance.
This is the sample code for conditional format in TableSheet view.
This is the sample code for sparkline conditional format rules in TableSheet view.
/*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;
initProductSheet(spread);
initSparklineRuleSheet(spread);
spread.setActiveSheetIndex(0);
spread.resumePaint();
}
function initProductSheet(spread) {
//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, "Conditional Format", 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);
});
}
function initSparklineRuleSheet(spread) {
var dataManager = spread.dataManager();
// Regional sales data with a mix of above/below target results
var salesTable = dataManager.addTable("salesTable", {
data: [
{ region: "North America", target: 850, actual: 920, forecast: 880, actual1: 920, actual2: 920 },
{ region: "Europe", target: 720, actual: 680, forecast: 700, actual1: 680, actual2: 680 },
{ region: "Asia Pacific", target: 650, actual: 710, forecast: 670, actual1: 710, actual2: 710 },
{ region: "Latin America", target: 380, actual: 350, forecast: 360, actual1: 350, actual2: 350 },
{ region: "Middle East", target: 280, actual: 310, forecast: 290, actual1: 310, actual2: 310 },
{ region: "Africa", target: 180, actual: 165, forecast: 170, actual1: 165, actual2: 165 },
{ region: "Oceania", target: 150, actual: 160, forecast: 155, actual1: 160, actual2: 160 },
{ region: "South Asia", target: 220, actual: 195, forecast: 210, actual1: 195, actual2: 195 },
]
});
// Create a table sheet for sparkline rules
var sheet = spread.addSheetTab(1, "Sparkline Rule", GC.Spread.Sheets.SheetType.tableSheet);
sheet.options.allowAddNew = false;
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.setDefaultRowHeight(40);
var bulletRule = {
ruleType: "sparklineRule",
sparklineType: "BULLETSPARKLINE",
sparklineOptions: {
measure: '@', // current cell value = actual revenue
target: 500, // company-wide benchmark target ($500K)
maxi: 1000, // maximum scale ($1000K)
good: 700, // "exceeding expectations" threshold
bad: 250, // "needs improvement" threshold
forecast: 600, // company average forecast
colorScheme: '#2F5496'
},
showSparklineOnly: true
};
var lollipopRule = {
ruleType: "sparklineRule",
sparklineType: "LOLLIPOPVARISPARKLINE",
sparklineOptions: {
plannedValue: '[target]',
actualValue: '$CF_RANGE$',
index: '@', // 0-based index of current cell within rule range
reference: 0, // reference line at 0% variance
mini: -0.3, // minimum variance scale (-30%)
maxi: 0.3, // maximum variance scale (+30%)
tickUnit: 0.1, // tick marks every 10%
legend: true, // show legend labels
colorPositive: '#70AD47', // green for above target
colorNegative: '#ED7D31', // orange for below target
lollipopHeaderColor: '#2F5496' // dot color
},
showSparklineOnly: true
};
var numericStyle = { formatter: "$#,##0" };
var salesView = salesTable.addView("salesView", [
{ value: "region", caption: "Region", width: 130 },
{ value: "target", caption: "Target ($K)", width: 100, style: numericStyle },
{ value: "actual", caption: "Actual ($K)", width: 100, style: numericStyle },
{ value: "forecast", caption: "Forecast ($K)", width: 100, style: numericStyle },
{ value: "actual1", caption: "Performance (Bullet)", width: 220, conditionalFormats: [bulletRule] },
{ value: "actual2", caption: "Variance (Lollipop)", width: 220, conditionalFormats: [lollipopRule] }
]);
salesView.fetch().then(function () {
sheet.setDataView(salesView);
});
}
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;
}
