js-xls/x JavaScript plugin for reading and parsing Excel table files (xls/xlsx)

Recently there was a request for one of my projects to parse Excel files (with xls/xlsx extensions) in the browser (using JavaScript of course). So I looked online and found these 2 useful libraries:

• Js-xls
• Js-xlsx

Let's see how they work.

Compatibility

Support for reading file formats

• Excel 2007+ XML Formats (XLSX/XLSM)
• Excel 2007+ Binary Format (XLSB)
• Excel 2003-2004 XML Format (xml "SpreadsheetML")
• Excel 97-2004 (XLS BIFF8)
• Excel 5.0/95 (XLS BIFF5)
• OpenDocument Spreadsheet (ODS)

Supports write file format

• XLSX
• CSV (and General DSV)
• JSON and JS objects (various styles)

Demo:http://oss.sheetjs.com/js-xlsx

Source:http://git.io/xlsx

Js-xls

The installation of Js-xls is simple, use the script file in the browser, and load it with a script tag:

<!--https://github.com/SheetJS/js-xls/blob/master/xls.js--><script src= "/path/to/xls.js" ></ Script>

For node. js, simply install via NPM:

$ NPM Install xlsjs$ node> require (' Xlsjs '). ReadFile (' Excel_file.xls ');

Then we can simply use the code they provide in their index.html file parsing and converting the XLS file Json,csv or the formula-based output.

