Script that can parse .xlsx file to generate models

parse_models.rb

require 'roo'
require 'pp'


def parse_excel_sheets(filename)
  xlsx = Roo::Spreadsheet.open(filename)

  sheets = {}

  # each sheet has three tables
  sections = ['head', 'body', 'tail']

  xlsx.sheets.each do |sheet|
    # use an object to keep track of each section separately
    sheets[sheet.to_s] = {}
    section_id = 0

    # excel starting row
    row = 1

    while row <= xlsx.last_row(sheet)
      seq = xlsx.cell(row, 1, sheet)

      # if row starts with a cell "Seq.", recognize it as a section
      if seq  == "Seq."
        # specs array represents list of "field"s
        specs = []

        # legend keeps track of which column corresponds to which key
        legend = {}
        col = 1
        while val = xlsx.cell(row, col, sheet)
          legend[col] = val
          col += 1
        end

        row += 1
        while val = xlsx.cell(row, 1, sheet)
          col = 1
          # make an object where the key is the column (i.e. "Description" or "From"/"To") and the value is the specification
          obj = {}
          while val = xlsx.cell(row, col, sheet)
            obj[legend[col]] = val
            col += 1
          end
          # add current spec object to list of specs
          specs << obj
          row += 1
        end

        # add list of specs to the section (head/body/tail)
        sheets[sheet.to_s][sections[section_id]] = specs

        # next iteration, save specs to the next section
        section_id += 1
      end

      # parse next row
      row += 1
    end
  end

  return sheets
end


def parse_txt_using_models(filename, models, sheet_name="DLR-Dealer Master")
  file = File.open(filename, "rb")
  contents = file.read

  head = contents.lines.first
  tail = contents.lines.last

  sheet = models[sheet_name]

  output = {
    head: nil,
    body: [],
    tail: nil,
  }

  contents.each_line do |line|
    if line == head
      section = "head"
    elsif line == tail
      section = "tail"
    else
      section = "body"
    end

    result = {}

    specs = sheet[section]
    specs.each do |spec|
      from = spec["From"].to_i - 1
      to = spec["To"].to_i - 1
      desc = spec["Description"]

      slice = line[from..to].strip
      result[desc] = slice
    end

    if section == "head"
      output[:head] = result
    elsif section == "body"
      output[:body] << result
    elsif section == "tail"
      output[:tail] = result
    end
  end

  return output
end


models = parse_excel_sheets('./models.xlsx')
output = parse_txt_using_models("KMM_DLR_20160331000277.txt", models)

pp output