Complete Guide to Excel Data Import/Export with Node.js and ExcelJS

Abhiraj GhoshAbhiraj Ghosh
13 min read

Table of contents

Introduction

Handling Excel files is a common requirement in many business applications. Whether you need to import large datasets from Excel into your database or export query results to Excel for reporting, having a robust solution can save time and improve user experience.

In this comprehensive guide, we'll explore how to use Node.js, Express, and ExcelJS to create a powerful system for importing data from Excel files into a database and exporting database data back to Excel. We'll also cover advanced topics like data validation and custom dropdown implementation.

Setting Up Your Project

Let's start by setting up a basic Express application with the necessary dependencies:

// Initialize project
npm init -y

// Install required dependencies
npm install express exceljs multer mongoose

The main packages we'll be using are:

  • Express: For our web server and API endpoints

  • ExcelJS: For reading from and writing to Excel files

  • Multer: For handling file uploads

  • Mongoose: For database interactions with MongoDB

Basic Server Setup

// server.js
const express = require('express');
const mongoose = require('mongoose');
const multer = require('multer');
const path = require('path');

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

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));

// Connect to MongoDB
mongoose.connect('mongodb://localhost:27017/exceldb', {
  useNewUrlParser: true,
  useUnifiedTopology: true
})
.then(() => console.log('Connected to MongoDB'))
.catch(err => console.error('Could not connect to MongoDB', err));

// Routes will go here

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

Creating a Data Model

// models/Product.js
const mongoose = require('mongoose');

const productSchema = new mongoose.Schema({
  productId: {
    type: String,
    required: true,
    unique: true
  },
  name: {
    type: String,
    required: true
  },
  category: {
    type: String,
    required: true
  },
  price: {
    type: Number,
    required: true
  },
  inStock: {
    type: Boolean,
    default: true
  },
  lastUpdated: {
    type: Date,
    default: Date.now
  }
});

module.exports = mongoose.model('Product', productSchema);

Importing Data from Excel to Database

Let's create a route to handle Excel file uploads and import the data into our database.

Setting Up File Upload

First, we'll configure Multer to handle file uploads:


// Configure storage for uploaded files
const storage = multer.diskStorage({
  destination: function(req, file, cb) {
    cb(null, 'uploads/');
  },
  filename: function(req, file, cb) {
    cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
  }
});

// File filter to accept only Excel files
const fileFilter = (req, file, cb) => {
  if (
    file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' || 
    file.mimetype === 'application/vnd.ms-excel'
  ) {
    cb(null, true);
  } else {
    cb(new Error('Only Excel files are allowed!'), false);
  }
};

const upload = multer({ 
  storage: storage,
  fileFilter: fileFilter,
  limits: { fileSize: 1024 * 1024 * 5 } // 5MB file size limit
});

Creating the Import Endpoint

Now let's create the route that will handle the Excel file and process it:

// routes/import.js
const express = require('express');
const router = express.Router();
const ExcelJS = require('exceljs');
const Product = require('../models/Product');

// Import products from Excel
router.post('/import', upload.single('excelFile'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: 'Please upload an Excel file' });
    }

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(req.file.path);

    const worksheet = workbook.getWorksheet(1); // Get the first worksheet

    const products = [];
    const errors = [];

    // Skip header row and process each row
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      // Skip header row
      if (rowNumber > 1) {
        // Validate row data
        if (!row.getCell(1).value || !row.getCell(2).value) {
          errors.push(`Row ${rowNumber}: Missing required fields`);
          return;
        }

        // Create product object from row data
        const product = {
          productId: row.getCell(1).value.toString(),
          name: row.getCell(2).value.toString(),
          category: row.getCell(3).value.toString(),
          price: parseFloat(row.getCell(4).value) || 0,
          inStock: row.getCell(5).value === 'Yes' ? true : false
        };

        products.push(product);
      }
    });

    // If validation errors occurred
    if (errors.length > 0) {
      return res.status(400).json({ errors });
    }

    // Insert products into database
    await Product.insertMany(products, { ordered: false });

    res.status(200).json({ 
      message: `Successfully imported ${products.length} products` 
    });
  } catch (error) {
    // Handle duplicate key errors specially
    if (error.code === 11000) {
      return res.status(400).json({ 
        error: 'Duplicate product IDs found in the Excel file' 
      });
    }

    console.error('Import error:', error);
    res.status(500).json({ error: 'Failed to import data' });
  }
});