function Get_radio_value (radioname) {  var radios = document.getelementsbyname (radioname);  for (var i = 0; i < radios.length; i++) {    if (radios[i].checked) {      return radios[i].value;  & nbsp } }} function To_json (workbook) {  var result = {};  workbook. Sheetnames.foreach (function (sheetname) {    var roa = XLS.utils.sheet_to_row_object_array (workbook. Sheets[sheetname]);    if (roa.length > 0) {      result[sheetName] = Roa;    } });  return result;  function to_csv (Workbook) {  var result = [];  workbook. Sheetnames.foreach (function (sheetname) {    var csv = XLS.utils.make_csv (workbook. Sheets[sheetname]);    if (csv.length > 0) {      result.push ("SHEET:" + SheetName);      Result.push ("");     &nBsp Result.push (CSV);   } });  return Result.join ("\ n");}  function to_formulae (Workbook) {  var result = [];  workbook. Sheetnames.foreach (function (sheetname) {    var formulae = XLS.utils.get_formulae (workbook. Sheets[sheetname]);    if (formulae.length > 0) {      result.push ("SHEET : "+ sheetname);      Result.push (" ");      Result.push ( Formulae.join ("\ n"));   } });  return Result.join ("\ n");}  var Tarea = document.getElementById (' b64data '); function b64it () {  var CFB = XLS. Cfb.read (Tarea.value, {type: ' base64 '});  var wb = XLS.PARSE_XLSCFB (CFB);  PROCESS_WB (WB);}  function PROCESS_WB (WB) {  var output = "";  Switch (get_radio_value ("format")) {    case "JSON":      output = json.stringify (To_json (WB), 2, 2);      BReak;    case "form":      output = to_formulae (WB);       break;     default:      output = to_csv (WB); }  if (out.innertext = = = Undefined) out.textcontent = output;  else Out.innertext = output;}  var drop = document.getElementById (' drop '), function Handledrop (e) {  e.stoppropagation ();  E.preventdefault ();  var files = e.datatransfer.files;  var i,f;  for (i = 0, f = files[i]; I! = Files.leng Th ++i) {    var reader = new FileReader ();    var name = f.name;    reader.onl Oad = function (e) {      var data = e.target.result;      var CFB = XLS . Cfb.read (data, {type: ' binary '});     //var arr = String.fromCharCode.apply (null, new Uint8array (data));     //var CFB = XLS. Cfb.read (Btoa (arr), {type: ' base;      var wb = XLS.PARSE_XLSCFB (CFB);      PROCESS_WB (WB);    };    reader.readasbinarystring (f);   // Reader.readasarraybuffer (f); }} function Handledragover (e) {  e.stoppropagation ();  E.preventdefault ();  e.datatransfer.dropeffect = ' copy ';}  if (Drop.addeventlistener) {  drop.addeventlistener (' DragEnter ', Handledragover, False);  Drop.addeventlistener (' DragOver ', Handledragover, False);  Drop.addeventlistener (' drop ', Handledrop, false);}

The JS code is easy to understand, it uses the HTML5 drag-and-drop API to allow us to upload files and then look at them as binary strings. Binary, and then pass directly to the library, and further analyze the use of this code:

var CFB = XLS. Cfb.read (data, {type: ' binary '}); var wb = XLS.PARSE_XLSCFB (CFB);

Eventually, WB (Workbook) objects are processed and converted to the desired format (json,csv and Excel formulas). This does not HTML5 drag and drop the file Reader API, which can be used for the same node. js code.

Note: XLSX is not supported for this module. To overcome this limitation, we can use another package by the same author called Js-xlsx.

Js-xlsx

Again, installation and use are almost identical to the previous section. To load Jszip.js and xlsx files using a browser:

<!--https://github.com/SheetJS/js-xlsx/blob/master/jszip.js--><script src= "/path/to/jszip.js" ></ script><!--https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js--><script src= "/path/to/xlsx.js" ></script>

For node. js, simply install via NPM:

$ NPM Install xlsx$ node> require (' xlsx '). ReadFile (' excel_file.xlsx ');

We can use the index.html in the project again to provide code to convert xlsx json,csv or formula-based output.

function Get_radio_value (radioname) {    var radios = document.getelementsbyname (radioName);  & nbsp;  for (var i = 0; i < radios.length; i++) {        if (radios[i].checked ) {            return radios[i].value;        }   }} function To_json (workbook) {    var result = {};& nbsp;   workbook. Sheetnames.foreach (function (sheetname) {        var roa = XLSX.utils.sheet_to_ Row_object_array (workbook. Sheets[sheetname]);        if (roa.length > 0) {             Result[sheetname] = roa;       }    });    return result;  function to_csv (Workbook) {    var result = [];    workBook. Sheetnames.foreach (function (sheetname) {        var csv = XLSX.utils.sheet_to_ CSV (workbook. Sheets[sheetname]);        if (csv.length > 0) {             Result.push ("SHEET:" + sheetname);             Result.push ("");             Result.push (CSV);       }   });    return Result.join ("\ n");}  function to_formulae (Workbook) {    var result = [];    workbook. Sheetnames.foreach (function (sheetname) {        var formulae = XLSX.utils.get_ Formulae (workbook. Sheets[sheetname]);        if (formulae.length > 0) {             Result.push ("SHEET: "+ sheetname);            Result.push (" ");             Result.push (Formulae.join ("\ n"));        }   });    return Result.join ("\ n");}  var Tarea = document.getElementById (' b64data '); function b64it () {    var wb = Xlsx.read ( Tarea.value, {type: ' base64 '});    PROCESS_WB (WB);}  function PROCESS_WB (WB) {    var output = "";    Switch (get_radio_value ("format") ) {        case "JSON":        output = Json.stringify (To_json (WB), 2, 2);            break;         case "Form":             OUTPUT = to_formulae (WB); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;      break;         default:        output = to_csv (WB);    }    if (out.innertext = = = undefined) Out.textcontent = output;    else Out.innertext = output;}  var drop = document.getElementById (' drop '), function Handledrop (e) {    e.stoppropagation ();     E.preventdefault ();    var files = e.datatransfer.files;    var i,f;     for (i = 0, f = files[i]; I! = files.length; ++i) {        var reade R = new FileReader ();        var name = f.name;         reader.onload = function (e) {            var data = e.target.result;           //var WB = XLSX.read (data, {type:' binary '});            var arr = String.fromCharCode.apply ( NULL, new Uint8array (data));            var wb = XLSX.read ( Btoa (arr), {type: ' base64 '});            PROCESS_WB (WB);        };       // Reader.readasbinarystring (f);        Reader.readasarraybuffer (f);    }} function handledragover (e) {    e.stoppropagation ();    E.preventdefault ();    e.datatransfer.dropeffect = ' copy ';}  if (Drop.addeventlistener) {    drop.addeventlistener (' DragEnter ', Handledragover, false);     drop.addeventlistener (' DragOver ', Handledragover, False);    Drop.addeventlistener (' drop ', Handledrop, false);}

js-xls/x JavaScript Plug-ins for reading and parsing Excel table files (xls/xlsx)