Posts Tagged SheetJS

How to read Excel files in Javascript/Jquery and convert the value to Json

Introduction

We have multiple ways to read excel file in C# and then convert it to JSON or insert the data in to database etc. However there are not much options available when it comes to reading the Excel file using javascript. In this article I will explain an option using a Jquery Plugin called SheetJS.

SheetJS is Excel parser which supports multiple formats of excel files like 2007, 2010, 2013 office versions etc.

Implementation

To use the SheetJS user need to add the reference files first. You can do this either by downloading files from here and then refer these js files to project. Another option is to use the online CDN available.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>

Reading the Excel File

To read the excel file we use the read method in SheetJs like below

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

Converting Excel value to Json
To convert the values to Json will use “sheet_to_json” method

          var sheet_name_list = workbook.SheetNames;
           sheet_name_list.forEach(function (y) { /* iterate through sheets */
                    //Convert the cell value to Json
                    var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                    if (roa.length > 0) {
                        result = roa;
                    }
                });

Complete Code

Sample Excel I used for this Demo

ujcrvsc

 

, , , ,

1 Comment