Best way to read data in . XLSX file in NodeJS and convert to JSON format.

Through the internet, there are a lot of methods to read .xlsx files. But most, are work with local servers, and won’t work with online servers. Here is the best way that I’m dealing with. Here’s a brief explanation of what your code does:

1. Convert Local .xlsx file to base64 format.
2. Pass that base64 string to the backend.
3. Decodes the Base64 string to a Buffer.
4. Convert the Buffer to an XLSX workbook.
5. Convert workbook to XLSX sheet.
6. Sheet into a JSON object.

1. Convert Local .xlsx file to base64 format.

// Check for the File API support.
if (window.File && window.FileReader && window.FileList && window.Blob) {
  document.getElementById('files').addEventListener('change', handleFileSelect, false);
} else {
  alert('The File APIs are not fully supported in this browser.');
}

function handleFileSelect(evt) {
  var f = evt.target.files[0]; // FileList object
  var reader = new FileReader();
  // Closure to capture the file information.
  reader.onload = (function(theFile) {
    return function(e) {
      var binaryData = e.target.result;
      //Converting Binary Data to base 64
      var base64String = window.btoa(binaryData);
      //showing file converted to base64
      document.getElementById('base64').value = base64String;
      alert('File converted to base64 successfuly!\nCheck in Textarea');
    };
  })(f);
  // Read in the image file as a data URL.
  reader.readAsBinaryString(f);
}

2. Parses the Base64 string from the JSON object.

3. Decodes the Base64 string to a Buffer.

This will return you the buffer format of this base64 object.

4. Convert the Buffer to an XLSX workbook.

Use that buffer object and convert it to XLSX workbook.

5. Convert workbook to XLSX sheet.

This extracting the name of the first sheet from an Excel workbook. Let me break it down:

1. workbook: This variable presumably holds an instance of an Excel workbook. In the context of the previous examples, it would be created using the xlsx or exceljs library.
2. workbook.SheetNames: This property provides an array of sheet names in the workbook.
3. [0]: This index [0] is accessing the first element in the array, effectively retrieving the name of the first sheet.

6. Sheet into a JSON object.

“jsonData” will return the data that is read from raw by raw in a .xslx formatted file as a JSON object. Using that object you may have access to the data to store in DB.

Hope this will help you. As usual, please feel free to leave a comment with your thoughts! Until we meet again.