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}'));
Advertisement
Answer
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 withfs
.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"); });