<?php

namespace xmvc;

use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Excel类
 * - composer require phpoffice/phpspreadsheet
 */
class Excel
{
    protected Spreadsheet $spreadsheet;
    protected Worksheet $activeSheet;
    protected Xlsx $writer;
    public int $activeRowIndex = 1;

    /**
     * 构造
     */
    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
        $this->getActiveSheet();
    }

    public static function getData(string $excelFilename)
    {
        $spreadsheet = IOFactory::load($excelFilename);
        $sheet = $spreadsheet->getActiveSheet();
        $data = [];
        foreach ($sheet->getRowIterator() as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); // 这也将迭代空单元格
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $value = $cell->getValueString();
                $rowData[] = $value;
            }
            $data[] = $rowData;
        }
        return $data;
    }

    public function getActiveSheet()
    {
        $this->activeSheet = $this->spreadsheet->getActiveSheet();
        return $this->activeSheet;
    }

    public function setActiveSheet(int|string $sheetIdxOrName)
    {
        if (is_int($sheetIdxOrName)) {
            $this->spreadsheet->setActiveSheetIndex($sheetIdxOrName);
        } else {
            $this->spreadsheet->setActiveSheetIndexByName($sheetIdxOrName);
        }
    }

    public function createSheet(string $title)
    {
        $this->activeSheet = $this->spreadsheet->createSheet();
        $this->activeSheet->setTitle($title);
    }

    public function setSheetTitle(string $title)
    {
        $this->activeSheet = $this->spreadsheet->getActiveSheet();
        $this->activeSheet->setTitle($title);
    }

    public function getWriter()
    {
        if (!isset($this->writer)) {
            $this->writer = new Xlsx($this->spreadsheet);
        }
        return $this->writer;
    }

    public function header(string $filename)
    {
        $writer = $this->getWriter();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        ob_end_clean();
        $writer->save('php://output');
        exit;
    }

    public function save(string $filename)
    {
        $writer = $this->getWriter();
        $writer->save($filename);
        exit;
    }

    public function addHeaderRow(array $data, array $formatArr = [], array $styleArray = [])
    {
        if (empty($styleArray)) {
            $styleArray = [
                'font' => [
                    'color' => [
                        'rgb' => 'FFFFFF'
                    ]
                ],
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'startColor' => [
                        'rgb' => '000088'
                    ]
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => [
                            'rgb' => 'FFFFFF'
                        ]
                    ]
                ]
            ];
        }
        foreach ($data as $i => $v) {
            if (!isset($formatArr[$i])) {
                $formatArr[$i] = [
                    'width' => 20
                ];
            }
        }
        $this->addRow($data, $formatArr, $styleArray);
        $sheet = $this->getActiveSheet();
        $highestColumn = $sheet->getHighestColumn();
        $rowIndex = $this->activeRowIndex - 1;
        $sheet->setAutoFilter("A{$rowIndex}:" . $highestColumn . $rowIndex);
    }

    public function addRow(array $data, array $formatArr = [], array $styleArray = [])
    {
        $sheet = $this->getActiveSheet();
        $sheet->fromArray($data, null, "A{$this->activeRowIndex}");
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $sheet->getStyle("A{$this->activeRowIndex}:{$highestColumn}{$highestRow}")->applyFromArray($styleArray);
        $currentColumnIndex = 1; // A列是索引1
        if (isset($formatArr['height'])) {
            $sheet->getRowDimension('A')->setRowHeight($formatArr['height']);
        }
        while (Coordinate::stringFromColumnIndex($currentColumnIndex) <= $highestColumn) {
            $format = $formatArr[$currentColumnIndex - 1] ?? [];
            $column = Coordinate::stringFromColumnIndex($currentColumnIndex);
            if (isset($format['width'])) {
                $sheet->getColumnDimension($column)->setWidth($format['width']);
            }
            $currentColumnIndex++;
        }
        $this->activeRowIndex = $highestRow + 1;
    }

    public function setStyle(int $rowIndex, int $colIndex, array|string $styleArray = [])
    {
        if (is_string($styleArray)) {
            switch ($styleArray) {
                case "bgRed":
                    $styleArray = [
                        'font' => [
                            'color' => [
                                'rgb' => 'FFFFFF'
                            ]
                        ],
                        'fill' => [
                            'fillType' => 'solid',
                            'startColor' => [
                                'rgb' => 'CC0000'
                            ]
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => 'thin',
                                'color' => [
                                    'rgb' => 'FFFFFF'
                                ]
                            ]
                        ]
                    ];
                    break;
            }
        }
        $sheet = $this->getActiveSheet();
        $column = Coordinate::stringFromColumnIndex($colIndex + 1); // 转换为列字母
        $rowIndex = $rowIndex + 1;
        $sheet->getStyle("{$column}{$rowIndex}:{$column}{$rowIndex}")->applyFromArray($styleArray);
    }
}