How to read sheet data of uploaded file with using sheetJS and node?

Question

So the file uploaded is an excel file that sheetJS needs to read, otherwise it will show as {}.

app.post('/sendExcel', function(req, res) {
    let data = req.body;
    var workbook = sheetJS.read(data, {type: 'buffer'});
    console.log(workbook.Sheets['Sheet1); //prints... "{ A1: { t: 's', v: '[object Object]' }, '!ref': 'A1' }"
    let excel = workbook.Sheets['Sheet1']['A1']['v'][0]; //prints... "["

So I've tried various things including changing the type client side as I had problems with it being of type buffer. So now it works partially, but I still can't access the data in the sheet.

As an example, I used the file path instead here, and it's shown to work as normal.

app.get('/excel', function(err, res, data) {
    var wb = sheetJS.readFile("data.xlsx");
    let excel = wb.Sheets['Sheet1']['A1']['v'];
    console.log(excel); //this prints "vehicle", which is what is supposed to happen, not "[".
      res.send(excel)
});

I am supposed to get the excel data from the form upload. That's the issue. It is is now successful when sending to the db, but will not access the whole data. I believe I need to change it back to an array.

You can use:

var fileReader = new FileReader();
fileReader.readAsArrayBuffer(workbook);

But this will not run in app.js

Here is my other answer with client-side and server-side. It might be helpful to others.

Javascript Read Excel file on server with SheetJS

@KunalMukherjee hi kunal, I have just found a solution for this but thank you for commenting anyway! I was really stuck on this — imatwork, Oct 14 '20 at 14:17

imatwork · Accepted Answer · 2021-01-07T01:55:01.973

Don't use the file reader. Append the excel sheet to the form in the body normally.

Client side:

let excelInput = document.getElementById("fileToUpload"); 
//excelInput: this html element allows you to upload the excel sheet to it
let excelFile = excelInput.files[0];
  let form = new FormData();
  form.append("excel", excelFile);

  fetch('/sendExcel', {method: "POST", body: form})
  .then((data) => {
    console.log('Success:', data);
  })
  .catch((error) => {
    console.error('Error:', error);
  });

Then use formidable server side.

Server side:

const sheetJS  = require('xlsx');
const formidable = require('formidable');
app.post('/excel', function(req, res) {
let data = req.body;
const form = formidable({ multiples: true });
  form.parse(req, (err, fields, files, next) => {
    if (err) {
      next(err);
      return;
    }

    var f = files[Object.keys(files)[0]];
    var workbook = sheetJS.readFile(f.path);
    res.send(workbook);

  });
});

So formidable has to be used otherwise it won't work. Then you can use sheetJS.readFile instead of sheetJS.read.

How to read sheet data of uploaded file with using sheetJS and node?

1 Answers1