Reputation: 473

How to upload and read excel file in nodejs?

I want to build an API "/upload/excel" that will allow users to import an excel file and inside it after receiving an excel file, it will read its field and save it into database.

How to achieve this?

Upvotes: 1

Answers (2)

abderrezague mohamed

Reputation: 211

I am using multer for uploading the file and xlsx to process it and mongodb as a database (mongoose for the model):

Lead is the data model, you have to add the Excel sheet columns name. See mongoose documentation for more information

const express = require("express");
const multer = require("multer");
const connectDB = require("./config/db");
const Lead = require("./models/Lead");
const XLSX = require("xlsx");

const uploadXLSX = async (req, res, next) => {
  try {
    let path = req.file.path;
    var workbook = XLSX.readFile(path);
    var sheet_name_list = workbook.SheetNames;
    let jsonData = XLSX.utils.sheet_to_json(
      workbook.Sheets[sheet_name_list[0]]
    );
    if (jsonData.length === 0) {
      return res.status(400).json({
        success: false,
        message: "xml sheet has no data",
      });
    }
    let savedData = await Lead.create(jsonData);

    return res.status(201).json({
      success: true,
      message: savedData.length + " rows added to the database",
    });
  } catch (err) {
    return res.status(500).json({ success: false, message: err.message });
  }
};

var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, "uploads");
  },
  filename: function (req, file, cb) {
    cb(null, Date.now() + "-" + file.originalname);
  },
});

const upload = multer({ storage: storage });

app.post("/upload", upload.single("xlsx"), uploadXLSX);

const port = process.env.PORT || 5000;

const server = app.listen(port, () => {
  console.log("app running on port", port);
});

So from here when you make a call to localhost:5000/upload with postman see picture below