Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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."
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement