<?php
/*
 本代码由 焕岱科技 创建
 技术支持 WX：15285007200
 严禁反编译、逆向等任何形式的侵权行为，违者将追究法律责任
*/

namespace app\common\model;

use think\Model;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class PhpOffice
{
    /**
     * excel表格导出(封装)
     * @param string $fileName 文件名称 $name='测试导出';
     * @param array $headArr 表头名称 $header=['表头A','表头B'];
     * @param array $data 要导出的数据 $data=[['测试','测试'],['测试','测试']]
     * @param array $merge 合并配置 $merge=['mergek'=>0,'merges'=>[0,1,2,3,6,7,8,9,10]
     * @param bool $auto 是否开启根据表头自适应宽度 默认开启
     * */
    public static function excelExport($fileName = '', $headArr = [], $data = [],$merge=[],$imgs=[], $auto = true, $format="xlsx")
    {
        // $dir = rtrim(str_replace('\\', '/', $_SERVER['DOCUMENT_ROOT']), '/') . '/public/excel';
        // if (!is_dir($dir)) {
        //     mkdir($dir, 0777, true);
        // }
    
        $fileName .= ".".$format;
        $objPHPExcel = new Spreadsheet();
        $objPHPExcel->getProperties();
        $key = ord("A"); // 设置表头
        $key2 = ord("@"); //	超过26列会报错的解决方案
    
        // 居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    
        // 设置表头
        foreach ($headArr as $v) {
            // 超过26列会报错的解决方案
            if ($key > ord("Z")) {
                $key2 += 1;
                $key = ord("A");
                $colum = chr($key2) . chr($key); //超过26个字母时才会启用
            } else {
                if ($key2 >= ord("A")) {
                    $colum = chr($key2) . chr($key);
                } else {
                    $colum = chr($key);
                }
            }
            // 写入表头
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            // 自适应宽度
            if ($auto) {
                // $len = strlen(iconv('utf-8','gb2312',$v));//会报错
                $len = strlen(iconv('utf-8', 'gbk', $v));
                $len = $len > 25 ? 30 : 20;
                $objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($len+5);
            }
            $key += 1;
        }
    
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        // 写入行数据
        
        foreach ($data as $key => $rows) {
            
            if(!empty($imgs)){
                $objActSheet->getRowDimension($key+2)->setRowHeight(80);
            }
            
            $span = ord("A");
            $span2 = ord("@");
            // 按列写入
            foreach ($rows as $keyName => $value) {
                // 超过26列会报错的解决方案
                if ($span > ord("Z")) {
                    $span2 += 1;
                    $span = ord("A");
                    $tmpSpan = chr($span2) . chr($span); //超过26个字母时才会启用
                } else {
                    if ($span2 >= ord("A")) {
                        $tmpSpan = chr($span2) . chr($span);
                    } else {
                        $tmpSpan = chr($span);
                    }
                }
                
                if(count($data) - 1 != $key && !empty($merge)){
                    if(in_array($keyName,$merge['merges']) && $data[$key][$merge['mergek']] == $data[$key+1][$merge['mergek']]){
                        $objPHPExcel->getActiveSheet()->mergeCells($tmpSpan . $column . ':'.$tmpSpan . ($column + 1));
                    }
                }
                
                if(!empty($imgs) && in_array($keyName,$imgs) && file_exists($value)){
                    // 插入图片
                    $objDrawing = new Drawing();
                    $objDrawing->setName($headArr[$keyName]);
                    $objDrawing->setDescription($headArr[$keyName]);
                    $objDrawing->setPath($value); // 图片路径
                    $objDrawing->setCoordinates($tmpSpan . $column); // 图片插入的单元格位置
                    $objDrawing->setOffsetX(10); // 图片在单元格中的偏移量
                    $objDrawing->setOffsetY(10);
                    $objDrawing->setWidth(80); // 图片宽度
                    $objDrawing->setHeight(80); // 图片高度
                    $objDrawing->setWorksheet($objActSheet);
                }
                else{
                    // 写入数据
                    $objActSheet->setCellValue($tmpSpan . $column, $value);
                }
                
                
                
                $span++;
            }
            $column++;
        }
    
        // 自动加边框
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //粗的是thick
                ),
    
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $colum . --$column)->applyFromArray($styleThinBlackBorderOutline);
        // 重命名表
        $fileName = iconv("utf-8", "gbk", $fileName);
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
       
        // header("Content-Type: application/octet-stream");
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }
        header("Content-Disposition: attachment;filename=$fileName");
        header('Cache-Control: max-age=0');
        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
    
        $writer = IOFactory::createWriter($objPHPExcel, 'Xlsx');
        $writer = new Xlsx($objPHPExcel);
        $writer->save('php://output'); // 文件通过浏览器下载（不知为何直接打印出来一大堆乱码，不能直接下载）
        exit();
        $writer->save($dir.'/'.$fileName);	// 文件下载到服务器
       
        $result = [
            'fileurl' => 'http://'.$_SERVER['HTTP_HOST'].'/public/excel/'.$fileName,
            'filename' => $fileName,
        ];
        return $result;
        exit();
    }

    public static function excelExportFile($fileName = '', $headArr = [], $data = [],$merge=[], $auto = true, $format="xlsx")
    {
        
        $fileName .= ".".$format;
        $objPHPExcel = new Spreadsheet();
        $objPHPExcel->getProperties();
        $key = ord("A"); // 设置表头
        $key2 = ord("@"); //	超过26列会报错的解决方案
    
        // 居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    
        // 设置表头
        foreach ($headArr as $v) {
            // 超过26列会报错的解决方案
            if ($key > ord("Z")) {
                $key2 += 1;
                $key = ord("A");
                $colum = chr($key2) . chr($key); //超过26个字母时才会启用
            } else {
                if ($key2 >= ord("A")) {
                    $colum = chr($key2) . chr($key);
                } else {
                    $colum = chr($key);
                }
            }
            // 写入表头
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            // 自适应宽度
            if ($auto) {
                // $len = strlen(iconv('utf-8','gb2312',$v));//会报错
                $len = strlen(iconv('utf-8', 'gbk', $v));
                $len = $len > 25 ? 30 : 20;
                $objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($len+5);
            }
            $key += 1;
        }
    
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        // 写入行数据
        
        foreach ($data as $key => $rows) {
            $span = ord("A");
            $span2 = ord("@");
            // 按列写入
            foreach ($rows as $keyName => $value) {
                // 超过26列会报错的解决方案
                if ($span > ord("Z")) {
                    $span2 += 1;
                    $span = ord("A");
                    $tmpSpan = chr($span2) . chr($span); //超过26个字母时才会启用
                } else {
                    if ($span2 >= ord("A")) {
                        $tmpSpan = chr($span2) . chr($span);
                    } else {
                        $tmpSpan = chr($span);
                    }
                }
                
                if(count($data) - 1 != $key && !empty($merge)){
                    if(in_array($keyName,$merge['merges']) && $data[$key][$merge['mergek']] == $data[$key+1][$merge['mergek']]){
                        $objPHPExcel->getActiveSheet()->mergeCells($tmpSpan . $column . ':'.$tmpSpan . ($column + 1));
                    }
                }
                
                // 写入数据
                $objActSheet->setCellValue($tmpSpan . $column, $value);
                $span++;
            }
            $column++;
        }
    
        // 自动加边框
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //粗的是thick
                ),
    
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $colum . --$column)->applyFromArray($styleThinBlackBorderOutline);
        // 重命名表
        $fileName = iconv("utf-8", "gbk", $fileName);
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
       
    
        $writer = IOFactory::createWriter($objPHPExcel, 'Xlsx');
        $writer = new Xlsx($objPHPExcel);
        
        $dir = ROOT_PATH."public/data/excel/";
        
        $writer->save($dir.$fileName);	// 文件下载到服务器
       
        $result = [
            'url' => '/data/excel/'.$fileName,
        ];
        
        return $result;
    }
}