BroadbandSaler/app/Models/SpoutExport.php

<?php
/**
 * Author: lexuan
 * Name: SpoutExport
 * version: 1.0.0
 * Tip: 安装 composer require box/spout
 */

namespace App\Models;

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Common\Entity\Style\CellAlignment;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Log;

class SpoutExport
{
    private $enum = []; // 枚举
    private $toStrFields = []; // 需字符串处理的字段
    private $headers = []; // 表头
    private $fileFormat = 'xlsx'; // 文件格式
    private $download = false; // 是否下载
    private $handleType = 'chunk'; // 数据处理方式 chunk-分块处理 cursor-逐行处理
    private $query = null; // 数据模型对象
    private $dataList = []; // 数据列表
    private $processStyle = false; // 是否后处理样式

    private $chunkSize = 2000; // 分块处理时每块大小
    private $processStyleMaxRow = 20000; // 后处理样式时最大行数
    private $styleWidth = null; // 列宽
    private $styleHeight = 30; // 行高

    /**
     * 初始化
     *
     * @param array $headers 表头
     */
    public function __construct(array $headers)
    {
        $this->headers = $headers;
    }

    /**
     * 设置数据模型对象
     *
     * @param models $query 数据模型对象
     * @param string $handleType 数据处理方式 chunk-分块处理 cursor-逐行处理
     * @param integer $chunkSize 分块处理时每块大小
     * @return $this
     */
    public function setQuery($query, $handleType = 'chunk', $chunkSize = 2000)
    {
        $this->query = $query;
        $this->handleType = $handleType;
        $this->chunkSize = $chunkSize;
        return $this;
    }

    // 设置数据
    public function setData(array|object $dataList)
    {
        $this->dataList = $dataList;
        return $this;
    }

    // 设置枚举
    public function setEnum(array $enum)
    {
        $this->enum = $enum;
        return $this;
    }

    // 设置文件格式 xlsx 或 csv
    public function setFileFormat($format)
    {
        $this->fileFormat = $format;
        return $this;
    }

    // 设置下载 默认不下载，返回文件路径
    public function setDownload()
    {
        $this->download = true;
        return $this;
    }

    // 设置字符串处理字段
    public function setToStrFields(array $toStrFields)
    {
        $this->toStrFields = $toStrFields;
        return $this;
    }

    /**
     * 后处理样式
     *
     * @param $styleWidth 列宽
     * @param $styleHeight 行高
     */
    public function setProcessStyle($styleWidth = null, $styleHeight = null)
    {
        $this->processStyle = true;
        $this->styleWidth = $styleWidth;
        if (!empty($styleHeight)) {
            $this->styleHeight = $styleHeight;
        }
        return $this;
    }

    // 格式化数据
    private function formatData($item, $headers, $dataRowStyle)
    {
        $rowData = [];

        if (is_object($item)) {
            $item = $item->toArray();
        }

        foreach ($headers as $key => $field) {
            // 处理关联模型
            $rowData[$key] = data_get($item, $field, '');

            // 处理枚举
            if (isset($this->enum[$field])) {
                $rowData[$key] = $this->enum[$field][$rowData[$key]] ?? '';
            }

            // 字符串处理
            if (in_array($field, $this->toStrFields)) {
                $rowData[$key] = "\t" . $rowData[$key];
            }
        }
        $dataRow = WriterEntityFactory::createRowFromArray($rowData, $dataRowStyle);
        return $dataRow;
    }

    // 格式化数据
    private function formatDataByArrayToExcel($item, $dataRowStyle)
    {
        $rowData = [];

        if (is_object($item)) {
            $item = $item->toArray();
        }

        foreach ($item as $key => $value) {
            $rowData[$key] = "\t" . $value;
        }

        $dataRow = WriterEntityFactory::createRowFromArray($rowData, $dataRowStyle);
        return $dataRow;
    }

    // 获取列范围（例如，从 A 到 AZ）
    public function getColumnRange($start, $end) {
        $columns = [];
    
        // 将列字母转换为大写
        $start = strtoupper($start);
        $end = strtoupper($end);
    
        // 辅助函数：将列名转换为对应的数字索引（例如 A -> 1, Z -> 26, AA -> 27）
        $convertToColumnIndex = function ($column) {
            $column = strtoupper($column);
            $length = strlen($column);
            $index = 0;
    
            for ($i = 0; $i < $length; $i++) {
                $index = $index * 26 + (ord($column[$i]) - ord('A') + 1);
            }
    
            return $index;
        };
    
        // 辅助函数：将列的数字索引转换为列名（例如 1 -> A, 27 -> AA）
        $convertToColumnName = function ($index) {
            $column = '';
    
            while ($index > 0) {
                $mod = ($index - 1) % 26;
                $column = chr($mod + ord('A')) . $column;
                $index = intval(($index - $mod) / 26);
            }
    
            return $column;
        };
    
        // 获取列的数字索引
        $startIndex = $convertToColumnIndex($start);
        $endIndex = $convertToColumnIndex($end);
    
        // 获取范围内的列名
        for ($i = $startIndex; $i <= $endIndex; $i++) {
            $columns[] = $convertToColumnName($i);
        }
    
        return $columns;
    }

