[PHP] Excelの列名(英字)を数字に変換

// 列名を数字に変換
alpha2numric('ABC'); // →731
alpha2numric('BB'); // →54
alpha2numric('Z'); // →26

function alpha2numric($alpha)
{
	$alphas = str_split($alpha);
	$convert_num = 0;
	foreach($alphas as $key => $column)
	{
		if($key+1 < count($alphas))
		{
			$convert_num += pow(26 , count($alphas) - ($key+1)) * convert_alpha_numric($column);
		}
		else
		{
			$convert_num += convert_alpha_numric($column);
		}
	}
	return $convert_num;
}

// アルファベットを数字変換
function convert_alpha_numric($alpha = '')
{
	$arr = array(
		'A' => 1,
		'B' => 2,
		'C' => 3,
		'D' => 4,
		'E' => 5,
		'F' => 6,
		'G' => 7,
		'H' => 8,
		'I' => 9,
		'J' => 10,
		'K' => 11,
		'L' => 12,
		'M' => 13,
		'N' => 14,
		'O' => 15,
		'P' => 16,
		'Q' => 17,
		'R' => 18,
		'S' => 19,
		'T' => 20,
		'U' => 21,
		'V' => 22,
		'W' => 23,
		'X' => 24,
		'Y' => 25,
		'Z' => 26,
	);
	
	return (isset($arr[$alpha])) ? $arr[$alpha] : $arr;
}
よかったらシェアしてね!

この記事を書いた人

目次
閉じる