How to Create a JavaScript Spreadsheet Report

A spreadsheet is a blank canvas with unlimited use cases for many business applications and environments. This is why SpreadJS JavaScript and data presentation components are particularly useful to businesses. From storing data to managing and processing the data sets, spreadsheets can perform mathematical, statistical, financial, and organizational conversions on data sets while presenting data visually.

Get the sample zip for this project

In this tutorial, we'll show you how to create a JavaScript report template using SpreadJS. We'll demonstrate a step-by-step process of binding data to our template to generate charts and sparklines.

Setting up the SpreadJS page

First, add the Spread.Sheets library references to the page:

<script src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.11.0.0.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.11.0.0.css">

In order to check that our script is running correctly as we are writing it, we can define a DIV element to contain the Spread instance.

We'll name this spreadsheet:

<div id="spreadSheet" style="width: 100%; height: 900px; border: 1px solid gray"></div>

Load report template and spreadsheet data

For this tutorial, the template was made using the Spread.Sheets Designer, as shown above. Two sheets were created:

• Dashboard: A sheet to contain the report dashboard.
• DataSheet: A sheet to contain the data used in the dashboard.

In the Dashboard sheet, I created outlines for the areas that will contain the charts. I created a table to hold data for one of those charts. In the data sheet, I created outlines to hold the tables (for the data that we'll be loading).

This template was saved as SSJSON, Spread.Sheets' JSON file type, and then exported to JS to be referenced in the page:

<script src="./ReportTemplate.js"></script>

We'll also need to load reference data in the page. This is stored in a JS file:

<script src="./data.js"></script>

Setup script for the spreadsheets component

With the data and template referenced, we'll load them into the Spread.Sheets component. We can create a function for when the window is loaded to initialize the Spread instance and bind the data:

var spread, activeSheet, dataSheet, source;
window.onload = function () {
    // Initialize variables and load template
    spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 2 });
    spread.fromJSON(ReportTemplate);
    activeSheet = spread.getActiveSheet();
    activeSheet.options.rowHeaderVisible = false;
    activeSheet.options.colHeaderVisible = false;
    dataSheet = spread.getSheet(1);

    // Turn painting off while adding the data to the sheets
    spread.suspendPaint();
    source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
    activeSheet.setDataSource(source);
    dataSheet.setDataSource(source);

    // Change the color of the gray area behind the sheets in Spread
    spread.options.grayAreaBackColor = "lightgray";
    spread.resumePaint();
}

Load Data into sheets

The first part of the data to load references the revenue chart on the Dashboard sheet. In the data file, this specific data is in the revenue_chart section, with the month, revenue, and unitsSold fields. First, define the table columns, and then bind the table to that specific section in the data file:

function LoadRevenueTable() {
    var tableColumns = [],
        names = ['month', 'revenue', 'unitsSold'],
        labels = ['Month', 'Revenue ($)', 'Units Sold'];

    names.forEach(function (name, index) {
        var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
        tableColumn.name(labels[index]);
        tableColumn.dataField(name);
        tableColumns.push(tableColumn);
    });

    // Find the table in the sheet and add the data to it
    var table = activeSheet.tables.findByName("RevenueTable");
    table.autoGenerateColumns(false);
    table.bindColumns(tableColumns);
    table.bindingPath('revenue_chart');

    activeSheet.getRange(4, 14, 12, 1).formatter("$###,0");
}

We'll fill in the data sheet with the data that we need for the charts and sparklines. Similar to the above code for the revenue table, we can set the column names and data binding paths for each table in the data sheet:

