Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excelのラベルを参照するクラス
<?php
class Excel_cell_pointer {
private $map = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Z'];
/**
*
* @param string $col_label // 列
* @param integer $row_label // 行
*/
public function __construct($col_label = 'A', $row_label = 1){
$this->set_row($row_label);
$this->set_col($col_label);
}
/**
* 列のラベルをセットする
*
* @param string $col_label
*/
public function set_col($col_label) {
$this->col_label = strtoupper($col_label);
}
/**
* 列のラベルを取得する
*
* @return string
*/
public function get_col() {
return $this->col_label;
}
/**
* 行のラベルをセットする
*
* @param integer $row_label
*/
public function set_row($row_label){
$this->row_label = $row_label;
}
/**
* 行のラベルを取得する
*
* @return integer
*/
public function get_row() {
return $this->row_label;
}
/**
* セルのラベルを返す (列ラベル + 行ラベル)
*
* @return string
*/
public function get_cell() {
$col = $this->get_col();
$row = (string) $this->get_row();
return $col.$row;
}
/** @see now */
public function now() { return $this->get_cell(); }
/**
* ポインタを指定したセルにセットする
*
* @param string $cell_label ex. A1 | AB12
* @return void
*/
public function set_cell($cell_label) {
preg_match('/^([a-z]+)([0-9]+)$/i', $cell_label, $m);
$this->set_col($m[1]);
$this->set_row((int) $m[2]);
}
/** @see set_cell */
public function to($cell_label) { $this->set_cell($cell_label); }
/**
* ポインタを移動する
*
* @param integer $to_col +1|-1
* @param integer $to_row +1|-1
* @return void
*/
public function move($to_col = 0, $to_row = 0) {
$col = $this->to_number($this->get_col());
$row = $this->get_row();
$col += $to_col;
$row += $to_row;
if ( $col < 1 ) $col = 1;
if ( $row < 1 ) $row = 1;
$this->set_col($this->to_alphabet($col));
$this->set_row($row);
}
public function move_col($num) { $this->move($num, 0); }
public function move_row($num) { $this->move(0, $num); }
public function next_col() { $this->move(+1, 0); }
public function prev_col() { $this->move(-1, 0); }
public function next_row() { $this->move(0, +1); }
public function prev_row() { $this->move(0, -1); }
public function reset_col() { $this->set_col('A'); }
public function reset_row() { $this->set_row(1); }
public function reset() { $this->set_cell('A1'); }
/**
* change column label to decimal
*
* @param string $str column label. ex:B | AA | BAB
* @return integer decimal number
*/
public function to_number($str) {
$alphabet_arr = preg_split("//u", $str, -1, PREG_SPLIT_NO_EMPTY);
$number_arr = [];
$map_count = count($this->map);
// 1桁の場合
if ( count($alphabet_arr) === 1 ) return array_search($alphabet_arr[0], $this->map) + 1;
// 2桁以上の場合
for ( $i = 0, $l = count($alphabet_arr); $i < $l; $i++ ) {
$alphabet_index = array_search($alphabet_arr[$i], $this->map);
$digit = $l-$i; //
if ( $digit === 1) { // 1の位の場合
$number_arr[$i] = $alphabet_index + 1;
} else { // 10の位以上の場合
$base = pow($map_count, $digit - 1);
$distance = $alphabet_index + 1;
$number_arr[$i] = $base * $distance;
}
}
return array_sum($number_arr);
}
/**
* change decimal to column label
*
* @param string|integer $num column label. ex:B | AA | BAB
* @return string
*/
public function to_alphabet($num) {
$number_arr = [];
$alphabet_arr = [];
$map_count = count($this->map);
$num = gettype($num) === 'string' ? (int) $num : $num;
do {
$rem = $num % $map_count; // 余り
$num = floor($num / $map_count);
if ( $rem === 0 ) {
$num--;
$rem = $map_count;
}
$number_arr[] = $rem;
} while($num);
$number_arr = array_reverse($number_arr);
foreach ($number_arr as $number) $alphabet_arr[] = $this->map[$number-1];
return implode($alphabet_arr, '');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment