Search code examples
excellualibreoffice-calc

How to calculate the "names" of Excel/LO Calc columns in Lua

I need 1 to become 'A', 26 to become 'Z', 27 to become 'AA' so on until (in this case) 81 becoming 'CC'

from 'A' to 'Z' I can do something like

string.char(64 + i)

But can't figure out how to do this for the larger columns.

Solution

  • Hope this helps

    function num2column(n)
  assert(type(n) == "number")

  local i = n
  local j = 0
  local ret = ""

  while true do
    j = i % 26
    i = i // 26 -- use i = math.floor(i / 26) if using Lua older than 5.3

    if j == 0 then
      j = 26
      i = i - 1
    end

    -- prepend the letter to ret
    ret = string.char(64 + j) .. ret

    if i == 0 then break end
  end
  return ret
end

print(num2column(27)) -- 'AA'
print(num2column(80)) -- 'CB'