PHPでExcelを出力する方法【PhpSpreadsheet(PHPExcel)】
PhpSpreadsheet(PHPExcel)は、PHPでExcelを出力するための有名なライブラリです。
ここでは、その「PhpSpreadsheet(PHPExcel)」を使用してExcelを出力する一般的な方法を紹介します。
「PHPOffice/PhpSpreadsheet」公式サイト
https://github.com/PHPOffice/PhpSpreadsheet
PHPExcelのインストール
composerを使用してインストールすることができます。
*「composer」のインストール方法は、「composerのインストールと使い方」で紹介しています。
mkdir PhpSpreadsheet cd PhpSpreadsheet composer require phpoffice/phpexcel
基本的な出力方法
<ソース>
// ライブラリ読み込み require "./PhpSpreadsheet/vendor/autoload.php"; // PHPExcelインスタンス $objPHPExcel = new PHPExcel(); // シートの場所(0始まり) $objPHPExcel->setActiveSheetIndex(0); // シートを取得 $activeSheet = $objPHPExcel->getActiveSheet(); // 文字列の記述 $activeSheet->setCellValue('A1', 12345) ->setCellValue('A2', 'テスト'); // Excel作成 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); // サーバーに出力 $filename = "sample_" . date('YmdHis') . ".xlsx"; //ファイル名 $objWriter->save("./data/" . $filename);
<出力結果>
<ブラウザに出力する場合は、サーバー出力の部分を下記に変更する>
//ブラウザに出力 $filename = "sample_" . date('YmdHis') . ".xlsx"; //ファイル名 header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment;filename*=utf-8''" . urlencode($filename)); header("Content-Transfer-Encoding: binary "); $objWriter->save('php://output');
値の形式を指定する
<ソース>
// 値を設定する $activeSheet->setCellValueExplicit('A1', '01234', PHPExcel_Cell_DataType::TYPE_STRING); //文字列 $activeSheet->setCellValueExplicit('A2', 56789, PHPExcel_Cell_DataType::TYPE_NUMERIC); //数値 $activeSheet->setCellValueExplicit('A3', 1, PHPExcel_Cell_DataType::TYPE_BOOL); //ブール(0=false、1=true) $activeSheet->setCellValueExplicit('A4', 1+2+3+4+5, PHPExcel_Cell_DataType::TYPE_FORMULA); //式
<出力結果>
文字揃えを指定する
<ソース>
// 文字列を出力する $activeSheet->setCellValue('A1', '右寄せ') ->setCellValue('B1', '中央寄せ') ->setCellValue('C1', '左寄せ'); // 文字揃えを指定する $activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //右寄せ $activeSheet->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //中央寄せ $activeSheet->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //左寄せ
<出力結果>
罫線を引く
<ソース>
// 罫線を引く $activeSheet->getStyle('B2')->applyFromArray([ 'borders' => [ 'top' => [ 'style' => PHPExcel_Style_Border::BORDER_THIN, ], 'bottom' => [ 'style' => PHPExcel_Style_Border::BORDER_DASHED, ], 'left' => [ 'style' => PHPExcel_Style_Border::BORDER_DOTTED, ], 'right' => [ 'style' => PHPExcel_Style_Border::BORDER_DOUBLE, 'color' => ['rgb' => 'FF0000'], ], ], ]); $activeSheet->getStyle('B4:C5') ->applyFromArray([ 'borders' => [ 'allborders' => [ 'style' => PHPExcel_Style_Border::BORDER_THIN, ], ], ]);
<出力結果>
<罫線の種類>
PHPExcel_Style_Border::BORDER_DASHDOT
PHPExcel_Style_Border::BORDER_DASHDOTDOT
PHPExcel_Style_Border::BORDER_DASHED
PHPExcel_Style_Border::BORDER_DOTTED
PHPExcel_Style_Border::BORDER_DOUBLE
PHPExcel_Style_Border::BORDER_HAIR
PHPExcel_Style_Border::BORDER_MEDIUM
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHED
PHPExcel_Style_Border::BORDER_SLANTDASHDOT
PHPExcel_Style_Border::BORDER_THICK
PHPExcel_Style_Border::BORDER_THIN
色を付ける
<ソース>
// 文字列を出力する $activeSheet->setCellValue('A1', 'テキスト'); // 文字色を指定 $activeSheet->getStyle('A1')->getFont()->getColor()->setRGB("FF0000"); // 背景色を指定 $activeSheet->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); //塗りつぶしの形式 $activeSheet->getStyle('A2')->getFill()->getStartColor()->setRGB("FFFF00");
<出力結果>
<背景の種類>
PHPExcel_Style_Fill::FILL_NONE
PHPExcel_Style_Fill::FILL_SOLID
PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR
PHPExcel_Style_Fill::FILL_GRADIENT_PATH
PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN
PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY
PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID
PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL
PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS
PHPExcel_Style_Fill::FILL_PATTERN_DARKUP
PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL
PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625
PHPExcel_Style_Fill::FILL_PATTERN_GRAY125
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL
PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY
セルを結合する
<ソース>
// セルを結合する $activeSheet->mergeCells('A1:C5'); // セル結合を解除する $activeSheet->unmergeCells('A1:C5');
フォントに太字、斜体、サイズを設定する
<ソース>
$activeSheet->getStyle('A1')->getFont()->setBold(true); // 太字 $activeSheet->getStyle('B1')->getFont()->setItalic(true); // 斜体 $activeSheet->getStyle('C1')->getFont()->setSize(20); // サイズ
セルをコピーする
<ソース>
$moto_sheet_no = 0; // コピー元のシート番号 $saki_sheet_no = 1; // コピー先のシート番号 $copy_moto_row = 1; // コピー元の行番号 $copy_saki_row = 7; // コピー先の行番号 $gyou_su = 1; // コピーする行数 $retsu_su = 5; // コピーする列数 $sheet_moto = $objPHPExcel->getSheet($moto_sheet_no); //コピー元のシート $sheet_saki = $objPHPExcel->getSheet($saki_sheet_no); //コピー先のシート for($row=0; $row<$gyou_su; $row++) { // セルの書式と値の複製 for ($col=0; $col<$retsu_su; $col++) { $cell = $sheet_moto->getCellByColumnAndRow($col, $copy_moto_row + $row); $style = $sheet_moto->getStyleByColumnAndRow($col, $copy_moto_row + $row); $copy_saki_cell = PHPExcel_Cell::stringFromColumnIndex($col).(string)($copy_saki_row + $row); $sheet_saki->setCellValue($dstCell, $cell->getValue()); $sheet_saki->duplicateStyle($style, $copy_saki_cell); } // 行の高さ複製 $h = $sheet_moto->getRowDimension($copy_moto_row + $row)->getRowHeight(); $sheet_saki->getRowDimension($copy_saki_row + $row)->setRowHeight($h); } // セル結合の複製 foreach ($sheet_moto->getMergeCells() as $merge_cell) { $mc = explode(":", $merge_cell); $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])) - $copy_moto_row; $row_e = ((int)preg_replace("/[A-Z]*/" , "",$mc[1])) - $copy_moto_row; // 複製先の行範囲なら if (0<=$row_s && $row_s<$height) { $merge = $col_s . (string)($copy_saki_row + $row_s) . ":" . $col_e . (string)($copy_saki_row + $row_e); $sheet_saki->mergeCells($merge); } }
テンプレートを読み込んで出力する
<ソース>
// ライブラリ読み込み require "./PhpSpreadsheet/vendor/autoload.php"; // テンプレートファイル $template_file = "./data/template.xlsx"; // ファイル読み込み $objPHPExcel = PHPExcel_IOFactory::load($template_file); // シートの場所(0始まり) $objPHPExcel->setActiveSheetIndex(0); // シートを取得 $activeSheet = $objPHPExcel->getActiveSheet(); // 文字列を出力する $activeSheet->setCellValue('A1', 'テキスト'); // Excel作成 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); // サーバーに出力 $filename = "sample_" . date('YmdHis') . ".xlsx"; //ファイル名 $objWriter->save("./data/" . $filename);
シートに名前を付ける
<ソース>
// シート名を変更 $objPHPExcel->getActiveSheet()->setTitle("シート1です");
<出力結果>
Excelのデフォルトフォントを指定する
PHPExcelの初期のフォントは「Calibri」になっていたので変更する。
<ソース>
// デフォルトフォントを変更 $objPHPExcel->getDefaultStyle()->getFont()->setName('游ゴシック')->setSize(11);
<出力結果>
プロパティを設定する
<ソース>
// Excelのプロパティ設定 $objPHPExcel->getProperties() ->setCreator("作成者") ->setCompany('会社名') ->setManager('管理者') ->setTitle("タイトル") ->setSubject("サブタイトル") ->setCategory("分類") ->setKeywords("キーワード") ->setDescription("コメント") ->setCreated(strtotime(date("Y-M-d H:i:s"))) //作成日時 ->setModified(strtotime("Y-M-d H:i:s")); //更新日時
<出力結果>
参考サイト
http://blog.pionet.co.jp/experience/archives/316
https://qiita.com/suin/items/7a8d0979b7675d6fd05b