How to parse Excel XLS file in Javascript HTML5

Question

I am able to read Excel file via FileReader but it outputs text as well as weird characters with it  I need to read xls file row-wise  read data in every column and convert it to JSON    How to read xls file row by row

User · Answer

If you are ever wondering how to read a file from server this code might be helpful.

Restrictions :

File should be in the server (Local/Remote).
You will have to setup headers or have CORS google plugin.

<Head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <script lang="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.12.4/xlsx.core.min.js"></script>
</head>

<body>
    <script>
    /* set up XMLHttpRequest */


    // replace it with your file path in local server
    var url = "http://localhost/test.xlsx";

    var oReq = new XMLHttpRequest();
    oReq.open("GET", url, true);
    oReq.responseType = "arraybuffer";

    oReq.onload = function(e) {
        var arraybuffer = oReq.response;

        /* convert data to binary string */
        var data = new Uint8Array(arraybuffer);

        var arr = new Array();
        for (var i = 0; i != data.length; ++i) {
            arr[i] = String.fromCharCode(data[i]);
        }

        var bstr = arr.join("");

        var cfb = XLSX.read(bstr, { type: 'binary' });

        cfb.SheetNames.forEach(function(sheetName, index) {

            // Obtain The Current Row As CSV
            var fieldsObjs = XLS.utils.sheet_to_json(cfb.Sheets[sheetName]);

            fieldsObjs.map(function(field) {
                $("#my_file_output").append('<input type="checkbox" value="' + field.Fields + '">' + field.Fields + '<br>');
            });

        });
    }

    oReq.send();
    </script>
</body>
<div id="my_file_output">
</div>

</html>

User · Answer

If you want the simplest and tiniest way of reading an   xlsx file in a browser then this library might do   https   catamphetamine github io read-excel-file    lt input type  file  id  input    gt    import readXlsxFile from  read-excel-file   const input   document getElementById  input    input addEventListener  change        gt      readXlsxFile input files 0   then  data    gt            data  is an array of rows        each row being an array of cells            In the example above data is raw string data  It can be parsed to JSON with a strict schema by passing schema argument  See API docs for an example of that   API docs  http   npmjs com package read-excel-file

User · Answer

This code can help youMost of the time jszip js is not working so include xlsx full min js in your js code   Html Code    lt input type  file  id  file  ng-model  csvFile        onchange  angular element this  scope   ExcelExport event    gt    Javascript   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 xlsx js  gt   lt  script gt   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 jszip js  gt   lt  script gt   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 10 8 xlsx full min js  gt   lt  script gt    scope ExcelExport  function  event          var input   event target      var reader   new FileReader        reader onload   function            var fileData   reader result          var wb   XLSX read fileData   type    binary              wb SheetNames forEach function sheetName           var rowObj  XLSX utils sheet to row object array wb Sheets sheetName            var jsonObj   JSON stringify rowObj           console log jsonObj                        reader readAsBinaryString input files 0

User · Answer

var excel new ActiveXObject  Excel Application    var book excel Workbooks Open your full file name here xls   var sheet book Sheets Item 1   var value sheet Range  A1     when you have the sheet  You could use VBA functions as you do in Excel

User · Answer

include the xslx js   xlsx full min js   jszip js  add a onchange event handler to the file input   function showDataExcel event                var file   event target files 0               var reader   new FileReader                var excelData                   reader onload   function  event                    var data   event target result                  var workbook   XLSX read data                        type   binary                                       workbook SheetNames forEach function  sheetName                           Here is your object                     var XL row object   XLSX utils sheet to row object array workbook Sheets sheetName                         for  var i   0  i  lt  XL row object length  i                                                  excelData push XL row object i   your column name                                                 var json object   JSON stringify XL row object                       console log json object                       alert excelData                                                   reader onerror   function  ex                    console log ex                               reader readAsBinaryString file

User · Answer

Below Function converts the Excel sheet  XLSX format  data to JSON  you can add promise to the function     lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 jszip js  gt  lt  script gt   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 xlsx js  gt  lt  script gt   lt script gt  var ExcelToJSON   function        this parseExcel   function file        var reader   new FileReader         reader onload   function e          var data   e target result        var workbook   XLSX read data            type   binary                   workbook SheetNames forEach function sheetName               Here is your object         var XL row object   XLSX utils sheet to row object array workbook Sheets sheetName            var json object   JSON stringify XL row object           console log json object                          reader onerror   function ex          console log ex               reader readAsBinaryString file            lt  script gt    Below post has the code for XLS format Excel to JSON javascript code

User · Answer

Thank you for the answer above  I think the scope  of answers  is completed but I would like to add a  quot react way quot  for whoever using react  Create a file called importData js  import React   Component  from  react   import XLSX from  xlsx   export default class ImportData extends Component      constructor props           super props           this state               excelData                        excelToJson reader           var fileData   reader result          var wb   XLSX read fileData   type    binary             var data               wb SheetNames forEach function sheetName                var rowObj  XLSX utils sheet to row object array wb Sheets sheetName                 var rowString   JSON stringify rowObj                data sheetName    rowString                      this setState  excelData  data              loadFileXLSX event           var input   event target          var reader   new FileReader            reader onload   this excelToJson bind this reader           reader readAsBinaryString input files 0              render            return                lt input type  quot file quot  onChange  this loadFileXLSX bind this    gt                      Then you can use the component in the render method like  import ImportData from    importData js   import React   Component  from  react   class ParentComponent extends Component      render            return   lt ImportData  gt              lt ImportData  gt  would set the data to its own state  you can access Excel data in the  quot parent component quot  by following this

