Advertisement
beckyconning

Untitled

Nov 28th, 2011
105
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Ruby 1.96 KB | None | 0 0
  1. require 'spreadsheet'
  2.  
  3. file_name = ARGV[0]
  4. sheet_number = ARGV[1].to_i - 1
  5. 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"]
  6. column_number = letters.index(ARGV[2])
  7. start_row = ARGV[3].to_i
  8.  
  9. formatted_file_name = "formatted " + file_name
  10. formatted_dates = ""
  11. number_of_non_formattable_dates = 0
  12. months = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]
  13.  
  14. Spreadsheet.client_encoding = 'UTF-8'
  15. book = Spreadsheet.open file_name
  16. sheet = book.worksheet sheet_number
  17. column = sheet.column(column_number)
  18.  
  19. column.each_with_index do |cell, row|
  20.   new_cell_value = ""
  21.   if ( row >= start_row && cell )
  22.     unformatted_date_components = cell.gsub(/  /, " ").split(/[\s\/]/)
  23.     date_day = nil
  24.     date_month = nil
  25.     date_year = nil
  26.     unformatted_date_components.each do |component|
  27.   component_length = component.length
  28.   if ( date_day == nil && component_length < 3 && component.match(/^\d+$/) )
  29.     date_day = component
  30.     next
  31.       elsif ( date_month == nil && months.index(component[0..2].downcase) )
  32.         date_month = months.index(component[0..2].downcase) + 1
  33.         break if ( date_day && date_month )
  34.         next
  35.       elsif ( date_year == nil && date_day && component.match(/^\d+$/) )
  36.         if component_length < 3 then
  37.           date_year = component + 2000
  38.         else
  39.           date_year = component
  40.         end
  41.         next
  42.       end
  43.     end
  44.    
  45.     if ( date_day.to_s != "" && date_month.to_s != "" && date_year.to_s != "" )
  46.       new_cell_value = Date.new(date_year, date_month, date_day)
  47.     else
  48.       new_cell_value = "\n"
  49.       number_of_non_formattable_dates += 1
  50.     end
  51.    
  52.   end
  53.   sheet[row, column_number] = new_cell_value
  54. end
  55. column.each do |cell|
  56.   puts cell
  57. end
  58. book.write file_name.split(".")[0] + " formatted.xls"
  59. puts "Formatting complete. " + number_of_non_formattable_dates.to_s + " dates were unformattable."
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement