To import custom channels from simple media plan xls document!

gistfile1.rb

class CustomChannel < ActiveRecord::Base
  include Roo
  
  def self.import_websites
    s = Excelx.new("#{Rails.root}/db/data/simple_media_plan.xlsx")

    #load custom channel sheet
    s = s.sheet("Custom Channel")

    # Iterate through each column
    (s.first_column..s.last_column).to_a.each do |col|

      column_data = s.column(col)
  
      media_name = column_data.delete_at(0)

      #Delete last row
      column_data.delete_at(column_data.length-1)
      column_data.compact!


      media_name = get_valid_media_name(s, media_name, col)

      city = nil
      column_data.each do |city_or_url|
        if is_valid_url?(city_or_url)
          website = city_or_url
          if !website.blank?
            CustomChannel.create!({:media_name => media_name, :city => city, :website => website})
          end
        else
        city = city_or_url
        end
      end
    end

  end

  def self.is_valid_url?(url)
    url =~ /^[a-z0-9]+([\-\.]{1}[a-z0-9]+)*\.[a-z]{2,5}(:[0-9]{1,5})?(\/.*)?$/
  end

  def self.get_valid_media_name(s, media_name, col)
    if media_name.blank?
      (0...col).to_a.reverse.each do |col_no|
        unless s.cell(s.first_row, col_no).blank?
          media_name = s.cell(s.first_row, col_no)
          break;
        end
      end
    end
    media_name.gsub(':', '')
  end
end