Javascript Read Excel File (Into Array & JSON)

Welcome to a tutorial on how to read an Excel file into an array or JSON in Javascript. Yes, that is right. We can actually read Excel files in client-side Javascript, without having to do all those “upload to a temp folder on the server then read the Excel file”. Read on for the example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Read Excel Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

READ EXCEL IN JAVASCRIPT

All right, let us now get into the example of reading an Excel file in Javascript.

 

PART 1) DUMMY EXCEL FILE

NAME EMAIL
Jo Doe jo@doe.com
Job Doe job@doe.com
Joe Doe joe@doe.com
Jog Doe jog@doe.com
Joh Doe joh@doe.com
Joi Doe joi@doe.com
Jol Doe jol@doe.com
Jon Doe jon@doe.com
Jou Doe jou@doe.com
Joy Doe joy@doe.com

First, let us start with the Excel file… There is nothing “special” here, just a list of dummy users.

 

PART 2) THE HTML

2-read-excel.html
<!-- (A) JAVASCRIPT -->
<!-- https://sheetjs.com/ -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script defer src="3a-read-array.js"></script>
<!--
<script defer src="3b-read-json.js"></script>
-->
 
<!-- (B) FILE PICKER -->
<input type="file" id="demo" accept=".xls,.xlsx"/>

Yep, that’s all for the HTML.

  1. Javascript does not have native functions to read Excel files. So we will be using a library called SheetJS – Check out their Github page if you want to follow up, links are below in the extras section.
  2. Just a regular file input field to pick an Excel file.

 

 

PART 3A) READ INTO AN ARRAY

3a-read-array-js
document.getElementById("demo").onchange = (evt) => {
  // (A) NEW FILE READER
  var reader = new FileReader();
 
  // (B) ON FINISH LOADING
  reader.addEventListener("loadend", (evt) => {
    // (B1) GET THE FIRST WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]],
        range = XLSX.utils.decode_range(worksheet["!ref"]);
 
    // (B2) READ CELLS IN ARRAY
    var data = [];
    for (let row=range.s.r; row<=range.e.r; row++) {
      let i = data.length;
      data.push([]);
      for (let col=range.s.c; col<=range.e.c; col++) {
        let cell = worksheet[XLSX.utils.encode_cell({r:row, c:col})];
        data[i].push(cell.v);
      }
    }
    console.log(data);
  });
 
  // (C) START - READ SELECTED EXCEL FILE
  reader.readAsArrayBuffer(evt.target.files[0]);
};

Not going to explain this line-by-line (very boring), so here’s a quick walkthrough.

  1. Create a var reader = new FileReader() to read the selected Excel file.
  2. What to do when the Excel file is loaded. I.E. Start reading the cells of the worksheet.
    • (B1) Use the SheetJS library to get the first worksheet.
    • (B2) Then loop through the rows/columns, and keep them in the data array.
  3. reader.readAsArrayBuffer(evt.target.files[0]) In English, “read the selected Excel file as an array buffer”. This will effectively start (B) and extract the cell values into an array.

 

PART 3B) READ INTO JSON STRING

3b-read-json.js
// (B2) READ HEADER ROW
var data = {}, keys = [];
for (let col=range.s.c; col<=range.e.c; col++) {
  let cell = worksheet[XLSX.utils.encode_cell({r:0, c:col})];
  data[cell.v] = [];
  keys.push(cell.v);
} 
 
// (B3) READ DATA ROWS
for (let row=range.s.r + 1; row<=range.e.r; row++) {
  for (let col=range.s.c; col<=range.e.c; col++) {
    let cell = worksheet[XLSX.utils.encode_cell({r:row, c:col})];
    data[keys[col]].push(cell.v);
  }
}
 
// (B4) JSON ENCODE
data = JSON.stringify(data);
console.log(data);

For you guys who need a different “data arrangement” or JSON encoded string – The process is the same, we are only organizing the data in a different structure.

  • (B2) First, we deal with the “header row” and “data key”.
    • Take note, var data is now an object, not an array.
    • Read the header row to extract the key – data = { NAME:[], EMAIL:[] }.
    • Also, keep the keys in an array – keys = ["NAME", "EMAIL"].
  • (B3) As usual, loop through the rows and columns to “fill up” data.
  • (B4) Lastly, JSON encode data into a string.

 

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

COMPATIBILITY CHECKS

This example will work on all modern “Grade A” browsers.

 

TABLE FORMAT & ERROR HANDLING

Captain Obvious at your service – We are assuming that the data is always in the first worksheet, and the first row is always the header. If that is not the case, you will have to change the entire section B2 in the Javascript accordingly. Also, you may want to add some sort of error handling when the worksheet cannot be read properly.

 

PERFORMANCE WARNING

Yes, we are reading an entire Excel file into an array, this works fine for small/medium Excel files. But you will face performance issues and memory limitations for massive files. So you may also want to implement some sort of “limiter” on your own; Maybe stop reading at row 1000, or process it “batch-by-batch”.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.