Expense Budget

The following sample shows how you can use SpreadJS spreadsheet to create budgeting reports for your JavaScript applications using custom icon sets and conditional formatting.

The example highlights the use of tables, formulas, and conditional formatting to better help visual data using the IconSetRule and DataBarRule.
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; }