こんにちはWEBの佐野です、もうすぐ桜の季節ですね。
私は最近、目が痒くて、鼻が詰って、くしゃみがよく出ます。
花見の季節なのに、花粉症で辛いです。
そんな時にはこれを見て花見気分を味わいたいと思います。
さてさて、私が所属しているWEBチームも花粉症に負けず、色々な案件を進めているのですが、WEBサイト制作のほかにもシステム開発などを行っております！
主にはPHPを使ったWEBシステムの制作を行っているのですが、今回はそこで使う事が多いPHPExcelについて書きたいと思います。
基本操作となる、新規ファイルの作成・ファイルの読み書きの部分をサンプルと合わせて紹介します。
最後にすぐ試してもらえる様にコピペで使えるサンプルコードを載せておきますので、興味がある方は最後までご覧ください！
PHPExcelとは？
PHPでエクセルの新規ファイルを生成したり、既存ファイルの読み書きが可能な無償のPHPライブラリです。
一般向けサイトよりも、社内システムなどのエクセルの利用が保証された環境下でのシステムで使う事が多いです。
メリット・デメリット
PHPでエクセル操作を行う際の代名詞とも言えるPHPExcelの、メリット・デメリットを簡単に書いておきます。
メリット
・無料で利用できる
・設置が簡単
・だいたいの事ができる
デメリット
・メモリの使用量が多い
今まで使ってみた感覚としては以上の事を強く感じます。
デメリットが１つとかなり優秀な様にも感じますが、このデメリットが意外と深刻な問題で、色々な事ができる反面、細かい操作を行うと出力までに膨大な時間を要したり、タイムアウトする可能性もあるので、詰め込み過ぎた使い方にはご注意ください。
今回の実装内容
今回は冒頭でも話た様に、PHPExcelを使って
・新規のエクセルファイル作成と値の設定
・サーバー上にあるエクセルファイル内の値の読み込み
これら２つを実装したいと思います。
PHPExcelの準備
まずは、下記のサイトからファイル一式をZIPでダウンロードします。
解凍すると、下の様になっているので「Classes」フォルダのみ作業フォルダへコピーしてください。
サンプルの作業フォルダはこの様になっています。
・作業フォルダ ┗index.php ┗Classes ← ダウンロード後、ZIPを解凍するとあるフォルダ ┗create ← 作成したファイルの保管先フォルダ(空でOK)
index.phpの内容としてはこちらになります。
<?php
// PHPExcelライブラリの読み込み
include_once ( dirname(__FILE__) . '/Classes/PHPExcel.php');
include_once ( dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php');
if($_POST["create"]){
// 新規ファイル作成の処理
}
if($_POST["read"]){
// 既存ファイル読込の処理
}
?>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<form name="form" action="./" method="post">
<input type="submit" name="create" value="ファイル作成">
<input type="submit" name="read" value="ファイル読込">
</form>
</body>
</html>
これだけでエクセル操作以外の準備は完了です、もの凄く簡単です。
新規ファイルの作成
ここからがエクセル操作の処理に入ります。
ここでは新規ファイルを作成、値をセット、ファイルのダウンロードまで行っています。
サンプルでは下記の内容をセットします。
| 名前 | 年齢 | 氏名 |
|---|---|---|
| 山田 | 20 | 男性 |
| 佐藤 | 25 | 女性 |
| 田中 | 32 | 男性 |
コードはこちら、ファイルの作成からダウンロードまでを記述しています。
// 作成するファイル名の保存先パス
$path = "./create/";
$file = "sample.xlsx";
// ダウンロード時のファイル名
$dl_file = "サンプル.xlsx";
// PHPExcelのオブジェクト作成
$obj = new PHPExcel();
// シートを選択(0が1つ目のシートです)
$obj->setActiveSheetIndex(0);
// 操作するシートのオブジェクトを作成
$sheet = $obj->getActiveSheet();
// シート名を設定
$sheet->setTitle("シート名");
// 内容を設定
$sheet->setCellValue("A1", "山田");
$sheet->setCellValue("A2", "佐藤");
$sheet->setCellValue("A3", "田中");
$sheet->setCellValue("B1", "20");
$sheet->setCellValue("B2", "25");
$sheet->setCellValue("B3", "32");
$sheet->setCellValue("C1", "男性");
$sheet->setCellValue("C2", "女性");
$sheet->setCellValue("C3", "男性");
// ファイルの保存
$writer = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
$writer->save($path.$file);
// メモリの解放
$obj->disconnectWorksheets();
unset($obj);
// ファイルのダウンロード
header('Content-Type: application/force-download');
header('Content-Length: '.filesize($path.$file));
header('Content-Disposition: attachment; filename="'.mb_convert_encoding($dl_file, 'SJIS-win', 'UTF-8').'"');
readfile($path.$file);
exit();
出力結果
要所にコメントを書いているので、それぞれの命令が何をしているかはわかっていただけるかと思いますので、1点だけ！
35,36行目のメモリの解放は必ず行いましょう！
このメモリの解放を忘れてしまうとメモリを大きく取られた状態になるため、その他の処理に影響しかねません！
細かい処理を組む前に最初から書いておくことをオススメします。
セルに値をセットする別の方法
上のコードでは”A1″や”B2″の様なセル指定を行っていますが、出力する値を配列で管理していてループを使った出力を考えた時に少し不便かと思います。
setCellValueByColumnAndRowメソッド
そこで、setCellValueByColumnAndRowメソッドを使って出力する方法があります。
// 配列の作成
$ary[0]["name"] = "山田";
$ary[0]["age"] = "20";
$ary[0]["gender"] = "男性";
$ary[1]["name"] = "佐藤";
$ary[1]["age"] = "25";
$ary[1]["gender"] = "女性";
$ary[2]["name"] = "田中";
$ary[2]["age"] = "32";
$ary[2]["gender"] = "男性";
// セルに値をセット
$i = 0;
foreach ($ary as $data => $val) {
$i++;
$sheet->setCellValueByColumnAndRow(0, $i, $val["name"]);
$sheet->setCellValueByColumnAndRow(1, $i, $val["age"]);
$sheet->setCellValueByColumnAndRow(2, $i, $val["gender"]);
}
setCellValueByColumnAndRow(列番号,行番号,値);
と言う様に引数を渡すと該当箇所に値をセットしてくれるので、状況に応じて使い分けてください！
このコードに差し替えたサンプルでも出力結果は同じものになります。
ここまで記述すればエクセルファイルの新規作成完了です！
既存ファイルの読み込み
次に既存ファイルの中から値を取得してみたいと思います。
取得だけならものすごく簡単で、下のコードだけで読み込めます！
ちなみに読み込みファイルは上で作成したファイルにしています。
// 読込するファイル $readFile = "./create/sample.xlsx"; // 指定ファイルを読み込む $objPExcel = PHPExcel_IOFactory::load($readFile); // 読み込んだエクセルの参照シートを指定 $sheet = $objPExcel->getActiveSheet(0); // 配列で保持する $ary = $sheet->toArray(null,true,true,true); // 結果を出力してみる echo "<pre>"; print_r($ary); echo "</pre>";
結果がこちら
Array( [1] => Array( [A] => 山田 [B] => 20 [C] => 男性 ) [2] => Array( [A] => 佐藤 [B] => 25 [C] => 女性 ) [3] => Array( [A] => 田中 [B] => 32 [C] => 男性 ) )
行・列の二次元配列で返ってきます。
ここまで出来ていれば、あとは好きにできますね！
セルを指定して値を取得する
先ほどの方法は設定している全ての値を配列にしていますが、指定したセルの値を取得する方法もあります。
getCellByColumnAndRowメソッド
getCellByColumnAndRow(列[int],行[int])メソッドを使えば指定セルの値を取得できます。
この方法のメリットとしては
・必要なセルの値だけを取得できる
・取得のタイミングで値の加工が出来る
・好きな形で配列が作れる
と言うところかと思います、ちなみに私はこちらの方法をよく使います。
特にエクセルはセル毎に形式が指定されていて、意図していない形で取得する事があります。
その際には形式を戻す必要があるので、こちらの方が都合よく処理できます。
for($i=1;$i<=$sheet->getHighestRow();$i++){
$idx = $i-1;
// 名前の取得
$ary[$idx]["name"] = $sheet->getCellByColumnAndRow(0, $i)->getValue();
// 年齢の取得
$ary[$idx]["age"] = $sheet->getCellByColumnAndRow(1, $i)->getValue();
// 性別の取得
$ary[$idx]["gender"] = $sheet->getCellByColumnAndRow(2, $i)->getValue();
}
// 結果を出力してみる
echo "<pre>";
print_r($ary);
echo "</pre>";
結果はこちら
Array( [0] => Array( [name] => 山田 [age] => 20 [gender] => 男性 ) [1] => Array( [name] => 佐藤 [age] => 25 [gender] => 女性 ) [2] => Array( [name] => 田中 [age] => 32 [gender] => 男性 ) )
この様に配列が作る事ができました。
どちらの方法でも良いと思うので作業しやすい方で試してみてください！
最後に
今回はPHPExcelの基本部分だけを紹介しました。
他にも線を引いたり、色を塗ったり、サイズを変えたりなど色々な事ができるのですが、そこはまた機会があれば書いてみたいと思います。
システム案件以外にもデータのコンバートで使ったりと活躍してきたPHPExcelですので、ご存知なかった方はぜひこの機会に使ってみてください！
全体のサンプルコードの載せておきます。
<?php
// PHPExcelライブラリの読み込み
include_once ( dirname(__FILE__) . '/Classes/PHPExcel.php');
include_once ( dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php');
if($_POST["create"]){
// 新規ファイル作成の処理
create_file();
}
if($_POST["read"]){
// 既存ファイル読込の処理
read_file();
}
function create_file(){
// 作成するファイル名の保存先パス
$path = "./create/";
$file = "sample.xlsx";
// ダウンロード時のファイル名
$dl_file = "サンプル.xlsx";
// PHPExcelのオブジェクト作成
$obj = new PHPExcel();
// シートを選択(0が1つ目のシートです)
$obj->setActiveSheetIndex(0);
// 操作するシートのオブジェクトを作成
$sheet = $obj->getActiveSheet();
// シート名を設定
$sheet->setTitle("シート名");
// 内容を設定
$sheet->setCellValue("A1", "山田");
$sheet->setCellValue("A2", "佐藤");
$sheet->setCellValue("A3", "田中");
$sheet->setCellValue("B1", "20");
$sheet->setCellValue("B2", "25");
$sheet->setCellValue("B3", "32");
$sheet->setCellValue("C1", "男性");
$sheet->setCellValue("C2", "女性");
$sheet->setCellValue("C3", "男性");
// ファイルの保存
$writer = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
$writer->save($path.$file);
// メモリの解放
$obj->disconnectWorksheets();
unset($obj);
// ファイルのダウンロード
header('Content-Type: application/force-download');
header('Content-Length: '.filesize($path.$file));
header('Content-Disposition: attachment; filename="'.mb_convert_encoding($dl_file, 'SJIS-win', 'UTF-8').'"');
readfile($path.$file);
exit();
}
function read_file(){
// 読込するファイル
$readFile = "./create/sample.xlsx";
// 指定ファイルを読み込む
$obj = PHPExcel_IOFactory::load($readFile);
// 読み込んだエクセルの参照シートを指定
$sheet = $obj->getActiveSheet(0);
// 配列で保持する
$ary = $sheet->toArray(null,true,true,true);
// 結果を出力してみる
echo "<pre>";
print_r($ary);
echo "</pre>";
// メモリの解放
$obj->disconnectWorksheets();
unset($obj);
}
?>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<form name="form" action="./" method="post">
<input type="submit" name="create" value="ファイル作成">
<input type="submit" name="read" value="ファイル読込">
</form>
</body>
</html>
ピックアップ
