Reading data into Google charts from excel sheet

kpj

unread,

Aug 2, 2013, 9:29:25 AM8/2/13

to google-visua...@googlegroups.com

Hello,

I have data in my excel sheet.

I have written a VBA function which converts my data in to array format( which looks exactly like the data in google charts examples)

But, I dont know how to Read my array into

var data = google.visualization.arrayToDataTable(

I got my data array written into another file which has a function FillSourceArray()

and i am calling that function into the VBA function using DataArray = FillSourceArray()

I am not sure about how to read the data.

Thanks in advance

asgallant

unread,

Aug 2, 2013, 12:33:27 PM8/2/13

to google-visua...@googlegroups.com

If you want to read data live from your Excel sheet, you need server-side code that is capable of reading Excel. I am given to understand that there are some tools from Microsoft that can hook in to ASP.net and read Excel files, but I don't know where you can get them. Reading Excel files in other scripting languages is difficult at best. As an alternative, if your needs permit it, you can import the Excel file into Google Spreadsheets and query the data from there.

If you don't need to read the data live, then you can export the data to your javascript and format it for input into the DataTable.

kpj

unread,

Aug 2, 2013, 3:09:56 PM8/2/13

to google-visua...@googlegroups.com

Thanks for your response

I have already formatted my excel sheet data and wrote it in a different text file.

My aim is to read the formatted data from that text file and use it in the charts.

Is it possible to do that?

Thanks in advance.

asgallant

unread,

Aug 2, 2013, 3:22:50 PM8/2/13

to google-visua...@googlegroups.com

What does the structure of the text file look like?

kpj

unread,

Aug 2, 2013, 4:00:11 PM8/2/13

to google-visua...@googlegroups.com

the structure looks like this

[
[
'ID',
'Label',
'Longitude',
'Latitude',
'Country',
'City',
'Inventory',
'Safety stock',
'Ttl_wght_flw_thru',
],
[
'AFKBL',
'Kabul, Afghanistan',
69.136749,
34.53091,
'Afghanistan',
'Kabul',
12,
1845,
1304,
],
[
'AFKDH',
'Kandahar, Afghanistan',
65.700279,
31.61087,
'Afghanistan',
'Kandahar',
18,
1193,
151029,
]

if you want to see the code , i will send it in an attachment

thanks

asgallant

unread,

Aug 2, 2013, 5:31:29 PM8/2/13

to google-visua...@googlegroups.com

You should be able to make an AJAX request from your javascript to grab that file from your server. Using jQuery, it would look something like this:

function drawChart() {
    $.ajax({
        url: 'path/to/file',
        type: 'json',
        success: function (json) {
            var data = google.visualization.arrayToDataTable(json);

            // do something with data
        }
    });
}
google.setOnLoadCallback(drawChart);
google.load("visualization", "1", {packages:[/* whatever packages you need */]});

To use that code, you will need to load the jQuery library (if you don't already):

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js" ></script>

You can write an equivalent AJAX call without jQuery, but this way is easier.

kpj

unread,

Aug 3, 2013, 4:22:03 PM8/3/13

to google-visua...@googlegroups.com

Hi asgallant,

I tried using your method..but, some how, its not showing up anything in the GE

Please take a look at the code and correct me..

<!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Strict//EN " "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd" >
<html xmlns="http://www.w3.org/1888/xhtml">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>
     Google Visualization Charts
    </title>
   <script type="text/javascript" src= "http://www.google.com/jsapi"></script>
     <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js" ></script>
    <script type="; Text / javascript; ">
      google.load('visualization', '1.1', {packages: ['controls']});
   </script>
    <script type= "text/javascript">
    function drawChart() {
               $.ajax({
         url:'C:\Users\jastik\Desktop\_DataArray.kml',

       type: 'json',
       success: function (json) {
           var data = google.visualization.arrayToDataTable(json);

        // Define category pickers for 'Country', 'Region/State' and 'City'
        var countryPicker = new google.visualization.ControlWrapper({
         'controlType': 'CategoryFilter',
         'containerId': 'control1',
          'options': {
           'filterColumnLabel': 'Country',
           'ui': {
             'labelStacking': 'vertical',
              'allowTyping': false,
             'allowMultiple': false
           }
         }
      });

      var cityPicker = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
          'containerId': 'control2',
         'options': {
            'filterColumnLabel': 'City',
           'ui': {
             'labelStacking': 'vertical',
             'allowTyping': false,
           'allowMultiple': false
            }
          }
        });

        }
   });
}
      // Create the dashboard.
        new google.visualization.Dashboard(document.getElementById('dashboard')).
          // Configure the controls so that:- the 'Country' selection drives the 'Region' one,- the 'Region' selection drives the 'City' one,
        // - and finally the 'City' output drives the chart
          bind(countryPicker, cityPicker).
          bind(cityPicker, barChart).
         // Draw the dashboard
         draw(data);
     }
google.setOnLoadCallback(drawChart);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
   <div id="dashboard" style= "width:800px;height:220px">
    <table>
       <tr style='vertical-align: top'>
         <td style='width: 300px; font-size: 0.8em;'>
           <div id="; control1; "></div>
          <div id="; control2; "></div>
         </td>
         <td style='width: 600px'>
           <div style=" float: left; " id="chart1"></div>
          </td>
        </tr>
   </table>
</div>
</body>
</html>

Thanks in advance

asgallant

unread,

Aug 4, 2013, 1:23:18 AM8/4/13

to google-visua...@googlegroups.com

If you are running that from your local machine, you need to specify the "http:" in the url for jQuery tag:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js" ></script>

Leaving it out makes it protocol-agnostic (so you can view from an http or an https website without throwing errors), but it doesn't work for the "file:" protocol.

Reply all

Reply to author

Forward