PhpExcelWrapper::getUnderlineType() - Code Metrics - Inspection of "Merge pull request #4 from satthi/testAdd" - satthi/PhpExcelWrapper - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 9beaa5...ef6547 )

by satoru

created 2016-05-25 08:15 UTC

PhpExcelWrapper::getUnderlineType() A

↳ Parent: PhpExcelWrapper

Complexity

Conditions	2
Paths	2

Size

Total Lines	8
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php

namespace PhpExcelWrapper;

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

/**
* PhpExcelWrapper
* PHPExcelを記載しやすくするためのラッパー
*/
class PhpExcelWrapper
{
    private $__phpexcel;
    private $__sheet = [];
    private $__deleteSheetList = [];
    private static $__underlineType = [
        'double' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,
        'doubleaccounting' => PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING,
        'none' => PHPExcel_Style_Font::UNDERLINE_NONE,
        'single' => PHPExcel_Style_Font::UNDERLINE_SINGLE,
        'singleaccounting' => PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING,
    ];

    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($proportial)) {
            $objDrawing->setResizeProportional($proportial);//縦横比の変更なし
        }
        if (!is_null($height)) {
            $objDrawing->setHeight($height);//画像の高さを指定
        }
        if (!is_null($width)) {
            $objDrawing->setWidth($width);//画像の高さを指定
        }
        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,
            'border' => 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']);
        $this->setBorder($col, $row, $sheetNo, $style['border']);
    }

    /**
    * 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 string|null $underline 下線の種類
    * @author hagiwara
    */
    public function setUnderline($col, $row, $sheetNo, $underline)
    {
        if (is_null($underline)) {
            return;
        }
        $this->getFont($col, $row, $sheetNo)->setUnderline($underline);
    }


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

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

satthi / PhpExcelWrapper

Push — master ( 9beaa5...ef6547 )

PhpExcelWrapper::getUnderlineType() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like