excel_column_notation.rb

#!/usr/bin/env ruby

module Spreadsheet
  module ColumnNotation
    Alphabet = Hash[ (?A..?Z).each_with_index.map {|chr,idx| [ idx + 1, chr ] } ]

    def self.position_to_column_notation(num)
      quotient, remainder = num.divmod(Alphabet.size)

      if remainder == 0
        quotient -= 1
        remainder = 26
      end

      str = ''
      str << position_to_column_notation(quotient) unless quotient == 0
      str << Alphabet[remainder]
    end

    # http://stackoverflow.com/a/667902
    def self.column_notation_to_position(str)
      str.chars.reduce(0) do |sum,char|
        sum *= 26
        sum += char.ord - ?A.ord + 1
      end
    end
  end
end

[ 1, 25, 26, 27, 701, 702, 703, 704, 18277, 18278, 18279 ]
  .each do |num|
    str = Spreadsheet::ColumnNotation.position_to_column_notation(num)
    puts "#{num}\t→\t#{str}"
  end

%w[ A Y Z AA ZY ZZ AAA AAB ZZY ZZZ AAAA ]
  .each do |str|
    num = Spreadsheet::ColumnNotation.column_notation_to_position(str)
    puts "#{str}\t→\t#{num}"
  end

__END__

$ ruby excel_column_notation.rb
1       →       A
25      →       Y
26      →       Z
27      →       AA
701     →       ZY
702     →       ZZ
703     →       AAA
704     →       AAB
18277   →       ZZY
18278   →       ZZZ
18279   →       AAAA
A       →       1
Y       →       25
Z       →       26
AA      →       27
ZY      →       701
ZZ      →       702
AAA     →       703
AAB     →       704
ZZY     →       18277
ZZZ     →       18278
AAAA    →       18279