module.exports = router;

Implementing Data Validation

Let's enhance our import process to include more robust validation:


// Validate row data function
function validateRowData(row, rowNumber) {
  const errors = [];

  // Check required fields
  if (!row.getCell(1).value) {
    errors.push(`Row ${rowNumber}: Product ID is required`);
  }

  if (!row.getCell(2).value) {
    errors.push(`Row ${rowNumber}: Product name is required`);
  }

  // Validate price is a number
  const price = row.getCell(4).value;
  if (price && isNaN(parseFloat(price))) {
    errors.push(`Row ${rowNumber}: Price must be a number`);
  }

  // Validate category is from an allowed list
  const category = row.getCell(3).value?.toString();
  const allowedCategories = ['Electronics', 'Clothing', 'Food', 'Books', 'Other'];
  if (category && !allowedCategories.includes(category)) {
    errors.push(`Row ${rowNumber}: Category must be one of ${allowedCategories.join(', ')}`);
  }

  return errors;
}

// Then in your route handler:
worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
  // Skip header row
  if (rowNumber > 1) {
    // Validate row data
    const rowErrors = validateRowData(row, rowNumber);
    if (rowErrors.length > 0) {
      errors.push(...rowErrors);
      return;
    }

    // Process valid row...
  }
});

Exporting Data from Database to Excel

Now, let's implement the functionality to export data from the database to an Excel file.

Creating the Export Endpoint


// routes/export.js
const express = require('express');
const router = express.Router();
const ExcelJS = require('exceljs');
const Product = require('../models/Product');

// Export products to Excel
router.get('/export', async (req, res) => {
  try {
    // Query products from database
    // You can add filters here based on query parameters
    const products = await Product.find({});

    // Create a new workbook and worksheet
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Products');

    // Define columns
    worksheet.columns = [
      { header: 'Product ID', key: 'productId', width: 15 },
      { header: 'Name', key: 'name', width: 30 },
      { header: 'Category', key: 'category', width: 15 },
      { header: 'Price', key: 'price', width: 12 },
      { header: 'In Stock', key: 'inStock', width: 10 },
      { header: 'Last Updated', key: 'lastUpdated', width: 20 }
    ];

    // Style the header row
    worksheet.getRow(1).font = { bold: true, size: 12 };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF4F81BD' }
    };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    worksheet.getRow(1).font = { color: { argb: 'FFFFFFFF' } };

    // Add products to the worksheet
    products.forEach(product => {
      worksheet.addRow({
        productId: product.productId,
        name: product.name,
        category: product.category,
        price: product.price,
        inStock: product.inStock ? 'Yes' : 'No',
        lastUpdated: product.lastUpdated.toLocaleDateString()
      });
    });

    // Format the price column
    worksheet.getColumn('price').numFmt = '"$"#,##0.00';

    // Set response headers
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=products.xlsx'
    );

    // Write the workbook to the response
    await workbook.xlsx.write(res);
    res.end();
  } catch (error) {
    console.error('Export error:', error);
    res.status(500).json({ error: 'Failed to export data' });
  }
});

module.exports = router;

Adding Custom Styling to Excel Exports

Let's enhance our exports with more styling:

