How can I pass local Excel file data to Javascript array (for google charts)?

Question

I have a simple problem but I can't find a simple solution:

I have an Excel file (always the same), with several WorkSheets that non-IT coworkers want to be able to update so that it also updates the Google Charts (or other chart API you can advise me) webpage.

(By the way, I only need a certain part of each worksheet so if you know a way to extract only what I need that would be great :) )

I would like to avoid having server-side operation but it's maybe not possible since I want to read a local file.

I have looked at ways to convert to JSON, or to load as base64 string, using xlsx.js but I find nothing simple that my colleagues could easily use.

Thank you very much.

Edit

I found a way to do exactly what I wanted so if it can help anyone:

First, I put all the data I needed in one csv file.

Then, I used HTML5 FileAPI to read the csv file that I loaded with a file input.

Finally I used a cvstojson script to parse the file then passed it to th google graph dataTable the way I wanted.

However, this allowed to load the data only once so I used a button-styled label for the file input that triggered a hidden "reset" button (actually cloning more than resetting) after the file was loaded so now it looks like a single button that allows to load the file multiple times after I changed the data in it. I also used jQuery's localStorage to populate my json. I know this is pretty messy so here's a code extract:

<div id="load">
    <button id="clear">Clear</button>
    <label id="forcvs" for="cvs" > Load data </label>
    <input type="file" id="cvs" onchange="handleFiles(this.files);" accept=".csv"><br/>
</div>

The "handleFiles" fonction leads to the cvstojson script:

var json = JSON.parse(localStorage.getItem('json'))

function handleFiles(files) {
    // Check for the various File API support.
    if (window.FileReader) {
        // FileReader are supported.
        getAsText(files[0]);
    } else {
        alert('FileReader are not supported in this browser.');
    }
}

function getAsText(fileToRead) {
    var reader = new FileReader();
    // Read file into memory as UTF-8      
    reader.readAsText(fileToRead);
    // Handle errors load
    reader.onload = loadHandler;
    reader.onerror = errorHandler;
}

function loadHandler(event) {
    var csv = event.target.result;
    processData(csv);          
}

function processData(csv) {
  object = $.csv.toObjects(csv, {separator:";"})
    drawOutput(object);
}

function errorHandler(evt) {
    if(evt.target.error.name == "NotReadableError") {
        alert("Canno't read file !");
    }
}

function drawOutput(object){
  json = object
  localStorage.setItem('json', JSON.stringify(json))
  drawChart()
  $( "#clear" ).trigger( "click" );
}

and then before updating my data and drawing my chart I used:

json = JSON.parse(localStorage.getItem('json'))

Here's an extract of the jquery I used for that:

var load = $('#cvs')
$('#clear').click(function () {
load.replaceWith(load = load.clone(true))
})

And the CSS:

#cvs, #clear {
  display: none;
}


#forcvs {
  -webkit-appearance: button;
  -moz-appearance: button;
  appearance: button;
  line-height: 16px;
  padding: .2em .4em;
  margin: .2em;
}

Hope it can help some one and thanks for the replies :)

Answer 1

You can try Alasql JavaScript library to load XLSX data into JavaScript array without any complex programming. Alasql uses XLSX.js library to work with Excel files.

This is a sample:

<script src="alasql.min.js"></script>
<script src='xlsx.core.min.js'></script>
<script>
    alasql('select * from xlsx("cities.xlsx",{headers:true, sheetid:"Sheet1", range:"A1:B6"})',
           [],function(data) {
                console.log(data);
    });
</script>

You can run this sample at alasql.org site.

By default headers option is false, sheetid is "Sheet1", range - all cell on the sheet.

Alasql also supports TXT, TAB, CSV, JSON, and XLS formats. You also can produce XLSX file back from JavaScript array:

var stars = [{star:"Aldebaran"},{star:"Algol"},{star:"Vega"}];
alasql('SELECT * INTO XSLX("stars.xlsx",{headers:true}) FROM ?',[stars]);

Answer 2

You can read system excel file without uploading using sheet.js

$(document).ready( function() {
   var data = SheetName = '';
   var fileUrl = "data.xlsx";
   var oReq;

  if(window.XMLHttpRequest) oReq = new XMLHttpRequest();
   else if(window.ActiveXObject) oReq = new ActiveXObject('MSXML2.XMLHTTP.3.0');
   else throw "XHR unavailable for your browser";

   oReq.open("GET", fileUrl, true);

   if(typeof Uint8Array !== 'undefined') {
     oReq.responseType = "arraybuffer"; // Set response type
     oReq.onload = function(e) {
       if(typeof console !== 'undefined')
       var arraybuffer = oReq.response;
       var data = new Uint8Array(arraybuffer);
       var wb = XLSX.read(data, {type:"array"}); // Read file data
       toJson(wb);
    };
  }
  else {
    oReq.setRequestHeader("Accept-Charset", "x-user-defined"); 
    oReq.onreadystatechange = function() {
    if(oReq.readyState == 4 && oReq.status == 200) {
      var ff = convertResponseBodyToText(oReq.responseBody);
      if(typeof console !== 'undefined')
        var wb = XLSX.read(ff, {type:"binary"});
        toJson(wb);
      }
    };
 }
 oReq.send(); // sends request

 function toJson(excel) {
   SheetName = excel.SheetNames[0];
   excel.SheetNames.forEach(function(sheetName) {
    var json = XLSX.utils.sheet_to_json(excel.Sheets[sheetName]); // convert sheet into json format
    if( !json.length ) return false;
    data = json;
   });
}

});

More help you may get here:

http://onegibi.com/programming/javascript-jquery-read-system-excel-file-without-uploading/