Efficient Excel File Reading in Node.js with REST API Integration

In modern web applications, it is common to upload files from the frontend, such as Angular, to a backend REST API built with Node.js. One frequent requirement is to read Excel files and process their data, for example, to insert rows into a database. This article explores efficient methods to read Excel files in Node.js, focusing on popular libraries and their integration into a REST API.

Recommended Libraries for Excel File Reading

Several Node.js libraries facilitate reading Excel files. Based on community feedback and performance, two highly recommended libraries are node-xlsx and ExcelJS. node-xlsx is a wrapper around the robust JS-XLSX library, which implements the Office Open XML specification in pure JavaScript. ExcelJS offers extensive features for reading, manipulating, and writing spreadsheet data, with active development and a comprehensive API.

Using the node-xlsx Library

To use node-xlsx, first install it via npm: npm install node-xlsx. Then, in your Node.js code, require the module and parse the Excel file. The parse method can accept a file path or a buffer. For instance, if the file is uploaded via a REST API, you might read it from the request object. Here's a code example:

const xlsx = require('node-xlsx');
const fs = require('fs');

// Assuming the file is saved temporarily or available as a buffer
const fileBuffer = fs.readFileSync('/path/to/uploaded/file.xlsx');
const workbook = xlsx.parse(fileBuffer); // Parses the buffer into a workbook object

// workbook is an array of sheets, each containing data
workbook.forEach(sheet => {
  const sheetName = sheet.name;
  const data = sheet.data; // Array of rows, each row is an array of cells
  // Process data, e.g., convert to JSON and insert into database
  console.log(`Sheet: ${sheetName}, Data:`, data);
});

This code reads the Excel file and converts it into a structured object. You can then iterate through the sheets and rows to extract data for database insertion.

Using the ExcelJS Library

ExcelJS is another powerful option. Install it with npm install exceljs. It supports reading from files or streams, making it suitable for handling uploaded files. Example code:

const Excel = require('exceljs');
const workbook = new Excel.Workbook();

// Read from a file
workbook.xlsx.readFile('/path/to/uploaded/file.xlsx')
  .then(() => {
    workbook.eachSheet((sheet, sheetId) => {
      sheet.eachRow((row, rowNumber) => {
        const rowData = row.values; // Array of cell values
        // Process each row, e.g., convert to object and save to database
        console.log(`Row ${rowNumber}:`, rowData);
      });
    });
  })
  .catch(error => {
    console.error('Error reading Excel file:', error);
  });

ExcelJS provides promises for asynchronous operations, which is beneficial for non-blocking code in Node.js applications.

Integrating with REST API

In the context of the user's question, where a file is uploaded from Angular to a Node.js REST API, you need to handle the file in the POST endpoint. Using a middleware like multer for file uploads in Express.js can simplify this. First, set up multer to handle file uploads, then use one of the libraries to read the Excel file. Modified code for the API route:

const express = require('express');
const multer = require('multer');
const xlsx = require('node-xlsx'); // or use ExcelJS

const router = express.Router();
const upload = multer({ dest: 'uploads/' }); // Temporary storage for uploaded files

router.route('/api/uploadCompetence')
  .post(upload.single('file'), function (req, res) {
    // req.file contains the uploaded file info
    const filePath = req.file.path;

    // Read the Excel file using node-xlsx
    const workbook = xlsx.parse(filePath);
    let allData = [];

    workbook.forEach(sheet => {
      const data = sheet.data;
      data.forEach(row => {
        // Assuming each row is an array, convert to object if needed
        const rowObject = { /* map row cells to object properties */ };
        allData.push(rowObject);
      });
    });

    // Now, insert allData into the database
    // Example using a Competence model (pseudo-code)
    allData.forEach(item => {
      Competence.build(item).save()
        .then(() => console.log('Inserted'))
        .catch(err => console.error(err));
    });

    res.json({ message: 'Excel data processed and inserted!' });
  });

This example uses multer to handle file uploads, reads the Excel file with node-xlsx, processes the data, and inserts it into the database. Error handling should be added for production use.

Additional Libraries and Considerations

Another popular library is the xlsx module (js-xlsx), which is the foundation for node-xlsx. It can be used directly for more control. Installation: npm install xlsx. Example:

const XLSX = require('xlsx');
const workbook = XLSX.readFile('/path/to/file.xlsx');
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet);
console.log(jsonData); // Array of objects representing rows

This approach directly converts a sheet to JSON, which can be convenient for database insertion.

Conclusion

Reading Excel files in Node.js is straightforward with libraries like node-xlsx, ExcelJS, and xlsx. Choosing the right library depends on factors such as ease of use, feature set, and performance. For REST API integration, handling file uploads with middleware and processing data asynchronously ensures efficient and scalable applications. By following the examples provided, developers can seamlessly integrate Excel file reading into their Node.js backends.