    /**
     * 后处理样式，仅支持Xlsx格式，大量数据慎用
     * [ 安装PhpSpreadsheet扩展 composer require phpoffice/phpspreadsheet ]
     * @param String $filePath 文件路径
     * @return void
     */
    private function postProcessStyle($filePath)
    {
        if ($this->fileFormat != 'xlsx') {
            return;
        }

        ini_set('memory_limit', '800M'); // 设置临时内存限制 -1 为不限内存，可注释关闭

        // 创建读取器并设置相关属性
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);

        $spreadsheet = $reader->load($filePath);
        $sheet = $spreadsheet->getActiveSheet();

        // 超过指定行不处理样式，防止内存溢出
        if ($sheet->getHighestRow() > $this->processStyleMaxRow) {
            return;
        }

        // 获取表头行（假设第一行为表头，可根据实际调整）
        $headerRow = 1;
        $headerRange = 'A' . $headerRow . ':' . $sheet->getHighestColumn() . $headerRow;
        // 设置表头样式
        $headerStyle = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ],
            'font' => [
                'bold' => true,
                'size' => 12
            ]
        ];
        $sheet->getStyle($headerRange)->applyFromArray($headerStyle);

        // 设置数据行样式
        $dataRowStyle = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => false
            ]
        ];

        // 逐行迭代处理数据（设置样式等）
        foreach ($sheet->getRowIterator() as $rowIndex => $row) {
            if ($rowIndex > 0) { // 跳过表头行
                $cellRange = 'A' . ($rowIndex + 1) . ':' . $sheet->getHighestColumn() . ($rowIndex + 1);
                $sheet->getStyle($cellRange)->applyFromArray($dataRowStyle);
            }

            // 设置行高
            if ($this->styleHeight) {
                $sheet->getRowDimension($rowIndex + 1)->setRowHeight($this->styleHeight);
            }

            // 释放当前行单元格对象引用
            foreach ($row->getCellIterator() as $cell) {
                $cell = null;
            }
            $row = null;
        }

        // 分块处理列宽设置（示例按10列一块，可根据实际调整）
        $columnChunks = array_chunk($this->getColumnRange('A', $sheet->getHighestColumn()), 10);
        foreach ($columnChunks as $chunk) {
            foreach ($chunk as $column) {
                if ($this->styleWidth) {
                    $sheet->getColumnDimension($column)->setWidth($this->styleWidth);
                } else {
                    $sheet->getColumnDimension($column)->setAutoSize(true);
                }
            }
        }

        // 保存修改后的表格
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($filePath);

        // 释放整个表格对象引用
        $spreadsheet = null;
    }

    /**
     * 使用 Spout 导出 Excel 文件
     *
     * @param string $fileName 导出的文件名（不包含路径）
     * @param string $directory 文件保存目录
     * @return array|bool 导出的文件完整 URL，失败时返回 false
     */
    public function spoutToExcel($fileName = "export", $directory = "exports/")
    {
        try {
            $headers = $this->headers;
            if (empty($headers)) {
                throw new \Exception('表头不能为空');
            }

            // 确保文件格式正确
            if (!in_array($this->fileFormat, ['csv', 'xlsx'])) {
                throw new \Exception('文件格式不正确');
            }

            // 确保文件名不为空
            if (empty($fileName)) {
                throw new \Exception('文件名不能为空');
            }

            if (empty($this->dataList) && empty($this->query)) {
                throw new \Exception('未设置数据或模型对象');
            }

            // 确保目录存在
            $directoryPath = $directory;
            if (!is_dir($directoryPath)) {
                File::makeDirectory($directoryPath, 0755, true);
            }

            // 生成文件名
            $uniqueId = uniqid();
            $fileExtension = $this->fileFormat === 'csv' ? 'csv' : 'xlsx';
            $fullFileName = "{$fileName}-{$uniqueId}.{$fileExtension}";
            $filePath = $directoryPath . '/' . $fullFileName;

            // 创建 Writer
            $writer = $this->fileFormat === 'csv'
                ? WriterEntityFactory::createCSVWriter()
                : WriterEntityFactory::createXLSXWriter();

            $writer->openToFile($filePath);

            // 定义样式
            $headerStyle = (new StyleBuilder())->setCellAlignment(CellAlignment::CENTER)->setFontBold()->setFontSize(12)->build();
            $dataRowStyle = (new StyleBuilder())->setCellAlignment(CellAlignment::CENTER)->setShouldWrapText(false)->build();

            // 写入表头
            $headerRow = WriterEntityFactory::createRowFromArray(array_keys($headers), $headerStyle);
            $writer->addRow($headerRow);

            // 数据列表导出
            if (!empty($this->dataList)) {
                foreach ($this->dataList as $item) {
                    $dataRow = $this->formatData($item, $headers, $dataRowStyle);
                    $writer->addRow($dataRow);
                }
            }

            if (!empty($this->query) && $this->handleType == 'chunk') {
                $this->query->chunk($this->chunkSize, function ($dataList) use ($writer, $headers, $dataRowStyle) {
                    foreach ($dataList as $item) {
                        $dataRow = $this->formatData($item, $headers, $dataRowStyle);
                        $writer->addRow($dataRow);
                    }
                });
            }

            if (!empty($this->query) && $this->handleType == 'cursor') {
                $this->query->cursor()->each(function ($item) use ($writer, $headers, $dataRowStyle) {
                    $dataRow = $this->formatData($item, $headers, $dataRowStyle);
                    $writer->addRow($dataRow);
                });
            }

            // 关闭文件
            $writer->close();

            // 后处理样式
            if ($this->processStyle) {
                $this->postProcessStyle($directory . $fullFileName);
            }

            if ($this->download) {
                return response()->download($filePath, $fullFileName);
            } else {
                $result = [
                    'name' => $fullFileName,
                    'path' => $directory . $fullFileName,
                    'url' => asset($directory . $fullFileName),
                ];

                return $result;
            }
        } catch (\Exception $e) {
            // 记录错误日志
            Log::error('SpoutExcel 导出失败：' . $e->getMessage());
            // 返回失败
            return false;
        }
    }

    /**
     * 使用 Spout 查询结果导出 Excel 文件
     *
     * @param string $fileName 导出的文件名（不包含路径）
     * @param string $directory 文件保存目录
     * @return array|bool 导出的文件完整 URL，失败时返回 false
     */
    public function arrayToExcel($fileName = "export", $directory = "exports/")
    {
        try {
            $headers = $this->headers;
            if (empty($headers)) {
                return [false, '表头不能为空', []];
            }

            // 确保文件格式正确
            if (!in_array($this->fileFormat, ['csv', 'xlsx'])) {
                return [false, '文件格式不正确', []];
            }

            // 确保文件名不为空
            if (empty($fileName)) {
                return [false, '文件名不能为空', []];
            }

            if (empty($this->dataList)) {
                return [false, '未设置数据或模型对象', []];
            }

            // 确保目录存在
            $directoryPath = $directory;
            if (!is_dir($directoryPath)) {
                File::makeDirectory($directoryPath, 0755, true);
            }

            // 生成文件名
            $uniqueId = uniqid();
            $fileExtension = $this->fileFormat === 'csv' ? 'csv' : 'xlsx';
            $fullFileName = "{$fileName}_{$uniqueId}.{$fileExtension}";
            $filePath = $directoryPath . '/' . $fullFileName;

            // 创建 Writer
            $writer = $this->fileFormat === 'csv'
                ? WriterEntityFactory::createCSVWriter()
                : WriterEntityFactory::createXLSXWriter();

            $writer->openToFile($filePath);

            // 定义样式
            $headerStyle = (new StyleBuilder())->setCellAlignment(CellAlignment::CENTER)->setFontBold()->setFontSize(12)->build();
            $dataRowStyle = (new StyleBuilder())->setCellAlignment(CellAlignment::CENTER)->setShouldWrapText(false)->build();

            // 写入表头
            $headerRow = WriterEntityFactory::createRowFromArray($headers, $headerStyle);
            $writer->addRow($headerRow);

            // 数据列表导出
            if (!empty($this->dataList)) {
                foreach ($this->dataList as $item) {
                    $dataRow = $this->formatDataByArrayToExcel($item, $dataRowStyle);
                    $writer->addRow($dataRow);
                }
            } else {
                return [false, '导出数据为空', []];
            }

            // 关闭文件
            $writer->close();

            // 后处理样式
            if ($this->processStyle) {
                $this->postProcessStyle($directory . $fullFileName);
            }

            if ($this->download) {
                return response()->download($filePath, $fullFileName);
            } else {
                $result = [
                    'name' => $fullFileName,
                    'path' => $directory . $fullFileName,
                    'url' => asset($directory . $fullFileName),
                ];

                return [true, '导出成功', $result];
            }
        } catch (\Exception $e) {
            // 记录错误日志
            Log::error('SpoutExcel 导出失败：' . $e->getMessage());
            // 返回失败
            return [false, 'SpoutExcel 导出异常', []];
        }
    }
}