require 'spreadsheet'

file_name = ARGV[0]
sheet_number = ARGV[1].to_i - 1
letters = ["a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"]
column_number = letters.index(ARGV[2])
start_row = ARGV[3].to_i

formatted_file_name = "formatted " + file_name
formatted_dates = ""
number_of_non_formattable_dates = 0
months = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]

Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet.open file_name
sheet = book.worksheet sheet_number
column = sheet.column(column_number)

column.each_with_index do |cell, row|
  new_cell_value = ""
  if ( row >= start_row && cell )
    unformatted_date_components = cell.gsub(/  /, " ").split(/[\s\/]/)
    date_day = nil
    date_month = nil
    date_year = nil
    unformatted_date_components.each do |component|
  component_length = component.length
  if ( date_day == nil && component_length < 3 && component.match(/^\d+$/) )
    date_day = component
    next
      elsif ( date_month == nil && months.index(component[0..2].downcase) )
        date_month = months.index(component[0..2].downcase) + 1
        break if ( date_day && date_month )
        next
      elsif ( date_year == nil && date_day && component.match(/^\d+$/) )
        if component_length < 3 then
          date_year = component + 2000
        else
          date_year = component
        end
        next
      end
    end

    if ( date_day.to_s != "" && date_month.to_s != "" && date_year.to_s != "" )
      new_cell_value = Date.new(date_year, date_month, date_day)
    else
      new_cell_value = "\n"
      number_of_non_formattable_dates += 1
    end

  end
  sheet[row, column_number] = new_cell_value
end
column.each do |cell|
  puts cell
end
book.write file_name.split(".")[0] + " formatted.xls"
puts "Formatting complete. " + number_of_non_formattable_dates.to_s + " dates were unformattable."