Getting a perspective of your business' expenses and sales is critical when making important financial and investment planning choices. For example, viewing the expenses of your company can help you judge the ROI of expanding your business.
SpreadJS can be used to create a financial application to help you keep track of your business' expenses and sales. This post will demonstrate how to create a financial application in JavaScript.
Follow along and download the sample used in this post.
Creating a Template
To make the application even easier to design, we can use the SpreadJS Designer to set cell types, styles, and spacing. For the purposes of this tutorial, I have already created a template we can use:
This template has a few different sheets:
- CompanyDashboard: the main view for the application, summarizing the other sheets with some charts
- Banking: a view of all of the payments the company makes as well as the current balance for the account
- Expenses: a list of daily expenses that the company makes
- SalesTransactions: a list of all of the sales that the company makes to their customers
- CheckingTemplate: the range template for the checking account balance on the "Banking" sheet
- DashboardBalanceTemplate: the range template for the checking account balance on the "CompanyDashboard" sheet
Setup JavaScript Application
To start off, we will need to create an HTML file to load all of the required libraries and files, including the SJS references, JS data files, and template:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>Spread JS</title>
<link href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
<script type="text/javascript" src="./node_modules/@grapecity/spread-excelio/dist/gc.spread.excelio.min.js"></script>
<script type="text/javascript" src="./node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js"></script>
<script type="text/javascript" src="./FinancialTemplate.js"></script>
<script type="text/javascript" src="./data/checkingRegisterData.js"></script>
<script type="text/javascript" src="./data/expenses.js"></script>
<script type="text/javascript" src="./data/salesTransactions.js"></script>
<script type="text/javascript" src="./app.js"></script>
</head>
<body>
<div id="spreadSheet" style="width: 1300px; height: 700px; border: 1px solid gray"></div>
</body>
</html>
In this case, I'm using the SpreadJS files from NPM, which can be installed in your app with the following command run from within the application's folder:
npm install @grapecity/spread-excelio @grapecity/spread-sheets @grapecity/spread-sheets-charts
Next we can initialize the workbook with the template we just created, as well as setting some variables for the sheets:
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 3 });
spread.fromJSON(FinancialTemplate);
spread.options.allowDynamicArray = true;
spread.setActiveSheetIndex(0);
var bankingSheet = spread.getSheet(1);
var expensesSheet = spread.getSheet(2);
var salesTransactionsSheet = spread.getSheet(3);
}
We will also create some functions for loading each sheet to set data and formulas later on, but for now we will just call those functions:
spread.suspendPaint();
setBankingSheet(spread);
setExpensesSheet(expensesSheet);
setSalesTransactionsSheet(salesTransactionsSheet);
setDashboardSheet(spread);
sheetsInitialized[0] = true;
spread.resumePaint();
The "sheetsInitialized" array is used in some logic to determine if charts need to be set for the first time on a sheet. The charts in this workbook are going to be initialized using the pixel locations of specific cells, so we need to initialize each sheet individually and then the sheet's charts. The functions for setting these charts can be called in the ActiveSheetChanged event, using the "sheetsInitialized" array:
spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function(sender, args) {
switch (args.newSheet.name()) {
case "Banking":
(!sheetsInitialized[1]) && setBankingChartLocations(bankingSheet);
sheetsInitialized[1] = true;
break;
case "Expenses":
(!sheetsInitialized[2]) && setExpensesChartLocations(expensesSheet);
sheetsInitialized[2] = true;
break;
case "SalesTransactions":
(!sheetsInitialized[3]) && setSalesChartLocations(salesTransactionsSheet);
sheetsInitialized[3] = true;
break;
}
});
We'll also hide the range template sheets and set the scrollbar settings for the workbook:
spread.getSheet(4).visible(false);
spread.getSheet(5).visible(false);
spread.options.scrollbarMaxAlign = true;
Initializing a Sheet
Most of the sheets in this workbook have similar structures: 1 or more charts, a range template, and a data-bound table:
Before using the range template or setting the charts, we will need to set the data in the table. In this demo, I have put together some sample data that is contained within 3 different JS files, which we loaded earlier in this blog. From each file, we can create an array of table columns to initialize a table with, as well as formatting the data within the table:
function setBankingSheet(spread) {
var sheet = spread.getSheet(1);
// Freeze the top area of the sheet
sheet.frozenRowCount(16);
// Set Table data from checkingRegisterData.js
var tableColumns = [],
names = ['Date', 'Payee', 'Memo', 'Payment', 'Deposit', 'Balance', 'Type', 'Account', 'Added in Banking'],
labels = ['Date', 'Payee', 'Memo', 'Payment', 'Deposit', 'Balance', 'Type', 'Account', ' Added in Banking'];
var table = sheet.tables.add('checkingRegisterTable', 15, 0, checkingRegisterData.data.length, 8, GC.Spread.Sheets.Tables.TableThemes.medium21);
table.autoGenerateColumns(false);
names.forEach(function (name, index) {
var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn.name(labels[index]);
tableColumn.dataField(name);
tableColumns.push(tableColumn);
});
table.bind(tableColumns, 'data', checkingRegisterData);
// Convert strings to numbers
for (var r = 16; r < 16 + checkingRegisterData.data.length; r++) {
sheet.setValue(r, 0, new Date(sheet.getValue(r, 0)));
(sheet.getValue(r, 3) != "") && sheet.setValue(r, 3, parseFloat(sheet.getText(r, 3).replace(/,/g,'')));
(sheet.getValue(r, 4) != "") && sheet.setValue(r, 4, parseFloat(sheet.getText(r, 4).replace(/,/g,'')));
(sheet.getValue(r, 5) != "") && sheet.setValue(r, 5, parseFloat(sheet.getText(r, 5).replace(/,/g,'')));
}
// Format currency
sheet.getRange(16, 3, checkingRegisterData.data.length, 3).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
}
Sorting Data for Charts
To more easily create the charts, we can use formulas to sort the data and use only values without formatting in the charts:
// Create sorted combined data for line chart
sheet.setText(0, 9, "Date");
sheet.setText(0, 10, "Date String");
sheet.setText(0, 11, "Total");
// Sort the dates and get all unique values by millisecond
sheet.setFormula(1, 9, "SORT(UNIQUE(checkingRegisterTable[[#Data],[Date]]))");
sheet.setFormula(1, 11, "SUMIF(checkingRegisterTable[[#Data],[Date]],$J2,checkingRegisterTable[[#Data],[Balance]]");
startRange = new GC.Spread.Sheets.Range(1, 11, 1, 1);
wholeRange = new GC.Spread.Sheets.Range(1, 11, 25, 1);
sheet.fillAuto(startRange, wholeRange, {
fillType: GC.Spread.Sheets.Fill.FillType.auto,
series: GC.Spread.Sheets.Fill.FillSeries.column,
fillDirection:GC.Spread.Sheets.Fill.FillDirection.down
});
// Create a separate column for formatting those unique sorted millisecond values as strings
for (var r = 1; r < 26; r++) {
sheet.setValue(r, 10, formatDateString(sheet.getValue(r, 9)));
}
sheet.setText(0, 13, "Type");
sheet.setText(0, 14, "Payment");
sheet.setFormula(1, 13, "SORT(UNIQUE(checkingRegisterTable[[#Data],[Type]]))");
sheet.setFormula(1, 14, "SUMIF(checkingRegisterTable[[#Data],[Type]],$N2,checkingRegisterTable[[#Data],[Payment]])");
var startRange = new GC.Spread.Sheets.Range(1, 14, 1, 1);
var wholeRange = new GC.Spread.Sheets.Range(1, 14, 10, 1);
sheet.fillAuto(startRange, wholeRange, {
fillType: GC.Spread.Sheets.Fill.FillType.auto,
series: GC.Spread.Sheets.Fill.FillSeries.column,
fillDirection:GC.Spread.Sheets.Fill.FillDirection.down
});
Create Range Template
With the template sheet for ranges defined in the CheckingTemplate sheet, we can create the range template celltype and set it in the sheet:
// Create range template
var templateSheet = spread.getSheetFromName("CheckingTemplate");
var cellType = new GC.Spread.Sheets.CellTypes.RangeTemplate(templateSheet);
sheet.setCellType(0, 0, cellType, GC.Spread.Sheets.SheetArea.viewport);
// Bind data for the range template
var balanceInfo = {
CurrentBalance: sheet.getValue(16, 5)
};
// The value for the cell is used as the data source for the range template
sheet.setValue(0, 0, balanceInfo);
The range template will use the value of the cell that uses the celltype, which in this case will be an object that only has one property: CurrentBalance. The template sheet just has a data source template defined in it with the CurrentBalance property set in one of the cells.
Create Charts
Once we have that sorted data, we can create the charts from that data. These charts are just initialized at the location 0, 0 in the sheet with a default size of 100 px by 100 px. The activeSheetChanged event we bound earlier is what will be used along with custom functions for changing the size and position of each chart after the sheet is initially loaded:
// Create charts
var lineChart = sheet.charts.add('line', GC.Spread.Sheets.Charts.ChartType.line, 0, 0, 100, 100, "K1:L26");
var lineTitle = lineChart.title();
lineTitle.text = "Balance Over Time";
lineChart.title(lineTitle);
var lineLegend = lineChart.legend();
lineLegend.visible = false;
lineChart.legend(lineLegend);
lineChart.ignoreHidden(false);
lineChart.useAnimation(true);
var pieChart = sheet.charts.add('pie', GC.Spread.Sheets.Charts.ChartType.pie, 0, 100, 100, 100, "N1:O11");
var pieTitle = pieChart.title();
pieTitle.text = "Payments by Type";
pieChart.title(pieTitle);
var pieLegend = pieChart.legend();
pieLegend.position = GC.Spread.Sheets.Charts.LegendPosition.right;
pieChart.legend(pieLegend);
pieChart.ignoreHidden(false);
pieChart.useAnimation(true);
Once the sheet is navigated to and set as the active sheet, the ActiveSheetChanged event calls the function to set the size and position manually:
function setBankingChartLocations(sheet) {
var lineChart = sheet.charts.all()[0];
var lineStartCell = sheet.getCellRect(5, 0);
var lineEndCell = sheet.getCellRect(15, 5);
lineChart.x(lineStartCell.x);
lineChart.y(lineStartCell.y);
lineChart.width(lineEndCell.x - lineStartCell.x);
lineChart.height(lineEndCell.y - lineStartCell.y);
var pieChart = sheet.charts.all()[1];
var pieStartCell = sheet.getCellRect(0, 5);
var pieEndCell = sheet.getCellRect(15, 8);
pieChart.x(pieStartCell.x);
pieChart.y(pieStartCell.y);
pieChart.width(pieEndCell.x - pieStartCell.x);
pieChart.height(pieEndCell.y - pieStartCell.y);
}
The other sheets follow the same sort of logic for setting a table, range template, and charts. 
That is all that is needed to create your own financial application for your business but the possibilities are endless with what can be done with SpreadJS. Please leave us your thoughts in the comments below, and happy coding!
