Convert from Excel to JSON

gistfile1.md

Convert from Excel to JSON

roo document: roo

require 'rubygems'
require 'roo'
require 'json'

hash = {}
# oo = Roo::Excelx.new('post_prod.xlsx')
oo = Roo::Openoffice.new("post_prod.ods")

oo.default_sheet = oo.sheets[2]  
hash_child = {}
1.upto(4) do |line|
  next if oo.cell(line,'A').nil?
    
  date    = oo.cell(line,'A')
  start_time = oo.cell(line,'B')
  end_time   = oo.cell(line,'C')
  pause      = oo.cell(line,'D')
  comment    = oo.cell(line,'F')
	
  hash_child["#{date}"] = start_time
  hash = hash_child
end

item = {}
test_a = %w{A B C D E F G H I}
8.upto(15) do |line| next if oo.cell(line, 'A').nil?            
	h = {}
	test_a.each do |a|
	  h["#{oo.cell(7, a)}"] = oo.cell(line, a)
  end
  item["project_#{line}"] = h  
end	
hash["items"] = item

puts hash.to_json

The result will be looked like(ugly)

{"Title:":"Editorial","Output File:":"editorial.xml","Copy:":"Deluxe's unmatched depth of film editing talent and leading-edge technologies help bring visions to to life for the film, TV, and avertising industries, services are part of an integrated, end-to-end suite of \"creative services\".","items":{"project_8":{"Project Title":"1Kia Space Babies","Primary Company":"Method Studios1","Master Copy":"Deluxe's unmatched depth of film editing talent and leading-edge technologies help bring visions to to life for the film, TV, and avertising industries, services are part of an integrated, end-to-end suite of \"creative services\".","Media Item 1 Path":"some_image.png","Media Item 1 Copy":"my name","Media Item 2 Path":"some_movie.mp4","Media Item 2 Copy":null,"Media Item 3 Path":"some_other_image.png","Media Item 3 Copy":124124.0},"project_9":{"Project Title":"2Kia Space Babies","Primary Company":"Method Studios2","Master Copy":"Deluxe's unmatched depth of film editing talent and leading-edge technologies help bring visions to to life for the film, TV, and avertising industries, services are part of an integrated, end-to-end suite of \"creative services\".","Media Item 1 Path":"some_image.png","Media Item 1 Copy":null,"Media Item 2 Path":"some_movie.mp4","Media Item 2 Copy":null,"Media Item 3 Path":"some_other_image.png","Media Item 3 Copy":4123424.0},"project_10":{"Project Title":"3Kia Space Babies","Primary Company":"Method Studios3","Master Copy":"Deluxe's unmatched depth of film editing talent and leading-edge technologies help bring visions to to life for the film, TV, and avertising industries, services are part of an integrated, end-to-end suite of \"creative services\".","Media Item 1 Path":"some_image.png","Media Item 1 Copy":null,"Media Item 2 Path":"some_movie.mp4","Media Item 2 Copy":null,"Media Item 3 Path":"some_other_image.png","Media Item 3 Copy":412341.0}}}