Excel Data Import Process

Setting Up the Import Route

Create a route handler for data upload functionality:

const express = require('express');
const router = express.Router();

router.get('/upload', (request, response, next) => {
  response.send('Data upload endpoint');
});

module.exports = router;

Installing Required Dependencies

Install the Excel parsing library:

npm install node-xlsx --save

Reading Excel File Data

Implement file reading functionality:

const xlsx = require('node-xlsx');
const filePath = "/absolute/path/to/spreadsheet.xlsx";

router.get('/upload', (req, res) => {
  const fileData = xlsx.parse(filePath);
  res.json(fileData);
});

Extracting Relevant Data

Process the spreadsheet data structure:

router.get('/upload', (req, res) => {
  const spreadsheet = xlsx.parse(filePath)[0];
  const rawData = spreadsheet.data;
  res.json(rawData);
});

Transforming Data Structure

Convert spreadsheet rows into database-ready objects:

const uuid = require('uuid');

router.get('/upload', (req, res) => {
  const worksheet = xlsx.parse(filePath)[0];
  const rows = worksheet.data;
  const records = [];
  
  for (let i = 1; i < rows.length; i++) {
    records.push({
      userId: 'user_' + uuid.v4(),
      name: rows[i][0],
      gender: parseInt(rows[i][1]),
      userAge: parseInt(rows[i][2]),
      employer: rows[i][3],
      passcode: rows[i][4].toString(),
      courseLevel: parseInt(rows[i][5]),
      hometown: rows[i][6]
    });
  }
  
  res.json(records);
});

Database Insertion

Save transformed data to MongoDB:

router.get('/upload', async (req, res) => {
  const worksheet = xlsx.parse(filePath)[0];
  const rows = worksheet.data;
  const userRecords = [];
  
  for (let i = 1; i < rows.length; i++) {
    userRecords.push({
      userId: 'user_' + uuid.v4(),
      name: rows[i][0],
      gender: parseInt(rows[i][1]),
      userAge: parseInt(rows[i][2]),
      employer: rows[i][3],
      passcode: rows[i][4].toString(),
      courseLevel: parseInt(rows[i][5]),
      hometown: rows[i][6]
    });
  }
  
  try {
    await database.insert(UserModel, userRecords);
    res.redirect('/users');
  } catch (error) {
    res.status(500).send('Insertion failed');
  }
});

Frontend Integration

Add upload trigger in the interface:

<a href="/users/upload">
  <button class="btn btn-primary">Import Data</button>
</a>

User Authentication System

Admin Schema Design

Define administrator collection structure:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const adminSchema = new Schema({
  adminId: { type: String },
  username: { type: String },
  encryptedPassword: { type: String },
  permissionLevel: { type: Number }
});

module.exports = mongoose.model('Admin', adminSchema);

Password Security

Implement secure password hashing:

const bcrypt = require('bcryptjs');

const generateHash = (plainText) => {
  const saltRounds = 10;
  const salt = bcrypt.genSaltSync(saltRounds);
  return bcrypt.hashSync(plainText, salt);
};

const verifyPassword = (plainText, hash) => {
  return bcrypt.compareSync(plainText, hash);
};

Authentication Route

Handle login requests:

router.post('/authenticate', async (req, res) => {
  const { username, password } = req.body;
  
  try {
    const admin = await database.find(AdminModel, { username });
    
    if (admin.length === 0) {
      return res.redirect('/login');
    }
    
    const isValid = bcrypt.compareSync(password, admin[0].encryptedPassword);
    
    if (isValid) {
      req.session.authenticated = true;
      res.redirect('/dashboard');
    } else {
      res.redirect('/login');
    }
  } catch (error) {
    res.status(500).send('Authentication error');
  }
});

Session Management

Session Configuration

Set up session middleware:

const session = require('express-session');

app.use(session({
  secret: "session-secret-key",
  cookie: { maxAge: 30 * 60 * 1000 },
  resave: false,
  saveUninitialized: true
}));

Authentication Middleware

Protect routes with session validation:

app.use('*', (req, res, next) => {
  const publicRoutes = ['/login', '/authenticate'];
  
  if (publicRoutes.includes(req.path)) {
    return next();
  }
  
  if (req.session.authenticated) {
    next();
  } else {
    res.redirect('/login');
  }
});

Logout Functionality

Implement session termination:

router.get('/logout', (req, res) => {
  req.session.destroy();
  res.redirect('/login');
});

RESTful API Development

Pagination Implementation

Add pagination support too data base operations:

const paginate = (Model, filter, projection, itemsPerPage, currentPage) => {
  return new Promise((resolve, reject) => {
    Model.find(filter, projection)
      .limit(itemsPerPage)
      .skip(itemsPerPage * currentPage)
      .exec((error, data) => {
        if (error) reject(error);
        resolve(data);
      });
  });
};

API Endpoint with Pagination

Create paginated user listing endpoint:

router.get('/users', async (req, res) => {
  const pageSize = parseInt(req.query.limit) || 10;
  const currentPage = parseInt(req.query.page) || 0;
  
  try {
    const users = await paginate(UserModel, {}, { _id: 0 }, pageSize, currentPage);
    
    res.json({
      status: 200,
      message: 'Success',
      data: users
    });
  } catch (error) {
    res.status(500).json({
      status: 500,
      message: 'Server error'
    });
  }
});

Cross-Origin Resource Sharing (CORS)

CORS Middleware

Enable cross-domain requests:

const enableCORS = (req, res, next) => {
  res.header('Access-Control-Allow-Origin', '*');
  res.header('Access-Control-Allow-Headers', 'Content-Type');
  next();
};

app.use(enableCORS);

Frontend API Consumption

Client-side data retrieval:

$.ajax({
  url: 'http://localhost:3000/users',
  method: 'GET',
  success: function(response) {
    console.log('Data received:', response.data);
  }
});