wu :: forums - Print Page


    
      
        wu :: forums
        (http://www.ocf.berkeley.edu/~wwu/cgi-bin/yabb/YaBB.cgi)
      

        riddles >> cs >> Excel Simulator
        
(Message started by: Misha Kruk on Oct 17^th, 2002, 1:05pm)

Title: Excel Simulator
Post by Misha Kruk on Oct 17^th, 2002, 1:05pm

This one is easy, but a little bit tricky (in the "I guarantee that your first solution will fail" way):

write a function which converts a number into Excel spreadsheet column name.
0 = A
...
25 = Z
26 = AA
...
701 = ZZ
702 = AAA
etc

P.S. Yes, I know that Excel has a limit of 256 columns; they probably couldn't solve this one ;)

Title: Re: Excel Simulator
Post by towr on Oct 18^th, 2002, 3:14am

string const & f(int i)
{
i = i+1;
std::string s;

while (i)
{
s = string(chr(64+i%26)).append(s);
i /=26;
}
return s;
}

Title: Re: Excel Simulator
Post by towr on Oct 18^th, 2002, 3:25am

ok.. so now you made good on your guarantee.. it fails ;)
now I must think more..

Title: Re: Excel Simulator
Post by towr on Oct 18^th, 2002, 3:55am

std::string f(int i)
{
std::string s;

i++;
while (i--)
{
s = std::string(1,static_cast<char>(65+i%26)).append(s);
i /=26;
}
return s;
}

seems to work.. lacks in elegance..
I seem to have forgotten most c++ I learned last year..

Title: Re: Excel Simulator
Post by S. Owen on Oct 18^th, 2002, 8:18am

Here's my Java solution:

public String toExcelCol(int i) {
StringBuffer result = new StringBuffer();
while(i >= 0) {
result.append((char)('A'+(i%26)));
i = i/26 - 1;
}
return result.reverse().toString();
}

I also missed a couple times before getting it... I thought it would be like writing the number in base 26, and it sort of is, but not quite. Interesting.

Edited to add: hmm... this is exactly what towr came up with, isn't it, just looks a little different. I didn't realize it.

Title: Re: Excel Simulator
Post by Garzahd on Oct 30^th, 2002, 4:03pm

Recursion is the first thing that comes to my mind. (using c++ strings)

string foo (int x) {
return (x<0) ? "" : foo((x/26)-1)+(char)('A'+(x%26));
}

And yes, of course it took me more than one try.

Title: Re: Excel Simulator
Post by FluffysWhole on Feb 25^th, 2003, 6:16am

string label;
bool is_neg=( i<0 );
if( is_neg ) i=-i;
do{
s.push_front( 65+(i%26) );
i/=26;
}while( i>26 );
if( i ) s.push_front( 65+((i-1)%26) );
if( is_neg ) s.push_front( '-' );
return label;

The reason why this fools people is that the resulting label is not a standard base 26 value.
The leftmost column of the resulting label value, assuming more then one column, is actually base 27. A=1, Z=26, just that the zero state is never displayed.
If only one column is required then the single column is base 26.

<attached email address is rubbish to avoid spam>