Read a csv or excel (xlsx) file with just javascript and html?

Question

Is it possible to read a excel xlsx or csv, preferably xlsx, using just JavaScript and html. All the solutions (sheetsJS, d3{d3 uses the Fetch API}) I have found require a webserver. I understand I can get a simple webserver using web server for chrome or python or node.js. Futhermore, I understand I can run chrome with certain flags, but I would like to not do this because of security concerns. I am building a demo for someone who is not web savvy and would like to avoid doing this.

my file structure is very simple :

TestFolder
| index.html
| js/
    | test.js
| data/
    | test.xlsx
| css/
    | test.css

I simply need to read the xlsx and then display that data in html page.

Solution 1

I've added a simple example that accepts Excel or CSV files (current example accepts a single file), uses the SheetJS library to parse the Excel file type, convert the data to JSON and logs the contents to the console.

This should be more than enough to complete your demo. Hope this helps!

var file = document.getElementById('docpicker')
var viewer = document.getElementById('dataviewer')
file.addEventListener('change', importFile);

function importFile(evt) {
  var f = evt.target.files[0];

  if (f) {
    var r = new FileReader();
    r.onload = e => {
      var contents = processExcel(e.target.result);
      console.log(contents)
    }
    r.readAsBinaryString(f);
  } else {
    console.log("Failed to load file");
  }
}

function processExcel(data) {
  var workbook = XLSX.read(data, {
    type: 'binary'
  });

  var firstSheet = workbook.SheetNames[0];
  var data = to_json(workbook);
  return data
};

function to_json(workbook) {
  var result = {};
  workbook.SheetNames.forEach(function(sheetName) {
    var roa = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {
      header: 1
    });
    if (roa.length) result[sheetName] = roa;
  });
  return JSON.stringify(result, 2, 2);
};

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<label for="avatar">Choose an Excel or CSV file:</label>
<input type="file" id="docpicker" accept=".csv,application/vnd.ms-excel,.xlt,application/vnd.ms-excel,.xla,application/vnd.ms-excel,.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,.xltx,application/vnd.openxmlformats-officedocument.spreadsheetml.template,.xlsm,application/vnd.ms-excel.sheet.macroEnabled.12,.xltm,application/vnd.ms-excel.template.macroEnabled.12,.xlam,application/vnd.ms-excel.addin.macroEnabled.12,.xlsb,application/vnd.ms-excel.sheet.binary.macroEnabled.12">

<div id="dataviewer">

Solution 2

You could try using the Fetch API to download the file and process it with JavaScript.

fetch('data/test.xlsx').then(function(resp) {
  // Process the data here...
  console.log('Data Response: ', resp);
});

It would be much easier to work with if your data file was in JSON format, but this might work for your needs.

Update - Example when the data is in JSON format

fetch('data/test.xlsx').then(function(resp) {
  var records = resp.json(); // Assuming that we receive a JSON array.

  console.log('Records: ', records.length);

  records.forEach(function(record){
    console.log('Record Name: ', record.name); // Assuming each record has a name property
  });
});

Comments

[manny]

unfortunately, the Fetch Api will also not work in this instance. d3 actually utilizes the fetch api

[Tom Faltesek]

Yeah, you don't have too many options without a web server (didn't notice that requirement at first), especially if you don't want to mangle the browser security settings. I would strongly consider using something like node (it's free and easy) to serve your files.

[manny]

So the user wont be dropipng in a csv or xlsx file its more like I'll be using the csv or xsls for data to populate fields/ do math. I would need to read the file like var file = 'data/text.xslx'; then use that process excel function. Not sure if I tried something like this last night and got the cors error

[Peter]

@manny I’m not clear what your comment means or is asking me.

[manny]

suppose the data file was a json file how would that work? I'm sure I can write some python script to convert the csv or xlsx to a json file.

[Tom Faltesek]

Fetching the data from your server would work the same way. The difference is that working with JSON data in JavaScript is very straightforward. You won't have to parse the data into a format you can work with.

[Polyvios P]

how to save JSON content on file ?

[FreeSoftwareServers]

getting readAsBinaryString undefined :(