avatar

ShinChven's Blog

Process Spreadsheet Data with SheetJS

Mon Nov 21 2022

SheetJS, aka the xlsx package for JavaScript, is probably the most commonly used sheet processing library.

Compared to ExcelJS, it supports more data formats like the old xls and can be used across multiple platforms.

Access Spreadsheet Data

To access spreadsheet data with SheetJS, you need to first read the sheet data from byte[] or file, then convert it to JSON format using the provided utils XLSX.utils.sheettojson;

Here's an example of how I do it:

import XLSX from 'xlsx';

const filepath = "path/to/file.xlsx";
await fs.access(filepath, fs.constants.R_OK);

// Read workbook from file
const workbook = XLSX.readFile(filepath);
// Access sheet in a workbook
const [sheetName] = workbook.SheetNames;
const sheet = workbook.Sheets[sheetName];
// Convert sheet data to json, which is an array of arrays.
const rows = XLSX.utils.sheet_to_json(sheet, {header: 1});
const customers: Array<Partial<CustomerModel>> = [];
// Access data from each row
for (let i = 0; i < rows.length; i++) {
  if (i > 0) { // Skip sheet header row
    // Row data to object.
    const row = rows[i] as any[];
    const customer = {
      code: row[0],
      name: row[1],
      gender: row[2],
    };
    customers.push(customer);
  }
}
console.log(customers);