<?php
/**
 * Created by PhpStorm.
 * User: wanggang
 * Date: 2018/9/29
 * Time: 下午8:23
 */

namespace app\admin\controller;
use PHPExcel_IOFactory,PHPExcel;

class OutputExcel
{
    /*存储excel类对象*/
    private static $obpe;

    /*初始化*/
    public function __construct(){self::$obpe = new PHPExcel();}


    /**
     * 导出
     * @param array $mulit_arr
     * @param string $filename
     * @param string $Title
     * @param string $Subject
     * @param string $Description
     * @param string $Keywords
     * @param string $Category
     * @param string $type
     */
    public function export($mulit_arr = [],$filename = 'mulit_sheet',$Title='',$Subject='',$Description='',$Keywords='',$Category='',$type = 'output')
    {
        /* @func 设置文档基本属性 */
        $obpe_pro = self::$obpe->getProperties();
        $obpe_pro->setCreator('美行者')/*设置创建者*/
        ->setLastModifiedBy(date('Y/m/d H:i'))/*设置时间 2013/2/16 15:00*/
        ->setTitle($Title)/*设置标题*/
        ->setSubject($Subject)/*设置备注*/
        ->setDescription($Description)/*设置描述*/
        ->setKeywords($Keywords)/*设置关键字 | 标记*/
        ->setCategory($Category);/*设置类别*/

        self::$obpe->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('p')->setAutoSize(true);
        self::$obpe->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
        /*
         * 设置宽度
            $obpe->getActiveSheet()->getColumnDimension()->setAutoSize(true);
            $obpe->getActiveSheet()->getColumnDimension('B')->setWidth(10);
            设置当前sheet索引,用于后续的内容操作
            一般用在对个Sheet的时候才需要显示调用
            缺省情况下,PHPExcel会自动创建第一个SHEET被设置SheetIndex=0
        */
        /*$mulit_arr*/
        foreach ($mulit_arr['data'] as $key=>$value)
        {
            /*下面这一块可以封装后 实现多个工作空间的布局发生变化*/
            $next_key = $key+1;

            /*设置SHEET*/
            self::$obpe->setactivesheetindex($key);

            /*设置工作空间名称*/
            $sheet = self::$obpe->getActiveSheet();
            $sheet->setTitle($mulit_arr['sheet'][$key]['title']);

            /*写入数据*/
            foreach ($value as $k=>$v)
            {
                /* @func 设置列 */
                $kkup = $k+1;

                foreach ($v as $kk=>$vv)
                {
                    $where = self::getLetterByNumber($kk);/*X轴 改变这个位置可以改变文档布局*/
                    $x = $where.$kkup;
                    self::$obpe->getactivesheet()->setcellvalue($x,$vv);
                }
            }
            /*是否需要创建下一个工作空间*/
            if(isset($mulit_arr['data'][$next_key])) self::$obpe->createSheet();
        }

        /*写入内容*/
        $obwrite = PHPExcel_IOFactory::createWriter(self::$obpe, 'Excel5');

        $filename = $filename.'.xls';
        if($type == 'output')
        {
            /**直接在浏览器输出*/
            header('Pragma: public');
            header('Expires: 0');
            header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
            header('Content-Type:application/force-download');
            header('Content-Type:application/vnd.ms-execl');
            header('Content-Type:application/octet-stream');
            header('Content-Type:application/download');
            header("Content-Disposition:attachment;filename='$filename'");
            header('Content-Transfer-Encoding:binary');
            $obwrite->save('php://output');
        }else if($type == 'put') {
            /*ob_end_clean();*/
            /*保存文件*/
            $obwrite->save($filename);
        }
    }


    /**
     * 确定文件X横轴 # 这里暂时就弄这么多吧
     * @param int $number 传进来的就是$model的key
     * @return mixed
     */
    protected static function getLetterByNumber($number = 1)
    {
        return ['A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'][$number];
    }

}


