<?php

namespace App\Library\Tools;

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

/**
 * Class ExcelTool
 * @package App\Library\Tools
 */
class ExcelTool
{
    /**
     * 导出Excel
     * @param $file_name
     * @param $header
     * @param $rows
     * @param int $row_width
     * @param ...$funcs
     */
    public static function export($file_name, $header, $rows, $row_width = 20, ...$funcs)
    {
        $excel_funcs = (isset($funcs[0]) && $funcs[0]) ? $funcs[0] : [];
        $sheet_funcs = (isset($funcs[1]) && $funcs[1]) ? $funcs[1] : [];

        Excel::create($file_name, function ($excel) use ($header, $rows, $row_width, $excel_funcs, $sheet_funcs) {
            foreach ($excel_funcs as $excel_func) {
                $excel = $excel_func($excel);
            }

            $excel->sheet('数据', function ($sheet) use ($header, $rows, $row_width, $excel_funcs, $sheet_funcs) {
                foreach($header as $key=>$title){
                    $header_title[] = $title;
                    $header_key[] = $key;
                }
                $header = $header_title;
                $new_rows = array();
                foreach($rows as $row){
                    $new_row = array();
                    foreach($header_key as $key){
                        $new_row[$key] = isset($row[$key])?$row[$key]:'';
                    }
                    $new_rows[] = $new_row;
                }
                $rows = $new_rows;
                $letters = '';
                $header_count = count($header);
                for ($i = 0; $i < $header_count; $i++) {
                    $multiple = floor(($i/26));
                    if ($multiple) {
                        $first_letter = chr($multiple + 64);
                        $letters = $first_letter . chr(($i - $multiple * 26) + 65);
                    } else {
                        $letters = chr($i + 65);
                    }
                    $width[$letters] = $row_width;
                }
                $sheet->setWidth($width);
                $sheet->row(1, $header);
                $sheet->rows($rows);
                $sheet->freezeFirstRow();
                $max = $letters . (count($rows) + 1);
                $sheet->setBorder('A1:' . $max, 'thin');
                foreach ($sheet_funcs as $sheet_func) {
                    $sheet = $sheet_func($sheet);
                }
            });
        })->export('xlsx');
    }

    /**
     * 导入excel文件，以数组形式返回内容
     * @param $file
     * @return mixed
     */
    public static function import($file)
    {
        return Excel::selectSheets('Sheet1')
            ->load($file, function ($reader) {})
            ->toArray();
    }

