2012/04/06
PHPExcelで行のコピー
PHPの仕事で一番厄介だった所をメモっとく。
社内システムをWeb化するときに今まで使ってた Excel
の文書をそのまま使いたいんだよねー、
とかお客さん言われる事は多いと思うんだけど今回もそうだった。
で、PHPでExcelってどうすんだろと思ってググったら PHPExcel
ってのが定番らしいのでこれを使うことにした。
こいつが重くってどうにもならないのだが今回は
お客さんが許してくれたので結果おーらい。
次は使わないけど。
本題。
Excelのテンプレートに項目を埋めて出力するってパターンが基本だと思うのだが 今回はテンプレートの行をコピーしながら表を作る仕様になっていた。 PHPExcelの機能に行コピーぐらい用意されてると思ったら無い。
ググったら自前でセル単位に書式と値をコピーするサンプルが有った。
これを使って見たのだがダメダメ。
セルの結合がコピーできて無い。
PHPExcelでセルの結合をコピーする方法を調べたのだがまともに出来ない事が分かった。
require_once 'PHPExcel/Classes/PHPExcel.php';
class Excel {
public $sheet;
public function __construct($templ) {
$reader = PHPExcel_IOFactory::createReader('Excel5');
$this->excel = $reader->load($templ);
$this->sheet = $this->excel->setActiveSheetIndex(0);
$this->templ = $templ;
}
public function apply($map) {
foreach ($map as $key => $value) {
$this->sheet->setCellValue($key, $value); // Note: $valueはUTF-8必須
}
}
public function copyRows(
$srcRow, // 複製元行番号
$dstRow, // 複製先行番号
$height, // 複製行数
$width // 複製カラム数
) {
$sheet = $this->sheet;
for($row=0; $row<$height; $row++) {
// セルの書式と値の複製
for ($col=0; $col<$width; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $srcRow+$row);
$style = $sheet->getStyleByColumnAndRow($col, $srcRow+$row);
$dstCell = PHPExcel_Cell::stringFromColumnIndex($col).(string)($dstRow+$row);
$sheet->setCellValue($dstCell, $cell->getValue());
$sheet->duplicateStyle($style, $dstCell);
}
// 行の高さ複製。
$h = $sheet->getRowDimension($srcRow+$row)->getRowHeight();
$sheet->getRowDimension($dstRow+$row)->setRowHeight($h);
}
// セル結合の複製
// - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
// - $merge="AB16:AC19"
foreach ($sheet->getMergeCells() as $mergeCell) {
$mc = explode(":", $mergeCell);
$col_s = preg_replace("/[0-9]*/" , "",$mc[0]);
$col_e = preg_replace("/[0-9]*/" , "",$mc[1]);
$row_s = ((int)preg_replace("/[A-Z]*/" , "",$mc[0])) - $srcRow;
$row_e = ((int)preg_replace("/[A-Z]*/" , "",$mc[1])) - $srcRow;
// 複製先の行範囲なら。
if (0 <= $row_s && $row_s < $height) {
$merge = $col_s.(string)($dstRow+$row_s).":".$col_e.(string)($dstRow+$row_e);
$sheet->mergeCells($merge);
}
}
}
public function output() {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$this->templ);
header("Expires: Thu, 01 Dec 1994 16:00:00 GMT");
header("Last-Modified: ". gmdate("D, d M Y H:i:s"). " GMT");
header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Cache-Control: private",false);
header("Pragma: no-cache");
$writer = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$writer->save('php://output');
}
}
実行サンプル。
require_once 'Excel.php';
setlocale(LC_ALL,'ja_JP.UTF-8');
function doMap($excel, $data, $count) {
$L = ($count+1) * 12;
$excel->sheet->insertNewRowBefore($L+1, 12);
$excel->copyRows(1, $L+1, 12, 53);
$L2 = $L + 2;
$L4 = $L + 4;
$L5 = $L + 5;
$L6 = $L + 6;
$L8 = $L + 8;
$L9 = $L + 9;
$L10 = $L + 10;
$L11 = $L + 11;
$L12 = $L + 12;
$ptime = strptime($data[0],"%Y/%m");
$map = array(
"K$L2" => $ptime["tm_year"]+1900, // 年
"O$L2" => $ptime["tm_mon"]+1, // 月
"Y$L2" => date("Y",time()), // 支給年
"AC$L2"=> date("m",time()), // 月
"AF$L2"=> date("d",time()), // 日
"AM$L2"=> $data[1], // 所属
"AV$L2"=> $data[2], // 氏名
//------------------------------
"J$L4" => $data[3], // 出勤日数
"R$L4" => $data[4], // 欠勤日数
"Z$L4" => $data[5], // 有給
"AH$L4"=> $data[6], // 代休
"J$L5" => $data[7], // 終業時間
"R$L5" => $data[8], // 残業時間
"Z$L5" => $data[9], // 休出時間
//------------------------------
"J$L6" => $data[10], // 基本給
"R$L6" => $data[11], // 残業手当
"Z$L6" => $data[12], // 通勤手当
"AH$L6"=> $data[13], // 住宅手当
"AP$L6"=> $data[14], // 家族手当
"J$L8" => $data[15], // 支給額
//------------------------------
"J$L9" => $data[16], // 健康保険
"R$L9" => $data[17], // 介護保険
"Z$L9" => $data[18], // 構成年金
"AP$L9"=> $data[19], // 雇用保険
"J$L10"=> $data[20], // 所得税
"R$L10"=> $data[21], // 住民税
"J$L11"=> $data[22], // 控除額
//------------------------------
"J$L12"=> $data[23], // 差引支給額
);
$excel->apply($map);
}
$excel = new Excel("kyuuyo-templ.xls");
$fp = fopen("sample.csv","r");
$header = fgetcsv($fp);
$count = 0;
while ($data=fgetcsv($fp)) {
doMap($excel, $data, $count++);
}
fclose($fp);
//$excel->sheet->removeRow(1, 12);
$excel->output();
exit;
実行結果：一番上のテンプレート１２行を複製しながら中身をCSVから埋め込んでいます。
これ、使いたければ使っても良いけどすっごい遅い事は覚悟して下さい。
このサンプルでも数十人分なら応答時間は分単位になるでしょう。
できれば PHPExcel 自体の使用を止めたほうが良いです (^^;
参考：http://atamoco.boy.jp/php5/phpexcel/20110406_1.php
Excelのテンプレートに項目を埋めて出力するってパターンが基本だと思うのだが 今回はテンプレートの行をコピーしながら表を作る仕様になっていた。 PHPExcelの機能に行コピーぐらい用意されてると思ったら無い。
ググったら自前でセル単位に書式と値をコピーするサンプルが有った。
これを使って見たのだがダメダメ。
セルの結合がコピーできて無い。
PHPExcelでセルの結合をコピーする方法を調べたのだがまともに出来ない事が分かった。
- セルの結合状態はシート全体に対してしか取れない。
しかも文字列で "AB12:AC15" みたいな形式の配列。 - 設定は同じ形式を関数に渡せば結合される。
- セルの書式
- セルの値
- セルの結合
- 行の高さ
実行サンプル。
実行結果：一番上のテンプレート１２行を複製しながら中身をCSVから埋め込んでいます。
(出典：http://template.k-solution.info/2006/12/01_excel_9.html )
これ、使いたければ使っても良いけどすっごい遅い事は覚悟して下さい。
このサンプルでも数十人分なら応答時間は分単位になるでしょう。
できれば PHPExcel 自体の使用を止めたほうが良いです (^^;
参考：http://atamoco.boy.jp/php5/phpexcel/20110406_1.php
この投稿へのコメント
コメント・フォーム
20年勤めた会社がリーマンショックで消滅、紆余曲折を経て現在はフリーランスのSE。
失業をきっかけにこのブログを始める。
