PHP

【phpExcel】印刷範囲を設定すると改ページが設定されない現象と対応

2016年8月29日

phpExcelで改ページ処理を行おうとしたがとある条件下において正しく動作しなかったのでメモ

条件

印刷範囲がA列以外(例えばB列とか)からに設定すると改ページ処理が行ってくれない。

PHPExcel:1.8.1

テンプレートイメージはこちら

ソースは前回作成したものに追記

  ・test.phpに23行目及び27行目を追加

<?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);
    
    //改ページ処理
    $sheet->setBreak('A' . (35 * $i), PHPExcel_Worksheet::BREAK_ROW);
}

//印刷範囲
$sheet->getPageSetup()->setPrintArea('A:L');

//テンプレート用シートの削除
$excel->oBook->removeSheetByIndex(1);
// $excel->getActiveSheet()->getPageSetup()->setPrintArea(""); //印刷範囲の設定

//ダウンロード処理
$excel->download();

//実行時間計測
$time = microtime(true) - $time_start;
error_log($time . "\n", 3, "/tmp/test.log");
<?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)) {
            //複製先シート行番号が未指定の場合、最大行数+1を挿入
            $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');
    }
    
}

印刷範囲がA列以外(例えばB列とか)からに設定すると改ページ処理が行ってくれない。

template.xlsxの2シート目を1シート目にコピーし、35行目ごとに改ページする処理を追加しています。

test.phpの27行目に記載されている印刷範囲がA:Lなら問題なく改ページが表示されるのですが、B:Lに変更すると改ページが表示されなくなります。

印刷範囲がA:LのEXCEL
印刷範囲がB:LのEXCEL

印刷範囲がA列からなら問題ないのですがさすがに使い勝手が悪いのでどうしたものか…。

原因

原因は改ページ処理を行うことを記述しているタグに問題があるようです。

通常、改ページ処理を行うとxmlでは

<rowBreaks count="1" manualBreakCount="1"><brk id="34" max="16383" man="1"/></rowBreaks>

のような書式で表示されるのですが、phpExcelの場合はbrkタグのmaxパラメータが存在しないため条件によって改ページが正しく動作しないようです。

対策

phpExcelのソースを修正します。

行と列にそれぞれmax値を追記します。

下記Worksheet.php(抜粋)の_writeBreaks関数内に2行追記します。

<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2014 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 ・
 ・
 ・
 
 //894行目あたり
 /**
 * Write Breaks
 *
 * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
 * @param PHPExcel_Worksheet $pSheet Worksheet
 * @throws PHPExcel_Writer_Exception
 */
private function _writeBreaks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
    // Get row and column breaks
    $aRowBreaks    = array();
    $aColumnBreaks = array();
    foreach ($pSheet->getBreaks() as $cell => $breakType) {
        if ($breakType == PHPExcel_Worksheet::BREAK_ROW) {
            $aRowBreaks[] = $cell;
        } else if ($breakType == PHPExcel_Worksheet::BREAK_COLUMN) {
            $aColumnBreaks[] = $cell;
        }
    }
    
    // rowBreaks
    if (!empty($aRowBreaks)) {
        $objWriter->startElement('rowBreaks');
        $objWriter->writeAttribute('count', count($aRowBreaks));
        $objWriter->writeAttribute('manualBreakCount', count($aRowBreaks));
        
        foreach ($aRowBreaks as $cell) {
            $coords = PHPExcel_Cell::coordinateFromString($cell);
            
            $objWriter->startElement('brk');
            $objWriter->writeAttribute('id', $coords[1]);
            $objWriter->writeAttribute('max', '16383');
            $objWriter->writeAttribute('man', '1');
            $objWriter->endElement();
        }
        
        $objWriter->endElement();
    }
    
    // Second, write column breaks
    if (!empty($aColumnBreaks)) {
        $objWriter->startElement('colBreaks');
        $objWriter->writeAttribute('count', count($aColumnBreaks));
        $objWriter->writeAttribute('manualBreakCount', count($aColumnBreaks));
        
        foreach ($aColumnBreaks as $cell) {
            $coords = PHPExcel_Cell::coordinateFromString($cell);
            
            $objWriter->startElement('brk');
            $objWriter->writeAttribute('id', PHPExcel_Cell::columnIndexFromString($coords[0]) - 1);
            $objWriter->writeAttribute('max', '1048575');
            $objWriter->writeAttribute('man', '1');
            $objWriter->endElement();
        }
        
        $objWriter->endElement();
    }
}

これで再度印刷範囲がB:LのEXCELを取得したら改ページが表示されました。

MAX値の意味は?

Excelで表示できる最大行および最大列を表示しているようです。

最大行:1,048,576
最大列:16,384

改ページ(行指定)の場合は全列に適用する為max=16384
改ページ(列指定)の場合は全行に適用する為max=1048576
と指定が必要になるようです。maxの値が上記より少ないとExcelで表示した際におかしくなる場合があるので注意が必要です。

Excel の仕様および制限(Excel2007)
Excel の仕様および制限(Excel2010)
Excel の仕様および制限(Excel2013、Excek2016)

最後に

今回はExcel2007形式(xlsx)のみでExcel2003形式(xls)は対応不要です。

2007形式からXML形式になっているので調査する際は追いやすいですけど色々暗黙の仕様が多いらしいので探していかないといけないですね。

2007形式はISO認定されているOOXMLに準拠しているけど非公開仕様が多い。。。

-PHP
-, , ,