TP 5 PHPExcelエクスポートクラス

12556 ワード

電子商取引のショッピングモールの小さいプログラムの公衆番号のオープンソースのプロジェクト:住所をダウンロードして表のピクチャーを書き出します
 array(
            'allborders' => array(
//                PHPExcel_Style_Border       ,         
//                'style' => \PHPExcel_Style_Border::BORDER_THICK,//     
//                'style' => \PHPExcel_Style_Border::BORDER_DOUBLE,//   
//                'style' => \PHPExcel_Style_Border::BORDER_HAIR,//  
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUM,//   
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,//   
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,//    
                'style' => \PHPExcel_Style_Border::BORDER_THIN,//   
                //'color' => array('argb' => 'FFFF0000'),
            ),
        ),
        'font'=>[
            'bold'=>true
        ],
        'alignment'=>[
            'horizontal'=>\PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical'=>\PHPExcel_Style_Alignment::VERTICAL_CENTER
        ]
    );
    /**
     *   PHPExcel 
     *@param $data array()
     *@param $fun function()
     * return
     */
    private static function initialize($data,$fun){
        vendor("PHPExcel.PHPExcel.PHPExcel");
        self::$PHPExcel= new \PHPExcel();
        if($fun!==null && is_callable($fun)){
            self::$styleArray=$fun();
        }
        if(!is_array($data)) exit(Json::fail('data    '));
        self::$data=$data;
    }
    /**
     *      
     *@param $title string   
     * return string
     */
    public static function setUtf8($title){
        return iconv('utf-8', 'gb2312', $title);
    }
    /**
     *
     * execl    
     * @param  $list                   
     * @param  $list                  $sheet PHPExcel->getActiveSheet(),self::$topNumber       ,$cellkey      ,self::$cells         
     *
     *     :               
     */
    public function setExcelContent($list=null)
    {
        $sheet=self::$PHPExcel->getActiveSheet();
        foreach(self::$data as $key=>$val){
            $row=self::$cellKey[$key];
            $sheet->getColumnDimension($row)->setWidth(isset($val['w'])?$val['w']:self::$where);
            $sheet->setCellValue($row.self::$topNumber,isset($val['name'])?$val['name']:$val);
        }
        $cellkey=array_slice(self::$cellKey,0,self::$count);
        if($list!==null && is_array($list)){
            foreach ($cellkey as $k=>$v){
                foreach ($list as $key=>$val){
                    if(isset($val[$k]) && !is_array($val[$k])){
                        $sheet->setCellValue($v.(self::$topNumber+1+$key),$val[$k]);
                    }else if(isset($val[$k]) && is_array($val[$k])){
                        $str='';
                        foreach ($val[$k] as $value){
                            $str.=$value.chr(10);
                        }
                        $sheet->setCellValue($v.(self::$topNumber+1+$key),$str);
                    }
                }
            }
            $sheet->getDefaultRowDimension()->setRowHeight(self::$height);
            //    
            $sheet->getStyle('A1:'.self::$cells.(count($list)+self::$topNumber))->applyFromArray(self::$styleArray);
            //      
            $sheet->getStyle('A4:'.self::$cells.(count($list)+self::$topNumber))->getAlignment()->setWrapText(true);
        }else if($list!==null && is_callable($list)){
            $list($sheet,self::$topNumber,$cellkey,self::$cells)->applyFromArray(self::$styleArray);
        }

        return $this;
    }
    /**
     *       ,   
     * @param
     * @return
     */
    public function ExcelSave(){
        $objWriter=\PHPExcel_IOFactory::createWriter(self::$PHPExcel,'Excel2007');
        $filename=self::$title.'.xlsx';
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
        exit;
    }

    /**
     *       
     * @param $data array
     * @param $fun function()          
     * @return $this
     */
    public static function setExcelHeader($data,$fun=null)
    {
        self::initialize($data,$fun);

        if(self::$count=count(self::$data)){
            if(self::$count>count(self::$cellKey)){
                return Json::fail('      ');
            }
            self::$cells=self::$cellKey[self::$count-1];
        }else{
            return Json::fail('data        ');
        }
        return new self;
    }
    /**
     *     
     * @param $title string || array ['title'=>'','name'=>'','info'=>[]]
     * @param $Name string
     * @param $info string || array;
     * @param $funName function($style,$A,$A2)          
     * @return $this
     */
    public function setExcelTile($title='',$Name='',$info=[],$funName=null){
        //    
        if(is_array($title)){
            if(isset($title['title'])) $title=$title['title'];
            if(isset($title['name'])) $Name=$title['name'];
            if(isset($title['info'])) $info=$title['info'];
        }
        if(empty($title))
            $title=self::$title;
        else
            self::$title=$title;
        if(empty($Name)) $Name=time();
        //  Excel  
        self::$PHPExcel ->getProperties()
            ->setCreator("Neo")
            ->setLastModifiedBy("Neo")
            ->setTitle(self::setUtf8($title))
            ->setSubject($Name)
            ->setDescription("")
            ->setKeywords($Name)
            ->setCategory("");
        self::$PHPExcel ->getActiveSheet()->setCellValue('A1', $title);
        //    
        self::$PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        self::$PHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        self::$PHPExcel->setActiveSheetIndex(0);
        self::$PHPExcel->getActiveSheet()->setTitle($Name);

        self::$PHPExcel->getActiveSheet()->setCellValue('A2',self::setCellInfo($info));
        //       
        self::$PHPExcel->getActiveSheet()->mergeCells('A1:'.self::$cells.'1');
        self::$PHPExcel->getActiveSheet()->mergeCells('A2:'.self::$cells.'2');

        self::$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
        self::$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
        //      
        if($funName!==null && is_callable($funName)){
            $fontstyle=self::$PHPExcel->getActiveSheet();
            $funName($fontstyle,'A1','A2');
        }else{
            //      
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('  ');
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('  ');
            self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
        }
        self::$PHPExcel->getActiveSheet()->getStyle('A3:'.self::$cells.'3')->getFont()->setBold(true);

        return $this;
    }
    /**
     *          
     * @param $info  array (['name'=>'','site'=>'','phone'=>123] || ['    ','    ','      '] ) || string    
     * @return string
     */
    private static function setCellInfo($info){
        $content=['   :','    :'.date('Y-m-d',time()),'  :','  :'];
        if(is_array($info) && !empty($info)){
            if(isset($info['name'])){
                $content[0].=$info['name'];
            }else{
                $content[0].=isset($info[0])?$info[0]:'';
            }
            if(isset($info['site'])){
                $content[2].=$info['site'];
            }else{
                $content[2].=isset($info[1])?$info[1]:'';
            }
            if(isset($info['phone'])){
                $content[3].=$info['phone'];
            }else{
                $content[3].=isset($info[2])?$info[2]:'';
            }
            return implode(' ',$content);
        }else if(is_string($info)){
            return empty($info)?implode(' ',$content):$info;
        }
    }
}

