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

Completed

Push — master ( 299fed...852dbe )

by satoru

created 2016-09-13 07:27 UTC

PhpExcelWrapper::setUnderline() A

↳ Parent: PhpExcelWrapper

Complexity

Conditions	2
Paths	2

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	7
rs	9.4285
cc	2
eloc	4
nc	2
nop	4

<?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);
        }
    }

    /**
    * getVal
    * 値の取得
    * @param integer $col 行
    * @param integer $row 列
    * @param integer $sheetNo シート番号
    * @author hagiwara
    */
    public function getVal($col, $row, $sheetNo = 0)
    {
        $cellInfo = $this->cellInfo($col, $row);
        //値の取得
        return $this->getSheet($sheetNo)->getCell($cellInfo)->getValue();
    }

    /**
    * 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			* getVal
132			* 値の取得
133			* @param integer $col 行
134			* @param integer $row 列
135			* @param integer $sheetNo シート番号
136			* @author hagiwara
137			*/
138			public function getVal($col, $row, $sheetNo = 0)
139			{
140			$cellInfo = $this->cellInfo($col, $row);
141			//値の取得
142			return $this->getSheet($sheetNo)->getCell($cellInfo)->getValue();
143			}
144
145			/**
146			* setImage
147			* 画像のセット
148			* @param string $img 画像のファイルパス
149			* @param integer $col 行
150			* @param integer $row 列
151			* @param integer $sheetNo シート番号
152			* @param integer $height 画像の縦幅
153			* @param integer $width 画像の横幅
154			* @param boolean $proportial 縦横比を維持するか
155			* @param integer $offsetx セルから何ピクセルずらすか（X軸)
156			* @param integer $offsety セルから何ピクセルずらすか（Y軸)
157			* @author hagiwara
158			*/
159			public function setImage($img, $col, $row, $sheetNo = 0, $height = null, $width = null, $proportial = false, $offsetx = null, $offsety = null)
160			{
161			$cellInfo = $this->cellInfo($col, $row);
162
163			$objDrawing = new PHPExcel_Worksheet_Drawing();//画像用のオプジェクト作成
164			$objDrawing->setPath($img);//貼り付ける画像のパスを指定
165			$objDrawing->setCoordinates($cellInfo);//位置
166			if (!is_null($proportial)) {
167			$objDrawing->setResizeProportional($proportial);//縦横比の変更なし
168			}
169			if (!is_null($height)) {
170			$objDrawing->setHeight($height);//画像の高さを指定
171			}
172			if (!is_null($width)) {
173			$objDrawing->setWidth($width);//画像の高さを指定
174			}
175			if (!is_null($offsetx)) {
176			$objDrawing->setOffsetX($offsetx);//指定した位置からどれだけ横方向にずらすか。
177			}
178			if (!is_null($offsety)) {
179			$objDrawing->setOffsetY($offsety);//指定した位置からどれだけ縦方向にずらすか。
180			}
181			$objDrawing->setWorksheet($this->getSheet($sheetNo));
182			}
183
184			/**
185			* cellMerge
186			* セルのマージ
187			* @param integer $col1 行
188			* @param integer $row1 列
189			* @param integer $col2 行
190			* @param integer $row2 列
191			* @param integer $sheetNo シート番号
192			* @author hagiwara
193			*/
194			public function cellMerge($col1, $row1, $col2, $row2, $sheetNo)
195			{
196			$cell1Info = $this->cellInfo($col1, $row1);
197			$cell2Info = $this->cellInfo($col2, $row2);
198
199			$this->getSheet($sheetNo)->mergeCells($cell1Info . ':' . $cell2Info);
200			}
201
202
203			/**
204			* styleCopy
205			* セルの書式コピー
206			* @param integer $col 行
207			* @param integer $row 列
208			* @param integer $sheetNo シート番号
209			* @param integer $refCol 参照セル行
210			* @param integer $refRow 参照セル列
211			* @param integer $refSheet 参照シート
212			* @author hagiwara
213			*/
214			public function styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet)
215			{
216			$cellInfo = $this->cellInfo($col, $row);
217			$refCellInfo = $this->cellInfo($refCol, $refRow);
218			$style = $this->getSheet($refSheet)->getStyle($refCellInfo);
219
220			$this->getSheet($sheetNo)->duplicateStyle($style, $cellInfo);
221			}
222
223			/**
224			* setStyle
225			* 書式のセット(まとめて)
226			* @param integer $col 行
227			* @param integer $row 列
228			* @param integer $sheetNo シート番号
229			* @param array $style スタイル情報
230			* @author hagiwara
231			*/
232			public function setStyle($col, $row, $sheetNo, $style)
233			{
234			$default_style = [
235			'font' => null,
236			'underline' => null,
237			'bold' => null,
238			'italic' => null,
239			'strikethrough' => null,
240			'color' => null,
241			'size' => null,
242			'alignh' => null,
243			'alignv' => null,
244			'bgcolor' => null,
245			'bgpattern' => null,
246			'border' => null,
247			];
248			$style = array_merge($default_style, $style);
249			$this->setFontName($col, $row, $sheetNo, $style['font']);
250			$this->setUnderline($col, $row, $sheetNo, $style['underline']);
251			$this->setFontBold($col, $row, $sheetNo, $style['bold']);
252			$this->setItalic($col, $row, $sheetNo, $style['italic']);
253			$this->setStrikethrough($col, $row, $sheetNo, $style['strikethrough']);
254			$this->setColor($col, $row, $sheetNo, $style['color']);
255			$this->setSize($col, $row, $sheetNo, $style['size']);
256			$this->setAlignHolizonal($col, $row, $sheetNo, $style['alignh']);
257			$this->setAlignVertical($col, $row, $sheetNo, $style['alignv']);
258			$this->setBackgroundColor($col, $row, $sheetNo, $style['bgcolor'], $style['bgpattern']);
259			$this->setBorder($col, $row, $sheetNo, $style['border']);
260			}
261
262			/**
263			* setFontName
264			* フォントのセット
265			* @param integer $col 行
266			* @param integer $row 列
267			* @param integer $sheetNo シート番号
268			* @param string\|null $fontName フォント名
269			* @author hagiwara
270			*/
271			public function setFontName($col, $row, $sheetNo, $fontName)
272			{
273			if (is_null($fontName)) {
274			return;
275			}
276			$this->getFont($col, $row, $sheetNo)->setName($fontName);
277			}
278
279			/**
280			* setUnderline
281			* 下線のセット
282			* @param integer $col 行
283			* @param integer $row 列
284			* @param integer $sheetNo シート番号
285			* @param string\|null $underline 下線の種類
286			* @author hagiwara
287			*/
288			public function setUnderline($col, $row, $sheetNo, $underline)
289			{
290			if (is_null($underline)) {
291			return;
292			}
293			$this->getFont($col, $row, $sheetNo)->setUnderline($underline);
294			}
295
296
297			/**
298			* getUnderlineType
299			* 下線の種類の設定
300			* @param string $type
301			* @author hagiwara
302			*/
303			private function getUnderlineType($type)
304			{
305			$type_list = self::$__underlineType;
306			if (array_key_exists($type, $type_list)) {
307			return $type_list[$type];
308			}
309			return PHPExcel_Style_Border::UNDERLINE_NONE;
310			}
311
312			/**
313			* setFontBold
314			* 太字のセット
315			* @param integer $col 行
316			* @param integer $row 列
317			* @param integer $sheetNo シート番号
318			* @param boolean\|null $bold 太字を引くか
319			* @author hagiwara
320			*/
321			public function setFontBold($col, $row, $sheetNo, $bold)
322			{
323			if (is_null($bold)) {
324			return;
325			}
326			$this->getFont($col, $row, $sheetNo)->setBold($bold);
327			}
328
329			/**
330			* setItalic
331			* イタリックのセット
332			* @param integer $col 行
333			* @param integer $row 列
334			* @param integer $sheetNo シート番号
335			* @param boolean\|null $italic イタリックにするか
336			* @author hagiwara
337			*/
338			public function setItalic($col, $row, $sheetNo, $italic)
339			{
340			if (is_null($italic)) {
341			return;
342			}
343			$this->getFont($col, $row, $sheetNo)->setItalic($italic);
344			}
345
346			/**
347			* setStrikethrough
348			* 打ち消し線のセット
349			* @param integer $col 行
350			* @param integer $row 列
351			* @param integer $sheetNo シート番号
352			* @param boolean\|null $strikethrough 打ち消し線をつけるか
353			* @author hagiwara
354			*/
355			public function setStrikethrough($col, $row, $sheetNo, $strikethrough)
356			{
357			if (is_null($strikethrough)) {
358			return;
359			}
360			$this->getFont($col, $row, $sheetNo)->setStrikethrough($strikethrough);
361			}
362
363			/**
364			* setColor
365			* 文字の色
366			* @param integer $col 行
367			* @param integer $row 列
368			* @param integer $sheetNo シート番号
369			* @param string\|null $color 色(ARGB)
370			* @author hagiwara
371			*/
372			public function setColor($col, $row, $sheetNo, $color)
373			{
374			if (is_null($color)) {
375			return;
376			}
377			$this->getFont($col, $row, $sheetNo)->getColor()->setARGB($color);
378			}
379
380			/**
381			* setSize
382			* 文字サイズ
383			* @param integer $col 行
384			* @param integer $row 列
385			* @param integer $sheetNo シート番号
386			* @param integer\|null $size
387			* @author hagiwara
388			*/
389			public function setSize($col, $row, $sheetNo, $size)
390			{
391			if (is_null($size)) {
392			return;
393			}
394			$this->getFont($col, $row, $sheetNo)->setSize($size);
395			}
396
397			/**
398			* getFont
399			* fontデータ取得
400			* @param integer $col 行
401			* @param integer $row 列
402			* @param integer $sheetNo シート番号
403			* @author hagiwara
404			*/
405			private function getFont($col, $row, $sheetNo)
406			{
407			$cellInfo = $this->cellInfo($col, $row);
408			return $this->getSheet($sheetNo)->getStyle($cellInfo)->getFont();
409			}
410
411			/**
412			* setAlignHolizonal
413			* 水平方向のalign
414			* @param integer $col 行
415			* @param integer $row 列
416			* @param integer $sheetNo シート番号
417			* @param string\|null $type
418			* typeはgetAlignHolizonalType参照
419			* @author hagiwara
420			*/
421			public function setAlignHolizonal($col, $row, $sheetNo, $type)
422			{
423			if (is_null($type)) {
424			return;
425			}
426			$this->getAlignment($col, $row, $sheetNo)->setHorizontal($this->getAlignHolizonalType($type));
427			}
428
429			/**
430			* setAlignVertical
431			* 垂直方法のalign
432			* @param integer $col 行
433			* @param integer $row 列
434			* @param integer $sheetNo シート番号
435			* @param string\|null $type
436			* typeはgetAlignVerticalType参照
437			* @author hagiwara
438			*/
439			public function setAlignVertical($col, $row, $sheetNo, $type)
440			{
441			if (is_null($type)) {
442			return;
443			}
444			$this->getAlignment($col, $row, $sheetNo)->setVertical($this->getAlignVerticalType($type));
445			}
446
447			/**
448			* getAlignment
449			* alignmentデータ取得
450			* @param integer $col 行
451			* @param integer $row 列
452			* @param integer $sheetNo シート番号
453			* @author hagiwara
454			*/
455			private function getAlignment($col, $row, $sheetNo)
456			{
457			$cellInfo = $this->cellInfo($col, $row);
458			return $this->getSheet($sheetNo)->getStyle($cellInfo)->getAlignment();
459			}
460
461			/**
462			* setBorder
463			* 罫線の設定
464			* @param integer $col 行
465			* @param integer $row 列
466			* @param integer $sheetNo シート番号
467			* @param array\|null $border
468			* borderの内部はgetBorderType参照
469			* @author hagiwara
470			*/
471			public function setBorder($col, $row, $sheetNo, $border)
472			{
473			if (is_null($border)) {
474			return;
475			}
476			$cellInfo = $this->cellInfo($col, $row);
477			$default_border = [
478			'left' => null,
479			'right' => null,
480			'top' => null,
481			'bottom' => null,
482			'diagonal' => null,
483			'all_borders' => null,
484			'outline' => null,
485			'inside' => null,
486			'vertical' => null,
487			'horizontal' => null,
488			];
489			$border = array_merge($default_border, $border);
490			foreach ($border as $border_position => $border_setting) {
491			if (!is_null($border_setting)) {
492			$borderInfo = $this->getSheet($sheetNo)->getStyle($cellInfo)->getBorders()->{'get' . $this->camelize($border_position)}();
493			if (array_key_exists('type', $border_setting)) {
494			$borderInfo->setBorderStyle($this->getBorderType($border_setting['type']));
495			}
496			if (array_key_exists('color', $border_setting)) {
497			$borderInfo->getColor()->setARGB($border_setting['color']);
498			}
499			}
500			}
501			}
502
503			/**
504			* setBackgroundColor
505			* 背景色の設定
506			* @param integer $col 行
507			* @param integer $row 列
508			* @param integer $sheetNo シート番号
509			* @param string $color 色
510			* @param string $fillType 塗りつぶし方(デフォルトsolid)
511			* fillTypeの内部はgetFillType参照
512			* @author hagiwara
513			*/
514			public function setBackgroundColor($col, $row, $sheetNo, $color, $fillType = 'solid')
515			{
516			$cellInfo = $this->cellInfo($col, $row);
517
518			$this->getSheet($sheetNo)->getStyle($cellInfo)->getFill()->setFillType($this->getFillType($fillType))->getStartColor()->setARGB($color);
519			}
520
521			/**
522			* getBorderType
523			* 罫線の種類の設定
524			* @param string $type
525			* @author hagiwara
526			*/
527			private function getBorderType($type)
528			{
529			$type_list = self::$__borderType;
530			if (array_key_exists($type, $type_list)) {
531			return $type_list[$type];
532			}
533			return PHPExcel_Style_Border::BORDER_NONE;
534			}
535
536			/**
537			* getAlignHolizonalType
538			* 水平方向のAlignの設定
539			* @param string $type
540			* @author hagiwara
541			*/
542			private function getAlignHolizonalType($type)
543			{
544			$type_list = self::$__alignHolizonalType;
545			if (array_key_exists($type, $type_list)) {
546			return $type_list[$type];
547			}
548			return PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
549			}
550
551			/**
552			* getAlignVerticalType
553			* 垂直方向のAlignの設定
554			* @param string $type
555			* @author hagiwara
556			*/
557			private function getAlignVerticalType($type)
558			{
559			$type_list = self::$__alignVerticalType;
560			if (array_key_exists($type, $type_list)) {
561			return $type_list[$type];
562			}
563			return null;
564			}
565
566			/**
567			* getFillType
568			* 塗りつぶしの設定
569			* @param string $type
570			* @author hagiwara
571			*/
572			private function getFillType($type)
573			{
574			$type_list = self::$__fillType;
575			if (array_key_exists($type, $type_list)) {
576			return $type_list[$type];
577			}
578			return PHPExcel_Style_Fill::FILL_SOLID;
579			}
580
581			/**
582			* createSheet
583			* シートの作成
584			* @param string $name
585			* @author hagiwara
586			*/
587			public function createSheet($name = null)
588			{
589			//シートの新規作成
590			$newSheet = $this->__phpexcel->createSheet();
591			$sheetNo = $this->__phpexcel->getIndex($newSheet);
592			$this->__sheet[$sheetNo] = $newSheet;
593			if (!is_null($name)) {
594			$this->renameSheet($sheetNo, $name);
595			}
596			}
597
598			/**
599			* deleteSheet
600			* シートの削除
601			* @param integer $sheetNo
602			* @author hagiwara
603			*/
604			public function deleteSheet($sheetNo)
605			{
606			//シートの削除は一番最後に行う
607			$this->__deleteSheetList[] = $sheetNo;
608			}
609
610			/**
611			* copySheet
612			* シートのコピー
613			* @param integer $sheetNo
614			* @param integer $position
615			* @param string $name
616			* @author hagiwara
617			*/
618			public function copySheet($sheetNo, $position = null, $name = null)
619			{
620			$base = $this->getSheet($sheetNo)->copy();
621			if ($name === null) {
622			$name = uniqid();
623			}
624			$base->setTitle($name);
625
626			// $positionが null(省略時含む)の場合は最後尾に追加される
627			$this->__phpexcel->addSheet($base, $position);
628			}
629
630			/**
631			* renameSheet
632			* シート名の変更
633			* @param integer $sheetNo
634			* @param string $name
635			* @author hagiwara
636			*/
637			public function renameSheet($sheetNo, $name)
638			{
639			$this->getSheet($sheetNo)->setTitle($name);
640			}
641
642			/**
643			* write
644			* xlsxファイルの書き込み
645			* @param string $file 書き込み先のファイルパス
646			* @author hagiwara
647			*/
648			public function write($file, $type = 'Excel2007')
649			{
650			//書き込み前に削除シートを削除する
651			foreach ($this->__deleteSheetList as $deleteSheet) {
652			$this->__phpexcel->removeSheetByIndex($deleteSheet);
653			}
654			$writer = PHPExcel_IOFactory::createWriter($this->__phpexcel, $type);
655			$writer->save($file);
656			}
657
658			/**
659			* getReader
660			* readerを返す(※直接PHPExcelの関数を実行できるように)
661			* @author hagiwara
662			*/
663			public function getReader()
664			{
665			return $this->__phpexcel;
666			}
667
668			/**
669			* getSheet
670			* シート情報の読み込み
671			* @param integer $sheetNo シート番号
672			* @author hagiwara
673			* @return null\|\PHPExcel_Worksheet
674			*/
675			private function getSheet($sheetNo)
676			{
677			if (!array_key_exists($sheetNo, $this->__sheet)) {
678			$this->__sheet[$sheetNo] = $this->__phpexcel->setActiveSheetIndex($sheetNo);
679			}
680			return $this->__sheet[$sheetNo];
681			}
682
683			/**
684			* cellInfo
685			* R1C1参照をA1参照に変換して返す
686			* @param integer $col 行
687			* @param integer $row 列
688			* @author hagiwara
689			*/
690			private function cellInfo($col, $row)
691			{
692			$stringCol = PHPExcel_Cell::stringFromColumnIndex($col);
693			return $stringCol . $row;
694			}
695
696			/**
697			* cellInfo
698			* http://qiita.com/Hiraku/items/036080976884fad1e450
699			* @param string $str
700			*/
701			private function camelize($str)
702			{
703			$str = ucwords($str, '_');
704			return str_replace('_', '', $str);
705			}
706			}
707

satthi / PhpExcelWrapper

Push — master ( 299fed...852dbe )

PhpExcelWrapper::setUnderline() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like