    /**
     * 带合并单元格功能的excel
     * 
     * 注意：
     * 如果写position为AC:A3这种错误写法就会报内存溢出
     *
    ExcelTool::exportMergeWithTitle('站场预付费车辆统计表' .date('YmdHis'), [
    ['name' => '站场预付费车辆统计表', 'position' => 'A1:R1'],
    ['config' => ['setFreeze' => 'B5'],], // 冻结单元格，要看单元格的左上角

    ['name' => '复核：', 'position' => ['row' => 'D', 'col' => 'last']],
    ['name' => '时间：', 'position' => ['row' => 'A', 'col' => 'last+1']],

    ['name' => '序号', 'position' => 'A2:A3', 'key' => 'i'],
    ['name' => '站场', 'position' => 'B2:B3', 'key' => 'station_name'],
    ['name' => '路段（财务别名）', 'position' => 'C2:C3', 'key' => 'finance_name'],
    ['name' => '路段', 'position' => 'A2', 'key' => 'road_name', 'colspan' => true],
    ['name' => '预付费', 'position' => ExcelTool::incPosition('D2:F2', 1)],

    ], $list);
     *
     * @author aogg
     * @param $filename
     * @param $headers
     * @param $list
     * @param bool $widthBool
     */
    public static function exportMergeWithTitle($filename, $headers, $list, $widthBool = true)
    {

        Excel::create($filename, function ($excel) use ($headers, $list, $filename, $widthBool) {

            $excel->sheet('数据', function ($sheet) use ($headers, $list, $filename, $widthBool) {
                /** @var \Maatwebsite\Excel\Classes\LaravelExcelWorksheet $sheet */

                // 处理位置，处理单元格合并
                $handleStringPositionAtCell = function ($data)use(&$sheet){
                    if (strpos($data['position'], ':') !== false){
                        $sheet->mergeCells($data['position']);
                        $sheet->cell(current(explode(':', $data['position'])), function($cell) use($data){
                            /** @var \Maatwebsite\Excel\Writers\CellWriter $cell */
                            // manipulate the cell
                            $cell->setValue($data['name']);
                            $cell->setAlignment('center');
                            $cell->setValignment('center');

                            $cell->setFontWeight('bold');
                            $cell->setFontSize(14);
                            $cell->setFontColor('#3e446b');
                        });
                    }else{
                        $sheet->cell($data['position'], function($cell) use($data){
                            /** @var \Maatwebsite\Excel\Writers\CellWriter $cell */
                            // manipulate the cell
                            $cell->setValue($data['name']);

                            // 设置标题样式
                            $cell->setFontWeight('bold');
                            $cell->setFontSize(16);
                            $cell->setFontColor('#3e446b');
                        });
                    }
                };

                $getStartRow = function ()use(&$sheet){ // @see \Maatwebsite\Excel\Classes\LaravelExcelWorksheet::getStartRow
                    if ($sheet->getHighestRow() == 1)
                        return 1;

                    return $sheet->getHighestRow() + 1;
                };

                $rowStartNum = 1;
                $headerKeyArr = $colspanArr = $lastArr = $configArr = [];
                foreach($headers as $key=>$data){
                    if (!empty($data['config'])) { // 自定义配置
                        $configArr = array_merge($configArr, $data['config']);
                        continue;
                    }

                    if (empty($data['position'])) {
                        continue;
                    }

                    $matches = [];
                    // 那数据的开始行
                    if (is_array($data['position'])) { // 暂不支持冒号
                        if (!isset($data['position']['row']) || !isset($data['position']['col'])) {
                            continue;
                        }
                        if (strpos($data['position']['col'], 'last') === 0) { // 放最后
                            $lastArr[$key] = $data;
                            continue;
                        }else{
                            continue; // 暂无其他
                        }

                    }else{
                        if (preg_match('/\d+/', $data['position'], $matches)) {
                            $rowStartNum = max($rowStartNum, $matches[0]);
                        }
                    }

                    if (isset($data['key'])) {
                        $headerKeyArr[$data['key']] = $data['key'];

                        if (isset($data['colspan'])) { // 定义竖列
                            $colspanArr[$data['key']] = ['colspan' => $data['colspan'], 'position' => $data['position']];
                        }
                    }

                    $handleStringPositionAtCell($data);
                }

                if (!empty($configArr)) {
                    foreach ($configArr as $funcName => $args) {
                        if (!method_exists($sheet, $funcName)) {
                            continue;
                        }

                        call_user_func_array([$sheet, $funcName], (array)$args);
                    }
                }

                // 处理宽度
                if ($widthBool) {
                    $header_count = count($headers);
                    for ($i = 0; $i < $header_count; $i++) {
                        $multiple = floor(($i/26));
                        if ($multiple) {
                            $first_letter = chr($multiple + 64);
                            $letters = $first_letter . chr(($i - $multiple * 26) + 65);
                        } else {
                            $letters = chr($i + 65);
                        }
                        $width[$letters] = 20;
                    }
                    $sheet->setWidth($width);
                }


                // 过滤数据数组
                $new_rows = $colspanRowNumArr = $colspanRowTempArr = array();

                $i = $rowStartNum + 1;
                foreach($list as $row){
                    $new_row = array();

                    foreach($headerKeyArr as $key){
                        $value = Arr::get($row, $key, '');

                        if (!empty($colspanArr[$key])) { // 竖列
                            if (isset($colspanRowTempArr[$key]) && $colspanRowTempArr[$key]['value'] === $value){ // 和上一个相等
                                $tempKey = $key . '_' . $colspanRowTempArr[$key]['numKey']; // 找到第一个

                                // A2:A3
                                $colspanRowNumArr[$tempKey] = isset($colspanRowNumArr[$tempKey])?
                                    static::incPosition($colspanRowNumArr[$tempKey], 1, 2, 2): // 已有数据，递增后面
                                    // 初始化数据
                                    static::incPosition(
                                        static::replacePosition( // 替换右边
                                            static::replacePosition( // 替换左边
                                                (
                                                    // 处理为必须带冒号
                                                    strpos($colspanArr[$key]['position'], ':')!==false?
                                                    $colspanArr[$key]['position']:
                                                    $colspanArr[$key]['position'] . ':' . $colspanArr[$key]['position']
                                                ),
                                                $colspanRowTempArr[$key]['i'], 2, 1
                                            ),
                                            $i - 1, 2, 2
                                        ),
                                        1, 2, 2);
                            }else{ // 没和上一个相等
                                $colspanRowTempArr[$key] = [
                                    'value' => $value,
                                    'i' => $i,
                                    'numKey' => isset($colspanRowTempArr[$key]) && !empty($colspanRowTempArr[$key]['numKey']) ?
                                        ++$colspanRowTempArr[$key]['numKey'] : 1
                                ];
                            }

                        }

                        $new_row[] = $value;
                    }
                    $new_rows[] = $new_row;
                    ++$i;
                }
                $sheet->rows($new_rows);

                // 合并竖列
                if (!empty($colspanRowNumArr)) {
                    foreach ($colspanRowNumArr as $itemPosition){
                        $sheet->mergeCells($itemPosition, true);
                        $sheet->cells($itemPosition, function ($cell)
                        {
                            /** @var \Maatwebsite\Excel\Writers\CellWriter $cell */
                            $cell->setValignment('center'); // 垂直居中
                        });
                    }
                }

                // 最后
                if (!empty($lastArr)) {
                    $rowEndRow = $getStartRow();
                    foreach ($lastArr as $item) {
                        $matches = [];
                        if (preg_match('/([a-z]+)([+-]\d+)?$/i', $item['position']['col'], $matches)) { // 放最后
                            $item['position'] = $item['position']['row'] . ($rowEndRow + ($matches[2]??0));
                        }else{
                            continue; // 暂无其他
                        }

                        $handleStringPositionAtCell($item);
                    }
                }

            });
        })->export('xlsx');
    }

