Process Spreadsheet Data with SheetJS
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);