tetu式

ゲームと音楽・作曲の自己満足と悩みどころの多いプログラムのブログ。

phpExcel続き

PHP

先日自分が今作っているweb勤怠登録にphpExcelライブラリを入れました。

今回はphpExcelの基本構文や、プログラム的に使えると思ったことをまとめます。

 

とりあえずphpExcelで何かするにはライブラリを読み込み、アクティブシートの宣言をしなくてはなりません。

 

    //ライブラリ読み込み
    require_once 'PHPExcel/PHPExcel.php';
    require_once 'PHPExcel/PHPExcel/IOFactory.php';

    // PHPExcelオブジェクトを生成する
    $book = new PHPExcel();

    //シート設定
    $book->setActiveSheetIndex(0);
    $sheet = $book->getActiveSheet();
    $sheet->setTitle('sheet name');    //シート名指定

 

phpExcelを使う上でとりあえずこの文は処理の冒頭に必要です。

後は大体 $sheet-> に続いていろんな処理を書いていくようになります。

 

・セルに値を入力する

    $sheet->setCellValue('セルの座標' , '任意文字列');

ex) $sheet->setCellValue('B5' , 'test');

 

例の通りだとB5セルにtestと言う文字を入力する処理になります。意味的にも非常に分かりやすいですね。

任意文字列部分に '=B3+B4' と言う風に書くとB3とB４をちゃんと足し算してくれます。

もちろん '=SUM(B3:B4)' と書いて関数を使用することも可能。

 

セルに値を入力する方法としてもう一つ。

 

    $sheet->setCellValueByColumnAndRow(列番 , 行番 , '文字列');

ex) $sheet->setCellValueByColumnAndRow(1 , 6 , 'test2');

 

と言う書き方があります。

これはB6にtest2という文字を入力しています。

前回の記事でもちらっと出ましたが列番号は0をA列として始まりますが、行番号は1を１行目としてます。

ちょっとまどろっこしいし、処理名が長い書き方ですが、ループ処理になるといちいち座標指定して入力するよりははるかにスマートな文が書けます。

 

for($i = 1 ; $ i < 10 ; $i++)

{

    $sheet->setCellValueByColumnAndRow(0 , $i , $i);

}

 

この処理でA1～A9まで１～９の連番が入る形で処理されます。

 

・セルの文字スタイルを変える

    $sheet->getStyle('セルの座標')->getFont()->set他処理()->set・・・;

ex) $sheet->getStyle('B5')->getFont()->setSize(18)->setBold(true);

 

指定した座標の文字スタイルを色々変更する処理です。

例の内容だとフォントサイズを１８に、かつ太字に設定しています

getFont()の後に文字をどのように編集するかをset他処理()を続けて書いていく流れになります。

いっぺんに書きたいなんていう欲張りさんのために、

 

applyFromArray(array());

 

という書き方があります。

これは配列で設定したい項目名と値を一度に設定するものです。

phpExcelの処理コメントをそのまま見ると

 

$sheet->getStyle('B5')->getFont()ｰ>applyFromArray(array(

'name' => 'Arial',

'bold' => TRUE,

'italic' => FALSE,

'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,

'strike' => FALSE,

'color' => array('rgb' => '808080')

)

);

 

こんな感じ。

それぞれの意味は説明しなくても何となく分かるかと思います。

 

この処理もgetStyleの後にByColumnAndRowと入れることで列番号と行番号を指定する選択が出来ます。

引数に座標をそのまま入力する処理は大体これがあると思って大丈夫です。

 

・セル結合

    $sheet->mergeCells('始点セル座標:終点セル座標');

ex) $sheet->mergeCells('B2:D4');

 

Excelを使う上で欠かせない要素、セル結合です。

他の処理と違って何か入力する訳ではないので文自体はかなり短め。

これも例によってByColumnAndRowがありますが、これは引数が４つになります。

上の例と同じようにやろうとすると

    $sheet->mergeCellsByColumnAndRow(1,2,3,4);

 

こうなります。

結合後の仕様はExcelと同じで、結合しているセルの一番左上の座標が編集点になります。

 

また、この始点セル座標：終点セル座標の書き方は他の処理でもセルを指定するときに使えますが、ByColumnAndRowは基本的にある1ヶ所のセルだけを指定するのが基本です。

 

・セル内の文字整列

//横中央揃え

    $sheet->getStyle('セルの座標')->
    getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);       

 

//縦中央揃え

    $sheet->getStyle('セルの座標')->
    getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);           

 

通常のExcelではセル結合と同時に中央揃えしてくれますがphpExcelでは自動でしてくれません。

中央揃えには横方向と縦方向の2種類があります。

とりあえず迷ったらどっちもCENTERにしておけばいいと思います。

