## Posts Tagged ‘column’

### Programming Praxis – Excel Columns

February 4, 2011

In today’s Programming Praxis exercise our task is to write two functions to convert numbers to and from Excel’s letter-based column names. Let’s get started, shall we?

A quick import:

import Data.Char

The easiest way to covert numbers to Excel notation is recursively. I also wrote a version that uses an unfold to mirror the single fold below, but this version is more concise.

toExcel :: Int -> String
toExcel 0 = ""
toExcel n = toExcel d ++ [chr \$ m + 65] where (d,m) = divMod (n - 1) 26

Converting back to numbers is even easier and can be done in a single fold.

fromExcel :: String -> Int
fromExcel = foldl (\a x -> 26 * a + ord x - 64) 0

Some test cases:

main :: IO ()
main = do print \$ toExcel   1 == "A"
print \$ toExcel  26 == "Z"
print \$ toExcel  27 == "AA"
print \$ toExcel 256 == "IV"
print \$ fromExcel  "A" ==   1
print \$ fromExcel  "Z" ==  26
print \$ fromExcel "AA" ==  27
print \$ fromExcel "IV" == 256
print \$ all (\n -> n == (fromExcel . toExcel) n) [1..2^16]

Everything seems to be working correctly.