The Magic Ruby vs the Url of Excel: a short story with long code

Once there was a hacker who needed to rescue a beautiful princess from her prison on a creaking ship moored in the middle of the sky.   He made it onto the noisy old ship, slipped past the guards and tiptoed down the swaying halls to the room where she wept, chained to an excel spreadsheet.

“Hello there Princess! I’ve come to whisk you away!”

“My Hero! I can’t leave this spreadsheet till I know that every URL in this column is correct. I’m stuck clicking on each link and it’s terrible! Sometimes Excel does things I don’t want, it is confusing and I fear I’ll be here till I die…”

The hacker thought for a moment and drew from his bag of tricks a magical Ruby that could cut her chains.

# You need to install 2 things to use this tool
# 1. Ruby - http://www.ruby-lang.org/en/downloads/
# 2. WxRuby - once you've installed Ruby, go to Start, Program Files, Ruby, RubyGems, Ruby Gems Package Manager
#   Now type gem install wxruby
# 3. you should be ready to run this
require 'net/http'
require 'uri'
require 'win32ole'
require 'wx'
include Wx
class MyApp < App

  path_to_wbook = 'C:\temp\file_to_check.xls'
  worksheet_with_urls = 'Generic Content'
  column_with_urls = 9
  file_types = "Excel files (*.xls)|*.xls|New Excel Files (*.xlsx)|*.xlsx"

  def get_worksheet(sheet_name, wbook_path)
    wbook = open_workbook(wbook_path)
    sheet = wbook.Worksheets(sheet_name)
    return sheet
  end
  def open_workbook(wbook_path)
    excel = WIN32OLE.new('Excel.Application')
    workbook = excel.Workbooks.Open(wbook_path)
    return workbook
  end
  def url_is_good(url)
    uri = URI.parse(url)
    response = nil
    Net::HTTP.start(uri.host, uri.port) { |http|
      response = http.head(uri.path.size > 0 ? uri.path : "/")
    }
    if response.code != "200"
      puts "Response code was " + response.code
    end
    return response.code == "200"
  end
  def on_init()
    file = Wx::FileDialog.new( nil, "Choose a file", "", "", file_types, Wx::OPEN|Wx::CHANGE_DIR|Wx::FILE_MUST_EXIST )
    case file.show_modal()
        when Wx::ID_OK
          puts "File: %s, Directory: %s" % [ file.get_filename, file.get_directory ]
          path_to_wbook = file.get_path
        when Wx::ID_CANCEL
          puts "Starting up with the default workbook"
    end
    position = Wx::SingleChoiceDialog.new(nil, "Which column are the URLS in?", "URL Column", "ABCDEFGHIJKLMNOPQRSTUVWXYZ".split(//))
    position.set_selection(8)
    case position.show_modal()
    when Wx::ID_OK
      puts "Chose column " + position.get_string_selection
      column_with_urls = position.get_selection + 1
      puts column_with_urls
    when Wx::ID_CANCEL
      puts "Canceled, going with default"
    end

    puts "Opening up " + path_to_wbook
    sheet = get_worksheet(worksheet_with_urls, path_to_wbook)
    for row in sheet.UsedRange.Rows
      cell = row.Cells(column_with_urls)
      val = cell.text
      next if val.nil?
      #we need to pull any whitespace from the front or the back of the string
      val = val.strip
      #if the url doesn't contain % then we should try to escape it.
      val = URI.escape(val) unless val.include?('%')
      URI.extract(val) do |url|
         if url_is_good( url)
           #let's clear the cell color
           cell.Interior['ColorIndex'] = ExcelConst::XlColorIndexNone
           #puts "good: " + url
         else
           #highlight this cell
           cell.Interior['ColorIndex'] = 36
           puts "BAD LINK FOUND"
           puts "BAD: " + url
         end
      end
    end
    puts "Gone through all the urls, saving and closing down now."
    sheet.Parent.Close(1)
    puts "goodbye!"
  end
end

class ExcelConst
end
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, ExcelConst)
MyApp.new.main_loop()

The princess used the script and it slipped her chains. She took his hand, gave him a kiss, and they escaped down to the city streets. There they went to a nice dinner.

Much thanks to David Mullet’s “Ruby on Windows” blog, where I learned about using win32ole and wxRuby.

But wait, there's more

Leave a Reply

Your email address will not be published. Required fields are marked *