Wednesday, January 19, 2011

Excel column label calculation (increment)

Sharing out the code that calculate the excel column label by increment. Suitable for any version of excel.


 <?php
/**
 * Calculate the column label base on incremental value
 * @param string $txtCurrID Column label for start position
 * @param int $intIncrement Number of columns that need to be forwarded
 * @return string
 */
function increaseColumnID($txtCurrID, $intIncrement)
{
    $firstID="";
    $secondID="";
    $intCalVal="";

    /* Make sure the label is in upper case */
    $txtCurrID = strtoupper($txtCurrID);

    /* Split the label, since the max label is two character,
     * so take first and second character */
    if(2 == strlen($txtCurrID)) {
        $firstID = $txtCurrID[0];
        $secondID = $txtCurrID[1];
    } else if(1 == strlen($txtCurrID)) {
        $secondID = $txtCurrID;
    }

    /* If the increment more than 26, increase the first id by one
     * , reduct the increment 26 each loop until it smaller or
     * equal to 26 */
    while ($intIncrement > 26)
    {
        $intIncrement -= 26;
        if ($firstID == "") {
            $firstID = "A";
        } else {
            $firstID = chr(ord($firstID) + 1);
        }
    }

    /* If increment apply to current second label more than character 'Z'
     * (90), then we increment the first label again */
    if ((ord($secondID)+$intIncrement) > 90)
    {
        if ($firstID == "") {
            $firstID = "A";
        } else {
            $firstID = chr(ord($firstID) + 1);
        }

        $intIncrement = (ord($secondID)+$intIncrement) - 90;

        /* Need to minus one because we start from 'A' (65) */
        $secondID = chr(65 + $intIncrement -1);
    }
    else
    {
        $secondID = chr(ord($secondID)+$intIncrement);
    }

    /* return the calculated label in string format */
    return $firstID.$secondID;
}


echo "A:25 = ".increaseColumnID("A", 25)."<BR><BR>";
echo "A:26 = ".increaseColumnID("A", 26)."<BR><BR>";
echo "A:50 = ".increaseColumnID("A", 50)."<BR><BR>";
echo "aa:1 = ".increaseColumnID("aa", 1)."<BR><BR>";
echo "aa:26 = ".increaseColumnID("aa", 26)."<BR><BR>";
echo "aa:52 = ".increaseColumnID("aa", 52)."<BR><BR>";
echo "b:52 = ".increaseColumnID("b", 52)."<BR><BR>";
echo "A:100 = ".increaseColumnID("A", 100)."<BR><BR>";
?>

No comments:

Post a Comment