PHP Excelファイルを扱う PhpSpreadsheet 読み込み編

古いシステムからの移行をしています。
世の中作ろうとしているシステムは既に似たようなのがあるけど、なんだかんだカスタマイズ＆保守という形で高い値段取られるんですよね。
トータルで考えると作ったほうが柔軟性があるし安い。ということでPHP (LaravelではなくCodeIgniterというマイナーフレームワーク）でとある業務システムを作成しています。

普段はgoogle client API使ってスプレッドシートを・・という具合なんですが、古いデータからエクセルファイルで抽出したなら一旦googleドライブに入れるなんて、ユーザーには手間なのでPHPSpreadsheetを使ってやったりします。

今回は久々使った時に忘れた時用のメモを。

1.Excelファイル xlsxを開く
2. セルのデータを読み込む
参考リンク

1.Excelファイル xlsxを開く

ファイルパスを指定してIOFactoryでloadすればOKです。ちなみに昔のXls形式（古いエクセルはバイナリ）は読み込めません。今のXMLベースのXlsxに変換するなどしてください。
最初のシートを$sheetオブジェクトにセットします。


require_once dirname(__FILE__).'/../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php';


$sp = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
if( !$sp ){
  return FALSE;
}

$sheet = $sp->getActiveSheet();
if( !$sheet ){
 return FALSE;
}

2. セルのデータを読み込む

セルのデータを読み込むにも、シートにどれだけのデータがあるかわかりません。
まずは列、行数を取得します。

2.1 最大行、最大列を取得する

シートオブジェクトに対して、最大行=getHighestRow(), 最大列=getHighestColumn()を呼びます。列はアルファベットで返されるので、数字に変換しましょう。


$lastrow = $sheet->getHighestRow(); //200などの数字
$lastcolname = $sheet->getHighestColumn(); //AZなどの文字列
$lastcol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($lastcolname); //これで数字になる

2.2 ループでセルデータを取得

上記で最大行、最大列がわかったのでループで取得します。
getCellByColumnAndRow($col,$row)->getValue()です。
getValue()はフォーマット、関数など入力したデータそのまま返ってきます。
もし、関数使っていて、計算した後のデータが欲しい場合はgetValue()をgetCalculatedValue()に、書式設定された後のデータはgetFormattedValue()に変更してください。


for($row=1; $row<=$lastrow; $row++){
  for(col=1; $col<=lastcol; $col++){
    $cellval = $sheet->getCellByColumnAndRow($col,$row)->getValue();
  }
}

2.3 範囲を取得し配列にセット(個人的にオススメ)

場所がわかっていれば、A2:E5のような感じで指定し、2次元配列を取得できます。
ググると、rangeToArray(‘A2:E5’)とかだけですが、実際は引数はこれだけあります。初期値は見たままのデータを取得出来るようです。
TRUEをFALSEにするなどしてどういうデータが返ってくるかチェックしましょう。


$datas = $sheet->rangeToArray(
 'A2:E5',
 NULL, //空セル時の値
 TRUE, //TRUE式が使われていたら計算される
 TRUE, //TRUE書式設定されたもの
 TRUE  //TRUE 連想配列 {A:val, B:val..}　FALSEインデックス配列[val,val..]
 );

エクセルのデータでよくあるのは、書式を設定しているがためにデータとして取り出しにくいこと。こういう時は第4引数をFALSEにすると、書式がない生データを取得できます。
よく金額に\1,000,000とか円マークにカンマあったりしますよね。
TRUEだと見たまま取得してしまうので後でデータ整形が必要になり面倒です。

2.4 イテレーターを使ってループ取得

foreachで行回して、中でforeachでセルを回して取得。
ループとそれほどかわりありませんが、多分高速です。


foreach($sheet->getRowIterator() as $row ){
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(FALSE);//空セルも取得するようにする

  foreach( $cellIterator as $cell ){
    $cell->getValue();
  }
}