PhpExcelWrapper::getAlignment() - Code Metrics - Inspection of "Merge pull request #2 from satthi/scrutinizer_chec..." - satthi/PhpExcelWrapper - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 96b484...9beaa5 )

by satoru

created 2016-05-24 08:04 UTC

PhpExcelWrapper::getAlignment() A

↳ Parent: PhpExcelWrapper

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	5
rs	9.4285
cc	1
eloc	3
nc	1
nop	3

<?php

namespace PhpExcelWrapper;

use \PHPExcel_IOFactory;
use \PHPExcel_Cell;
use \PHPExcel_Worksheet_Drawing;
use \PHPExcel_Style_Border;
use \PHPExcel_Style_Alignment;
use \PHPExcel_Style_Fill;

/**
* PhpExcelWrapper
* PHPExcelを記載しやすくするためのラッパー
*/
class PhpExcelWrapper
{
    private $__phpexcel;
    private $__sheet = [];
    private $__deleteSheetList = [];
    private static $__borderType = [
        'none' => PHPExcel_Style_Border::BORDER_NONE,
        'thin' => PHPExcel_Style_Border::BORDER_THIN,
        'medium' => PHPExcel_Style_Border::BORDER_MEDIUM,
        'dashed' => PHPExcel_Style_Border::BORDER_DASHED,
        'dotted' => PHPExcel_Style_Border::BORDER_DOTTED,
        'thick' => PHPExcel_Style_Border::BORDER_THICK,
        'double' => PHPExcel_Style_Border::BORDER_DOUBLE,
        'hair' => PHPExcel_Style_Border::BORDER_HAIR,
        'mediumdashed' => PHPExcel_Style_Border::BORDER_MEDIUMDASHED,
        'dashdot' => PHPExcel_Style_Border::BORDER_DASHDOT,
        'mediumdashdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,
        'dashdotdot' => PHPExcel_Style_Border::BORDER_DASHDOTDOT,
        'mediumdashdotdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,
        'slantdashdot' => PHPExcel_Style_Border::BORDER_SLANTDASHDOT,
    ];

    private static $__alignHolizonalType = [
        'general' => PHPExcel_Style_Alignment::HORIZONTAL_GENERAL,
        'center' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'left' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        'right' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        'justify' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY,
        'countinuous' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS,
    ];

    private static $__alignVerticalType = [
        'bottom' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM,
        'center' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        'justify' => PHPExcel_Style_Alignment::VERTICAL_JUSTIFY,
        'top' => PHPExcel_Style_Alignment::VERTICAL_TOP,
    ];

    private static $__fillType = [
        'linear' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
        'path' => PHPExcel_Style_Fill::FILL_GRADIENT_PATH,
        'none' => PHPExcel_Style_Fill::FILL_NONE,
        'darkdown' => PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN,
        'darkgray' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY,
        'darkgrid' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID,
        'darkhorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL,
        'darktrellis' => PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS,
        'darkup' => PHPExcel_Style_Fill::FILL_PATTERN_DARKUP,
        'darkvertical' => PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL,
        'gray0625' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625,
        'gray125' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY125,
        'lightdown' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN,
        'lightgray' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY,
        'lightgrid' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID,
        'lighthorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL,
        'lighttrellis' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS,
        'lightup' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP,
        'lightvertical' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL,
        'mediumgray' => PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY,
        'solid' => PHPExcel_Style_Fill::FILL_SOLID,
    ];

    /**
    * __construct
    *
    * @param string $template テンプレートファイルのパス
    * @author hagiwara
    */
    public function __construct($template = null, $type = 'Excel2007')
    {
        if ($template === null) {
            //テンプレート無し
            $this->__phpexcel = new \PHPExcel();
        } else {
            //テンプレートの読み込み
            $reader = PHPExcel_IOFactory::createReader($type);
            $this->__phpexcel = $reader->load($template);
        }
    }

