synapsoft.rb

# -*- coding: utf-8 -*-
# http://synapsoft.co.kr/jsp/recruit/13_apply.html
# 엑셀 컬럼 글자 <-> 숫자 변환

class CellAddress
  def self.to_num(text)
    text.upcase.reverse.split("").map.with_index{ |item, i| (item.ord - 64) * 26 ** i }.reduce &:+
  end

  def self.to_text(num)
    num.to_s(26).tr('1-9a-p', 'A-Z').sub(/(A)(0*)0/){ "Y" * $2.length + "Z"} 
  end
end

describe CellAddress do
  describe "숫자를 컬럼 글자로 변환" do
    it "일반적인 경우" do
      CellAddress.to_text(3).should eq("C")
      CellAddress.to_text(26).should eq("Z")
      CellAddress.to_text(27).should eq("AA")
      CellAddress.to_text(267).should eq("JG")
      CellAddress.to_text(579).should eq("VG")
      CellAddress.to_text(703).should eq("AAA")
    end

    it "첫값, 끝값(LibreOffice 기준)" do
      CellAddress.to_text(1).should eq("A")
      CellAddress.to_text(1024).should eq("AMJ")
    end
    
    it "26진수 자리수 변환" do
      CellAddress.to_text(26 ** 2).should eq("YZ")
      CellAddress.to_text(26 ** 3).should eq("YYZ")
      CellAddress.to_text(26 ** 4).should eq("YYYZ")
    end
  end

  describe "컬럼 글자를 숫자로 변환" do
    it "일반적인 경우" do
      CellAddress.to_num("C").should eq(3)
      CellAddress.to_num("Z").should eq(26)
      CellAddress.to_num("AA").should eq(27)
      CellAddress.to_num("JG").should eq(267)
      CellAddress.to_num("VG").should eq(579)
      CellAddress.to_num("AAA").should eq(703)
    end

    it "첫값, 끝값(LibreOffice 기준)" do
      CellAddress.to_num("A").should eq(1)
      CellAddress.to_num("AMJ").should eq(1024)
    end

    it "26진수 자리수 변환" do
      CellAddress.to_num("YZ").should eq(26 ** 2)
      CellAddress.to_num("YYZ").should eq(26 ** 3)
      CellAddress.to_num("YYYZ").should eq(26 ** 4)
    end
  end
end