I have created an HTML form that takes in user data and using exceljs and a few other libraries I store this data into an excel spreadsheet. This part of my code works perfectly fine. However, when I refresh the server, the data stored in the excel file previously gets deleted.

I believe the reason is that I could not save the excel workbook....but when I searched up for some code to achieve this and executed it...the code didn't work.

Here is the code that collects user data into the excel worksheet:

//importing necessary libraries
const express = require("express");
const morgan = require("morgan");
const Prohairesis = require("prohairesis");
const bodyParser = require("body-parser");
const Excel = require("exceljs");

const app  = express();
const port = process.env.PORT || 8081;

var info = []

app
    .use(express.static('public'))
    .use(morgan('dev'))
    .use(bodyParser.urlencoded({extended: false}))
    .use(bodyParser.json())

    .post('/api/user', (req, res) => {

        res.json(req.body);

        //collecting user data into a javascript string 
        const user = req.body;
        const ud = JSON.stringify(user);
        const user_data = JSON.parse(ud);
        console.log(user_data);
        const user_li = [user_data.first, user_data.email, user_data.stdid, user_data.pwd, user_data.cpwd];
        console.log(user_li);

        //some simple validation
        for (i in user_li)
        {
            if (user_data.pwd != user_data.cpwd)
            {
                console.log("**Password does not match**");
                break;
            }
            if (user_data.pwd == user_data.cpwd)
            {
                info.push(user_li);
                console.log(info);
               
                //append row to excel worksheet
                const workbook = new Excel.Workbook();
                const worksheet = workbook.addWorksheet('Main Db');
                worksheet.addRows(info);
                workbook.xlsx.writeFile("Login-Db.xlsx")
                console.log('Row has been appended');
                break;
            }
        }

    })

    .listen(port, () => console.log('Server listening on port ${port}'));

CodePudding user response：

Your code is working fine. The issue is, each time your server is restarted, you are recreating a new excel file with same name.

You have to implement some extra logic to check if the file already exists in the server. If so, then don't create the file, just append the new row to the existing file otherwise create a new file and save the data.

I have added below working example:

//importing necessary libraries
const express = require("express");
const morgan = require("morgan");
const Prohairesis = require("prohairesis");
const bodyParser = require("body-parser");
const Excel = require("exceljs");
const fs = require("fs");

const app = express();
const port = process.env.PORT || 4444;

let info = [];

app
  .use(express.static("public"))
  .use(morgan("dev"))
  .use(bodyParser.urlencoded({ extended: false }))
  .use(bodyParser.json())

  .post("/api/user", async (req, res) => {
    res.json(req.body);

    //collecting user data into a javascript string
    const user = req.body;
    const ud = JSON.stringify(user);
    const user_data = JSON.parse(ud);
    console.log(user_data);
    const user_li = [
      user_data.first,
      user_data.email,
      user_data.stdid,
      user_data.pwd,
      user_data.cpwd,
    ];
    console.log(user_li);

    //some simple validation
    for (i in user_li) {
      if (user_data.pwd != user_data.cpwd) {
        console.log("**Password does not match**");
        break;
      }
      if (user_data.pwd == user_data.cpwd) {
        info.push(user_li);
        console.log(info);

        //append row to excel worksheet
        const workbook = new Excel.Workbook();
        // for safety
        try {
          // check if `Login-Db.xlsx` file exists
          if (fs.existsSync("Login-Db.xlsx")) {
            // load existing workbook
            workbook.xlsx.readFile("Login-Db.xlsx").then((workbook) => {
              // get worksheet
              const worksheet = workbook.getWorksheet("Main Db");
              // append rows to worksheet
              worksheet.addRows(info);
              // save workbook
              workbook.xlsx.writeFile("Login-Db.xlsx").then((err) => {
                if (!err) {
                  console.log("Row added to excel file");
                  return;
                }
                // if error, print it
                console.log(err);
              });
            });
          } else {
            // create new worksheet
            const worksheet = workbook.addWorksheet("Main Db");
            // add new rows to worksheet
            worksheet.addRows(info);
            // save workbook
            workbook.xlsx.writeFile("Login-Db.xlsx").then((err) => {
              if (!err) {
                console.log("Row added to excel file");
                return;
              }
              // if error, print it
              console.log(err);
            });
          }
        } catch (error) {
          console.log(error);
        }
        break;
      }
    }
  })

  .listen(port, () => console.log(`Server listening on port ${port}`));

Notes (extra):

• Your row is actually appened when you're doing worksheet.addRows(info);

• use a try/catch before checking the with fs.

• In the docs, it's mentioned that writing to a xlsx file is asynchronous hence await should be used. So use async/await to log the result after safely saving file.

// using async/await

.post("/api/user", async (req, res) => {
  // other code
  await workbook.xlsx.writeFile("Login-Db.xlsx");
}

// or using .then()

workbook.xlsx.writeFile("Login-Db.xlsx").then(() => {
  console.log("File has been written");
});