parse xlsx workbook

parseWorkbook.js

const xlsx = require('xlsx')

module.exports = function parseWorkbook(file) {
  const result = {}
  console.info(`Parsing workbook ${file}`)
  const wb = xlsx.readFile(file)
  if (!wb.SheetNames.length) throw new Error('Invalid workbook, no sheets available.')

  const extractDataWithHeaders = (data = {}) => {
    const result = []
    const headingKeys = Object.keys(data).filter(k => /^[A-Z]1$/g.test(k))
    const headings = headingKeys.map(h => [data[h].v.toLowerCase().split(' ').join('_'), h])
    // rows preceding the first will be our values, remove first row and sheet metadata
    const resultValueKeys = Object.keys(data).filter(
      k => !headingKeys.includes(k) && !k.startsWith('!')
    )
    const rowCount = new Set(resultValueKeys.map(value => value.replace(/[A-Z]/gi, '')))

    rowCount.forEach(row => {
      const rowItem = resultValueKeys.filter(value => value.endsWith(row))
      const rowResult = {}
      headings.forEach(([headingName, cell]) => {
        const currentCell = `${cell.slice(0, 1)}${rowItem[0].slice(1)}`
        const val =
          data[currentCell] !== undefined && data[currentCell].v !== undefined
            ? data[currentCell].v
            : data[currentCell] && data[currentCell].v === 0
            ? data[currentCell].v
            : null
        rowResult[headingName] = !val ? val : val.toString()
      })

      // push row results to our final result
      result.push(rowResult)
    })

    return result
  }

  for (const [sheet, values] of Object.entries(wb.Sheets)) {
    result[sheet.toLowerCase().split(' ').join('_')] = extractDataWithHeaders(values)
  }

  return result
}