使用例:
PHPExcelService::setExcelHeader(['   ','  ','  ','     ','     ','    ','    ',
            '   ','    ','  ','    ','    ','    ','    ','    ','    '])
            ->setExcelTile('    '.date('YmdHis',time()),'    '.time(),'     :'.date('Y-m-d H:i:s',time()))
            ->setExcelContent($export)
            ->ExcelSave();

$exportは配列変数、変数フォーマットは次のとおりです.
array(2) {
  [0]=>
  array(16) {
    [0]=>
    string(21) "wx2019071611093410001"
    [1]=>
    string(3) " "
    [2]=>
    NULL
    [3]=>
    string(4) "1123"
    [4]=>
    string(11) "13055558888"
    [5]=>
    string(30) "          123123"
    [6]=>
    array(1) {
      [0]=>
      string(48) "     F8         [1 * 0.10]"
    }
    [7]=>
    string(4) "0.10"
    [8]=>
    string(4) "0.10"
    [9]=>
    string(4) "0.00"
    [10]=>
    string(4) "0.00"
    [11]=>
    string(12) "    "
    [12]=>
    string(15) "2019/0716 11:09"
    [13]=>
    string(9) "   "
    [14]=>
    string(19) "2019-07-16 11:09:34"
    [15]=>
    string(0) ""
  }
  [1]=>
  array(16) {
    [0]=>
    string(21) "wx2019070209390010001"
    [1]=>
    string(3) " "
    [2]=>
    NULL
    [3]=>
    string(6) "  "
    [4]=>
    string(11) "18092217410"
    [5]=>
    string(37) "            1823 "
    [6]=>
    array(1) {
      [0]=>
      string(22) "      [1 * 10]"
    }
    [7]=>
    string(5) "10.00"
    [8]=>
    string(5) "10.00"
    [9]=>
    string(4) "0.00"
    [10]=>
    string(4) "0.00"
    [11]=>
    string(12) "    "
    [12]=>
    string(15) "2019/0702 09:39"
    [13]=>
    string(9) "   "
    [14]=>
    string(19) "2019-07-02 09:39:00"
    [15]=>
    string(0) ""
  }
}