PHP Excelで複数ページの帳票を作成する際、試行錯誤をしていたのでそのメモ
やりたいこと
・表示用のシートとコピー用シートの2つを用意（表示用及びコピー用は同じレイアウト）
・表示用シートに一定行数の書込みを行うとコピー用シートからレイアウトをコピーして最下部に追加
テンプレートイメージはこちら
出力イメージはこちら（10枚分）
環境
PHP：7.0.6
PHPExcel：1.8.1
挑戦
時々、失業SEの開発日誌さんにて行コピー方法が記載されていたのでコピー元シートからコピー先シートに行コピーするように修正。
行番号、列番号が指定されなかった場合は自動的にシート内の最大値を取るように設定しています。
（※$this->oBookは別関数にて定義しています。サンプルファイルはこちら）
[php title="指定した行を別シートにコピー"]
/**
* 指定した行を別シートにコピー
*
* @param int $srcSheetId 複製元シート番号
* @param int $dstSheetId 複製先シート番号
* @param int $srcRow 複製元行番号
* @param int $dstRow 複製先行番号
* @param int $height 複製行数（複製元シート）
* @param int $width 複製カラム数（複製元シート）
* @throws PHPExcel_Exception
*/
public function copySheetRowsOld($srcSheetId, $dstSheetId, $srcRow = 0, $dstRow = null, $height = null, $width = null) {
$srcSheet = $this->oBook->getSheet($srcSheetId);//挿入元シート
$dstSheet = $this->oBook->getSheet($dstSheetId);//挿入先シート
if(!isset($dstRow)){
//複製先シート行番号が未指定の場合、最大行数＋１を挿入
$dstRow = $dstSheet->getHighestRow() + 1;
}
if(!isset($height)){
//Heightが0で指定された場合、挿入元シートの最大行を指定する。
$height = $srcSheet->getHighestRow();
}
if(!isset($width)){
//Widthが0で指定された場合、挿入元シートの最大列を指定する。
$width = PHPExcel_Cell::columnIndexFromString($srcSheet->getHighestColumn()) - 1;
}
for ($row = 0; $row < $height; $row++) {
// セルの書式と値の複製
for ($col = 0; $col < $width; $col++) {
$srcCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($srcRow + $row);
$dstCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
$srcCell = $srcSheet->getCell($srcCellPath);
$srcStyle = $srcSheet->getStyle($srcCellPath);
//値のコピー
$dstSheet->setCellValueByColumnAndRow($col, $dstRow + $row, $srcCell);
//書式コピー
$dstSheet->duplicateStyle($srcStyle, $dstCellPath);
}
// 行の高さ複製。
$h = $srcSheet->getRowDimension($srcRow + $row)->getRowHeight();
$dstSheet->getRowDimension($dstRow + $row)->setRowHeight($h);
}
// セル結合の複製
foreach ($srcSheet->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);
$dstSheet->mergeCells($merge);
}
unset($mc);
}
}
[/php]
実行（失敗）
<p><a href="https://kaede.jp/sample/phpExcel/NG.xlsx">実行結果</a>を確認すると2ページ目からうまく書式が当たらない場合が…</p>
<p><a href="https://kaede.jp/2016/07/03051932.html/excel-ng" rel="attachment wp-att-5752"><img src="https://kaede.jp/wp-content/uploads/2016/07/excel-NG.png" alt="excel NG" width="904" height="1040" class="alignnone size-full wp-image-5752" /></a></p>
<p>納期列が数値で表示されてしまってます。</p>
<h2>再度挑戦</h2>
最初は日付書式がうまくコピーできないのが原因かと思ったのですが、表示形式が標準になったり、他の標準形式が割り当てられたリ、罫線が消えたり、出てたり等と挙動不審だったためうまく使えないなと思っていました。
<p>どうやって書式がうまく扱えるのだろうかとPHPExcelのドキュメントとにらめっこしながら試行錯誤をしていると<a href="http://www.osakac.ac.jp/labs/koeda/tmp/phpexcel/Documentation/API/PHPExcel_Cell/PHPExcel_Cell.html#methodgetXfIndex" target="_blank" rel="noopener noreferrer">GetXfIndex</a>関数でうまく表示することができました。</p>
[php title="指定した行を別シートにコピー（修正後）" highlight="31-52"]
/**
* 指定されたシート内の行をコピーする。
*
* @param int $srcSheetId 複製元シート番号
* @param int $dstSheetId 複製先シート番号
* @param int $srcRow 複製元行番号
* @param int $dstRow 複製先行番号
* @param int $height 複製行数（複製元シート）
* @param int $width 複製カラム数（複製元シート）
* @throws PHPExcel_Exception
*/
public function copySheetRows($srcSheetId, $dstSheetId, $srcRow = 0, $dstRow = null, $height = null, $width = null) {
$srcSheet = $this->oBook->getSheet($srcSheetId);//挿入元シート
$dstSheet = $this->oBook->getSheet($dstSheetId);//挿入先シート
if(!isset($dstRow)){
//複製先シート行番号が未指定の場合、最大行数＋１を挿入
$dstRow = $dstSheet->getHighestRow() + 1;
}
if(!isset($height)){
//Heightが0で指定された場合、挿入元シートの最大行を指定する。
$height = $srcSheet->getHighestRow();
}
if(!isset($width)){
//Widthが0で指定された場合、挿入元シートの最大列を指定する。
$width = PHPExcel_Cell::columnIndexFromString($srcSheet->getHighestColumn()) - 1;
}
for ($row = 0; $row < $height; $row++) {
// セルの書式と値の複製
for ($col = 0; $col < $width; $col++) {
/*
$srcCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($srcRow + $row);
$dstCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
$srcCell = $srcSheet->getCell($srcCellPath);
$srcStyle = $srcSheet->getStyle($srcCellPath);
//値のコピー
$dstSheet->setCellValueByColumnAndRow($col, $dstRow + $row, $srcCell);
//書式コピー
$dstSheet->duplicateStyle($srcStyle, $dstCellPath);
*/
$srcCell = $srcSheet->getCellByColumnAndRow($col, $srcRow + $row);
$dstCell = $dstSheet->getCellByColumnAndRow($col, $dstRow + $row);
//値のコピー
$dstCell->setValue($srcCell);
//書式コピー
$dstCell->setXfIndex($srcCell->getXfIndex());
}
// 行の高さ複製。
$h = $srcSheet->getRowDimension($srcRow + $row)->getRowHeight();
$dstSheet->getRowDimension($dstRow + $row)->setRowHeight($h);
}
// セル結合の複製
foreach ($srcSheet->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);
$dstSheet->mergeCells($merge);
}
unset($mc);
}
}
[/php]
31行目～43行目を45行目～52行目に置き換えています。
<h3>実行</h3>
<p><a href="https://kaede.jp/sample/phpExcel/output.xlsx">出力イメージ</a>と同じ出力結果が表示されました。</p>
<h2>処理速度も向上する</h2>
<p>旧式だと「スタイルオブジェクトを取得⇒割り当て」だったのが、「インデックス取得⇒割り当て」になったため速度がかなり早くなりました。</p>
条件
上記テンプレートイメージを1000ページ（コピー処理999回）作成
サンプルファイル（test.php)の19行目のFor条件を$i < 1000に変更
<p><strong>結果<br />
</strong> 旧：68.34秒<br />
新：12.99秒</p>
約5倍早くなりました！
<h2>課題</h2>
<p>一部の表示形式はうまく対応してくれない場合があります。（付き通貨の表示がうまくできませんでした。）<br />
↑テンプレート読み込みのみでも表示できないものもある為要確認。</p>
<p> </p>
<p>簡単なExcel帳票出力のみになると思いますが、なんとかできそうです。</p>
<p> </p>
<h3 id="sampleFile">サンプルファイル</h3>
[php title="test.php"]
<?php
$time_start = microtime(true);
require_once('Excel.php');
set_time_limit(300);
//第一期引数・テンプレートファイル名 【機能ID】/【帳票ファイル名】
//第二引数・ダウンロードする際のファイル名
//拡張子は自動付与
$excel = new Excel("./template.xlsx", "Excel帳票出力");
$excel->oBook->setActiveSheetIndex(0);//最初のシートを選択
$sheet = $excel->oBook->getActiveSheet(); //変数置き換え
$sheet->setTitle('出力結果');//シート名
//セル内の文字置き換え（以下2パターン）
// $sheet->setCellValue("A1","テスト挿入");
// $sheet->setCellValueByColumnAndRow(1, 7, "テスト2ファイル");
//別シートからデータをコピーする際の処理（以下の処理は10回実行）
for ($i = 1; $i < 10; $i++) {
$excel->copySheetRows(1, 0, 1, 35 * $i + 1);
}
//テンプレート用シートの削除
$excel->oBook->removeSheetByIndex(1);
// $excel->getActiveSheet()->getPageSetup()->setPrintArea(""); //印刷範囲の設定
//ダウンロード処理
$excel->download();
//実行時間計測
$time = microtime(true) - $time_start;
error_log($time . "n", 3, "/tmp/test.log");
[/php]
[php title="Excel.php"]
<?php
require_once('./PHPExcel-1.8.1/Classes/PHPExcel.php');
/**
* Excel出力関数
*/
class Excel extends PHPExcel {
/**
* 添付ファイルパス
* @var string
*/
private $sTempPath = "";
/**
* ダウンロード実行時に表示されるファイル名
* @var string
*/
private $sFileName = "";
/**
* Bookオブジェクト
* @var PHPExcel
*/
public $oBook;
/**
* フォーマットタイプ<br/>
* Excel5：xls<br/>
* Excel2007：xlsx
* @var string
*/
private $sFormat;
/**
* コンストラクタ
* @param string $sTempPath Excelテンプレートが置かれているファイルパス
* @param string $sFileName ファイル名（拡張子なし）
*/
public function __construct($sTempPath, $sFileName = "") {
parent::__construct();
$this->sTempPath = $sTempPath;
//ファイル名指定
if (empty($sFileName)) {
//ファイル名（引数）が空の場合、添付パスから取得
$this->sFileName = basename($this->sTempPath);
} else {
//引数が存在する場合、ファイル名を設定
$info = new SplFileInfo(basename($this->sTempPath));
$this->sFileName = $sFileName . "." . $info->getExtension();
}
$l_oReader = null;
//テンプレート読み込み
//xls、xlsxの順に読み込み処理を行う。
foreach (array('Excel5', 'Excel2007') as $format) {
$this->sFormat = $format;
try {
$l_oReader = PHPExcel_IOFactory::createReader($format);
$this->oBook = $l_oReader->load($this->sTempPath);
} catch (Exception $ex) {
//読み込み失敗はnull置き換え
$this->oBook = null;
$this->sFormat = null;
}
unset($l_oReader);
if (is_object($this->oBook)) {
break;
}
}
}
/**
* 指定されたシート内の行をコピーする。
*
* @param int $srcSheetId 複製元シート番号
* @param int $dstSheetId 複製先シート番号
* @param int $srcRow 複製元行番号
* @param int $dstRow 複製先行番号
* @param int $height 複製行数（複製元シート）
* @param int $width 複製カラム数（複製元シート）
* @throws PHPExcel_Exception
*/
public function copySheetRows($srcSheetId, $dstSheetId, $srcRow = 0, $dstRow = null, $height = null, $width = null) {
$srcSheet = $this->oBook->getSheet($srcSheetId);//挿入元シート
$dstSheet = $this->oBook->getSheet($dstSheetId);//挿入先シート
if(!isset($dstRow)){
//複製先シート行番号が未指定の場合、最大行数＋１を挿入
$dstRow = $dstSheet->getHighestRow() + 1;
}
if(!isset($height)){
//Heightが0で指定された場合、挿入元シートの最大行を指定する。
$height = $srcSheet->getHighestRow();
}
if(!isset($width)){
//Widthが0で指定された場合、挿入元シートの最大列を指定する。
$width = PHPExcel_Cell::columnIndexFromString($srcSheet->getHighestColumn()) - 1;
}
for ($row = 0; $row < $height; $row++) {
// セルの書式と値の複製
for ($col = 0; $col < $width; $col++) {
/* コメントアウト部分は修正前
$srcCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($srcRow + $row);
$dstCellPath = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
$srcCell = $srcSheet->getCell($srcCellPath);
$srcStyle = $srcSheet->getStyle($srcCellPath);
//値のコピー
$dstSheet->setCellValueByColumnAndRow($col, $dstRow + $row, $srcCell);
//書式コピー
$dstSheet->duplicateStyle($srcStyle, $dstCellPath);
*/
$srcCell = $srcSheet->getCellByColumnAndRow($col, $srcRow + $row);
$dstCell = $dstSheet->getCellByColumnAndRow($col, $dstRow + $row);
//値のコピー
$dstCell->setValue($srcCell);
//書式コピー
$dstCell->setXfIndex($srcCell->getXfIndex());
}
// 行の高さ複製。
$h = $srcSheet->getRowDimension($srcRow + $row)->getRowHeight();
$dstSheet->getRowDimension($dstRow + $row)->setRowHeight($h);
}
// セル結合の複製
foreach ($srcSheet->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);
$dstSheet->mergeCells($merge);
}
unset($mc);
}
}
/**
* 出力処理
*/
public function output() {
$writer = PHPExcel_IOFactory::createWriter($this->oBook, $this->sFormat);
$writer->save($this->sFileName);
}
/**
* ダウンロード処理
*/
public function download() {
if ($this->sFormat == "Excel5") {
//xlsのMIME-TYPE
header('Content-Type: application/vnd.ms-excel');
} else {
//xlsxのMIME-TYPE
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}
ob_end_clean();
header('Content-Disposition: attachment;filename*=UTF-8'''. rawurlencode($this->sFileName));
header('Cache-Control: max-age=0');
$l_oWriter = PHPExcel_IOFactory::createWriter($this->oBook, $this->sFormat);
$l_oWriter->save('php://output');
}
}
[/php]