// Fill in the second sheet with the data from the data source
function FillDataSheet() {
    // Add dates to metrics table
    for (var m = 2; m <= 13; m++) {
        dataSheet.setValue(m, 0, new Date(2018, m-1, 28));
        dataSheet.getCell(m, 0).formatter("MM/dd/yy")
    }

    // Create columns for metric table
    var metricsTableColumns = [],
        names = ['revenue', 'profit', 'expenses', 'order_size', 'customers', 'market_share'],
        labels = ['Revenue', 'Profit', 'Expenses', 'Average Order Size', 'New Customers', 'Market Share'];
    names.forEach(function (name, index) {
        var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
        tableColumn.name(labels[index]);
        tableColumn.dataField(name);
        metricsTableColumns.push(tableColumn);
    });

    // Add Key Metrics Table
    var metricsTable = dataSheet.tables.add("KeyMetricsTable", 1, 1, 13, 6, GC.Spread.Sheets.Tables.TableThemes.medium1);
    metricsTable.autoGenerateColumns(false);
    metricsTable.bindColumns(metricsTableColumns);
    metricsTable.bindingPath('key_metrics_by_month.values');

    // Create columns for the top 5 tables
    var top5TableColumns = [],
        names = ['name', 'revenue'],
        labels = ['Name', 'Revenue'];
    names.forEach(function (name, index) {
        var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
        tableColumn.name(labels[index]);
        tableColumn.dataField(name);
        top5TableColumns.push(tableColumn);
    });

    // Add Top 5 Sales People Table
    var top5SalesTable = dataSheet.tables.add("Top5SalesTable", 1, 8, 6, 2, GC.Spread.Sheets.Tables.TableThemes.medium1);
    top5SalesTable.autoGenerateColumns(false);
    top5SalesTable.bindColumns(top5TableColumns);
    top5SalesTable.bindingPath('five_best.sales_men');
    top5SalesTable.rowFilter().sortColumn(top5SalesTable.range().col + 1, true);

    // Add Top 5 Regions Table
    var top5RegionsTable = dataSheet.tables.add("Top5RegionsTable", 1, 11, 6, 2, GC.Spread.Sheets.Tables.TableThemes.medium1);
    top5RegionsTable.autoGenerateColumns(false);
    top5RegionsTable.bindColumns(top5TableColumns);
    top5RegionsTable.bindingPath('five_best.regions');
    top5RegionsTable.rowFilter().sortColumn(top5RegionsTable.range().col + 1, true);

    // Add Top 5 Products Table
    var top5ProductsTable = dataSheet.tables.add("Top5ProductsTable", 1, 14, 6, 2, GC.Spread.Sheets.Tables.TableThemes.medium1);
    top5ProductsTable.autoGenerateColumns(false);
    top5ProductsTable.bindColumns(top5TableColumns);
    top5ProductsTable.bindingPath('five_best.products');
    top5ProductsTable.rowFilter().sortColumn(top5ProductsTable.range().col + 1, true);
}

In that same function, we will want to set the target values from the data source for each key metric into one of the tables. To do this, we can drill down in each level of the data hierarchy and set the data that way:

// Add Target Values for each metric
dataSheet.setBindingPath(14, 1, "key_metrics.revenue.target");
dataSheet.setBindingPath(14, 2, "key_metrics.profit.target");
dataSheet.setBindingPath(14, 3, "key_metrics.expenses.target");
dataSheet.setBindingPath(14, 4, "key_metrics.order_size.target");
dataSheet.setBindingPath(14, 5, "key_metrics.customers.target");
dataSheet.setBindingPath(14, 6, "key_metrics.market_share.target");

Create charts

Now that the data has been loaded into the Spread instance, we can create charts based on that data. Starting with the Revenue Chart, we will use Spread.Sheets' code for getting the rectangle element on the page. This will represent the cells at the top left and bottom right corners (where the chart will be placed), so we can calculate the chart's location and size to fit within the defined bounds:

function CreateRevenueChart() { 
    // Create Revenue Column Chart
    var startCellRect = activeSheet.getCellRect(2, 2);
    var endCellRect = activeSheet.getCellRect(17, 11);
}

Since there will be different types of charts in the sheet, it would be best to separate the actual chart creation and give it a function.

For this function, we can pass in the following parameters:

• sheet: the sheet that the chart is on
• chartName: the name of the chart
• chartType: the type of chart which determines the code path to take in this function
• startCellRect: the rectangle element of the start cell
• endCellRect: the rectangle element of the end cell
• xValues: the cell range containing the values for the x-axis in the chart
• yValues: the cell range containing the values for the y-axis in the chart
• seriesBackColor: the color of the specific series (for the revenue chart we will let Spread use the default colors)

In that CreateRevenueChart function, we'll call the function so we don't forget to add it later:

BaseCreateChart(activeSheet, 'Revenue', GC.Spread.Sheets.Charts.ChartType.columnClustered, startCellRect, endCellRect, 'Dashboard!N5:N16', 'Dashboard!O5:O16', null);

Then we can start filling out the base chart function:

function BaseCreateChart(sheet, chartName, chartType, startCellRect, endCellRect, xValues, yValues, seriesBackColor) {
    // Calculate the position and size of the chart
    var leftBorderOffset = 3, topBorderOffset = 2;
    var chart;
    var xPosition, yPosition, height, width;
    if (sheet.options.rowHeaderVisible && sheet.options.colHeaderVisible) {
        xPosition = startCellRect.x - sheet.getColumnWidth(0, GC.Spread.Sheets.SheetArea.rowHeader);
        yPosition = startCellRect.y - sheet.getRowHeight(0, GC.Spread.Sheets.SheetArea.colHeader);
    } else {
        xPosition = startCellRect.x;
        yPosition = startCellRect.y;
    }
    width = endCellRect.x - startCellRect.x - leftBorderOffset;
    height = endCellRect.y - startCellRect.y - topBorderOffset;
}

