BroadbandSaler/app/Models/SpoutImport.php

<?php

namespace App\Models;

//安装 composer require box/spout

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

class SpoutImport
{
    private $headers = []; // 表头
    private $request = null; // 请求对象
    private $filePath = ""; // 文件路径
    private $importNum = 1000; // 单次导入数量
    private $filename = "excel_import"; // 文件名
    private $firstRowContent = []; // 第一行内容

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

    // 设置请求对象
    public function setRequest($request)
    {
        $this->request = $request;
        return $this;
    }

    // 设置文件路径
    public function setFilePath($filePath)
    {
        $this->filePath = $filePath;
        return $this;
    }

    // 设置单次导入数量
    public function setImportNum($importNum)
    {
        $this->importNum = $importNum;
        return $this;
    }

    // 设置文件名
    public function setFilename($filename)
    {
        $this->filename = $filename;
        return $this;
    }

    // 获取第一行内容
    public function getFirstRowContent()
    {
        return $this->firstRowContent;
    }

    /**
     * 获取表格数据
     *
     * @param string $saveCatalogue 文件流存储的目录，指定文件无需设置
     * @return array
     */
    public function getExcelData($saveCatalogue = "imports/")
    {
        if ($this->request) {
            // 获取上传的文件
            $file = $this->request->file('file');
            // 生成唯一的文件名
            $filename = $this->filename . uniqid() . $file->getClientOriginalExtension();
            $file->move(public_path($saveCatalogue), $filename);
            $filePath = public_path($saveCatalogue) . $filename;
        } else {
            if (empty($this->filePath)) {
                return false;
            }
            $filePath = $this->filePath;
        }

        // 读取 Excel 文件
        $reader = ReaderEntityFactory::createXLSXReader();
        $reader->open($filePath);

        $listAll = [];
        $isFirstRow = true;
        foreach ($reader->getSheetIterator() as $sheet) {
            foreach ($sheet->getRowIterator() as $row) {
                if ($isFirstRow) {
                    // 跳过第一行（表头）
                    foreach ($row->getCells() as $cell) {
                        $this->firstRowContent[] = trim($cell->getValue());
                    }
                    $isFirstRow = false;
                    continue;
                }

                if (count($listAll) > $this->importNum) {
                    return ['code' => 0, 'msg' => "单次导入限制{$this->importNum}条"];
                }

                $list = [];
                $cells = $row->getCells();

                if($this->headers){
                    $i = 0;
                    foreach ($this->headers as $value) {
                        $list[$value] = trim($cells[$i]->getValue());
                        $i++;
                    }
                }else{
                    foreach ($cells as $cell) {
                        $list[] = trim($cell->getValue());
                    }
                }

                $listAll[] = $list;
            }
        }

        $reader->close();

        return $listAll;
    }

    /**
     * 获取执行结果
     *
     * @param array $dataList 数据列表
     * @param string $saveCatalogue 文件流存储的目录
     * @return String 执行结果文件路径
     */
    public function getResult(array $dataList, $saveCatalogue = "imports/out/")
    {
        $writer = WriterEntityFactory::createXLSXWriter();
        $filename = $this->filename . "_out" . uniqid() . '.xlsx';

        if (!File::exists(public_path($saveCatalogue))) {
            File::makeDirectory(public_path($saveCatalogue), 0777, true, true);
        }

        $file_path = public_path($saveCatalogue) . $filename;
        $writer->openToFile($file_path);

        // 添加表头
        $headers = array_keys($this->headers);
        $headersValue = array_values($this->headers);
        array_push($headers, '执行结果');
        array_push($headersValue, 'result');
        $header = WriterEntityFactory::createRowFromArray($headers);
        $writer->addRow($header);
        foreach ($dataList as $value) {
            $rowData = [];

            foreach ($headersValue as $item) {
                $rowData[] = $value[$item];
            }
            $row = WriterEntityFactory::createRowFromArray($rowData);
            $writer->addRow($row);
        }

        $writer->close();

        return $saveCatalogue . $filename;
    }

    // 生成导入模板 [ 安装PhpSpreadsheet扩展 composer require phpoffice/phpspreadsheet ]
    function generateImportTemplat(array $headers, array $widthArr = [])
    {
        // 创建 Spout XLSX 写入器
        $writer = WriterEntityFactory::createXLSXWriter();

        // 保存到临时文件
        $tempFilePath = sys_get_temp_dir() . '/import_template.xlsx';

        // 打开 Spout 写入器并开始写入到临时文件
        $writer->openToFile($tempFilePath);

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

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

        // 关闭 Spout 写入器
        $writer->close();

        // 使用 PHPSpreadsheet 加载生成的临时文件
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($tempFilePath);

        // 获取活动工作表
        $sheet = $spreadsheet->getActiveSheet();

        if (empty($widthArr)) {
            // 设置列宽度，可以根据内容动态调整或自定义列宽
            for ($col = 'A'; $col <= $sheet->getHighestColumn(); $col++) {
                $sheet->getColumnDimension($col)->setAutoSize(true);  // 自动调整列宽
            }
        } else {
            // 设置列宽的示例（如果你想自定义宽度）
            $index = 0;
            for ($col = 'A'; $col <= $sheet->getHighestColumn(); $col++) {
                $sheet->getColumnDimension($col)->setWidth($widthArr[$index]);  // 自定义列宽
                $index++;
            }
        }

        // 保存处理后的文件
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $outputFilePath = $this->filename;

        // 提供下载
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $outputFilePath . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');

        $writer->save('php://output'); // 直接输出到浏览器

        // 删除临时文件（如果需要）
        unlink($tempFilePath);
    }
}