alaSQL and Google Charts

Question

The plan is to use alaSQL to extract data from an excel spread sheet and use the resultant array as the source for a Google chart. The problem is that I can't get the results of the alaSQL call to properly populate a 2-D array that Google the charts api requires. it does not error out, but produces no results.

Here is the code:

        var data_cities = [];
        data_cities = new google.visualization.DataTable();
        data_cities.addColumn('string', 'City');
        data_cities.addColumn('number', 'Population');
        data_cities.addRows(3);
        var row_Counter = 0;

        alasql('select * from xlsx("cities.xlsx", {headers:true, sheetid:"Cities", range:"A1:B4"})', //case senstitive on sheet, column name and value
              [], function (xlData) {
                  $.each(xlData, function (key, val) {
                      //alert(key + " : " + val);
                      items.push("<li>City: " + this['City'] + "</li>" + "<li>Pop: " + this['Population'] + "</li>");

                      data_cities.setCell(row_Counter, 0, val.City);
                      data_cities.setCell(row_Counter, 1, val.Population);
                      row_Counter = row_Counter + 1;
                  });

                  $('<ul/>', {
                      html: items.join('')
                  }).appendTo('div#divgetJSON');
              });

        var chart_cities = new google.visualization.ColumnChart(document.getElementById('chart_div_cities'));
        drawChart_Cities();

        function drawChart_Cities() {
            // Set chart options
            var options_cities = {
                'title': 'Populations of Major Cities',
                'width': 1800,
                'height': 400,
                vAxis: { title: "Population", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
                hAxis: { title: "City", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
                seriesType: "bars",
                animation: {
                    duration: 800,
                    easing: 'inout',
                },
                allowHtml: true,
                bar: { groupWidth: "65%" },
                legend: { position: "bottom" },
                is3D: true,
            };

            chart_cities.draw(data_cities, options_cities);
        }

Answer 1

I modified your code a little bit and put all files in this archive.

Probably, the problem is that Alasql read XLSX file with async interface, but you trying to read data outside the Alasql's callback function.

<script src="alasql.min.js"></script>
<script src="jquery.js"></script>
<script src="xlsx.core.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<div id="chart_div_cities"></div>
<div id="divgetJSON"></div>
<script>
    google.load('visualization', '1', {'packages':['corechart']});
    google.setOnLoadCallback(drawChart);

    function drawChart() {
      var data_cities = [];
      data_cities = new google.visualization.DataTable();
      data_cities.addColumn('string', 'City');
      data_cities.addColumn('number', 'Population');
      data_cities.addRows(3);
      var row_Counter = 0;

      alasql('select * from XLSX("cities.xlsx", {headers:true, sheetid:"Cities", range:"A1:B4"})', //case senstitive on sheet, column name and value
            [], function (xlData) {
                var items = [];
                $.each(xlData, function (key, val) {
                    items.push("<li>City: " + this['City'] + "</li>" + "<li>Pop: " + this['Population'] + "</li>");

                    data_cities.setCell(row_Counter, 0, val.City);
                    data_cities.setCell(row_Counter, 1, val.Population);
                    row_Counter = row_Counter + 1;
                });


            var chart_cities = new google.visualization.ColumnChart(document.getElementById('chart_div_cities'));
            drawChart_Cities();

          function drawChart_Cities() {
              // Set chart options
              var options_cities = {
                  'title': 'Populations of Major Cities',
                  'width': 1800,
                  'height': 400,
                  vAxis: { title: "Population", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
                  hAxis: { title: "City", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
                  seriesType: "bars",
                  animation: {
                      duration: 800,
                      easing: 'inout',
                  },
                  allowHtml: true,
                  bar: { groupWidth: "65%" },
                  legend: { position: "bottom" },
                  is3D: true,
              };

              chart_cities.draw(data_cities, options_cities);
            }

        });
    };
</script>

Disclaimer: I am author of Alasql.