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:
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:
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:
We'll also need to load reference data in the page. This is stored in a JS file:
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:
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:
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:
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:
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:
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:
Then we can start filling out the base chart function:
With the location and size calculated, we can add the chart to the sheet:
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:
We can also format those axes to ensure that the user understands the difference in units:
At the end of that BaseCreateChart function we can set the title for the chart:
Now we can work on the bar charts, which we can create a similar function that will call the BaseCreateChart function:
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:
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:
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:
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:
To finish the key metrics section we can format the target values:
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
