Slice and Dice Your Charts with SpreadJS Slicers

  • 0 Comments

Slicers are a newly added feature to Spread.Sheets that gives users the ability to filter their table data in a new way. With SpreadJS V11, we have also added chart support, which can be combined with slicers to give a better visual representation of filtered data.

Download the sample

Setup

Create an HTML file and add the Spread.Sheets script and style references. For this page, we will need to reference the main Spread.Sheet script as well as the chart script:

    <!DOCTYPE html> 
      <html xmlns="http://www.w3.org/1999/xhtml"> 
      <head> 
      <meta charset="utf-8" /> 
      <title>Spread  HTML Page</title> 
        <link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.11.0.0.css" rel="stylesheet" /> 
      <script src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.11.0.0.min.js" type="text/javascript"></script> 
      <script src="http://cdn.grapecity.com/spreadjs/hosted/scripts/plugins/gc.spread.sheets.charts.11.0.0.min.js" type="text/javascript"></script> 
    </head> 
      <body> 
      </body> 
      </html>

Then add a DIV element to host the Spread.Sheets instance:

    <body> 
      <div id="ss" style="position: absolute; width: 98%; height: 80%; border: 1px solid gray;"></div> 
    </body>

Now we can initialize the Spread component. For this, we will separate the process into 4 functions: the window’s onload function, initSpread, initSheet, and initChart. The onload function will be the starting function:

      window.onload  = function () {
        var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
      var activeSheet = spread.getActiveSheet();
        spread.options.tabStripRatio = 0.6;
      initSpread(spread);
      }

That function calls the initSpread function, which we will use to get the active sheet and then initialize the sheet and the chart:

      function initSpread(spread) {
      var sheet = spread.getActiveSheet();
        spread.suspendPaint();
      initSheet(sheet);
      initChart(sheet); 
      spread.resumePaint();
      }

When initiating the sheet, we will want to add a data-bound table. In this sample, the data is in a separate JavaScript file, so reference that in the page:

      <script src="./data/data.js" type="text/javascript"></script>

Now we can create a table and we can use that function to load the data and bind it to a table in the initSheet function:

      function initSheet(sheet) {
      sheet.suspendPaint();
        sheet.name("State  Resident Population 2016");
      sheet.setRowCount(69);
      sheet.setColumnCount(17);
        // Create the  table and set the array as the data source
      sheet.tables.add("table1", 16, 0,  data.length, data[0].length);
      sheet.setArray(16, 0, data);
        // Set decimal  places to 0
      for (var r = 16;  r <= 67; r++) {
      for (var c = 1; c  <= 3; c++) {
      sheet.getCell(r, c).formatter('#');
      }
      }
        sheet.resumePaint();
      }

Add Slicer

The data in this sample is census data of the age of the population of the United States, organized by State. Since there is a lot of data to look at, having a slicer to filter out the specific data that you want can be really helpful. We can add a slicer with just a few lines of code in the initSheet function:

      // Add a  slicer for the table to filter by "State"
      var slicer1 = sheet.slicers.add("slicer1", "table1", "State");
      slicer1.position(new GC.Spread.Sheets.Point(830, 25));
      // Show  the data items in their original positions
      slicer1.showNoDataItemsInLast(false);

Add Chart

Now we can add a chart to Spread. If the chart is bound to the table, then it will automatically be updated whenever the slicer is changed. In the initChart function we will add the table-bound chart, which will add the data to the chart. In order to ensure the series names are added, be sure to use “table1[#all]” as the table name to bind to.

      function initChart(sheet) {
      // Add the  chart
      var chart = sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 5, 800,  300, "table1[#all]");
      chart.title({ text: "State Resident Population 2016", color: "darkblue", fontSize: "17pt" });
      }

Finally, we can add some code to get rid of gridlines and headers to make it look more presentable (add this code to the initSheet function):

      sheet.options.gridline  = { showVerticalGridline: false,  showHorizontalGridline: false };
      sheet.options.colHeaderVisible  = false;
      sheet.options.rowHeaderVisible  = false;

By combining SJS built-in charts and slicers, developers can provide their users with powerful data analysis tools with just a small amount of code.

Download SpreadJS 11

Kevin Ashley - Spread Product Manager

Kevin Ashley

Product Manager