Excel file conversion on front-end and upload to Node server

I was working on a requirement for excel file upload to a node.js server but the problem was all the excel files has to be converted into xlsx extensions before uploading to the server, I struggled a lot for getting the solution for the problem so I decided to post my solution here,here we will take example of xls to xlsx conversion on front-end and uploading it to server, so lets get out hands dirty with some code.

Start Node.js Project

We will assume you know how to initialize a node project, lets have a look at package.json.

{
         "name": "xlstoxlsxconverter-blob",
         "version": "1.0.0",
         "description": "excel file conversion and upload",
         "main": "index.js",
         "dependencies": {
              "express": "^4.17.1",
              "formidable": "^1.2.1",
              "mv": "^2.1.1"
         }
}

Install the dependencies from package.json above using command

npm install

Server Side code

Now lets move on to write the server side code, we will use express framework for running our server and formidable for form parsing, in the below code there are two endpoints get is for loading the html page and post is for handling the file which will be posted from the UI, in this case file will be sent as a blob and will be written on the same folder as index.js is available which can be changed as per requirements

const express = require('express')
const app = express()
var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
const port = 3000
var formidable = require('formidable');
var mv = require('mv');
app.get('/', (req, res) => res.sendFile(__dirname +'/index.html'))
app.post('/upload',(req,res) => {
     var form = new formidable.IncomingForm();
     form.parse(req, function (err, fields, files) {
     var oldpath = files.filetoupload.path;
     var newpath = __dirname + "/" + files.filetoupload.name;
     mv(oldpath, newpath, function (err) {
          if (err) throw err;
          res.write('File uploaded and moved!');
          res.end();
          });
     });
});
app.listen(port, () => console.log(`App listening on ${port}!`))

Client side code

Now let us write the front-end code where the actual magic is happening, create a form having file field, you can apply some checks in it to take only xls files.

<p>Please select an Excel file from your computer:</p>
<input type="file" id="file-object">
<div id="result"></div>

For handling excel files we will use the SheetJS js-xlsx library and for handling upload we will include JQuery and AJAX, include them as follows

<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script><script src= "https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

Now to handle the file upload part we will start writing a function s2ab which will convert string to array buffer:

function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i=0; i!=s.length; ++i)
         view[i] = s.charCodeAt(i) & 0xFF;
         return buf;
}

As soon as the file will be changed, we will read the contents of the file using FileReader which will convert the file to binary for XLSX to read it, then we will convert the file from xls to xlsx using write method by XLSX library as shown in the below code, then convert it to blob using s2ab method defined above and post it to /upload method as shown in the code below.

document.getElementById('file-object').addEventListener("change",
  function(e) {
     var files = e.target.files,file;
     if (!files || files.length == 0) return;
     file = files[0];
     var fileReader = new FileReader();
     fileReader.onload = function (e) {
       var filename = file.name;
       var binary = "";
       var bytes = new Uint8Array(e.target.result);
       var length = bytes.byteLength;
       for (var i = 0; i < length; i++) {
         binary += String.fromCharCode(bytes[i]);
       }
      var workbook = XLSX.read(binary, {type: 'binary', cellDates:true, cellStyles:true});
      var wopts = { bookType:'xlsx', bookSST:false, type:'base64' };
      var wbout = XLSX.write(workbook,wopts);
      var blob = new Blob([s2ab(atob(wbout))],{type: 'application/octet-stream'});
      var formData = new FormData();
      formData.append('filetoupload', blob, 'test.xlsx');
      $.ajax({
          url: '/upload',
          type: 'POST',
          data: formData,
          success:function(data){
             console.log(data);
          },
          cache: false,
          contentType: false,
          processData: false
        });
      };
      fileReader.readAsArrayBuffer(file);
});

Now run the code and make the browser point to localhost:3000 and try to upload a xls file, it will be converted to xlsx and will be saved to the server as test.xlsx , you can save this file to any other destination as required.

Conclusion

This code can be used for front-end conversions of excel file, other applications could be in downloading the file after conversion to xlsx, other file types like csv, xlsm conversions can also be done similarly. The complete code for above can be found in this repository. In case of any issue respond below or create an issue in the linked GitHub repository.