A common requirement for most financial related applications is the need to create reports. This can be anything from Profit/Loss (P&L) Statements, Income Statements, Budgeting, Forecasting and Projections, and many more. The common factor for all these is data, and the need for spreadsheets to crunch the numbers.
In this article, we'll show you how to use SpreadJS, our enterprise JavaScript spreadsheet, to create a typical Aged Receivables Report that focuses specifically on how the end-user can easily filter that data.
This report will show all outstanding payments along with the customer's information. We'll then filter out the data and show the age of the past due payments along with that customer's details. The user of this type of reports can be a manager, an accountant, or director.
There are four different ways we can create this report using SpreadJS's built-in and custom features. These options include:
Note: to create these reports, we have used static JSON data and use the same data for different sections.
We've divided each part into separate article, so we'll have a series of four articles to demonstrate all options.
Here, we'll discuss creating the aging report with Slicers.
We will be creating this report with ReactJS. Learn more about how to use SpreadJS with ReactJS
For this application, we've a created a custom component in our react application using Spread.Sheets to demonstrate the slicers in SpreadJS.
Slicers are used to filter the data quickly in a very visual intuitive way. They work differently from a traditional filter where you need to select from the dropdown. With slicers you see the result of the filter as soon as you click on the slicer item (a condition that you want to filter with). You can further customize the slicers by setting the style and data.
Here we have created a custom react component with Spread.Sheets where we will be using Table Slicers to generate the report.
constructor(props) {
super(props);
this.COLS = 12;
this.hostStyle = props.hostStyle;
this.customerRowStyle = new GC.Spread.Sheets.Style();
this.customerRowStyle.backColor = "lightblue";
this.customerRowStyle.font = "bold normal 15px normal";
}
state = {
records: this.props.data,
organizaionName: this.props.organizationName,
title: this.props.title,
dateGeneratedOn: new Date(),
asOfDate: new Date(),
dateFormat: "dd/MM/yyyy",
spread: null
};
initSpread = workBook => {
workBook.suspendPaint();
this.initDataTableSheet(workBook.getSheetFromName("Aging Report
Table"));
this.initLookupTable(workBook.getSheetFromName("LookupTable"));
this.initSlicerSheet(workBook.getSheetFromName("Report with
Slicer"));
workBook.resumePaint();
this.setState({
spread: workBook
});
this.props.onWorkBookInitialized(workBook);
};
initDataTableSheet = sheet => {
sheet.setDataSource(this.state.records);
sheet.setColumnWidth(0, 120);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(3, 120);
sheet.setColumnWidth(4, 120);
};
The output appears as follows:
initLookupTable = sheet => {
let lookUpRowIndex = -1,
lookUpColIndex = 0;
let row = lookUpRowIndex;
sheet.getCell(++row, lookUpColIndex).text("Lookup");
sheet.getCell(row, lookUpColIndex + 1).text("Category");
sheet.getCell(++row, lookUpColIndex).formula("-90");
sheet.getCell(row, lookUpColIndex + 1).text("Current");
sheet.getCell(++row, lookUpColIndex).text("1");
sheet.getCell(row, lookUpColIndex + 1).text("[1-30]");
sheet.getCell(++row, lookUpColIndex).text("31");
sheet.getCell(row, lookUpColIndex + 1).text("[31-60]");
sheet.getCell(++row, lookUpColIndex).text("61");
sheet.getCell(row, lookUpColIndex + 1).text("[61-90]");
sheet.getCell(++row, lookUpColIndex).text("91");
sheet.getCell(row, lookUpColIndex + 1).text("91+");
};
The sheet looks as follows:
initSlicerSheet = sheet => {
this.addReportHeader(sheet);
sheet.setRowCount(this.state.records.length + 20);
sheet.setColumnCount(20);
let tableRowIndex = 4;
let table = sheet.tables.addFromDataSource(
"records",
tableRowIndex,
0,
this.state.records
);
sheet.setColumnWidth(0, 180);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(3, 120);
sheet.setColumnWidth(4, 150);
sheet.setColumnWidth(5, 80);
sheet.setColumnWidth(6, 120);
sheet.tables.resize(
table.name(),
new GC.Spread.Sheets.Range(tableRowIndex, 0,
this.state.records.length, 7)
);
//#region Add Tables
let spreadNS = GC.Spread.Sheets;
let customerCol = new spreadNS.Tables.TableColumn();
customerCol.name("Customer");
customerCol.dataField("Customer");
let invoiceIDCol = new spreadNS.Tables.TableColumn();
invoiceIDCol.name("Invoice #");
invoiceIDCol.dataField("Invoice Number");
let invoiceDtCol = new spreadNS.Tables.TableColumn();
invoiceDtCol.name("Invoice Date");
invoiceDtCol.dataField("Invoice Date");
let balanceCol = new spreadNS.Tables.TableColumn();
balanceCol.name("Balance");
balanceCol.dataField("Amount");
let dueDateCol = new spreadNS.Tables.TableColumn();
dueDateCol.name("Due Date");
dueDateCol.dataField("Due Date");
let daysLateCol = new spreadNS.Tables.TableColumn();
daysLateCol.name("DaysLate");
let cateogoryCol = new spreadNS.Tables.TableColumn();
cateogoryCol.name("Category");
table.bindColumns([
customerCol,
invoiceIDCol,
invoiceDtCol,
dueDateCol,
balanceCol,
daysLateCol,
cateogoryCol
]);
table.setColumnDataFormula(5, "=Today() - [Due Date] ");
table.highlightLastColumn(false);
table.setColumnDataFormula(
6,
`=VLOOKUP([@DaysLate],LookupTable!$A$1:$B$6,2)`
);
let categorySlicer = sheet.slicers.add(
"categorySlicer",
table.name(),
"Category"
);
categorySlicer.position(new GC.Spread.Sheets.Point(850, 100));
categorySlicer.style(GC.Spread.Sheets.Slicers.SlicerStyles.dark1());
let customerSlicer = sheet.slicers.add(
"customerSlicer",
table.name(),
"Customer"
);
customerSlicer.position(new GC.Spread.Sheets.Point(850, 400));
customerSlicer.style(GC.Spread.Sheets.Slicers.SlicerStyles.dark1());
sheet.setColumnVisible(6, false);
sheet.getRange(-1, 5, -1, 1).formatter(new
NegativeValueFormatter());
sheet.getRange(-1, 4, -1, 1).formatter("$#,#");
sheet.getRange(-1, 2, -1, 6,
GC.Spread.Sheets.SheetArea.viewport).hAlign(GC.Spread.Sheets.Horizontal
Align.right);
sheet.getRange(-1, 0, -1, 1,
GC.Spread.Sheets.SheetArea.viewport).hAlign(GC.Spread.Sheets.Horizontal
Align.left);
sheet.getRange(1, 2, 1, 2,
GC.Spread.Sheets.SheetArea.viewport).vAlign(GC.Spread.Sheets.Horizontal
Align.center);
sheet.getRange(4, 1, 1, 5,
GC.Spread.Sheets.SheetArea.viewport).hAlign(GC.Spread.Sheets.Horizontal
Align.center);
var cfs = sheet.conditionalFormats;
var style = new GC.Spread.Sheets.Style();
style.foreColor = "green";
cfs.addCellValueRule(
GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThan,
30,
0,
style,
[new GC.Spread.Sheets.Range(-1, 5, -1, 1)]
);
style = new GC.Spread.Sheets.Style();
style.foreColor = "red";
cfs.addCellValueRule(GC.Spread.Sheets.ConditionalFormatting.ComparisonO
perators.greaterThan,
90,
0,
style,
[new GC.Spread.Sheets.Range(-1, 5, -1, 1)]
);
var lessThanstyle = new GC.Spread.Sheets.Style();
lessThanstyle.foreColor = "orange";
cfs.addCellValueRule(
GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between,
31,
90,
lessThanstyle,
[new GC.Spread.Sheets.Range(-1, 5, -1, 1)]
);
cfs.addRule(dataBarRule);
};
let categorySlicer = sheet.slicers.add(
"categorySlicer",
table.name(),
"Category"
);
categorySlicer.position(new GC.Spread.Sheets.Point(850, 100));
categorySlicer.style(GC.Spread.Sheets.Slicers.SlicerStyles.dark1());
let customerSlicer = sheet.slicers.add(
"customerSlicer",
table.name(),
"Customer"
);
customerSlicer.position(new GC.Spread.Sheets.Point(850, 400));
customerSlicer.style(GC.Spread.Sheets.Slicers.SlicerStyles.dark1());
To set the style and formatting for the overall slicer worksheet:
addReportHeader = sheet => {
sheet.setColumnCount(this.COLS);
sheet.setRowCount(10000);
sheet
.getCell(1, 0)
.text(this.state.title)
.font("bold normal 20px normal")
.vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.addSpan(0, 0, 1, 2);
sheet
.getCell(0, 0)
.text(this.state.organizaionName)
.font("bold normal 20px normal")
.vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.addSpan(1, 0, 1, 2);
sheet
.getCell(1, 2)
.text("As of:")
.font("bold normal 15px normal");
sheet
.getCell(1, 3)
.text(this.state.dateGeneratedOn)
.formatter(this.state.dateFormat)
.font("bold normal 15px normal");
sheet.setRowHeight(0, 40);
sheet.setRowHeight(1, 28);
sheet.setColumnWidth(0, 150);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(3, 120);
sheet.setColumnWidth(4, 80);
sheet.setColumnWidth(5, 80);
sheet.setColumnWidth(6, 80);
sheet.setColumnWidth(7, 80);
sheet.setColumnWidth(8, 80);
sheet.setColumnWidth(9, 80);
sheet.setColumnWidth(10, 80);
sheet.setColumnWidth(11, 80);
let defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.foreColor = "Black";
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
sheet.setDefaultStyle(defaultStyle);
};
var dataBarRule = new
GC.Spread.Sheets.ConditionalFormatting.DataBarRule(GC.Spread.Sheets.Con
ditionalFormatting.ScaleValueType.Number, 500,
GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.Number, 12000,
"#6891c8", [new GC.Spread.Sheets.Range(-1,4,-1,1)]);
dataBarRule.showBorder(true);
dataBarRule.borderColor("#6891c8");
dataBarRule.dataBarDirection(GC.Spread.Sheets.ConditionalFormatting.Bar
Direction.LeftToRight);
dataBarRule.axisPosition(GC.Spread.Sheets.ConditionalFormatting.DataBar
AxisPosition.Automatic);
cfs.addRule(dataBarRule);
render() {
return (
<div>
<SpreadSheets
hostStyle={this.hostStyle}
workbookInitialized={this.initSpread.bind(this)}
>
<Worksheet name="Report with Slicer" />
<Worksheet name="LookupTable" />
<Worksheet name="Aging Report Table" />
</SpreadSheets>
</div>
);
}
}
export default SlicerComponent;
The outcome of the report renders as below:
As demonstrated in the image, the Spread.Sheets shows two slicers: one for 'category' and another for 'customer.' The user can click on the slicer category or customer, and the table displays the filtered records from the financial data from the data sheet.
The 'DaysLate' column shows data in different colors: red, green, and orange. The red color is for invoices older than 90 days, the orange color is for invoices that are less than 90 days old and more than 30, whereas the green color is for invoices less than 30 days old.
There are also Print and Save buttons in the toolbar at top. You can use these buttons to print the report and export the report to Excel format.
Please continue reading about the other options available with Spread.Sheets while working with financial data.
Happy coding, be sure to leave any thoughts or comments below.