<?php
/**
 * ===========================================================================
 * Veitool 快捷开发框架系统
 * Author: Niaho 26843818@qq.com
 * Copyright (c)2019-2025 www.veitool.com All rights reserved.
 * Licensed: 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
 * ---------------------------------------------------------------------------
 */

namespace app\event;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Db;

/**
 * phpoffice excel导入导出类
 */
class PhpOffice
{
    /****
     * 普通导入
     * @param $filePath
     * @return array
     */
    public static function importexcel($filePath)
    {

        // 加载 Excel 文件
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        return $sheet->toArray();

//        // 获取最高行和列
//        $highestRow = $sheet->getHighestRow();
//        $highestColumn = $sheet->getHighestColumn();
//        $data = [];
//        // 读取表头 (第一行)
//        $headers = [];
//        for ($col = 'A'; $col <= $highestColumn; $col++) {
//            $headers[] = $sheet->getCell($col . '1')->getValue();
//        }
//        // 读取数据 (从第二行开始)
//        for ($row = 2; $row <= $highestRow; $row++) {
//            $rowData = [];
//            $colIndex = 0;
//            for ($col = 'A'; $col <= $highestColumn; $col++) {
//                $rowData[$headers[$colIndex]] = $sheet->getCell($col . $row)->getValue();
//                $colIndex++;
//            }
//            $data[] = $rowData;
//        }
//
//        return $data;
    }

    /****
     * 分块导入适合大数据
     * @param $filePath
     * @param $callback
     * @param $chunkSize
     * @return void
     */
    public static function chunkedImportXlsx($filePath, $callback, $chunkSize = 1000)
    {


        // 创建适当的读取器 (自动检测文件类型)
        $reader = IOFactory::createReaderForFile($filePath);
        // 只读取数据，不读取样式信息 (提高性能)
        $reader->setReadDataOnly(true);

        // 获取工作表信息 (特别是总行数)
        $worksheetInfo = $reader->listWorksheetInfo($filePath);
        $totalRows = $worksheetInfo[0]['totalRows'];
        // 计算需要读取的块数
        $chunkCount = ceil($totalRows / $chunkSize);

        // 分块读取
        for ($i = 0; $i < $chunkCount; $i++) {
            $startRow = ($i * $chunkSize) + 1; // +1 因为Excel行从1开始
            $endRow = min((($i + 1) * $chunkSize), $totalRows);

//            // 设置读取过滤器
            $chunkFilter = new ChunkReadFilter($startRow, $endRow);
            $reader->setReadFilter($chunkFilter);

            // 加载当前块的数据
            $spreadsheet = $reader->load($filePath);
            $sheet = $spreadsheet->getActiveSheet();

            // 将数据转换为数组
            $data = $sheet->toArray();

            // 处理数据 (通过回调函数)
            $callback($data, $startRow, $endRow);

            // 重要！清理内存
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet, $sheet, $data);
        }
    }


    /*
     * 导出excel
     */
    public static function exportexcel($dataGenerator, $filenamedata = '', $chunkSize = 1000)
    {
        // 创建临时文件
        $tempFile = tempnam(sys_get_temp_dir(), 'phpxlsx');

        // 初始设置
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $writer = new Xlsx($spreadsheet);

        // 写入表头
        $headers = array_shift($dataGenerator);
        $sheet->fromArray($headers, null, 'A1');

        $row = 2; // 数据从第2行开始
        $chunkCount = 0;

        foreach ($dataGenerator as $item) {
            // 写入数据
            $sheet->fromArray($item, null, 'A' . $row++);
            // 每处理$chunkSize行保存一次
            if ($row % $chunkSize === 0) {
                $writer->save($tempFile);
                $spreadsheet->disconnectWorksheets(); // 释放内存
                // 重新加载
                $spreadsheet = IOFactory::load($tempFile);
                $sheet = $spreadsheet->getActiveSheet();
                $writer = new Xlsx($spreadsheet);

//                $chunkCount++;
//                echo "已处理 " . ($chunkCount * $chunkSize) . " 行数据\n";
            }
        }

        // 最终保存
        $writer->save($tempFile);
        // 设置路径
        $fileurl = VT_STATIC . 'downexcel/' . date('Ymd') . '/';
        $savePath = VT_PUBLIC . $fileurl;
        // 创建目录
        if (!is_dir($savePath)) {
            mkdir($savePath, 0755, true);
        }
        // 设置文件名
        $filename = uniqid() . '.xlsx';
        $fullPath = $savePath . $filename;//完整目录地址
        $fileurl = $fileurl . $filename; //存数据库的地址
        // 移动到最终位置
        if (!rename($tempFile, $fullPath)) {
            throw new \Exception('无法移动文件到目标位置');
        }
        return Db::name('excel_down')->insertGetId(['fileurl' => $fileurl, 'filename' => $filenamedata . '.xlsx', 'admin_id' => session(VT_MANAGER)['userid'], 'createtime' => time()]);

//        // 输出到浏览器
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        header('Content-Disposition: attachment;filename="' . $filename . '"');
//        header('Cache-Control: max-age=0');
//        readfile($tempFile);
//        unlink($tempFile);
    }

}

class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $startRow = 0;
    private $endRow = 0;

    public function __construct($startRow, $endRow)
    {
        $this->startRow = $startRow;
        $this->endRow = $endRow;
    }

    public function readCell($column, $row, $worksheetName = ''): bool
    {
        // 只读取指定行范围内的单元格
        return ($row >= $this->startRow && $row <= $this->endRow);
    }
}


