<?php
namespace App\Service;

use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Log;

class CommonService
{
    //保存内容到excel文件并下载,$exportType  excel文件操作类型  0存储数据直接保存， 1直接导出数据 ， 2保存并导出数据
    public function storeExcelStyle($excelName,$datas,$endLen='',$path='',$exportType=0){
        try{
            $arr = range('A','Z');
            if (!empty($endLen)){
                $endLen = $endLen - 1;
                if(isset($arr[$endLen])){
                    $arr = range('A',$arr[$endLen]);
                }
            }
            
            $e = Excel::create(iconv('UTF-8', 'GB2312', $excelName),function($excel) use ($datas,$arr){
                $excel->sheet('score', function($sheet) use ($datas,$arr){
                    $col =array();
                    $sheet->rows($datas);
                    foreach($arr as $v){
                        $col[$v]=12;
                    }
                    $sheet->setWidth($col);                    
                    foreach ($arr as $k=> $v){
                        $objRichText = new \PHPExcel_RichText();
                        if(!empty($datas[0][$k])){
                            if (strpos($datas[0][$k], '*') === 0){
                                $objPayable = $objRichText->createTextRun('*');
                                $objPayable->getFont()->setColor( new \PHPExcel_Style_Color( \PHPExcel_Style_Color::COLOR_RED ) );//设置颜色
                            }
                            $objRichText->createText(ltrim($datas[0][$k],'*'));
                            $sheet->setCellValue($arr[$k].'1',$objRichText);
                        }
                    }
                    
                });
            });
            
            if ($exportType == 1){
                if (empty($path)){//默认路径storage/exports
                    $e->export('xlsx');
                }else{//自定义路径 storage/$path
                    $e->export('xlsx', storage_path($path));
                }
            }elseif ($exportType == 2){
                if (empty($path)){//默认路径storage/exports
                    $e->store('xlsx')->export('xlsx');
                }else{//自定义路径 storage/$path
                    $e->store('xlsx', storage_path($path))->export('xlsx', storage_path($path));
                }
            }else{
                if (empty($path)){//默认路径storage/exports
                    $e->store('xlsx');
                }else{//自定义路径 storage/$path
                    $e->store('xlsx', storage_path($path));
                }
            }
            
            
            return true;
        }catch(\Exception $e){
            Log::channel('api')->error($e->getFile().'-'.$e->getLine().'-'.$e->getMessage());
            return false;
        }
    }
    
    //保存内容到excel文件，第一行*号红色,$firstRed为1则处理第一行红色*号,0不处理*
    public function exportPHPExcel($excelName,$datas,$firstRed=1){
        try{
            $datas0 = $datas[0];
            $objPHPExcel = new \PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            //$objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            $capitalArr = range('A','Z');
            if(count($datas0)>0 && $firstRed == 1){
                foreach($datas0 as $key => $value){
                    $objRichText = new \PHPExcel_RichText();
                    if (strpos($value, '*') === 0){
                        $objPayable = $objRichText->createTextRun('*');
                        $objPayable->getFont()->setColor( new \PHPExcel_Style_Color( \PHPExcel_Style_Color::COLOR_RED ) );//设置颜色
                    }
                    $objRichText->createText(ltrim($value,'*'));
                    $sheet = $key < 26 ? $capitalArr[$key] : 'A'.$capitalArr[$key-25];
                    $objPHPExcel->getActiveSheet()->setCellValue($sheet.'1', $objRichText);                    
                    $objPHPExcel->getActiveSheet()->getColumnDimension($sheet)->setWidth(12);
                }
            }
            
            foreach ($datas as $kdata=>$vdata){
                if ($kdata >= $firstRed){
                    foreach ($vdata as $key=>$value){
                        $sheet = $key < 26 ? $capitalArr[$key] : 'A'.$capitalArr[$key-25];
                        if ($key == 0){ //第一列
                            $objPHPExcel->getActiveSheet()->setCellValueExplicit($sheet.($kdata+1), $value,\PHPExcel_Cell_DataType::TYPE_STRING);
                        }elseif ($key == 1){//第二列
                            $objPHPExcel->getActiveSheet()->setCellValueExplicit($sheet.($kdata+1), $value,\PHPExcel_Cell_DataType::TYPE_NUMERIC);
                        }else{
                            $objPHPExcel->getActiveSheet()->setCellValue($sheet.($kdata+1), $value);
                        }
                        
                    }
                }
            }
            
            //$PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
            //header('Content-Type: application/vnd.ms-excel; charset=utf-8');
            //header('Content-Disposition: attachment;filename="'.$excelName.'.xls"');
            
            $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            ob_end_clean();
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$excelName.'.xlsx"');
            
            header('Cache-Control: max-age=0');//禁止缓存
            $PHPWriter->save("php://output");
            exit;
        }catch(\Exception $e){
            Log::channel('api')->info('下载失败：'.$e->getFile().'-'.$e->getLine().'-'.$e->getMessage());
            return false;
        }
    }
    
    
    
    //保存内容到excel文件，针对手机号码科学计数法特殊处理，$scientificCounting=1则处理第二列为string类型
    public function storeXlswriter($excelName,$datas,$path='',$scientificCounting=0){
        try{
            if ($scientificCounting == 1){
                foreach ($datas as $k=>$v){
                    if (strlen($v[1]) > 11 && is_numeric($v[1])){
                        $v[1] = (string)$v[1];
                        $datas[$k] = $v;
                    }
                }
            }
            
            if (empty($path)){
                $path = 'exports';
            }
            $pathDir = storage_path($path);
            $config = ['path' => $pathDir];
            $excel  = new \Vtiful\Kernel\Excel($config);
            $headers = $datas[0];
            unset($datas[0]);
            // fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数
            $filePath = $excel->fileName($excelName.'.xlsx', 'sheet1')
            ->header($headers)
            ->data($datas)
            ->output();
            
            if ($filePath == 'FilePath'){
                Log::info('保存文件失败'.$excelName);
                return false;
            }
            return true;
        }catch(\Exception $e){
            return response()->json(['rst' => 200,'msg'=>"异常".$e->getMessage()]);
        }
    }
    
    public function exportXlswriter($filename,$excel_data,$path=''){
        $config = ['path' => storage_path($path)];
        $excel  = new \Vtiful\Kernel\Excel($config);
        $filePath = $excel->fileName($filename.'.xlsx', 'sheet1')
        //->header(['Item', 'Cost'])
        ->data($excel_data)
        ->output();
        
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
        header('Content-Length: ' . filesize($filePath));
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        
        ob_clean();
        flush();
        
        if (copy($filePath, 'php://output') === false) {
            // Throw exception
        }
        
        // Delete temporary file
        @unlink($filePath);
    }
    
    public function readXlswriter($path,$filename){
        $config   = ['path' => $path];
        $excel    = new \Vtiful\Kernel\Excel($config);
        $datas = [];
        $datas = $excel->openFile($filename)
        ->openSheet()
        ->getSheetData();
        return array_merge(array_filter($datas));
    }
    
    
    
}

