Business Dashboard

The following sample shows how you can use the SpreadJS spreadsheet to create interactive business dashboards for your JavaScript applications using the built-in charts, sparklines, and custom formatting. The data can be found on the "DataSheet" sheet.

The example is loading a predefined template using fromJSON. Note the multiple sheets that contain interactive data.
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity/spread-sheets'; import '@grapecity/spread-sheets-charts'; import { SpreadSheets, Worksheet } from '@grapecity/spread-sheets-react'; import './styles.css'; const Component = React.Component; function _getElementById(id) { return document.getElementById(id); } class App extends Component { constructor(props) { super(props); } render() { return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}> <Worksheet> </Worksheet> </SpreadSheets> </div> </div>; } initSpread(spread) { spread.fromJSON(ReportTemplate); var activeSheet = spread.getActiveSheet(); var dataSheet = spread.getSheet(1); // Turn painting off while adding the data to the sheets spread.suspendPaint(); var source = new GC.Spread.Sheets.Bindings.CellBindingSource(businessDashboard); activeSheet.options.rowHeaderVisible = false; activeSheet.options.colHeaderVisible = false; activeSheet.setDataSource(source); dataSheet.setDataSource(source); // Add data to the revenue table and load the data in the data sheet this.LoadRevenueTable(activeSheet) this.FillDataSheet(dataSheet); // Create the column and bar charts this.CreateRevenueChart(activeSheet); this.CreateBarCharts(activeSheet); // Load the sparklines in the Key Metrics section of the first sheet this.CreateKeyMetricsSection(activeSheet); // Change the color of the gray area behind the sheets in Spread spread.options.grayAreaBackColor = "lightgray"; spread.resumePaint(); } LoadRevenueTable(activeSheet) { 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"); } // Fill in the second sheet with the data from the data source FillDataSheet(dataSheet) { // 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); // 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"); } // This is the base function that creates a chart using the parameters BaseCreateChart(sheet, chartName, chartType, startRow, startColumn, endRow, endColumn, xValues, yValues, seriesBackColor) { var chart; chart = sheet.charts.add(chartName, chartType, 0, 0, 1, 1, GC.Spread.Sheets.Charts.RowCol.rows); chart.startRow(startRow); chart.startColumn(startColumn); chart.endRow(endRow); chart.endColumn(endColumn); chart.startRowOffset(1); chart.startColumnOffset(1); chart.endRowOffset(-2); chart.endColumnOffset(-2); // Check if the chart is a clustered column chart if (chartType == GC.Spread.Sheets.Charts.ChartType.columnClustered) { // 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" }); // Change the format of the axes var axes = chart.axes(); axes.primaryValue.format = "$###,0"; axes.secondaryValue.format = "General"; chart.axes(axes); } else if (chartType == GC.Spread.Sheets.Charts.ChartType.barClustered) { // Check if the chart is a clustered bar chart // 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); } // Set the title of the chart if (chart != null) { var title = chart.title(); title.text = chartName; chart.title(title); } } CreateRevenueChart(activeSheet) { // Create Revenue Column Chart this.BaseCreateChart(activeSheet, 'Revenue', GC.Spread.Sheets.Charts.ChartType.columnClustered, 2, 2, 17, 11, 'Dashboard!N5:N16', 'Dashboard!O5:O16', null); } // Create Bar Charts CreateBarCharts(activeSheet) { // Create Top 5 Sales People Chart this.BaseCreateChart(activeSheet, 'Top 5 Sales People', GC.Spread.Sheets.Charts.ChartType.barClustered, 18, 2, 33, 8, 'DataSheet!I3:I7', 'DataSheet!J3:J7', undefined); // Create Top 5 Regions Chart this.BaseCreateChart(activeSheet, 'Top 5 Regions', GC.Spread.Sheets.Charts.ChartType.barClustered, 18, 8, 33, 13, 'DataSheet!L3:L7', 'DataSheet!M3:M7', 'DarkOrange'); // Create Top 5 Products Chart this.BaseCreateChart(activeSheet, 'Top 5 Products', GC.Spread.Sheets.Charts.ChartType.barClustered, 18, 13, 33, 17, 'DataSheet!O3:O7', 'DataSheet!P3:P7', 'Green'); } CreateKeyMetricsSection(activeSheet) { // 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 this.CreateLineSparklines(activeSheet); } CreateLineSparklines(activeSheet) { // Set Line Sparklines in Key Metrics Section var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting(); setting.options.lineWeight = 3; 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); 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); 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); 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); 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); 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); // 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)'); // 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("# %"); } } ReactDOM.render(<App />, _getElementById('app'));
<!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/react/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script> <script src="$DEMOROOT$/spread/source/data/businessDashboard.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/ReportTemplate.js" type="text/javascript"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/en/lib/react/license.js').then(function () { System.import('./src/app'); }); </script> </head> <body> <div id="app"></div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #app { height: 100%; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-charts': 'npm:@grapecity/spread-sheets-charts/index.js', '@grapecity/spread-sheets-react': 'npm:@grapecity/spread-sheets-react/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);