User · Answer

XLS is a binary proprietary format used by Microsoft  Parsing XLS with server side languages is very difficult without using some specific library or Office Interop  Doing this with javascript is mission impossible  Thanks to the HTML5 File API you can read its binary contents but in order to parse and interpret it you will need to dive into the specifications of the XLS format  Starting from Office 2007  Microsoft embraced the Open XML file formats  xslx for Excel  which is a standard

User · Answer

x000D   x000D   lt script src  https   ajax googleapis com ajax libs jquery 2 1 1 jquery min js  gt  lt  script gt  x000D   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 jszip js  gt  lt  script gt  x000D   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 xlsx js  gt  lt  script gt  x000D   lt script gt  x000D      var ExcelToJSON   function     x000D   x000D        this parseExcel   function file    x000D          var reader   new FileReader    x000D   x000D          reader onload   function e    x000D            var data   e target result  x000D            var workbook   XLSX read data    x000D              type   binary  x000D                x000D            workbook SheetNames forEach function sheetName    x000D                 Here is your object x000D              var XL row object   XLSX utils sheet to row object array workbook Sheets sheetName    x000D              var json object   JSON stringify XL row object   x000D              console log JSON parse json object    x000D              jQuery    xlx json    val  json object    x000D               x000D             x000D   x000D          reader onerror   function ex    x000D            console log ex   x000D             x000D   x000D          reader readAsBinaryString file   x000D           x000D       x000D   x000D    function handleFileSelect evt    x000D       x000D      var files   evt target files     FileList object x000D      var xl2json   new ExcelToJSON    x000D      xl2json parseExcel files 0    x000D      x000D   x000D   x000D    x000D   lt  script gt  x000D   x000D   lt form enctype  multipart form-data  gt  x000D       lt input id  upload  type file  name  files    gt  x000D   lt  form gt  x000D   x000D       lt textarea class  form-control  rows 35 cols 120 id  xlx json  gt  lt  textarea gt  x000D   x000D       lt script gt  x000D          document getElementById  upload   addEventListener  change   handleFileSelect  false   x000D   x000D       lt  script gt  x000D   x000D   x000D

User · Answer

Old question  but I should note that the general task of parsing XLS files from javascript is tedious and difficult but not impossible   I have basic parsers implemented in pure JS    http   oss sheetjs com js-xls   XLS files  what you wanted  http   oss sheetjs com js-xlsx   XLSX XLSM XLSB files    Both pages are HTML5 File API-driven XLS XLSX parsers  you can drag-drop your file and it will print out the data in the cells in a comma-separated list    You can also generate JSON objects  assuming the first row is a header row    The test suite http   oss sheetjs com  shows a version that uses XHR to get and parse files

User · Answer

Upload an excel file here and you can get the data in JSON format in console    x000D   x000D   lt script src  https   ajax googleapis com ajax libs jquery 2 1 1 jquery min js  gt  lt  script gt  x000D   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 jszip js  gt  lt  script gt  x000D   lt script src  https   cdnjs cloudflare com ajax libs xlsx 0 8 0 xlsx js  gt  lt  script gt  x000D   lt script gt  x000D      var ExcelToJSON   function     x000D   x000D        this parseExcel   function file    x000D          var reader   new FileReader    x000D   x000D          reader onload   function e    x000D            var data   e target result  x000D            var workbook   XLSX read data    x000D              type   binary  x000D                x000D            workbook SheetNames forEach function sheetName    x000D                 Here is your object x000D              var XL row object   XLSX utils sheet to row object array workbook Sheets sheetName    x000D              var json object   JSON stringify XL row object   x000D              console log JSON parse json object    x000D              jQuery    xlx json    val  json object    x000D               x000D             x000D   x000D          reader onerror   function ex    x000D            console log ex   x000D             x000D   x000D          reader readAsBinaryString file   x000D           x000D       x000D   x000D    function handleFileSelect evt    x000D       x000D      var files   evt target files     FileList object x000D      var xl2json   new ExcelToJSON    x000D      xl2json parseExcel files 0    x000D      x000D   x000D   x000D    x000D   lt  script gt  x000D   x000D   lt form enctype  multipart form-data  gt  x000D       lt input id  upload  type file  name  files    gt  x000D   lt  form gt  x000D   x000D       lt textarea class  form-control  rows 35 cols 120 id  xlx json  gt  lt  textarea gt  x000D   x000D       lt script gt  x000D          document getElementById  upload   addEventListener  change   handleFileSelect  false   x000D   x000D       lt  script gt  x000D   x000D   x000D    This is a combination of the following Stackoverflow posts    https   stackoverflow com a 37083658 4742733 https   stackoverflow com a 39515846 4742733   Good Luck

[javascript] How to parse Excel (XLS) file in Javascript/HTML5

Examples related to javascript

Examples related to json

Examples related to html

Examples related to xls

Examples related to filereader