<p>The example highlights the use of tables, formulas, and conditional formatting to better help visual data using the <strong>IconSetRule</strong> and <strong>DataBarRule</strong>.</p>
var spreadNS = GC.Spread.Sheets;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
initSpread(spread);
};
function initSpread(spread) {
var sheet = spread.getSheet(0);
sheet.suspendPaint();
var cfs = sheet.conditionalFormats ;
var table = sheet.tables.add("tblOperatingExpenses2", 4, 1, 11, 6, new spreadNS.Tables.TableTheme());
table.showFooter(true);
var dataTable = [];
dataTable[0] = new Array("STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)");
dataTable[1] = new Array(0, "Advertising", 600, 545, 0, 0);
dataTable[2] = new Array(0, "Debts", 125, 150, 0, 0);
dataTable[3] = new Array(0, "Benefits", 100, 100, 0, 0);
dataTable[4] = new Array(0, "Supplies", 100, 90, 0, 0);
dataTable[5] = new Array(0, "Postage", 150, 145, 0, 0);
dataTable[6] = new Array(0, "Rent or mortgage", 1000, 1000, 0, 0);
dataTable[7] = new Array(0, "Sales expenses", 500, 630, 0, 0);
dataTable[8] = new Array(0, "Taxes", 350, 375, 0, 0);
dataTable[9] = new Array(0, "Utilities", 400, 370, 0, 0);
dataTable[10] = new Array(0, "Other", 500, 435, 0, 0);
dataTable[11] = new Array(0, "Total Expenses", 0, 0, 0, 0);
sheet.addSpan(0, 1, 1, 4);
sheet.addSpan(0, 5, 1, 2);
sheet.addSpan(1, 1, 1, 6);
sheet.addSpan(2, 1, 1, 6);
sheet.addSpan(3, 1, 1, 2);
sheet.addSpan(16, 1, 1, 6);
sheet.addSpan(17, 1, 1, 6);
sheet.getDefaultStyle().vAlign = spreadNS.VerticalAlign.center;
sheet.getDefaultStyle().font = "lighter 10pt Calibri";
sheet.getDefaultStyle().foreColor = "rgb(68, 84, 106)";
sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
sheet.getCell(0, 1).value("Expense Budget").font("lighter 28pt Calibri");
sheet.getCell(0, 5).value("CONTOSO, 2013").font("11pt Calibri").foreColor("rgb(64, 64, 64)")
.hAlign(spreadNS.HorizontalAlign.right).vAlign(spreadNS.VerticalAlign.bottom);
sheet.getCell(3, 1).value("OPERATING BUDGET").font("bold 13pt Calibri ").foreColor("rgb(64, 64, 64)");
sheet.getRange(4, 1, 1, 6).font("bold 10pt Calibri")
.borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(15, 1, 1, 6).font("bold 10pt Calibri")
.borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(3, 1, 1, 2)
.borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
.borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
.borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(4, 1, 12, 1).borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin)).hAlign(spreadNS.HorizontalAlign.center);
sheet.getRange(4, 6, 12, 1).borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(5, 1, 10, 6).borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.dashed));;
for (var row = 4; row < 16; row++) {
for (var col = 1; col < 7; col++) {
sheet.setValue(row, col, dataTable[row - 4][col - 1]);
}
}
var rowHeights = new Array(74, 4, 20);
for (var row = 0; row < 3; row++) {
sheet.setRowHeight(row, rowHeights[row]);
}
for (var row = 3; row < 17; row++) {
sheet.setRowHeight(row, 24);
}
sheet.setRowHeight(17, 4);
var colWidths = new Array(18, 83, 111, 84, 85, 126, 129);
for (var col = 0; col < 7; col++) {
sheet.setColumnWidth(col, colWidths[col]);
}
sheet.getRange(1, 1, 1, 6)
.borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
.borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(17, 1, 1, 6)
.borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
.borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
sheet.getRange(3, 3, 13, 3).formatter('"$"#,##0.00');
sheet.getRange(3, 6, 12, 1).formatter('0%');
sheet.getCell(15, 6).formatter('0.0%');
var iconSetRule1 = new spreadNS.ConditionalFormatting.IconSetRule();
iconSetRule1.ranges([new spreadNS.Range(15, 1, 1, 1)]);
iconSetRule1.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsCircled)
.showIconOnly(true);
var icons = iconSetRule1.iconCriteria();
icons[0] = new spreadNS.ConditionalFormatting.IconCriterion(false, spreadNS.ConditionalFormatting.IconValueType.number, -1);
icons[1] = new spreadNS.ConditionalFormatting.IconCriterion(true, spreadNS.ConditionalFormatting.IconValueType.number, 1);
cfs.addRule(iconSetRule1);
var style = new spreadNS.Style();
style.foreColor = "red";
cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.lessThan, 0, 0, style, [new spreadNS.Range(15, 5, 1, 2)]);
var dataBarRule1 = new spreadNS.ConditionalFormatting.DataBarRule();
dataBarRule1.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);
dataBarRule1.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);
dataBarRule1.ranges([new spreadNS.Range(5, 4, 10, 1)]);
dataBarRule1.color("orange").showBorder(true).borderColor("orange").dataBarDirection(spreadNS.ConditionalFormatting.BarDirection.rightToLeft);
cfs.addRule(dataBarRule1);
var dataBarRule2 = new spreadNS.ConditionalFormatting.DataBarRule();
dataBarRule2.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);
dataBarRule2.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);
dataBarRule2.ranges([new spreadNS.Range(5, 3, 10, 1)]);
dataBarRule2.color("rgb(0,138,239)").showBorder(true).borderColor("rgb(0,138,239)");
cfs.addRule(dataBarRule2);
var dataBarRule3 = new spreadNS.ConditionalFormatting.DataBarRule();
dataBarRule3.minType(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue);
dataBarRule3.maxType(spreadNS.ConditionalFormatting.ScaleValueType.highestValue);
dataBarRule3.ranges([new spreadNS.Range(5, 6, 10, 1)]);
dataBarRule3.color("rgb(91,155,213)").showBorder(true).borderColor("rgb(91,155,213)")
.negativeFillColor("rgb(237,125,49)").useNegativeBorderColor(true).negativeBorderColor("rgb(237,125,49)")
.axisPosition(spreadNS.ConditionalFormatting.DataBarAxisPosition.cellMidPoint);
cfs.addRule(dataBarRule3);
var iconSetRule2 = new spreadNS.ConditionalFormatting.IconSetRule();
iconSetRule2.ranges([new spreadNS.Range(5, 1, 10, 1)]);
iconSetRule2.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsUncircled)
.reverseIconOrder(true)
.showIconOnly(true);
cfs.addRule(iconSetRule2);
table.setColumnFormula(0, "=tblOperatingExpenses2[[#Totals],[DIFFERENCE (%)]]");
table.setColumnFormula(2, "=SUBTOTAL(109,[BUDGET])");
table.setColumnFormula(3, "=SUBTOTAL(109,[ACTUAL])");
table.setColumnFormula(4, "=SUBTOTAL(109,[DIFFERENCE ($)])");
table.setColumnFormula(5, '=IFERROR(SUM(tblOperatingExpenses2[[#Totals],[DIFFERENCE ($)]]/tblOperatingExpenses2[[#Totals],[BUDGET]]),"")');
table.setColumnDataFormula(0, '=IFERROR([@ACTUAL]/[@BUDGET],"")');
table.setColumnDataFormula(4, '=[@BUDGET]-[@ACTUAL]');
table.setColumnDataFormula(5, '=IFERROR([@[DIFFERENCE ($)]]/[@BUDGET],"")');
sheet.resumePaint();
}
<!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" style="width:100%;height:100%"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}