Ben Poweski's Weblog

A co-worker of mine was writing a little script to parse an excel file that contained various network addresses and run various test cases against it. He was stumped on the algorithm how to decoded excel headers to specific indexes. While at first glance this looks like a simple problem, it ended up being more difficult than we thought. My co-worker approached the problem using a procedural approach, this ended up yielding a few nasty loops…far from elegant. The end result ended up being rather easy once the approach was modified to use recursion.

My Solution

require 'test/unit'

def to_excel(i)
  case i
  when 0
    return ''
  when 1..26
    return ('A'..'Z').to_a.at(i - 1)
  else
    q, r = (i - 1).div(26), (i - 1) % 26
    return "#{to_excel(q)}#{to_excel(r + 1)}"
  end
end

class ExcelNumberSeriesTest < Test::Unit::TestCase
  def test_simple
    assert_equal 'A', to_excel(1)
    assert_equal '', to_excel(0)
    assert_equal 'Z', to_excel(26)
  end

  def test_doubles
    assert_equal 'AA', to_excel(27)
    assert_equal 'AB', to_excel(28)
    assert_equal 'AZ', to_excel(52)
  end
end