I came across this problem a while ago with referencing Excel column names programatically. The problem is that its dificult to write a function to convert a number, say 28, into a set of letters, AC. In order to solve the column name problem I started with the basic example of any number from 0-25 that covers A-Z. In obyx its actuall very simple we just do a substring operation accross the alphabet:
<instruction operation="substring" note="get the char of the col name">
<input value="ABCDEFGHIJKLMNOPQRSTUVWXYZ" />
<input value="24" />
<input value="1" />
</instruction>
Y
The above example looks for the string of length 1 at position 24 which turns out to be Y. In fact this method can be used for an alphabet set whether its the while UTF-8 charset or the 16 hex characters.
The case above only covers A-Z though. Next I thought about how do I get AA or AZ and here I had to rely on my maths background to help me. In order to get the next letter thought how would I get two letters out of a number like 47.
The idea of performing a divide function and ignoring the decimal places came to me. If I divide the column number by 26 (the length of the alphabet) I will get how many letters are left to output. For example:
47 / 26 = 1.8 ==> 1
14 / 26 = 0.54 ==> 0
In Obyx there is an operation to do this: quotient, see Instruction: The Quotient Operation.
So now I know how many letters there are to be output, the next question is how do I calculate the letter for a particular letter position. I realised I could use the opposite mathematical function in my initial substring function to get the letter from the column number. If I take the remainder of 47/26 I get 21.
In effect I am getting the letter index for the column as determined by the quotient. I can do this calculation for every letter in the entire column name string. Example:
quotient: 48 / 26 ==> 1
remainder: 48 / 26 ==> 22
character_at(alphabet,22) => W
The quotient represents the position of the character in the column name and the remainder gives us the value of the letter when we do a substring.
This gives us the letter at position 22, W. Which is our second column because the quotient is 1, representing position 1 in a 0 indexed string. To get the character at position 0 we need to subtract the length of the alphabet off the column number and do the calculation again.
48 - 26 = 22
quotient: 22 /26 ==> 0
remainder: 22 / 26 ==> 0
character_at(alphabet,0) => A
We then try taking 26 off again, but it cannot be taken off 22 and remain a positive integer so we quit the process.
So the final column name is AV.
The challenge I now faced was to put this in a looping mechanism. I thought about putting it into an iteration, but that would over complicate the function and produce surplus code. I found it ... inelegant. So I though the simplest mechanism is just to call the function over and over until its complete. Using recursion seemed to be the correct solution.
The recursive operation does a single comparison to check if more letters are available, if they are then the function calls itself with the modified paramters. Once all the function calls have been completed the functions start unrolling themselves. Its at this stage where the letters are output in reverse order so we get the correct column name.
Obyx has no concept of a stacking mechanism because Obyx is aimed at the webscripting market. There is a single Obyx stack so a store that is created in an obyx script can be used anywhere regalrdless of scope. That anywhere includes sub-functions and evaulated files.
This means when making recursive calls we can not use temporary stores to hold information between recursions. The reason being that each successive call will overwrite the previously set store without a way of recalling the original value.
Why don't you try downloading the code and try figuring out how it works for yourself. There is a simple test harness in there that takes a paramater in the URL of 'col'. The algorithm is pretty fast always returning within a second. The only limitation is the size of the integer you pass to the function, with the maximum integer to column name conversion some where around the 9,000,000,000,000,000,000 mark. Thats one humunguous spreadsheet.