Sunday 19 March 2017

Data Visualisation Open-Source Tool:Webcharts using Excel

Data visualisation can be performed in the browser and as our team are familiar with Microsoft Excel a solution to combine webcharts with Excel was explored. Chart.js is open-source framework and after creating a number of JavaScript samples; it was chosen as the charting framework.

The next challenge is to import Excel Spreadsheets into a JSON or Array Object. The open-source library JS-XLSX was tested, even though reviews highlighted its complexity. After setting up a Node.js server the main issue was to parse JSON in a cross browser compatible way. A Node.js server would store the Excel spreadsheets and run the import code. As the server had to have the ability to run Node.js Heroku was selected; as they provide a free Node.js container for non-commercial projects. Heroku integration with Github enables easy deployment of applications.

A HTML5 boilerplate was downloaded along with Heroku's Node.js Starter Kit. Chart.js was installed using the NPM package manager along with JS-XLSX.

Completed application is located here (as iframes to be included in a DH6010 Blogpost)

https://dh6010.herokuapp.com/iframes/iframes.html

The DH6010 Data Visualisation App is located @ https://bitbucket.org/MuddyGames/dh6010

Import and Webchart JavaScript

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
<!doctype html>
<html class="no-js" lang="">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="x-ua-compatible" content="ie=edge">
        <title id="page_title"><title>
        <link rel="stylesheet" href="/stylesheets/graph.css">

  <script type="text/javascript" src="/engine/jquery/scripts/jquery.min.js"></script>
  <script type="text/javascript" src="/engine/chart.js/scripts/Chart.min.js"></script>
  <script type="text/javascript" src="/engine/xlsx/scripts/xlsx.full.min.js"></script>

  <script>
  function get(name){
      if(name=(new RegExp('[?&]'+encodeURIComponent(name)+'=([^&]*)')).exec(location.search))
        return decodeURIComponent(name[1]);
  }


  function loadSpreadsheet(filename, chart_type, chart_title) {

   // Define Vars
   var request;
   var keys = [];
   var values = [];


   if (window.XMLHttpRequest) {
       request = new XMLHttpRequest();
   } else {
       // code for older browsers
       request = new ActiveXObject("Microsoft.XMLHTTP");
   }
   request.onreadystatechange = function() {
       if (this.readyState == 4 && this.status == 200) {
       }
   };

   request.open("GET", "./spreadsheets/" + filename, true);
   request.responseType = "arraybuffer";

   request.onload = function(e) {
    var arraybuffer = request.response;
      var data = new Uint8Array(arraybuffer);
      var arr = new Array();
      for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
      var bstr = arr.join("");
      var workbook = XLSX.read(bstr, {type:"binary"});
      var first_sheet_name = workbook.SheetNames[0];
      var worksheet = workbook.Sheets[first_sheet_name];

      var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[first_sheet_name]);

      for (var i = 0; i < roa.length; i++) {
       var row = roa[i];

       if(i == 0){
        $.each(row,function(value, index){ 
           keys.push(value);
          }
        );
       }

       $.each(row,function(value, index){ 
          values.push(row[value]);
         }
       );
      }


       var ctx = document.getElementById("chart");

    var chart = new Chart(ctx, {
     type: chart_type,
     data: {
         labels: keys,
          datasets: [{
             label: chart_title,

             fill: false,
                lineTension: 0.1,
                backgroundColor: border_Color,
                borderColor: border_Color,
                borderCapStyle: 'butt',
                borderDash: [],
                borderDashOffset: 0.0,
                borderJoinStyle: 'miter',
                pointBorderColor: border_Color,
                pointBackgroundColor: "#fff",
                pointBorderWidth: 1,
                pointHoverRadius: 5,
                pointHoverBackgroundColor: border_Color,
                pointHoverBorderColor: border_Color,
                pointHoverBorderWidth: 2,
                pointRadius: 1,
                pointHitRadius: 10,

             data: values,
             }]
         },
         options: {
             scales: {
                 yAxes: [{
                     ticks: {
                         beginAtZero:true
                     }
                 }]
             }
         }
     });
   }

   request.send();
  }

  var page_title = get("page_title");
  var filename  = get("filename");
  var chart_type  = get("chart_type");
  var chart_title = get("chart_title");
  var border_Color = get("border_Color");

  window.onpaint = loadSpreadsheet(filename, chart_type, chart_title);

  </script>

 <body>
  <h2 id="chart_title"></h2>

     <div class="container">
         <div class="container">
             <canvas id="chart"></canvas>
         </div>
     </div>

     <script type="text/javascript">
      document.getElementById("chart_title").innerHTML = page_title;
      document.getElementById("page_title").innerHTML = page_title;
     </script>

 </body>


</html>

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)