<?php

namespace Application\Service\Extension\Helper;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Html;
use PhpOffice\PhpSpreadsheet\Writer\Xls;


class ExcelHelper
{
    /**
     * 导入
     *
     * @param $filePath
     * @param int $startRow
     * @param null $sheetIndex
     * @return array
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function import($filePath, $startRow = 1, $sheetIndex = null): array
    {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader->setReadDataOnly(true);
        if (!$reader->canRead($filePath)) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            // setReadDataOnly Set read data only 只读单元格的数据，不格式化 e.g. 读时间会变成一个数据等
            $reader->setReadDataOnly(true);

            if (!$reader->canRead($filePath)) {
                throw new \Exception('不能读取Excel');
            }
        }

        $spreadsheet = $reader->load($filePath);
        $sheetCount = $spreadsheet->getSheetCount();// 获取sheet的数量
        // 获取所有的sheet表格数据
        $excleDatas = [];
        $emptyRowNum = 0;
        $emptyColumnNum = 0;
        for ($i = 0; $i < $sheetCount; $i++) {
            if ($sheetIndex !== null && $i !== $sheetIndex) {
                continue;
            }
            $currentSheet = $spreadsheet->getSheet($i); // 读取excel文件中的第一个工作表
            $allColumn = $currentSheet->getHighestColumn(); // 取得最大的列号
            $allColumn = Coordinate::columnIndexFromString($allColumn); // 由列名转为列数('AB'->28)
            $allRow = $currentSheet->getHighestRow(); // 取得一共有多少行

            $arr = [];
            for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
                // 从第1列开始输出
                for ($currentColumn = 1; $currentColumn <= $allColumn; $currentColumn++) {
                    $val = $currentSheet->getCell([$currentColumn, $currentRow])->getValue();
                    if ($val === null && $currentColumn >= 7) {
                        $emptyColumnNum++;
                        if ($emptyColumnNum >= 1) {
                            $emptyColumnNum = 0;
                            break;
                        }
                    } else {
                        $emptyColumnNum = 0;
                    }
                    $arr[$currentRow][] = trim($val);
                }

                // $arr[$currentRow] = array_filter($arr[$currentRow]);
                // 统计连续空行
                if (empty($arr[$currentRow]) && $emptyRowNum <= 50) {
                    $emptyRowNum++;
                } else {
                    $emptyRowNum = 0;
                }
                // 防止坑队友的同事在excel里面弄出很多的空行，陷入很漫长的循环中，设置如果连续超过50个空行就退出循环，返回结果
                // 连续50行数据为空，不再读取后面行的数据，防止读满内存
                if ($emptyRowNum > 50) {
                    break;
                }
            }

            $excleDatas[$i] = $arr; // 多个sheet的数组的集合
        }

//        // 这里我只需要用到第一个sheet的数据，所以只返回了第一个sheet的数据
//        $returnData = $excleDatas ? array_shift($excleDatas) : [];
        $returnData = $excleDatas;

        // 第一行数据就是空的，为了保留其原始数据，第一行数据就不做array_fiter操作；
        return $returnData && isset($returnData[$startRow]) && !empty($returnData[$startRow]) ? array_filter($returnData) : $returnData;
    }

    /**
     * 导入表单表格
     *
     * @param $filePath
     * @param int $startRow
     * @param null $sheetIndex
     * @return array
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function importForm($filePath, $startRow = 1, $sheetIndex = null): array
    {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader->setReadDataOnly(true);
        if (!$reader->canRead($filePath)) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            // setReadDataOnly Set read data only 只读单元格的数据，不格式化 e.g. 读时间会变成一个数据等
            $reader->setReadDataOnly(true);

            if (!$reader->canRead($filePath)) {
                throw new \Exception('不能读取Excel');
            }
        }

        $spreadsheet = $reader->load($filePath);
        $sheetCount = $spreadsheet->getSheetCount();// 获取sheet的数量
        // 获取所有的sheet表格数据
        $excleDatas = [];
        $emptyRowNum = 0;
        $emptyColumnNum = 0;
        for ($i = 0; $i < $sheetCount; $i++) {
            if ($sheetIndex !== null && $i !== $sheetIndex) {
                continue;
            }
            $currentSheet = $spreadsheet->getSheet($i); // 读取excel文件中的第一个工作表

            // 这里取得不准, 不知道为什么。
//            $allColumn = $currentSheet->getHighestColumn(); // 取得最大的列号

            $allColumn = call_user_func(function() use ($currentSheet) {
                $startRow = 2;
                for ($currentColumn = 1; $currentColumn <= 9999; $currentColumn++) {
                    $val = $currentSheet->getCell([$currentColumn, $startRow])->getValue();
                    if ($val == null) {
                        return $currentColumn - 1;
                    }
                }
            });

//            $allColumn = Coordinate::columnIndexFromString($allColumn); // 由列名转为列数('AB'->28)
            $allRow = $currentSheet->getHighestRow(); // 取得一共有多少行

            $arr = [];
            for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
                // 从第1列开始输出
                for ($currentColumn = 1; $currentColumn <= $allColumn; $currentColumn++) {
                    $val = $currentSheet->getCell([$currentColumn, $currentRow])->getValue();
                    if ($val === null && $currentColumn >= $allColumn) {
                        $emptyColumnNum++;
                        if ($emptyColumnNum >= 1) {
                            $emptyColumnNum = 0;
                            break;
                        }
                    } else {
                        $emptyColumnNum = 0;
                    }
                    $arr[$currentRow][] = trim($val);
                }

                // $arr[$currentRow] = array_filter($arr[$currentRow]);
                // 统计连续空行
                if (empty($arr[$currentRow]) && $emptyRowNum <= 50) {
                    $emptyRowNum++;
                } else {
                    $emptyRowNum = 0;
                }
                // 防止坑队友的同事在excel里面弄出很多的空行，陷入很漫长的循环中，设置如果连续超过50个空行就退出循环，返回结果
                // 连续50行数据为空，不再读取后面行的数据，防止读满内存
                if ($emptyRowNum > 50) {
                    break;
                }
            }

            $excleDatas[$currentSheet->getTitle()] = $arr; // 多个sheet的数组的集合
        }

//        // 这里我只需要用到第一个sheet的数据，所以只返回了第一个sheet的数据
//        $returnData = $excleDatas ? array_shift($excleDatas) : [];
        $returnData = $excleDatas;

        // 第一行数据就是空的，为了保留其原始数据，第一行数据就不做array_fiter操作；
        return $returnData && isset($returnData[$startRow]) && !empty($returnData[$startRow]) ? array_filter($returnData) : $returnData;
    }


    public static function exportExcel($list = [], $header = [], $filename = '', $suffix = 'xlsx', $path = '', array $sheetTitle = [])
    {
        if (!is_array($list) || !is_array($header)) {
            return false;
        }

        !$filename && $filename = time();

        $spreadsheet = new Spreadsheet();

        foreach ($sheetTitle as $sheetIndex => $sheetTitleItem) {
            if ($sheetIndex == 0) {
                $sheet = $spreadsheet->getActiveSheet();
            } else {
                $sheet = $spreadsheet->createSheet();
            }

            $sheet->setTitle($sheetTitleItem);

            // 写入头部
            $hk = 1;

            foreach ($header[$sheetIndex] ?? [] as $k => $v) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($hk) . '1', $v[0]);
                $sheet->getStyle(Coordinate::stringFromColumnIndex($hk) . '1')->getFont()->setBold(true);
                self::autosizeColumn($sheet, Coordinate::stringFromColumnIndex($hk));
//                $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($hk))->setWidth(StringHelper::calculateStringLength($v[0]) + 2.71);
                $hk += 1;
            }

            // 开始写入内容
            $column = 2;
            $size = ceil(count($list) / 500);

            for ($i = 0; $i < $size; $i++) {
                $buffer = array_slice($list, $i * 500, 500);

                foreach ($buffer[$sheetIndex] as $k => $row) {
                    $span = 1;

//                    var_dump($header[$sheetIndex]);
                    foreach ($header[$sheetIndex] as $key => $value) {
                        // 解析字段
                        $realData = self::formatting($value, trim(self::formattingField($row, $value[1])), $row);
                        // 写入excel
                        $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($span) . $column, $realData, DataType::TYPE_STRING);
                        // $sheet->setCellValue(Coordinate::stringFromColumnIndex($span) . $column, $realData);
                        $span++;
                    }

                    $column++;
                    unset($buffer[$k]);
                }
            }
        }
        // 清除之前的错误输出
        ob_end_clean();
        ob_start();

        // 直接输出下载
        switch ($suffix) {
            case 'xlsx' :
                $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
                if (!empty($path)) {
                    $writer->save($path);
                } else {
                    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;");
                    header("Content-Disposition: inline;filename=\"{$filename}.xlsx\"");
                    header('Cache-Control: max-age=0');
                    $writer->save('php://output');
                }
                break;
            case 'xls' :
                $writer = new Xls($spreadsheet);
                if (!empty($path)) {
                    $writer->save($path);
                } else {
                    header("Content-Type:application/vnd.ms-excel;charset=utf-8;");
                    header("Content-Disposition:inline;filename=\"{$filename}.xls\"");
                    header('Cache-Control: max-age=0');
                    $writer->save('php://output');
                }
                break;
            case 'csv' :
                $writer = new Csv($spreadsheet);
                if (!empty($path)) {
                    $writer->save($path);
                } else {
                    header("Content-type:text/csv;charset=utf-8;");
                    header("Content-Disposition:attachment; filename={$filename}.csv");
                    header('Cache-Control: max-age=0');
                    $writer->save('php://output');
                }
                break;
            case 'html' :
                $writer = new Html($spreadsheet);
                if (!empty($path)) {
                    $writer->save($path);
                } else {
                    header("Content-Type:text/html;charset=utf-8;");
                    header("Content-Disposition:attachment;filename=\"{$filename}.{$suffix}\"");
                    header('Cache-Control: max-age=0');
                    $writer->save('php://output');
                }
                break;
        }

        /* 释放内存 */
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        exit();
    }

    /**
     * 格式化内容
     *
     * @param array $array 头部规则
     * @return false|mixed|null|string 内容值
     */
    protected static function formatting(array $array, $value, $row)
    {
        !isset($array[2]) && $array[2] = 'text';

        switch ($array[2]) {
            // 文本
            case 'text' :
                return $value;
                break;
            // 日期
            case 'date' :
                return !empty($value) ? date($array[3], $value) : null;
                break;
            // 选择框
            case 'selectd' :
                return $array[3][$value] ?? null;
                break;
            // 匿名函数
            case 'function' :
                return isset($array[3]) ? call_user_func($array[3], $row) : null;
                break;
            // 默认
            default :

                break;
        }

        return null;
    }

    /**
     * 解析字段
     *
     * @param $row
     * @param $field
     * @return mixed
     */
    protected static function formattingField($row, $field)
    {
        $newField = explode('.', $field);
        if (count($newField) == 1) {
            return $row[$field] ?? '';
        }

        foreach ($newField as $item) {
            if (isset($row[$item])) {
                $row = $row[$item];
            } else {
                break;
            }
        }

        return is_array($row) ? false : $row;
    }

    /**
     * 自适应宽度的方法
     *
     * @param Worksheet $worksheet 工作表
     * @param string $column 列编号（例如，A、B、C 等）
     * @param int $padding 左右内边距值
     * @param int $minWidth 最小宽度，默认为 0
     * @param int $maxWidth 最大宽度，默认为 80
     */
    private static function autosizeColumn(Worksheet $worksheet, string $column, int $padding = 1, int $minWidth = 0, int $maxWidth = 80)
    {
        $columnDimension = $worksheet->getColumnDimension($column);
        $cellValue = $worksheet->getCell($column . '1')->getValue();
        $cellWidth = (strlen($cellValue) + $padding * 2) * 1.2;

        if ($cellWidth > $maxWidth) {
            $columnDimension->setWidth($maxWidth);
        } elseif ($cellWidth < $minWidth) {
            $columnDimension->setWidth($minWidth);
        } else {
            $columnDimension->setWidth($cellWidth);
        }
//        $columnDimension->setAutoSize(true);
    }
}