Parsing Excel file into JSON with JavaScript XLSX library

readExcelFile.js

import XLSX from 'xlsx';

const readExcelFile = async (filename) => {
  const workbook = XLSX.readFile(filename);
  return workbook.SheetNames.reduce((acc, name) => {
    acc[name] = XLSX.utils.sheet_to_json(workbook.Sheets[name]);
    return acc;
  }, {});
};

readExcelFileOptions.js

// Syntax of mapping:
//
//  {
//    DIRECTION: {
//      ...
//      SHEET: {
//        [name: NEW_SHEET],
//        columns: {
//          COLUMN: [NEW_COLUMN, [, CALLBACK]],
//          ...
//        }
//      },
//      ...
//    }
//  }
//
//  DIRECTION: 'input' | 'output'
//  - Direction of parsing
//
//  SHEET: string
//  - Name of a sheet
//
//  NEW_SHEET: string
//  - New name of a sheet
//
//  COLUMN: string
//  - Name of a column
//
//  NEW_COLUMN: string
//  - New name of a column
//
//  CALLBACK: (cell) => result
//  - Callback to be performed on a cell value; it returns a processed result

const readExcelFileOptions = async (filename, direction, mapping) => {
  const lookupCell = (value, header, table) => {
    if (table === undefined) {
      return { header, result: value };
    }

    const lookupResult = table.columns[header];

    if (lookupResult === undefined) {
      return { header: undefined, result: undefined };
    }

    return {
      header: lookupResult[0],
      result: (lookupResult[1] && lookupResult[1](value)) || value,
    };
  };

  // cellDates flag parses date as is when set to true
  const workbook = XLSX.readFile(filename, { cellDates: true );
  const sheetNames = workbook.SheetNames;
  const dirOptions = mapping[direction];

  if (dirOptions === undefined) {
    throw new Error(`Invalid direction: ${direction}`);
  }

  return sheetNames.reduce((acc, name) => {
    const worksheet = workbook.Sheets[name];
    const sheetNameLookup = dirOptions[name];
    const headers = {};
    const data = [];

    // cell === alphabetical column + numerical row
    // Ex. A1
    Object.keys(worksheet).forEach((cell) => {
      if (cell[0] === "!") return;

      const col = cell.substring(0, 1);
      const row = parseInt(cell.substring(1), 10);
      const value = worksheet[cell].v;

      // store header names
      if (row === 1 && value) {
        headers[col] = value;
        return;
      }

      if (!data[row]) data[row] = {};
      const { header, result } = lookupCell(
        value,
        headers[col],
        sheetNameLookup
      );

      if (header !== undefined) data[row][header] = result;
    });

    // drop those first two rows which are empty
    data.shift();
    data.shift();

    const sheetName = (sheetNameLookup && sheetNameLookup.name) || name;
    acc[sheetName] = data;
    return acc;
  }, {});
};

Usage:

// readExcelFile
const main = async () => {
  const excelJson = await readExcelFile(/* filename */);
  process.stdout.write(JSON.stringify(excelJson));
};

// readExcelFileOptions
import mapping from './excel-mapping';

const main = async () => {
  const excelJson = await readExcelFileOptions(/* filename */, 'input', mapping);
  // simplest mapping:
  // const excelJson = await readExcelFileOptions(/* filename */, 'input', { input: {} });
  // const excelJson = await readExcelFileOptions(/* filename */, 'output', { output: {} });
  process.stdout.write(JSON.stringify(excelJson));
};

main();