    /**
     * 横向或竖向递增
     *
     * @param $position
     * @param $inc
     * @param int $incType 1为横向，2为竖向
     * @param bool $bothType 冒号分割，同时递增，1为只递增前面，2为只递增后面
     * @return string
     */
    public static function incPosition($position, $inc, $incType = 1, $bothType = true)
    {
        $arr = explode(':', $position);
        $matches = [];
        $incWordFunc = function (&$word, &$num){
            if (empty($num)){
                return;
            }

            ++$word;
            --$num;
        };

        foreach ($arr as &$item) {
            if (!preg_match('/([A-Z]+)(\d*)/', $item, $matches)) {
                continue;
            }

            if ($bothType === 2){ // 只递增后面
                $bothType = true;
                continue;
            }

            $wordIncNum = $incType === 1 ? $inc : 0;
            $incWordFunc($matches[1], $wordIncNum);

            $item = $matches[1] .
                ($matches[2] ? ($incType === 2 ? $matches[2] + $inc : $matches[2]) : '');

            if ($bothType === 1){ // 只递增前面
                break;
            }
        }

        return join(':', $arr);
    }

    /**
     * 横向或竖向递增
     *
     * @param $position
     * @param $inc
     * @param int $incType 1为横向，2为竖向
     * @param bool $bothType 冒号分割，同时递增，1为只递增前面，2为只递增后面
     * @return string
     */
    public static function replacePosition($position, $inc, $incType = 1, $bothType = true)
    {
        $arr = explode(':', $position);
        $matches = [];

        foreach ($arr as &$item) {
            if (!preg_match('/([A-Z]+)(\d*)/', $item, $matches)) {
                continue;
            }

            if ($bothType === 2){ // 只处理冒号后面
                $bothType = true;
                continue;
            }

            $item = ($incType === 1?$inc:$matches[1]) . ($incType === 2?$inc:$matches[2]);

            if ($bothType === 1){ // 只处理冒号前面
                break;
            }
        }

        return join(':', $arr);
    }
}