## 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>";
?>