// Add alternating row colors
products.forEach((product, index) => {
  const row = worksheet.addRow({
    productId: product.productId,
    name: product.name,
    category: product.category,
    price: product.price,
    inStock: product.inStock ? 'Yes' : 'No',
    lastUpdated: product.lastUpdated.toLocaleDateString()
  });

  // Add alternating row colors
  if (index % 2) {
    row.eachCell(cell => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
  }

  // Highlight out-of-stock items in red
  if (!product.inStock) {
    const inStockCell = row.getCell(5);
    inStockCell.font = { color: { argb: 'FFFF0000' } };
  }
});

// Add border to all cells
worksheet.eachRow((row, rowNumber) => {
  row.eachCell(cell => {
    cell.border = {
      top: { style: 'thin' },
      left: { style: 'thin' },
      bottom: { style: 'thin' },
      right: { style: 'thin' }
    };
  });
});

Implementing Custom Dropdowns in Excel

One powerful feature of ExcelJS is the ability to add data validation like dropdowns. Let's implement this for our category column:

// Add data validation (dropdown) for category column
worksheet.getColumn('category').eachCell({ includeEmpty: false }, (cell, rowNumber) => {
  // Skip header row
  if (rowNumber > 1) {
    cell.dataValidation = {
      type: 'list',
      allowBlank: false,
      formulae: ['"Electronics,Clothing,Food,Books,Other"']
    };
  }
});

This will create a dropdown in each cell of the category column with predefined options.

Adding Conditional Formatting

We can also add conditional formatting to highlight certain values:


// Add conditional formatting to price column
worksheet.addConditionalFormatting({
  ref: 'D2:D1000', // Price column range
  rules: [
    {
      type: 'cellIs',
      operator: 'greaterThan',
      formulae: [100],
      style: {
        fill: {
          type: 'pattern',
          pattern: 'solid',
          bgColor: { argb: 'FFE2EFDA' }
        }
      }
    },
    {
      type: 'cellIs',
      operator: 'lessThan',
      formulae: [50],
      style: {
        fill: {
          type: 'pattern',
          pattern: 'solid',
          bgColor: { argb: 'FFFCE4D6' }
        }
      }
    }
  ]
});

Creating a Simple Frontend

Let's create a basic frontend for our application:


<!-- public/index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Excel Data Manager</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    .container { max-width: 800px; margin-top: 50px; }
    .card { margin-bottom: 30px; }
    .progress { display: none; }
  </style>
</head>
<body>
  <div class="container">
    <h1 class="text-center mb-4">Excel Data Manager</h1>

    <!-- Import Section -->
    <div class="card">
      <div class="card-header bg-primary text-white">
        <h5 class="mb-0">Import Data from Excel</h5>
      </div>
      <div class="card-body">
        <form id="importForm" enctype="multipart/form-data">
          <div class="mb-3">
            <label for="excelFile" class="form-label">Select Excel File</label>
            <input class="form-control" type="file" id="excelFile" name="excelFile" accept=".xlsx, .xls">
            <div class="form-text">Only Excel files (.xlsx, .xls) are accepted.</div>
          </div>
          <div class="progress mb-3">
            <div id="importProgress" class="progress-bar" role="progressbar" style="width: 0%"></div>
          </div>
          <button type="submit" class="btn btn-primary">Import Data</button>
        </form>
        <div id="importResult" class="mt-3"></div>
      </div>
    </div>

    <!-- Export Section -->
    <div class="card">
      <div class="card-header bg-success text-white">
        <h5 class="mb-0">Export Data to Excel</h5>
      </div>
      <div class="card-body">
        <p>Export all product data to an Excel file.</p>
        <a href="/api/export" class="btn btn-success">Export to Excel</a>
      </div>
    </div>
  </div>

  <script>
    document.getElementById('importForm').addEventListener('submit', async (e) => {
      e.preventDefault();

      const formData = new FormData(e.target);
      const resultDiv = document.getElementById('importResult');
      const progressBar = document.getElementById('importProgress');
      const progressContainer = document.querySelector('.progress');

      if (!formData.get('excelFile').name) {
        resultDiv.innerHTML = '<div class="alert alert-danger">Please select a file</div>';
        return;
      }

      // Show progress bar
      progressContainer.style.display = 'flex';
      progressBar.style.width = '0%';
      progressBar.innerText = '0%';

      try {
        // Simulate progress (in a real app, you'd use XMLHttpRequest with progress events)
        let progress = 0;
        const interval = setInterval(() => {
          progress += 5;
          if (progress > 90) clearInterval(interval);
          progressBar.style.width = `${progress}%`;
          progressBar.innerText = `${progress}%`;
        }, 100);

        const response = await fetch('/api/import', {
          method: 'POST',
          body: formData
        });

        clearInterval(interval);
        progressBar.style.width = '100%';
        progressBar.innerText = '100%';

        const result = await response.json();

        if (response.ok) {
          resultDiv.innerHTML = `<div class="alert alert-success">${result.message}</div>`;
        } else {
          if (result.errors) {
            const errorList = result.errors.map(err => `<li>${err}</li>`).join('');
            resultDiv.innerHTML = `
              <div class="alert alert-danger">
                <p>Import failed with the following errors:</p>
                <ul>${errorList}</ul>
              </div>
            `;
          } else {
            resultDiv.innerHTML = `<div class="alert alert-danger">${result.error || 'Import failed'}</div>`;
          }
        }
      } catch (error) {
        console.error('Import error:', error);
        resultDiv.innerHTML = '<div class="alert alert-danger">An unexpected error occurred</div>';
        progressBar.style.width = '0%';
      }
    });
  </script>
</body>
</html>

Full API Implementation

Let's tie everything together into a complete Express API:


// server.js
const express = require('express');
const mongoose = require('mongoose');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const ExcelJS = require('exceljs');
const Product = require('./models/Product');

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

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));

