xlsx.js

import Excel from 'exceljs'
import saveAs from 'file-saver'

const readFileAsync = (file) => {
  return new Promise((resolve, reject) => {
    let reader = new FileReader()
    reader.onload = () => {
      resolve(reader.result)
    }
    reader.onerror = reject
    reader.readAsArrayBuffer(file)
  })
}

export const lettersToNumber = (val) => {
  const base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  let i = 0
  let j = 0
  let result = 0

  for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
    result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1)
  }

  return result
}

export const parseAddress = (address) => {
  return {
    col: parseInt(lettersToNumber(address.replace(/[0-9]/gm, ''))),
    row: parseInt(address.replace(/[a-zA-Z]/gm, '')),
  }
}

export const readExcelFile = async (file) => {
  const workbook = new Excel.Workbook()
  const data = await readFileAsync(file)

  return workbook.xlsx.load(data)
}

export const parseData = async (file) => {
  const w = await readExcelFile(file)

  const ws = w.worksheets[0]
  let rows = []
  let columns = []

  ws.getRow(1).eachCell((c) =>
    columns.push({
      title: c.value,
      name: c.value.toLocaleLowerCase(),
    }),
  )

  ws.eachRow((r, ri) => {
    if (ri > 1) {
      let row = {}
      columns.forEach((c, i) => {
        row[c.name] = r.getCell(i + 1).value
      })
      row.id = r.getCell(1).value
      rows.push(row)
    }
  })

  return {
    columns,
    rows,
  }
}

export const downloadExcel = async (rows) => {
  const wb = new Excel.Workbook()
  const ws = wb.addWorksheet('Price breakdown')

  wb.creator = 'Urkla tech'
  wb.lastModifiedBy = 'Urkla tech'
  wb.created = new Date()
  wb.modified = new Date()
  wb.lastPrinted = new Date()

  ws.columns = [
    { header: 'ID', key: 'id', width: 6 },
    { header: 'Name', key: 'name', width: 60 },
    { header: 'Quantity', key: 'quantity', width: 10 },
    { header: 'Price', key: 'price', width: 10 },
    { header: 'Unit', key: 'unit', width: 10 },
  ]
  ws.addRows(rows.map(({ ...r }) => r))
  ws.getRow(1).eachCell((c) => {
    c.style = {
      font: {
        bold: true,
      },
    }
  })
  const data = await wb.xlsx.writeBuffer()

  const blob = new Blob([data], {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  })

  saveAs(blob, 'Pricebreakdown.xlsx')
}