<?php

namespace excel;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


class Excel
{
    /**
     * 表格导出
     */
    public function excelExport($fileName, $headArr, $data, $msg)
    {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            die("data must be a array");
        }

        //检查文件名
        if (empty($fileName)) {
            die("filename must be existed");
        }

        //获取总列数
        $totalColumn = count($headArr);
        if ($totalColumn > 26) {
            $charColumn = chr(intval($totalColumn / 26) + 64) . chr(intval($totalColumn % 26) + 64); //超过26个字母时才会启用
        } else {
            $charColumn = chr($totalColumn + 64);
        }
        $date = date("Y-m-d", time());
        $fileName .= "_{$date}.xls";

        $spreadsheet = new Spreadsheet();
        $phpstyle = new \PhpOffice\PhpSpreadsheet\Style\Color();
        $sheet = $spreadsheet->getActiveSheet();
        //表头变颜色
        $sheet->getStyle('A1:' . $charColumn . '1')->getFont()->getColor()->setARGB($phpstyle::COLOR_BLUE);    //设置颜色
        //设置批注
        if (!empty($msg)) {
            $sheet->getStyle('A2')->getFont()->getColor()->setARGB($phpstyle::COLOR_RED);
            $sheet->setCellValue('A2', $msg);    //给单个单元格设置内容
            $sheet->mergeCells('A2:' . $charColumn . '2');    //合并单元格
        }

        //设置表头
        $key = ord("A");
        $key2 = ord("@"); //@--64
        // $key = foo('a');
        foreach ($headArr as $v) {
            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); //超过26个字母时才会启用
                } else {
                    $colum = chr($key);
                }
            }
            // $colum = chr($key);
            $sheet->setCellValue($colum . '1', $v);
            $sheet->getColumnDimension($colum)->setWidth(20);
            $key++;
        }

        //写入数据
        if (!empty($msg)) {
            $column = 3;
        } else {
            $column = 2;
        }

        $image_array = [];
        foreach ($data as $key => $rows) {     //行写入
            $span = ord("A");
            $span2 = ord("@"); //@--64
            foreach ($rows as $keyName => $value) {    // 列写入
                $judge = stristr($keyName, 'img');
                $judge_url = stristr($keyName, 'url');

                if ($span > ord("Z")) {
                    $span2 += 1;
                    $span = ord("A");
                    $j = chr($span2) . chr($span); //超过26个字母时才会启用
                } else {
                    if ($span2 >= ord("A")) {
                        $j = chr($span2) . chr($span); //超过26个字母时才会启用
                    } else {
                        $j = chr($span);
                    }
                }
                if ($judge != false) {
                    $sheet->getRowDimension($column)->setRowHeight(60);    //设置行高

                    if (is_array($value)) {
                        $count = count($value);
                        foreach ($value as $k => $v) {
                            if (is_file($v)) {
                                $m = $k + 1;
                                $image_array[] = $v;
                                $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                                //图片路径,项目目录下就行
                                $objDrawing->setPath($v);
                                $objDrawing->setCoordinates($j . $column);
                                $objDrawing->setWidth(50);
                                $objDrawing->setHeight(50);
                                //图片偏移距离
                                $objDrawing->setOffsetX($m * 10 + $k * 50);
                                $objDrawing->setOffsetY(10);
                                $objDrawing->setWorksheet($spreadsheet->getActiveSheet());
                            }
                            //设置列宽
                        }
                        $spreadsheet->getActiveSheet(0)->getColumnDimension($j)->setWidth(60);
                    } else {
                        if (is_file($value)) {
                            $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                            $objDrawing->setPath($value);
                            $objDrawing->setWidth(50);
                            $objDrawing->setHeight(50);
                            $objDrawing->setOffsetX(10);
                            $objDrawing->setOffsetY(10);
                            $objDrawing->setCoordinates($j . $column);
                            $objDrawing->setWorksheet($spreadsheet->getActiveSheet());
                        } else {
                            $sheet->setCellValue($j . $column, $value);
                        }
                    }
                } elseif ($judge_url != false) {
                    $sheet->setCellValue($j . $column, $value);
                    $sheet->getCell($j . $column)->getHyperlink()->setUrl($value);
                } else {
                    $sheet->setCellValue($j . $column, $value);
                }
                $span++;
            }
            $column++;
        }

        //处理中文输出问题
        $fileName = iconv("utf-8", "gb2312", $fileName);

        //接下来当然是下载这个表格了，在浏览器输出就好了
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');


        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        foreach ($image_array as $value) {
            unlink($value);
        }
        exit;
    }

    /**
     * 获取表格数据
     */
    public function getData($file_dir)
    {
        $array = explode('aliyuncs.com/', $file_dir);
        $object = $array[1];
        $array = explode('.', $file_dir);
        $postfix = end($array);
        $local_file = './uploads/uid' . 2 . '/download/' . md5(microtime(true)) . '.' . $postfix;
        create_directory($local_file);
        $oss_param = get_ali_oss_config(2);
        $oss_logic = new \ali\oss\Oss($oss_param);
        $file_dir = $oss_logic->getObject($object, $local_file);

        if($postfix == 'xls'){
            $PHPReader = IOFactory::createReader('Xls');
        }else{
            $PHPReader = IOFactory::createReader('Xlsx');
        }
        if (!$PHPReader->canRead($local_file)) {
            return array(0, '请上传文档');
        }

        //载入文件
        $PHPExcel = $PHPReader->load($local_file);

        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet = $PHPExcel->getSheet(0);

        //获取总行数
        $allRow = $currentSheet->getHighestRow();
        $allColumn = $currentSheet->getHighestColumn();
        $allColumn = ord($allColumn);

        for ($j = 2; $j <= $allRow; $j++) {
            for ($i = 65; $i <= $allColumn; $i++) {
                $colum = chr($i);
                $cell = $currentSheet->getCell($colum . $j)->getValue();
                if ($cell instanceof RichText) {     //富文本转换字符串
                    $cell = $cell->__toString();
                }

                $data[$j][$colum] = $cell;
            }
        }
        unlink($local_file);
        return array(1, $data);
    }

    /**
     * 获取表格数据
     */
    public function getDataFromLocalFile($file_path)
    {
        $array = explode('.', $file_path);
        $postfix = end($array);
        $local_file = $file_path;

        if($postfix == 'xls'){
            $PHPReader = IOFactory::createReader('Xls');
        }else{
            $PHPReader = IOFactory::createReader('Xlsx');
        }
        if (!$PHPReader->canRead($local_file)) {
            return array(0, '请上传文档');
        }

        //载入文件
        $PHPExcel = $PHPReader->load($local_file);

        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet = $PHPExcel->getSheet(0);

        //获取总行数
        $allRow = $currentSheet->getHighestRow();
        $allColumn = $currentSheet->getHighestColumn();
        $allColumn = ord($allColumn);

        for ($j = 2; $j <= $allRow; $j++) {
            for ($i = 65; $i <= $allColumn; $i++) {
                $colum = chr($i);
                $cell = $currentSheet->getCell($colum . $j)->getValue();
                if ($cell instanceof RichText) {     //富文本转换字符串
                    $cell = $cell->__toString();
                }

                $data[$j][$colum] = $cell;
            }
        }
//        unlink($local_file);
        return $data;
    }
}