// Create uploads directory if it doesn't exist
const uploadsDir = path.join(__dirname, 'uploads');
if (!fs.existsSync(uploadsDir)) {
  fs.mkdirSync(uploadsDir);
}

// Configure multer
const storage = multer.diskStorage({
  destination: function(req, file, cb) {
    cb(null, 'uploads/');
  },
  filename: function(req, file, cb) {
    cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
  }
});

const fileFilter = (req, file, cb) => {
  if (
    file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' || 
    file.mimetype === 'application/vnd.ms-excel'
  ) {
    cb(null, true);
  } else {
    cb(new Error('Only Excel files are allowed!'), false);
  }
};

const upload = multer({ 
  storage: storage,
  fileFilter: fileFilter,
  limits: { fileSize: 1024 * 1024 * 5 } // 5MB file size limit
});

// Connect to MongoDB
mongoose.connect('mongodb://localhost:27017/exceldb', {
  useNewUrlParser: true,
  useUnifiedTopology: true
})
.then(() => console.log('Connected to MongoDB'))
.catch(err => console.error('Could not connect to MongoDB', err));

// Import route
app.post('/api/import', upload.single('excelFile'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: 'Please upload an Excel file' });
    }

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(req.file.path);

    const worksheet = workbook.getWorksheet(1);

    const products = [];
    const errors = [];

    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      if (rowNumber > 1) {
        const rowErrors = validateRowData(row, rowNumber);
        if (rowErrors.length > 0) {
          errors.push(...rowErrors);
          return;
        }

        const product = {
          productId: row.getCell(1).value.toString(),
          name: row.getCell(2).value.toString(),
          category: row.getCell(3).value.toString(),
          price: parseFloat(row.getCell(4).value) || 0,
          inStock: row.getCell(5).value === 'Yes' ? true : false
        };

        products.push(product);
      }
    });

    if (errors.length > 0) {
      return res.status(400).json({ errors });
    }

    // Clean up the uploaded file
    fs.unlinkSync(req.file.path);

    await Product.insertMany(products, { ordered: false });

    res.status(200).json({ 
      message: `Successfully imported ${products.length} products` 
    });
  } catch (error) {
    if (error.code === 11000) {
      return res.status(400).json({ 
        error: 'Duplicate product IDs found in the Excel file' 
      });
    }

    console.error('Import error:', error);
    res.status(500).json({ error: 'Failed to import data' });
  }
});

// Data validation function
function validateRowData(row, rowNumber) {
  const errors = [];

  if (!row.getCell(1).value) {
    errors.push(`Row ${rowNumber}: Product ID is required`);
  }

  if (!row.getCell(2).value) {
    errors.push(`Row ${rowNumber}: Product name is required`);
  }

  const price = row.getCell(4).value;
  if (price && isNaN(parseFloat(price))) {
    errors.push(`Row ${rowNumber}: Price must be a number`);
  }

  const category = row.getCell(3).value?.toString();
  const allowedCategories = ['Electronics', 'Clothing', 'Food', 'Books', 'Other'];
  if (category && !allowedCategories.includes(category)) {
    errors.push(`Row ${rowNumber}: Category must be one of ${allowedCategories.join(', ')}`);
  }

  return errors;
}