With the location and size calculated, we can add the chart to the sheet:

// Check if the chart is a clustered column chart
if (chartType == GC.Spread.Sheets.Charts.ChartType.columnClustered) {
    chart = sheet.charts.add(chartName,
        chartType,
        xPosition,
        yPosition,
        width,
        height,
        GC.Spread.Sheets.Charts.RowCol.rows);
}

In that same IF block, add the main series for the column chart. This chart is going to be a combo chart, so it will have multiple axes: one for the columns and one for the line, which will both use different units of measurement.

To separate these, we can specify the axis' axisGroup property individually:

// Add the main series to the column chart
chart.series().add({
    chartType: GC.Spread.Sheets.Charts.ChartType.columnClustered,
    axisGroup: GC.Spread.Sheets.Charts.AxisGroup.primary,
    name: "Dashboard!$O$4",
    xValues: xValues,
    yValues: yValues
});
// Add a line series to the column chart
chart.series().add({
    chartType: GC.Spread.Sheets.Charts.ChartType.lineMarkers,
    axisGroup: GC.Spread.Sheets.Charts.AxisGroup.secondary,
    name: "Dashboard!$P$4",
    xValues: xValues,
    yValues: "Dashboard!$P$5:$P$16"
});

We can also format those axes to ensure that the user understands the difference in units:

// Change the format of the axes
var axes = chart.axes();
axes.primaryValue.format = "$###,0";
axes.secondaryValue.format = "General";
chart.axes(axes);         

At the end of that BaseCreateChart function we can set the title for the chart:

// Set the title of the chart
if (chart != null) {
    var title = chart.title();
    title.text = chartName;
    chart.title(title);
}

Now we can work on the bar charts, which we can create a similar function that will call the BaseCreateChart function:

// Create Bar Charts
function CreateBarCharts() {
    // Create Top 5 Sales People Chart
    var startCellRect = activeSheet.getCellRect(18, 2);
    var endCellRect = activeSheet.getCellRect(33, 8);
    BaseCreateChart(activeSheet, 'Top 5 Sales People', GC.Spread.Sheets.Charts.ChartType.barClustered, startCellRect, endCellRect, 'DataSheet!I3:I7', 'DataSheet!J3:J7', null);

    // Create Top 5 Regions Chart
    startCellRect = activeSheet.getCellRect(18, 8);
    endCellRect = activeSheet.getCellRect(33, 13);
    BaseCreateChart(activeSheet, 'Top 5 Regions', GC.Spread.Sheets.Charts.ChartType.barClustered, startCellRect, endCellRect, 'DataSheet!L3:L7', 'DataSheet!M3:M7', 'DarkOrange');

    // Create Top 5 Products Chart
    startCellRect = activeSheet.getCellRect(18, 13);
    endCellRect = activeSheet.getCellRect(33, 17);
    BaseCreateChart(activeSheet, 'Top 5 Products', GC.Spread.Sheets.Charts.ChartType.barClustered, startCellRect, endCellRect, 'DataSheet!O3:O7', 'DataSheet!P3:P7', 'Green');
}

Going back to the BaseCreateChart function, we can create another IF block specifically for the bar charts. We'll want to add the chart and series (just like with the column chart), but now we'll also want to turn the legend off and decrease the font size.

In addition, set the color of each chart that we passed in when we called the function:

// ...
} else if (chartType == GC.Spread.Sheets.Charts.ChartType.barClustered) {
    // Check if the chart is a clustered bar chart
    chart = sheet.charts.add(chartName,
        chartType,
        xPosition,
        yPosition,
        width,
        height,
        GC.Spread.Sheets.Charts.RowCol.rows
    );

    // Add the main series to the bar chart
    chart.series().add({
        chartType: GC.Spread.Sheets.Charts.ChartType.barClustered,
        axisGroup: GC.Spread.Sheets.Charts.AxisGroup.primary,
        xValues: xValues,
        yValues: yValues,
        backColor: seriesBackColor
    });

    // Turn the legend off for the bar charts
    var legend = chart.legend();
    legend.visible = false;
    chart.legend(legend);

    // Make the font size smaller for the bar charts
    var chartArea = chart.chartArea();
    chartArea.fontSize = 10;
    chart.chartArea(chartArea);
}

Creating the key metrics sparklines section

As with the target data in the data sheet, we'll use cell-level data-binding to bind the names and values of the key metrics data to specific cells in the dashboard:

