Convert Excel xlsx spreadsheet to JSON list of objects, allowing dotted strings as keys to imply nested objects.

xlsxToJson.coffee

###
 Create a list of json objects; 1 object per excel sheet row

 Assume: Excel spreadsheet is a square of data, where the first row is 
 object keys and remaining rows are object values and the desired json 
 is a list of objects.

 Dotted notation: Key row (0) containing firstName, lastName, address.street, 
 address.city, address.state, address.zip would produce, per row, a doc with 
 first and last names and an embedded doc named address, with the address.

 USE:
  clone
  npm install
  coffee xlsxToJson.coffee

###
fs = require "fs"
excel = require 'excel'


# Assign values to dotted property names - set values on sub-objects
assign = (obj, key, value) ->
  # On first call, a key is a string. Recursed calls, a key is an array
  key = key.split '.' unless typeof key is 'object'

  if key.length > 1
    e = key.shift()
    obj[e] ?= {}
    assign obj[e], key, value
  else
    obj[key[0]] = value


# Convert 2D array to nested objects.
convert = (data) ->
  keys = data[0]
  rows = data[1..]

  result = []
  for row in rows
    item = {}
    assign(item, keys[index], value) for value, index in row
    result.push item
  return result


# Write object hierarchy as JSON to file
write = (data, dst) ->
  fs.writeFile dst, JSON.stringify(data, null, 2), (err) ->
    if err then console.error("Error writing file #{dst}", err)
    else console.log "Updated #{dst}"


# The excel module reads sheet 0 from specified xlsx file
process = (src, dst) ->
  excel src, (err, data) ->
    if err then console.error "Error reading #{src}", err
    else write convert(data), dst


process './data/input.xlsx', './build/output.json'