// Export route
app.get('/api/export', async (req, res) => {
  try {
    const products = await Product.find({});

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Products');

    worksheet.columns = [
      { header: 'Product ID', key: 'productId', width: 15 },
      { header: 'Name', key: 'name', width: 30 },
      { header: 'Category', key: 'category', width: 15 },
      { header: 'Price', key: 'price', width: 12 },
      { header: 'In Stock', key: 'inStock', width: 10 },
      { header: 'Last Updated', key: 'lastUpdated', width: 20 }
    ];

    // Style the header row
    worksheet.getRow(1).font = { bold: true, size: 12 };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF4F81BD' }
    };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    worksheet.getRow(1).font = { color: { argb: 'FFFFFFFF' } };

    // Add products with styling
    products.forEach((product, index) => {
      const row = worksheet.addRow({
        productId: product.productId,
        name: product.name,
        category: product.category,
        price: product.price,
        inStock: product.inStock ? 'Yes' : 'No',
        lastUpdated: product.lastUpdated.toLocaleDateString()
      });

      // Add alternating row colors
      if (index % 2) {
        row.eachCell(cell => {
          cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFF2F2F2' }
          };
        });
      }

      // Highlight out-of-stock items in red
      if (!product.inStock) {
        const inStockCell = row.getCell(5);
        inStockCell.font = { color: { argb: 'FFFF0000' } };
      }
    });

    // Format the price column
    worksheet.getColumn('price').numFmt = '"$"#,##0.00';

    // Add data validation (dropdown) for category column
    worksheet.getColumn('category').eachCell({ includeEmpty: false }, (cell, rowNumber) => {
      if (rowNumber > 1) {
        cell.dataValidation = {
          type: 'list',
          allowBlank: false,
          formulae: ['"Electronics,Clothing,Food,Books,Other"']
        };
      }
    });

    // Add border to all cells
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell(cell => {
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };
      });
    });

    // Set response headers
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=products.xlsx'
    );

    // Write the workbook to the response
    await workbook.xlsx.write(res);
    res.end();
  } catch (error) {
    console.error('Export error:', error);
    res.status(500).json({ error: 'Failed to export data' });
  }
});

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

Best Practices and Tips

Error Handling

Always implement robust error handling for both import and export operations:

  1. Validate before processing: Check file format, size, and content before processing.

  2. Provide clear error messages: When validation fails, show users exactly what went wrong.

  3. Handle special cases: Duplicate keys, formatting issues, and other database constraints.

Performance Considerations

When dealing with large Excel files, consider these optimizations:

  1. Stream processing: For very large files, use streaming to process row by row.

  2. Batch operations: Use bulk inserts instead of individual document saves.

  3. Progress indicators: Implement a progress bar for long-running operations.

     // Stream processing example
 let rowCount = 0;
 const batchSize = 100;
 let batch = [];

 workbook.xlsx.createInputStream(req.file.path)
   .on('worksheet', worksheet => {
     worksheet.on('row', row => {
       rowCount++;

       // Skip header
       if (rowCount === 1) return;

       // Process and validate row
       const product = {
         productId: row.getCell(1).value.toString(),
         name: row.getCell(2).value.toString(),
         // ... other fields
       };

       batch.push(product);

       // Process in batches
       if (batch.length >= batchSize) {
         Product.insertMany(batch)
           .then(() => console.log(`Inserted batch of ${batch.length} products`))
           .catch(err => console.error('Batch insert error', err));

         batch = []; // Reset batch
       }
     });
   })
   .on('end', () => {
     // Insert any remaining products
     if (batch.length > 0) {
       Product.insertMany(batch)
         .then(() => console.log(`Inserted final batch of ${batch.length} products`))
         .catch(err => console.error('Final batch insert error', err));
     }

     console.log(`Processed ${rowCount-1} products total`);
   })
   .on('error', err => {
     console.error('Stream processing error', err);
   });

Conclusion

In this comprehensive guide, we've explored how to implement Excel data importing and exporting functionality in a Node.js application using Express and ExcelJS. We've covered everything from setting up the project to implementing advanced features like data validation and custom dropdowns.

The techniques shown here can be adapted to handle various business requirements for data processing and reporting. By leveraging the powerful features of ExcelJS, you can create professional-looking reports with advanced formatting, validations, and styling.

Remember to consider performance optimization techniques when working with large datasets, and always implement proper error handling to provide a good user experience.

I hope this guide helps you implement robust Excel data processing in your Node.js applications!

Additional Resources

Feel free to leave comments or questions below, and happy coding!

0
Subscribe to my newsletter

Read articles from Abhiraj Ghosh directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Node.js Excel import exportExcelJS tutorialExcel data validation Node.jsExpress Excel downloadMongoDB Excel integration

Written by

Abhiraj Ghosh
Abhiraj Ghosh