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.

<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; }