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);
        }

agershun · Answer 1 · 2015-03-12T12:52:54.163

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.

Thankyou agershun....I was hoping for a response from you as the author of Alasql. — ShaunK, Mar 13 '15 at 03:41
I'll try that again. I have changed the code accordingly and it works successfully on my development PC. However, when I move it to the Production box, I am getting the message "corrupted zip..cannot find end of central directory". I have tried all sorts of things to solve it but cannot find a solution. I will follow the link to your dropbox location of the js files as I can't get through to it from work. Thanks — ShaunK, Mar 13 '15 at 03:44
Can you download this file from here? https://drive.google.com/file/d/0B4zYdb8DULSGUHhoWWlBMmsxUDA/view?usp=sharing — agershun, Mar 13 '15 at 06:18
1. Probably, the "corrupted zip" is a message from XLSX.js library, and it cannot read your XLSX file (because it actually ZIP). To solve this you can try to resave this file from Excel. 2. Please, check that you are using XLSX, but not XLS format. For XLS you need to use XLS-JS library https://github.com/SheetJS/js-xls — agershun, Mar 13 '15 at 06:22

alaSQL and Google Charts

1 Answers1