function CreateKeyMetricsSection() {
    // Add Metric Names to Key Metrics Section
    activeSheet.setBindingPath(36, 8, "key_metrics.revenue.name");
    activeSheet.setBindingPath(37, 8, "key_metrics.profit.name");
    activeSheet.setBindingPath(38, 8, "key_metrics.expenses.name");
    activeSheet.setBindingPath(39, 8, "key_metrics.order_size.name");
    activeSheet.setBindingPath(40, 8, "key_metrics.customers.name");
    activeSheet.setBindingPath(41, 8, "key_metrics.market_share.name");

    // Add Current Values to Key Metrics Section
    activeSheet.setBindingPath(36, 15, "key_metrics.revenue.value");
    activeSheet.setBindingPath(37, 15, "key_metrics.profit.value");
    activeSheet.setBindingPath(38, 15, "key_metrics.expenses.value");
    activeSheet.setBindingPath(39, 15, "key_metrics.order_size.value");
    activeSheet.setBindingPath(40, 15, "key_metrics.customers.value");
    activeSheet.setBindingPath(41, 15, "key_metrics.market_share.value");

    // Add Sparklines to Key Metrics Section
    CreateLineSparklines();
}

Now we can create sparklines using the data, starting with the left side of the key metrics section, which will have line sparklines in it. These line sparklines will use the date column and specific key metric column in the Key Metrics by Month table in the data sheet:

function CreateLineSparklines() {
    // Set Line Sparklines in Key Metrics Section
    var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
    setting.options.lineWeight = 3;

    var revenueSparkline = activeSheet.setSparkline(36, 3, 'DataSheet!B3:B14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
    var profitSparkline = activeSheet.setSparkline(37, 3, 'DataSheet!C3:C14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
    var expensesSparkline = activeSheet.setSparkline(38, 3, 'DataSheet!D3:D14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
    var averageOrderSizeSparkline = activeSheet.setSparkline(39, 3, 'DataSheet!E3:E14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
    var newCustomerSparkline = activeSheet.setSparkline(40, 3, 'DataSheet!F3:F14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
    var marketShareSparkline = activeSheet.setSparkline(41, 3, 'DataSheet!G3:G14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical,
        GC.Spread.Sheets.Sparklines.SparklineType.line, setting,
        'DataSheet!A3:A14', GC.Spread.Sheets.Sparklines.DataOrientation.vertical);
}

In that same function we can create variance sparklines on the right side of the key metrics section. For these specific sparklines, we can use a formula as defined here.

We can set that formula in each cell where we want a variance sparkline:

// Set Variance Sparklines in Key Metrics Section
activeSheet.setFormula(36, 10, '=VARISPARKLINE(IF(Dashboard!P37 > DataSheet!B15, Dashboard!P37/DataSheet!B15, -Dashboard!P37/DataSheet!B15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');
activeSheet.setFormula(37, 10, '=VARISPARKLINE(IF(Dashboard!P38 > DataSheet!C15, Dashboard!P38/DataSheet!C15, -Dashboard!P38/DataSheet!C15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');
activeSheet.setFormula(38, 10, '=VARISPARKLINE(IF(Dashboard!P39 > DataSheet!D15, Dashboard!P39/DataSheet!D15, -Dashboard!P39/DataSheet!D15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');
activeSheet.setFormula(39, 10, '=VARISPARKLINE(IF(Dashboard!P40 > DataSheet!E15, Dashboard!P40/DataSheet!E15, -Dashboard!P40/DataSheet!E15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');
activeSheet.setFormula(40, 10, '=VARISPARKLINE(IF(Dashboard!P41 > DataSheet!F15, Dashboard!P41/DataSheet!F15, -Dashboard!P41/DataSheet!F15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');
activeSheet.setFormula(41, 10, '=VARISPARKLINE(IF(Dashboard!P42 > DataSheet!G15, Dashboard!P42/DataSheet!G15, -Dashboard!P42/DataSheet!G15), 0, -2, 2, 0, 0.5, FALSE, "CornflowerBlue", "red", FALSE)');

To finish the key metrics section we can format the target values:

// Format Current values after setting sparklines
activeSheet.getCell(36, 15).formatter("$#,#");
activeSheet.getCell(37, 15).formatter("$#,#");
activeSheet.getCell(38, 15).formatter("$#,#");
activeSheet.getCell(41, 15).formatter("# %");

In this tutorial, we created a dashboard and a data sheet to hold data for that specific dashboard. On the dashboard, we created charts and sparklines to show the data in a visual, user-friendly display. This demo highlighs several reasons why SpreadJS is a useful and powerful JavaScript spreadsheet component.

Get the sample zip for this project

Download a free trial of GrapeCity's SpreadJS