avatar

ShinChven's Blog

Read Large XLSX File with xlsx-extract

Tue Nov 15 2022

Excel file can be large, reading or opening a large xlsx file consumes a large amount of memory, it can be slow, and it may also cause Out of Memory Error.

To read a large xlsx file more efficiently, I used the xlsx-extract package in my projects. It reads the xlsx file and processes data in a stream, row by row and cell by cell, it solves the memory issue swiftly.

Here's a digest of how I used it in my project:

import {XLSX} from 'xlsx-extract';

type UserModel = {
  id: number;
  name: string;
  phone: string;
}

/**
 * Read XLSX file and return an array of orders
 * @param filePath
 */
export const readXLS = async (filePath: string): Promise<UserModel[]> => {
  // 1. Create an array to contain data
  const users = new Array<UserModel>();

  return new Promise((resolve, reject) => {
    const xlsx = new XLSX();
    let rowIndex = 0;
    xlsx.extract(filePath, {sheet_id: '1'})
      .on('row', function (row: any[]) {
        if (rowIndex > 0) { // Skip header, optional
          // 2. Process data: Convert row to an object
          const user = {
            id: row[0],
            name: row[1],
            phone: row[2],
          }
          // 3. Add the object to the array
          users.push(user);
        }
        rowIndex++;
      })
      .on('end', () => {
        // return data in promise
        resolve(users);
      })
      .on('error', (err: Error) => {
        reject(err);
      });
  });
};

(async () => {
  const users = await readXLS('users.xlsx');
  console.log(users);
})();