    /**
    * setVal
    * 値のセット
    * @param string $value 値
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param integer $refCol 参照セル行
    * @param integer $refRow 参照セル列
    * @param integer $refSheet 参照シート
    * @author hagiwara
    */
    public function setVal($value, $col, $row, $sheetNo = 0, $refCol = null, $refRow = null, $refSheet = 0)
    {
        $cellInfo = $this->cellInfo($col, $row);
        //値のセット
        $this->getSheet($sheetNo)->setCellValue($cellInfo, $value);

        //参照セルの指定がある場合には書式をコピーする
        if (!is_null($refCol) && !is_null($refRow)) {
            $this->styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet);
        }
    }

    /**
    * setImage
    * 画像のセット
    * @param string $img 画像のファイルパス
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param integer $height 画像の縦幅
    * @param integer $width 画像の横幅
    * @param boolean $proportial 縦横比を維持するか
    * @param integer $offsetx セルから何ピクセルずらすか（X軸)
    * @param integer $offsety セルから何ピクセルずらすか（Y軸)
    * @author hagiwara
    */
    public function setImage($img, $col, $row, $sheetNo = 0, $height = null, $width = null, $proportial = false, $offsetx = null, $offsety = null)
    {
        $cellInfo = $this->cellInfo($col, $row);

        $objDrawing = new PHPExcel_Worksheet_Drawing();//画像用のオプジェクト作成
        $objDrawing->setPath($img);//貼り付ける画像のパスを指定
        $objDrawing->setCoordinates($cellInfo);//位置
        if (!is_null($height)) {
            $objDrawing->setHeight($height);//画像の高さを指定
        }
        if (!is_null($width)) {
            $objDrawing->setWidth($width);//画像の高さを指定
        }
        if (!is_null($proportial)) {
            $objDrawing->setResizeProportional($proportial);//縦横比の変更なし
        }
        if (!is_null($offsetx)) {
            $objDrawing->setOffsetX($offsetx);//指定した位置からどれだけ横方向にずらすか。
        }
        if (!is_null($offsety)) {
            $objDrawing->setOffsetY($offsety);//指定した位置からどれだけ縦方向にずらすか。
        }
        $objDrawing->setWorksheet($this->getSheet($sheetNo));
    }

    /**
    * cellMerge
    * セルのマージ
    * @param integer $col1 行
    * @param integer $row1 列
    * @param integer $col2 行
    * @param integer $row2 列
    * @param integer $sheetNo シート番号
    * @author hagiwara
    */
    public function cellMerge($col1, $row1, $col2, $row2, $sheetNo)
    {
        $cell1Info = $this->cellInfo($col1, $row1);
        $cell2Info = $this->cellInfo($col2, $row2);

        $this->getSheet($sheetNo)->mergeCells($cell1Info . ':' . $cell2Info);
    }


    /**
    * styleCopy
    * セルの書式コピー
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param integer $refCol 参照セル行
    * @param integer $refRow 参照セル列
    * @param integer $refSheet 参照シート
    * @author hagiwara
    */
    public function styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet)
    {
        $cellInfo = $this->cellInfo($col, $row);
        $refCellInfo = $this->cellInfo($refCol, $refRow);
        $style = $this->getSheet($refSheet)->getStyle($refCellInfo);

        $this->getSheet($sheetNo)->duplicateStyle($style, $cellInfo);
    }

    /**
    * setStyle
    * 書式のセット(まとめて)
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param array $style スタイル情報
    * @author hagiwara
    */
    public function setStyle($col, $row, $sheetNo, $style)
    {
        $default_style = [
            'font' => null,
            'underline' => null,
            'bold' => null,
            'italic' => null,
            'strikethrough' => null,
            'color' => null,
            'size' => null,
            'alignh' => null,
            'alignv' => null,
            'bgcolor' => null,
            'bgpattern' => null,
        ];
        $style = array_merge($default_style, $style);
        $this->setFontName($col, $row, $sheetNo, $style['font']);
        $this->setUnderline($col, $row, $sheetNo, $style['underline']);
        $this->setFontBold($col, $row, $sheetNo, $style['bold']);
        $this->setItalic($col, $row, $sheetNo, $style['italic']);
        $this->setStrikethrough($col, $row, $sheetNo, $style['strikethrough']);
        $this->setColor($col, $row, $sheetNo, $style['color']);
        $this->setSize($col, $row, $sheetNo, $style['size']);
        $this->setAlignHolizonal($col, $row, $sheetNo, $style['alignh']);
        $this->setAlignVertical($col, $row, $sheetNo, $style['alignv']);
        $this->setBackgroundColor($col, $row, $sheetNo, $style['bgcolor'], $style['bgpattern']);
    }

    /**
    * setFontName
    * フォントのセット
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param string|null $fontName フォント名
    * @author hagiwara
    */
    public function setFontName($col, $row, $sheetNo, $fontName)
    {
        if (is_null($fontName)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setName($fontName);
    }

    /**
    * setUnderline
    * 下線のセット
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param boolean|null $underline 下線を引くか
    * @author hagiwara
    */
    public function setUnderline($col, $row, $sheetNo, $underline)
    {
        if (is_null($underline)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setUnderline($underline);
    }

    /**
    * setFontBold
    * 太字のセット
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param boolean|null $bold 太字を引くか
    * @author hagiwara
    */
    public function setFontBold($col, $row, $sheetNo, $bold)
    {
        if (is_null($bold)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setBold($bold);
    }

    /**
    * setItalic
    * イタリックのセット
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param boolean|null $italic イタリックにするか
    * @author hagiwara
    */
    public function setItalic($col, $row, $sheetNo, $italic)
    {
        if (is_null($italic)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setItalic($italic);
    }

    /**
    * setStrikethrough
    * 打ち消し線のセット
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param boolean|null $strikethrough 打ち消し線をつけるか
    * @author hagiwara
    */
    public function setStrikethrough($col, $row, $sheetNo, $strikethrough)
    {
        if (is_null($strikethrough)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setStrikethrough($strikethrough);
    }

    /**
    * setColor
    * 文字の色
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param string|null $color 色(ARGB)
    * @author hagiwara
    */
    public function setColor($col, $row, $sheetNo, $color)
    {
        if (is_null($color)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->getColor()->setARGB($color);
    }

    /**
    * setSize
    * 文字サイズ
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param integer|null $size
    * @author hagiwara
    */
    public function setSize($col, $row, $sheetNo, $size)
    {
        if (is_null($size)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setSize($size);
    }

    /**
    * getFont
    * fontデータ取得
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @author hagiwara
    */
    private function getFont($col, $row, $sheetNo)
    {
        $cellInfo = $this->cellInfo($col, $row);
        return $this->getSheet($sheetNo)->getStyle($cellInfo)->getFont();
    }

    /**
    * setAlignHolizonal
    * 水平方向のalign
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param string|null $type
    * typeはgetAlignHolizonalType参照
    * @author hagiwara
    */
    public function setAlignHolizonal($col, $row, $sheetNo, $type)
    {
        if (is_null($type)) {
            return;
        }
        $this->getAlignment($col, $row, $sheetNo)->setHorizontal($this->getAlignHolizonalType($type));
    }

    /**
    * setAlignVertical
    * 垂直方法のalign
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param string|null $type
    * typeはgetAlignVerticalType参照
    * @author hagiwara
    */
    public function setAlignVertical($col, $row, $sheetNo, $type)
    {
        if (is_null($type)) {
            return;
        }
        $this->getAlignment($col, $row, $sheetNo)->setVertical($this->getAlignVerticalType($type));
    }

    /**
    * getAlignment
    * alignmentデータ取得
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @author hagiwara
    */
    private function getAlignment($col, $row, $sheetNo)
    {
        $cellInfo = $this->cellInfo($col, $row);
        return $this->getSheet($sheetNo)->getStyle($cellInfo)->getAlignment();
    }

    /**
    * setBorder
    * 罫線の設定
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param array $border
    * borderの内部はgetBorderType参照
    * @author hagiwara
    */
    public function setBorder($col, $row, $sheetNo, $border)
    {
        $cellInfo = $this->cellInfo($col, $row);
        $default_border = [
            'left' => null,
            'right' => null,
            'top' => null,
            'bottom' => null,
            'diagonal' => null,
            'all_borders' => null,
            'outline' => null,
            'inside' => null,
            'vertical' => null,
            'horizontal' => null,
        ];
        $border = array_merge($default_border, $border);
        foreach ($border as $border_position => $border_setting) {
            if (!is_null($border_setting)) {
                $borderInfo =  $this->getSheet($sheetNo)->getStyle($cellInfo)->getBorders()->{'get' . $this->camelize($border_position)}();
                if (array_key_exists('type', $border_setting)) {
                    $borderInfo->setBorderStyle($this->getBorderType($border_setting['type']));
                }
                if (array_key_exists('color', $border_setting)) {
                    $borderInfo->getColor()->setARGB($border_setting['color']);
                }
            }
        }
    }

    /**
    * setBackgroundColor
    * 背景色の設定
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @param string $color 色
    * @param string $fillType 塗りつぶし方(デフォルトsolid)
    * fillTypeの内部はgetFillType参照
    * @author hagiwara
    */
    public function setBackgroundColor($col, $row, $sheetNo, $color, $fillType = 'solid')
    {
        $cellInfo = $this->cellInfo($col, $row);

        $this->getSheet($sheetNo)->getStyle($cellInfo)->getFill()->setFillType($this->getFillType($fillType))->getStartColor()->setARGB($color);
    }

    /**
    * getBorderType
    * 罫線の種類の設定
    * @param string $type
    * @author hagiwara
    */
    private function getBorderType($type)
    {
        $type_list = self::$__borderType;
        if (array_key_exists($type, $type_list)) {
            return $type_list[$type];
        }
        return PHPExcel_Style_Border::BORDER_NONE;
    }

    /**
    * getAlignHolizonalType
    * 水平方向のAlignの設定
    * @param string $type
    * @author hagiwara
    */
    private function getAlignHolizonalType($type)
    {
        $type_list = self::$__alignHolizonalType;
        if (array_key_exists($type, $type_list)) {
            return $type_list[$type];
        }
        return PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
    }

    /**
    * getAlignVerticalType
    * 垂直方向のAlignの設定
    * @param string $type
    * @author hagiwara
    */
    private function getAlignVerticalType($type)
    {
        $type_list = self::$__alignVerticalType;
        if (array_key_exists($type, $type_list)) {
            return $type_list[$type];
        }
        return null;
    }

    /**
    * getFillType
    * 塗りつぶしの設定
    * @param string $type
    * @author hagiwara
    */
    private function getFillType($type)
    {
        $type_list = self::$__fillType;
        if (array_key_exists($type, $type_list)) {
            return $type_list[$type];
        }
        return PHPExcel_Style_Fill::FILL_SOLID;
    }

    /**
    * createSheet
    * シートの作成
    * @param string $name
    * @author hagiwara
    */
    public function createSheet($name = null)
    {
        //シートの新規作成
        $newSheet = $this->__phpexcel->createSheet();
        $sheetNo = $this->__phpexcel->getIndex($newSheet);
        $this->__sheet[$sheetNo] = $newSheet;
        if (!is_null($name)) {
            $this->renameSheet($sheetNo, $name);
        }
    }

    /**
    * deleteSheet
    * シートの削除
    * @param integer $sheetNo
    * @author hagiwara
    */
    public function deleteSheet($sheetNo)
    {
        //シートの削除は一番最後に行う
        $this->__deleteSheetList[] = $sheetNo;
    }

    /**
    * copySheet
    * シートのコピー
    * @param integer $sheetNo
    * @param integer $position
    * @param string $name
    * @author hagiwara
    */
    public function copySheet($sheetNo, $position = null, $name = null)
    {
        $base = $this->getSheet($sheetNo)->copy();
        if ($name === null) {
            $name = uniqid();
        }
        $base->setTitle($name);

        // $positionが null(省略時含む)の場合は最後尾に追加される
        $this->__phpexcel->addSheet($base, $position);
    }

    /**
    * renameSheet
    * シート名の変更
    * @param integer $sheetNo
    * @param string $name
    * @author hagiwara
    */
    public function renameSheet($sheetNo, $name)
    {
        $this->__sheet[$sheetNo]->setTitle($name);
    }

    /**
    * write
    * xlsxファイルの書き込み
    * @param string $file 書き込み先のファイルパス
    * @author hagiwara
    */
    public function write($file, $type = 'Excel2007')
    {
        //書き込み前に削除シートを削除する
        foreach ($this->__deleteSheetList as $deleteSheet) {
            $this->__phpexcel->removeSheetByIndex($deleteSheet);
        }
        $writer = PHPExcel_IOFactory::createWriter($this->__phpexcel, $type);
        $writer->save($file);
    }

    /**
    * getReader
    * readerを返す(※直接PHPExcelの関数を実行できるように)
    * @author hagiwara
    */
    public function getReader()
    {
        return $this->__phpexcel;
    }

    /**
    * getSheet
    * シート情報の読み込み
    * @param integer $sheetNo シート番号
    * @author hagiwara
    * @return null|\PHPExcel_Worksheet
    */
    private function getSheet($sheetNo)
    {
        if (!array_key_exists($sheetNo, $this->__sheet)) {
            $this->__sheet[$sheetNo] = $this->__phpexcel->setActiveSheetIndex(0);
        }
        return $this->__sheet[$sheetNo];
    }

    /**
    * cellInfo
    * R1C1参照をA1参照に変換して返す
    * @param integer $col 行
    * @param integer $row 列
    * @author hagiwara
    */
    private function cellInfo($col, $row)
    {
        $stringCol = PHPExcel_Cell::stringFromColumnIndex($col);
        return $stringCol . $row;
    }

    /**
    * cellInfo
    * http://qiita.com/Hiraku/items/036080976884fad1e450
    * @param string $str
    */
    private function camelize($str)
    {
        $str = ucwords($str, '_');
        return str_replace('_', '', $str);
    }
}


1			<?php
2
3			namespace PhpExcelWrapper;
4
5			use \PHPExcel_IOFactory;
6			use \PHPExcel_Cell;
7			use \PHPExcel_Worksheet_Drawing;
8			use \PHPExcel_Style_Border;
9			use \PHPExcel_Style_Alignment;
10			use \PHPExcel_Style_Fill;
11
12			/**
13			* PhpExcelWrapper
14			* PHPExcelを記載しやすくするためのラッパー
15			*/
16			class PhpExcelWrapper
17			{
18			private $__phpexcel;
19			private $__sheet = [];
20			private $__deleteSheetList = [];
21			private static $__borderType = [
22			'none' => PHPExcel_Style_Border::BORDER_NONE,
23			'thin' => PHPExcel_Style_Border::BORDER_THIN,
24			'medium' => PHPExcel_Style_Border::BORDER_MEDIUM,
25			'dashed' => PHPExcel_Style_Border::BORDER_DASHED,
26			'dotted' => PHPExcel_Style_Border::BORDER_DOTTED,
27			'thick' => PHPExcel_Style_Border::BORDER_THICK,
28			'double' => PHPExcel_Style_Border::BORDER_DOUBLE,
29			'hair' => PHPExcel_Style_Border::BORDER_HAIR,
30			'mediumdashed' => PHPExcel_Style_Border::BORDER_MEDIUMDASHED,
31			'dashdot' => PHPExcel_Style_Border::BORDER_DASHDOT,
32			'mediumdashdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,
33			'dashdotdot' => PHPExcel_Style_Border::BORDER_DASHDOTDOT,
34			'mediumdashdotdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,
35			'slantdashdot' => PHPExcel_Style_Border::BORDER_SLANTDASHDOT,
36			];
37
38			private static $__alignHolizonalType = [
39			'general' => PHPExcel_Style_Alignment::HORIZONTAL_GENERAL,
40			'center' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
41			'left' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
42			'right' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
43			'justify' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY,
44			'countinuous' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS,
45			];
46
47			private static $__alignVerticalType = [
48			'bottom' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM,
49			'center' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
50			'justify' => PHPExcel_Style_Alignment::VERTICAL_JUSTIFY,
51			'top' => PHPExcel_Style_Alignment::VERTICAL_TOP,
52			];
53
54			private static $__fillType = [
55			'linear' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
56			'path' => PHPExcel_Style_Fill::FILL_GRADIENT_PATH,
57			'none' => PHPExcel_Style_Fill::FILL_NONE,
58			'darkdown' => PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN,
59			'darkgray' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY,
60			'darkgrid' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID,
61			'darkhorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL,
62			'darktrellis' => PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS,
63			'darkup' => PHPExcel_Style_Fill::FILL_PATTERN_DARKUP,
64			'darkvertical' => PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL,
65			'gray0625' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625,
66			'gray125' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY125,
67			'lightdown' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN,
68			'lightgray' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY,
69			'lightgrid' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID,
70			'lighthorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL,
71			'lighttrellis' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS,
72			'lightup' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP,
73			'lightvertical' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL,
74			'mediumgray' => PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY,
75			'solid' => PHPExcel_Style_Fill::FILL_SOLID,
76			];
77
78			/**
79			* __construct
80			*
81			* @param string $template テンプレートファイルのパス
82			* @author hagiwara
83			*/
84			public function __construct($template = null, $type = 'Excel2007')
85			{
86			if ($template === null) {
87			//テンプレート無し
88			$this->__phpexcel = new \PHPExcel();
89			} else {
90			//テンプレートの読み込み
91			$reader = PHPExcel_IOFactory::createReader($type);
92			$this->__phpexcel = $reader->load($template);
93			}
94			}
95
96			/**
97			* setVal
98			* 値のセット
99			* @param string $value 値
100			* @param integer $col 行
101			* @param integer $row 列
102			* @param integer $sheetNo シート番号
103			* @param integer $refCol 参照セル行
104			* @param integer $refRow 参照セル列
105			* @param integer $refSheet 参照シート
106			* @author hagiwara
107			*/
108			public function setVal($value, $col, $row, $sheetNo = 0, $refCol = null, $refRow = null, $refSheet = 0)
109			{
110			$cellInfo = $this->cellInfo($col, $row);
111			//値のセット
112			$this->getSheet($sheetNo)->setCellValue($cellInfo, $value);
113
114			//参照セルの指定がある場合には書式をコピーする
115			if (!is_null($refCol) && !is_null($refRow)) {
116			$this->styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet);
117			}
118			}
119
120			/**
121			* setImage
122			* 画像のセット
123			* @param string $img 画像のファイルパス
124			* @param integer $col 行
125			* @param integer $row 列
126			* @param integer $sheetNo シート番号
127			* @param integer $height 画像の縦幅
128			* @param integer $width 画像の横幅
129			* @param boolean $proportial 縦横比を維持するか
130			* @param integer $offsetx セルから何ピクセルずらすか（X軸)
131			* @param integer $offsety セルから何ピクセルずらすか（Y軸)
132			* @author hagiwara
133			*/
134			public function setImage($img, $col, $row, $sheetNo = 0, $height = null, $width = null, $proportial = false, $offsetx = null, $offsety = null)
135			{
136			$cellInfo = $this->cellInfo($col, $row);
137
138			$objDrawing = new PHPExcel_Worksheet_Drawing();//画像用のオプジェクト作成
139			$objDrawing->setPath($img);//貼り付ける画像のパスを指定
140			$objDrawing->setCoordinates($cellInfo);//位置
141			if (!is_null($height)) {
142			$objDrawing->setHeight($height);//画像の高さを指定
143			}
144			if (!is_null($width)) {
145			$objDrawing->setWidth($width);//画像の高さを指定
146			}
147			if (!is_null($proportial)) {
148			$objDrawing->setResizeProportional($proportial);//縦横比の変更なし
149			}
150			if (!is_null($offsetx)) {
151			$objDrawing->setOffsetX($offsetx);//指定した位置からどれだけ横方向にずらすか。
152			}
153			if (!is_null($offsety)) {
154			$objDrawing->setOffsetY($offsety);//指定した位置からどれだけ縦方向にずらすか。
155			}
156			$objDrawing->setWorksheet($this->getSheet($sheetNo));
157			}
158
159			/**
160			* cellMerge
161			* セルのマージ
162			* @param integer $col1 行
163			* @param integer $row1 列
164			* @param integer $col2 行
165			* @param integer $row2 列
166			* @param integer $sheetNo シート番号
167			* @author hagiwara
168			*/
169			public function cellMerge($col1, $row1, $col2, $row2, $sheetNo)
170			{
171			$cell1Info = $this->cellInfo($col1, $row1);
172			$cell2Info = $this->cellInfo($col2, $row2);
173
174			$this->getSheet($sheetNo)->mergeCells($cell1Info . ':' . $cell2Info);
175			}
176
177
178			/**
179			* styleCopy
180			* セルの書式コピー
181			* @param integer $col 行
182			* @param integer $row 列
183			* @param integer $sheetNo シート番号
184			* @param integer $refCol 参照セル行
185			* @param integer $refRow 参照セル列
186			* @param integer $refSheet 参照シート
187			* @author hagiwara
188			*/
189			public function styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet)
190			{
191			$cellInfo = $this->cellInfo($col, $row);
192			$refCellInfo = $this->cellInfo($refCol, $refRow);
193			$style = $this->getSheet($refSheet)->getStyle($refCellInfo);
194
195			$this->getSheet($sheetNo)->duplicateStyle($style, $cellInfo);
196			}
197
198			/**
199			* setStyle
200			* 書式のセット(まとめて)
201			* @param integer $col 行
202			* @param integer $row 列
203			* @param integer $sheetNo シート番号
204			* @param array $style スタイル情報
205			* @author hagiwara
206			*/
207			public function setStyle($col, $row, $sheetNo, $style)
208			{
209			$default_style = [
210			'font' => null,
211			'underline' => null,
212			'bold' => null,
213			'italic' => null,
214			'strikethrough' => null,
215			'color' => null,
216			'size' => null,
217			'alignh' => null,
218			'alignv' => null,
219			'bgcolor' => null,
220			'bgpattern' => null,
221			];
222			$style = array_merge($default_style, $style);
223			$this->setFontName($col, $row, $sheetNo, $style['font']);
224			$this->setUnderline($col, $row, $sheetNo, $style['underline']);
225			$this->setFontBold($col, $row, $sheetNo, $style['bold']);
226			$this->setItalic($col, $row, $sheetNo, $style['italic']);
227			$this->setStrikethrough($col, $row, $sheetNo, $style['strikethrough']);
228			$this->setColor($col, $row, $sheetNo, $style['color']);
229			$this->setSize($col, $row, $sheetNo, $style['size']);
230			$this->setAlignHolizonal($col, $row, $sheetNo, $style['alignh']);
231			$this->setAlignVertical($col, $row, $sheetNo, $style['alignv']);
232			$this->setBackgroundColor($col, $row, $sheetNo, $style['bgcolor'], $style['bgpattern']);
233			}
234
235			/**
236			* setFontName
237			* フォントのセット
238			* @param integer $col 行
239			* @param integer $row 列
240			* @param integer $sheetNo シート番号
241			* @param string\|null $fontName フォント名
242			* @author hagiwara
243			*/
244			public function setFontName($col, $row, $sheetNo, $fontName)
245			{
246			if (is_null($fontName)) {
247			return;
248			}
249			$this->getFont($col, $row, $sheetNo)->setName($fontName);
250			}
251
252			/**
253			* setUnderline
254			* 下線のセット
255			* @param integer $col 行
256			* @param integer $row 列
257			* @param integer $sheetNo シート番号
258			* @param boolean\|null $underline 下線を引くか
259			* @author hagiwara
260			*/
261			public function setUnderline($col, $row, $sheetNo, $underline)
262			{
263			if (is_null($underline)) {
264			return;
265			}
266			$this->getFont($col, $row, $sheetNo)->setUnderline($underline);
267			}
268
269			/**
270			* setFontBold
271			* 太字のセット
272			* @param integer $col 行
273			* @param integer $row 列
274			* @param integer $sheetNo シート番号
275			* @param boolean\|null $bold 太字を引くか
276			* @author hagiwara
277			*/
278			public function setFontBold($col, $row, $sheetNo, $bold)
279			{
280			if (is_null($bold)) {
281			return;
282			}
283			$this->getFont($col, $row, $sheetNo)->setBold($bold);
284			}
285
286			/**
287			* setItalic
288			* イタリックのセット
289			* @param integer $col 行
290			* @param integer $row 列
291			* @param integer $sheetNo シート番号
292			* @param boolean\|null $italic イタリックにするか
293			* @author hagiwara
294			*/
295			public function setItalic($col, $row, $sheetNo, $italic)
296			{
297			if (is_null($italic)) {
298			return;
299			}
300			$this->getFont($col, $row, $sheetNo)->setItalic($italic);
301			}
302
303			/**
304			* setStrikethrough
305			* 打ち消し線のセット
306			* @param integer $col 行
307			* @param integer $row 列
308			* @param integer $sheetNo シート番号
309			* @param boolean\|null $strikethrough 打ち消し線をつけるか
310			* @author hagiwara
311			*/
312			public function setStrikethrough($col, $row, $sheetNo, $strikethrough)
313			{
314			if (is_null($strikethrough)) {
315			return;
316			}
317			$this->getFont($col, $row, $sheetNo)->setStrikethrough($strikethrough);
318			}
319
320			/**
321			* setColor
322			* 文字の色
323			* @param integer $col 行
324			* @param integer $row 列
325			* @param integer $sheetNo シート番号
326			* @param string\|null $color 色(ARGB)
327			* @author hagiwara
328			*/
329			public function setColor($col, $row, $sheetNo, $color)
330			{
331			if (is_null($color)) {
332			return;
333			}
334			$this->getFont($col, $row, $sheetNo)->getColor()->setARGB($color);
335			}
336
337			/**
338			* setSize
339			* 文字サイズ
340			* @param integer $col 行
341			* @param integer $row 列
342			* @param integer $sheetNo シート番号
343			* @param integer\|null $size
344			* @author hagiwara
345			*/
346			public function setSize($col, $row, $sheetNo, $size)
347			{
348			if (is_null($size)) {
349			return;
350			}
351			$this->getFont($col, $row, $sheetNo)->setSize($size);
352			}
353
354			/**
355			* getFont
356			* fontデータ取得
357			* @param integer $col 行
358			* @param integer $row 列
359			* @param integer $sheetNo シート番号
360			* @author hagiwara
361			*/
362			private function getFont($col, $row, $sheetNo)
363			{
364			$cellInfo = $this->cellInfo($col, $row);
365			return $this->getSheet($sheetNo)->getStyle($cellInfo)->getFont();
366			}
367
368			/**
369			* setAlignHolizonal
370			* 水平方向のalign
371			* @param integer $col 行
372			* @param integer $row 列
373			* @param integer $sheetNo シート番号
374			* @param string\|null $type
375			* typeはgetAlignHolizonalType参照
376			* @author hagiwara
377			*/
378			public function setAlignHolizonal($col, $row, $sheetNo, $type)
379			{
380			if (is_null($type)) {
381			return;
382			}
383			$this->getAlignment($col, $row, $sheetNo)->setHorizontal($this->getAlignHolizonalType($type));
384			}
385
386			/**
387			* setAlignVertical
388			* 垂直方法のalign
389			* @param integer $col 行
390			* @param integer $row 列
391			* @param integer $sheetNo シート番号
392			* @param string\|null $type
393			* typeはgetAlignVerticalType参照
394			* @author hagiwara
395			*/
396			public function setAlignVertical($col, $row, $sheetNo, $type)
397			{
398			if (is_null($type)) {
399			return;
400			}
401			$this->getAlignment($col, $row, $sheetNo)->setVertical($this->getAlignVerticalType($type));
402			}
403
404			/**
405			* getAlignment
406			* alignmentデータ取得
407			* @param integer $col 行
408			* @param integer $row 列
409			* @param integer $sheetNo シート番号
410			* @author hagiwara
411			*/
412			private function getAlignment($col, $row, $sheetNo)
413			{
414			$cellInfo = $this->cellInfo($col, $row);
415			return $this->getSheet($sheetNo)->getStyle($cellInfo)->getAlignment();
416			}
417
418			/**
419			* setBorder
420			* 罫線の設定
421			* @param integer $col 行
422			* @param integer $row 列
423			* @param integer $sheetNo シート番号
424			* @param array $border
425			* borderの内部はgetBorderType参照
426			* @author hagiwara
427			*/
428			public function setBorder($col, $row, $sheetNo, $border)
429			{
430			$cellInfo = $this->cellInfo($col, $row);
431			$default_border = [
432			'left' => null,
433			'right' => null,
434			'top' => null,
435			'bottom' => null,
436			'diagonal' => null,
437			'all_borders' => null,
438			'outline' => null,
439			'inside' => null,
440			'vertical' => null,
441			'horizontal' => null,
442			];
443			$border = array_merge($default_border, $border);
444			foreach ($border as $border_position => $border_setting) {
445			if (!is_null($border_setting)) {
446			$borderInfo = $this->getSheet($sheetNo)->getStyle($cellInfo)->getBorders()->{'get' . $this->camelize($border_position)}();
447			if (array_key_exists('type', $border_setting)) {
448			$borderInfo->setBorderStyle($this->getBorderType($border_setting['type']));
449			}
450			if (array_key_exists('color', $border_setting)) {
451			$borderInfo->getColor()->setARGB($border_setting['color']);
452			}
453			}
454			}
455			}
456
457			/**
458			* setBackgroundColor
459			* 背景色の設定
460			* @param integer $col 行
461			* @param integer $row 列
462			* @param integer $sheetNo シート番号
463			* @param string $color 色
464			* @param string $fillType 塗りつぶし方(デフォルトsolid)
465			* fillTypeの内部はgetFillType参照
466			* @author hagiwara
467			*/
468			public function setBackgroundColor($col, $row, $sheetNo, $color, $fillType = 'solid')
469			{
470			$cellInfo = $this->cellInfo($col, $row);
471
472			$this->getSheet($sheetNo)->getStyle($cellInfo)->getFill()->setFillType($this->getFillType($fillType))->getStartColor()->setARGB($color);
473			}
474
475			/**
476			* getBorderType
477			* 罫線の種類の設定
478			* @param string $type
479			* @author hagiwara
480			*/
481			private function getBorderType($type)
482			{
483			$type_list = self::$__borderType;
484			if (array_key_exists($type, $type_list)) {
485			return $type_list[$type];
486			}
487			return PHPExcel_Style_Border::BORDER_NONE;
488			}
489
490			/**
491			* getAlignHolizonalType
492			* 水平方向のAlignの設定
493			* @param string $type
494			* @author hagiwara
495			*/
496			private function getAlignHolizonalType($type)
497			{
498			$type_list = self::$__alignHolizonalType;
499			if (array_key_exists($type, $type_list)) {
500			return $type_list[$type];
501			}
502			return PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
503			}
504
505			/**
506			* getAlignVerticalType
507			* 垂直方向のAlignの設定
508			* @param string $type
509			* @author hagiwara
510			*/
511			private function getAlignVerticalType($type)
512			{
513			$type_list = self::$__alignVerticalType;
514			if (array_key_exists($type, $type_list)) {
515			return $type_list[$type];
516			}
517			return null;
518			}
519
520			/**
521			* getFillType
522			* 塗りつぶしの設定
523			* @param string $type
524			* @author hagiwara
525			*/
526			private function getFillType($type)
527			{
528			$type_list = self::$__fillType;
529			if (array_key_exists($type, $type_list)) {
530			return $type_list[$type];
531			}
532			return PHPExcel_Style_Fill::FILL_SOLID;
533			}
534
535			/**
536			* createSheet
537			* シートの作成
538			* @param string $name
539			* @author hagiwara
540			*/
541			public function createSheet($name = null)
542			{
543			//シートの新規作成
544			$newSheet = $this->__phpexcel->createSheet();
545			$sheetNo = $this->__phpexcel->getIndex($newSheet);
546			$this->__sheet[$sheetNo] = $newSheet;
547			if (!is_null($name)) {
548			$this->renameSheet($sheetNo, $name);
549			}
550			}
551
552			/**
553			* deleteSheet
554			* シートの削除
555			* @param integer $sheetNo
556			* @author hagiwara
557			*/
558			public function deleteSheet($sheetNo)
559			{
560			//シートの削除は一番最後に行う
561			$this->__deleteSheetList[] = $sheetNo;
562			}
563
564			/**
565			* copySheet
566			* シートのコピー
567			* @param integer $sheetNo
568			* @param integer $position
569			* @param string $name
570			* @author hagiwara
571			*/
572			public function copySheet($sheetNo, $position = null, $name = null)
573			{
574			$base = $this->getSheet($sheetNo)->copy();
575			if ($name === null) {
576			$name = uniqid();
577			}
578			$base->setTitle($name);
579
580			// $positionが null(省略時含む)の場合は最後尾に追加される
581			$this->__phpexcel->addSheet($base, $position);
582			}
583
584			/**
585			* renameSheet
586			* シート名の変更
587			* @param integer $sheetNo
588			* @param string $name
589			* @author hagiwara
590			*/
591			public function renameSheet($sheetNo, $name)
592			{
593			$this->__sheet[$sheetNo]->setTitle($name);
594			}
595
596			/**
597			* write
598			* xlsxファイルの書き込み
599			* @param string $file 書き込み先のファイルパス
600			* @author hagiwara
601			*/
602			public function write($file, $type = 'Excel2007')
603			{
604			//書き込み前に削除シートを削除する
605			foreach ($this->__deleteSheetList as $deleteSheet) {
606			$this->__phpexcel->removeSheetByIndex($deleteSheet);
607			}
608			$writer = PHPExcel_IOFactory::createWriter($this->__phpexcel, $type);
609			$writer->save($file);
610			}
611
612			/**
613			* getReader
614			* readerを返す(※直接PHPExcelの関数を実行できるように)
615			* @author hagiwara
616			*/
617			public function getReader()
618			{
619			return $this->__phpexcel;
620			}
621
622			/**
623			* getSheet
624			* シート情報の読み込み
625			* @param integer $sheetNo シート番号
626			* @author hagiwara
627			* @return null\|\PHPExcel_Worksheet
628			*/
629			private function getSheet($sheetNo)
630			{
631			if (!array_key_exists($sheetNo, $this->__sheet)) {
632			$this->__sheet[$sheetNo] = $this->__phpexcel->setActiveSheetIndex(0);
633			}
634			return $this->__sheet[$sheetNo];
635			}
636
637			/**
638			* cellInfo
639			* R1C1参照をA1参照に変換して返す
640			* @param integer $col 行
641			* @param integer $row 列
642			* @author hagiwara
643			*/
644			private function cellInfo($col, $row)
645			{
646			$stringCol = PHPExcel_Cell::stringFromColumnIndex($col);
647			return $stringCol . $row;
648			}
649
650			/**
651			* cellInfo
652			* http://qiita.com/Hiraku/items/036080976884fad1e450
653			* @param string $str
654			*/
655			private function camelize($str)
656			{
657			$str = ucwords($str, '_');
658			return str_replace('_', '', $str);
659			}
660			}
661

satthi / PhpExcelWrapper

Push — master ( 96b484...9beaa5 )

PhpExcelWrapper::getAlignment() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like