alaSQL和谷歌图表

Question

该计划是使用alaSQL从excel电子表格中提取数据，并将结果数组用作Google图表的源。问题是我无法获得alaSQL调用的结果，无法正确填充Google charts api所需的二维数组。它不会出错，但不会产生任何结果。

代码如下：

        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

我稍微修改了一下你的代码，把所有的文件都放到了this archive中。

问题可能出在Alasql使用异步接口读取XLSX文件，而您试图在Alasql的回调函数之外读取数据。

<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>

免责声明:我是Alasql的作者。