一番後ろのCENTERは横方向はLEFTやRIGHT、縦方向はTOPやBOTTOMに変えることが出きます。

 

・セルに罫線を引く

    $sheet->getStyle('セルの座標')->getBorders()->

    getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

 

これもExcel上ではよく使う処理ですね。

getBottom()部分は他にgetTop()やgetRight()等、セルのどの部分に罫線を引くかを指定します。

セルの周囲全てに罫線を引きたいときはgetAllBorder()にします。

 

最後のserBorderStyleは引く罫線の種類の設定になります。

THINの部分をMEDIUMにすると太線、DOTにすると点線になったりします。

 

セルの座標に'B2:B7'のように範囲指定することも出来ます。

ただ、B７結合しているセルであった場合、その終点のセル座標を入れないと右側や下側の罫線を引きそびれるので注意。

 

・セルの背景色を設定する

    $sheet->getStyle('セルの座標')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->

getStartColor()->setRGB('RGB値');

 

セルの背景色の設定はちょっと長い文になります。

setFillTypeでセルの塗りつぶし方についての設定をしていますが、基本的には後のRGBがそのままの値が使えるFILL_SOLIDが無難で使いやすいかと思います。

RGB値はHTMLでよく見かける16進数6桁(FFFFFFで白)のものと同じです。

PHPで使うかどうかは微妙なところですが透明度も設定できるsetARGBなんかもあるようです。

 

 

 

とりあえずこの辺までが基本的なところでしょうか。

次は自分が使う上で困った後に解決したことを。と言っても今回悩んだのは1個なのですが・・・

 

・時間の代入について

勤怠管理を作る上で欠かせない時間の入力。

Excel上なら10:00と入力するだけで10時と判定してくれます。

しかし、phpExcel上では10:00という値は文字列としてしか見てくれないのです。

 

実際に10:00を入れてxls形式で保存し、Excelで見てみると'10:00として値が入力され、数値として扱わない用になっています。

時間による計算も必要な処理になるのでこのままではプログラムコード内では計算しようにも計算できないのです。

 

そこで思いついたこと。Excelって時間の計算はどうやってるんだ？

 

答えを言うと結局数字と数字の計算になります。

Excelは数字の情報を見て様々な形式に変換してくれる機能があります。

なので変換前の数値を使って計算した結果をセルに入力し、その後に変換すればいいのです。

 

以下、解決策。

まずExcel上では１と言う数値は1日、つまり24:00に変換することができます。

なので12:00は変換される前の数値だと0.5ということになります。

PHPプログラム上でどうやって0.5を出すかというと

 

$time = "12:00";

$hour = substr($time , 0 , 2) / 24;

 

文字列一部切り取りの関数substr()を使ってこうしちゃいます。

分があった場合も同様。

 

$time ="12:30";

$hour = substr($time , 0 , 2) / 24;

$min = substr($time , 3 , 2) / (24 * 60);

 

数字のみの文字列は数値としても扱ってくれるのがPHPのいいところ。

他の言語だったらキャストせにゃなりませんからね。

そしてこのhourとminを足し算した数値をセルに入力します。

 

    $time = $hour + $min;

    $sheet->setCellValueByColumnAndRow(0 , 1 , $time);

 

これで数値は入りましたが、このまま保存しても見た目が悪い小数点の数値しか出てきません。

Excel上で表示形式を設定すれば12:30と表示されますが、出来れば最初からその表示であってほしいです。

最後にもう一手間、指定セルの表示形式をあらかじめ設定するphpExcel関数を使います。

 

$sheet->getStyleByColumnAndRow(0 , 1)->getNumberFormat()->

setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

 

これはExcelの処理で言うと

A１セルを右クリック->セルの書式設定->表示形式を時刻にし、時：分で表示する

というものになります。

 

この辺りのところまでプログラム上で出来るのはなかなかいい感じです。

しかも数値からの変換になってるので09:30が9:30になるなど、余分な0をきっちり取り払ってくれてます。

 

 

とりあえずこんなとこでしょうか。

プログラムで扱う上ではByColumnAndRowはよく使うことになると思います。

 

今回は載せなかったのですがphpExcelの懸念点が一つ。

セルの幅や高さを数値で入力する関数があるのですが、その数値を入れる内容がどうにも不便です。

できる限り、元にあるテンプレを再現しようと幅と高さも細かく設定したのですが、入れる数値の単位がよく分からないのです・・・

なんでも標準的なサイズの半角英数字が入る文字数の幅、ということらしいですが・・・分かりにくい。

幅と高さで倍率も違うみたいですし・・・

どうせならどんな状況でも統一できるピクセル単位で入れさせてほしかったです。