Category: Programming Problems

Example PHP code to convert a number to an Excel column letter

Posted on by

I was looking for a good way to convert a number to the Excel column letter, for example:

1 -> A
26 -> Z
27 -> AA
28 -> AB
800 -> ADT

etc.

After searching on Google, I could not find any algorithm I want. (The closest one can handle up to two letters, i.e., ZZ. It will crash if the number is too large.). So I ended up creating my own.

Basically, you can think of this problem as a 26 based number conversion. Here is the php version. It should be pretty simple to convert it to a different language. Let me know if you have any problem.

function columnLetter($c){

    $c = intval($c);
    if ($c < = 0) return '';

    $letter = '';
             
    while($c != 0){
       $p = ($c - 1) % 26;
       $c = intval(($c - $p) / 26);
       $letter = chr(65 + $p) . $letter;
    }
    
    return $letter;
        
}



Usage:
columnLetter(1303618093);    //DERRICK

Note that if you prefer lower cases rather than upper cases, you can simply replace 65 by 96 in the code.

Enjoy~!

–Derrick

Our sponsors:

Tokyo Cabinet is 31 times faster than MySQL!!!

Posted on by

Today I was comparing the performance of Tokyo Cabinet, MySQL and Memcached using PHP. The result of Tokyo Cabinet is really impressive!

(Note that these results are generated from my computer. If you perform your own benchmarks, then you might find different results due to different system configurations. In fact, I encourage you to perform your own benchmarks. )

1. Quick Result

Total time used to write 10000 records and retrieve them back by each candidate  (The lower the better):
Tokyo Cabinet: 18.87s
Memcached: 11.309s
MySQL: 562.21s

A completed result will be given below.

2. Testing environment

Hardware

  • Intel Pentium II 450MHz
  • 160 MB RAM

Software

  • FreeBSD 7.2
  • Apache 2.2.11
  • PHP 5.2.9
  • Tokyo Cabinet 1.4.20
  • Tokyo Tyrant 1.1.26
  • MySQL 5.1.34
  • Memcached 1.2.6

3. Test scenario

For each candidate, I measure its performance using the following strategies through PHP:

1.) The time used to create all of the necessary objects.

2.) The time used to connect to the database.

3.) The time used to store 10000 randomly generated data.

4.) The time used to read 10000 randomly generated data.

5.) The time used to close the connection.

4. Test results

Tokyo Cabinet

time index ex time %
Start 1243136772.72818600 – 0.00%
create 1243136772.72838700 0.000201 0.00%
connect 1243136772.73224200 0.003855 0.02%
vanish 1243136772.73526800 0.003026 0.02%
put 1243136781.53287200 8.797604 46.60%
get 1243136791.60574800 10.072876 53.35%
close 1243136791.60755900 0.001811 0.01%
Stop 1243136791.60782300 0.000264 0.00%
total – 18.879637 100.00%


Memcached

time index ex time %
Start 1243131185.86078100 – 0.00%
create 1243131185.86092500 0.000144 0.00%
connect 1243131185.86688300 0.005958 0.05%
put 1243131191.49702900 5.630146 49.78%
get 1243131197.16868100 5.671652 50.15%
close 1243131197.16982400 0.001143 0.01%
Stop 1243131197.17003400 0.000210 0.00%
total – 11.309253 100.00%

MySQL

time index ex time %
Start 1243136138.19049000 – 0.00%
create 1243136138.19064700 0.000157 0.00%
connect 1243136138.22529900 0.034652 0.01%
put 1243136150.70760600 12.482307 2.22%
get 1243136700.40063700 549.693031 97.77%
close 1243136700.40078900 0.000152 0.00%
Stop 1243136700.40104500 0.000256 0.00%
total – 562.210555 100.00%

The overhead time (create objects, making connections, closing connections etc) of all candidates are about the same. The only difference is storing the record and retrieving the record. Both Tokyo Cabinet and MySQL took about 10 seconds to store 10000 records in the database. However, Tokyo Cabinet took about 10 seconds to retrieve the records while MySQL took about 550 seconds! That’s about 31 times longer!

benchmark-tokyocabinet-memcached-mysql

5. Materials

Here is the material I used to make this benchmark test.
Click here to download the package.

Please feel free to let me know if you have any question or comment.

Our sponsors: