Algorithm to get the excel-like column name of a number

Question 1

I'm working on a script that generate some Excel documents and I need to convert a number into its column name equivalent. For example:

1 => A
2 => B
27 => AA
28 => AB
14558 => UMX

I have already written an algorithm to do so, but I'd like to know whether are simpler or faster ways to do it:

function numberToColumnName($number){
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $abc_len = strlen($abc);

    $result_len = 1; // how much characters the column's name will have
    $pow = 0;
    while( ( $pow += pow($abc_len, $result_len) ) < $number ){
        $result_len++;
    }

    $result = "";
    $next = false;
    // add each character to the result...
    for($i = 1; $i<=$result_len; $i++){
        $index = ($number % $abc_len) - 1; // calculate the module

        // sometimes the index should be decreased by 1
        if( $next || $next = false ){
            $index--;
        }

        // this is the point that will be calculated in the next iteration
        $number = floor($number / strlen($abc));

        // if the index is negative, convert it to positive
        if( $next = ($index < 0) ) {
            $index = $abc_len + $index;
        }

        $result = $abc[$index].$result; // concatenate the letter
    }
    return $result;
}

Do you know a better way to do it? Maybe something to keep it simpler? or a performance improvement?

Edit

ircmaxell's implementation works pretty fine. But, I'm going to add this nice short one:

function num2alpha($n)
{
    for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
        $r = chr($n%26 + 0x41) . $r;
    return $r;
}

Question 2

Here's a nice simple recursive function (Based on zero indexed numbers, meaning 0 == A, 1 == B, etc)...

function getNameFromNumber($num) {
    $numeric = $num % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval($num / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2 - 1) . $letter;
    } else {
        return $letter;
    }
}

And if you want it one indexed (1 == A, etc):

function getNameFromNumber($num) {
    $numeric = ($num - 1) % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval(($num - 1) / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2) . $letter;
    } else {
        return $letter;
    }
}

Tested with numbers from 0 to 10000...

Question 3

Any optimization done without being measured and profiled first is useless.

PHP code profilers:

xDebug
PHP_Debug
time (Sometimes it is easy to spot bottlenecks in the code using a simple echo time())

Always measure before optimizing!

Question 4

Micro-optimisations, but don't do:

for ($p = 0; $p < sizeof($nextStopArray); $p++) { 
   ...
}

calculate the sizeof($nextStopArray) before the loop, otherwise you're doing the count every iteration (and this value isn't being changed)

$nextStopArraySize = sizeof($nextStopArray);
for ($p = 0; $p < $nextStopArraySize; ++$p) { 
   ...
}

There's a couple of places where this should be changed.

And if you're iterating several thousand times, ++$p is faster than $p++

But profile the function... find out which parts are taking the longest to execute, and look to optimise those.

EDIT

Get rid of array_push_key as a function, simply execute it inline... it's costing you an unnecessary function call otherwise

Build an array of all nodes from your database outside of the while(true) loop... retrieve all the data in a single SQL query and build a lookup array.

Replacing

for ($p = 0; $p < sizeof($nextStopArray); $p++) {

with

$nextStopArraySize = sizeof($nextStopArray);
$p = -1
while (++$p < $nextStopArraySize) { 
   ...
}

may also prove faster still (just check that the logic does loop through the correct number of times).

Question 5

Your mileage will likely vary on this but for what you have provided you can use a CSS selector combination to target the first link by the page styling.

I use #search div.r [href*=http] but you could simplify to #search .r a. I am interested in knowing there is an http in the href though.

The # is an id selector, a space " " is a descendant selector (selects a child of the preceeding element and the [] is an attribute selector. A "." is a class selector i.e. selects an element by class name.

I am looking for the first element with an href attribute containing http in its value that has a parent element div element with class name r, whose parent has an id of search.

Option Explicit
Public Sub GetLink()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .navigate "https://google.co.uk/search?q=Currys+241825"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Debug.Print .document.querySelector("#search div.r [href*=http]").href

        .Quit
    End With

End Sub

Question 6

To make this value its two's complement, you will have to manipulate the contents. That is of course impossible, so you first get the contents out, manipulate them and then get them into a new BigInteger:

public static BigInteger twosComplement(BigInteger original)
{
    // for negative BigInteger, top byte is negative  
    byte[] contents = original.toByteArray();

    // prepend byte of opposite sign
    byte[] result = new byte[contents.length + 1];
    System.arraycopy(contents, 0, result, 1, contents.length);
    result[0] = (contents[0] < 0) ? 0 : (byte)-1;

    // this will be two's complement
    return new BigInteger(result);
}

public static void main(String[] args)
{
    BigInteger a = new BigInteger("-173B8EC504479C3E95DEB0460411962F9EF2ECE0D3AACD749BE39E1006FC87B8", 16);
    BigInteger b = twosComplement(a);

    System.out.println(a.toString(16).toUpperCase());
    System.out.println(b.toString(16).toUpperCase());

    // for comparison, from question:
    System.out.println("E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641D61EFF9037848");
}

Output:

-173B8EC504479C3E95DEB0460411962F9EF2ECE0D3AACD749BE39E1006FC87B8
E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641C61EFF9037848
E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641D61EFF9037848

And this new BigInteger is really the two's complement, not just a re-interpretation of the bits.

Algorithm to get the excel-like column name of a number

Edit

Answers

php,algorithm,optimization