PHP Phalcon框架：Excel上传后台执行及进度条展示问题咨询

Hey there! Since you're new to Phalcon and need to build this Excel upload + background processing + progress bar feature, let's break this down into manageable steps with concrete code examples that fit Phalcon's ecosystem.

We'll split this into 4 key parts:

• Web-side: Handle Excel upload, generate a unique task ID, and kick off a background process
• Background Task: A Phalcon CLI script to process the Excel, run your conditional logic, and update progress
• Progress Tracking: A database table to store task progress/status for frontend polling
• Frontend: An upload form + progress bar that fetches updates via AJAX

First, create a table to track task progress. This lets the frontend check how far along the background process is:

CREATE TABLE task_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(36) NOT NULL UNIQUE,
    progress INT DEFAULT 0,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    error_message TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This controller handles file uploads, initializes the task record, and starts the background process. Create app/controllers/UploadController.php:

<?php

namespace Controllers;

use Phalcon\Mvc\Controller;
use Phalcon\Security;

class UploadController extends Controller
{
    // Show upload form
    public function indexAction()
    {
        $this->view->pick('upload/index');
    }

    // Handle file upload and start background task
    public function processAction()
    {
        if (!$this->request->hasFiles()) {
            return $this->response->setStatusCode(400, 'No file uploaded');
        }

        $security = new Security();
        $taskId = $security->generateUUID(); // Unique ID for progress tracking
        $uploadedFile = $this->request->getUploadedFiles()[0];

        // Move file to a temp directory (make sure it's writable!)
        $tempFilePath = APP_PATH . '/public/temp/' . $uploadedFile->getName();
        if (!$uploadedFile->moveTo($tempFilePath)) {
            return $this->response->setStatusCode(500, 'Failed to save file');
        }

        // Initialize progress record
        $this->db->insert(
            'task_progress',
            [
                'task_id' => $taskId,
                'progress' => 0,
                'status' => 'pending',
                'created_at' => date('Y-m-d H:i:s')
            ],
            ['task_id', 'progress', 'status', 'created_at']
        );

        // Launch background CLI task (silent mode, so user can close browser)
        $cliScript = APP_PATH . '/cli.php'; // Path to your Phalcon CLI bootstrap
        $command = "php {$cliScript} excelProcess main {$tempFilePath} {$taskId} > /dev/null 2>&1 &";
        shell_exec($command);

        // Send task ID back to frontend for progress tracking
        return $this->response->setJsonContent(['task_id' => $taskId]);
    }

    // Endpoint for frontend to fetch progress
    public function getProgressAction($taskId)
    {
        $progress = $this->db->fetchOne(
            'SELECT progress, status FROM task_progress WHERE task_id = :task_id',
            \Phalcon\Db\Enum::FETCH_ASSOC,
            ['task_id' => $taskId]
        );

        if ($progress) {
            return $this->response->setJsonContent($progress);
        }

        return $this->response->setJsonContent(['error' => 'Task not found'])->setStatusCode(404);
    }
}

Phalcon CLI apps run independently of the web server, so they'll keep processing even if the user closes the browser. Create app/cli/tasks/ExcelProcessTask.php:

<?php

namespace Cli\Tasks;

use Phalcon\Cli\Task;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelProcessTask extends Task
{
    // Main action to process Excel
    public function mainAction($filePath, $taskId)
    {
        try {
            // Load Excel file (use PhpSpreadsheet via Composer)
            $reader = IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(true); // Save memory for large files
            $spreadsheet = $reader->load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $totalRows = $worksheet->getHighestRow() - 1; // Subtract header row
            $processedRows = 0;

            // Update status to "processing"
            $this->db->update(
                'task_progress',
                ['progress' => 0, 'status' => 'processing'],
                ['task_id' => $taskId]
            );

            // Process each row
            for ($row = 2; $row <= $worksheet->getHighestRow(); $row++) {
                // Extract data from Excel columns
                $rowData = [
                    'name' => $worksheet->getCell('A' . $row)->getValue(),
                    'email' => $worksheet->getCell('B' . $row)->getValue(),
                    // Add your other columns here
                ];

                // Run your conditional logic
                if (!empty($rowData['email']) && filter_var($rowData['email'], FILTER_VALIDATE_EMAIL)) {
                    // Insert valid data into your target table
                    $this->db->insert(
                        'user_data',
                        array_values($rowData),
                        array_keys($rowData)
                    );
                }

                $processedRows++;
                // Update progress every 10 rows to avoid excessive DB calls
                if ($processedRows % 10 === 0) {
                    $progress = round(($processedRows / $totalRows) * 100);
                    $this->db->update(
                        'task_progress',
                        ['progress' => $progress],
                        ['task_id' => $taskId]
                    );
                }
            }

            // Mark task as completed
            $this->db->update(
                'task_progress',
                ['progress' => 100, 'status' => 'completed'],
                ['task_id' => $taskId]
            );

            // Clean up temp file
            unlink($filePath);
        } catch (\Exception $e) {
            // Log error and update task status
            $this->db->update(
                'task_progress',
                ['status' => 'failed', 'error_message' => $e->getMessage()],
                ['task_id' => $taskId]
            );
        }
    }
}

Create app/views/upload/index.volt for the upload form and progress tracking UI:

<!DOCTYPE html>
<html>
<head>
    <title>Excel Upload & Process</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-5">
        <h2>Upload Excel File</h2>
        <form id="uploadForm" enctype="multipart/form-data">
            <div class="mb-3">
                <label for="excelFile" class="form-label">Select Excel (.xlsx/.xls)</label>
                <input class="form-control" type="file" id="excelFile" name="file" accept=".xlsx, .xls" required>
            </div>
            <button type="submit" class="btn btn-primary">Upload & Start Processing</button>
        </form>

        <!-- Progress Bar Section -->
        <div id="progressSection" class="mt-4 d-none">
            <h4>Processing Status</h4>
            <div class="progress">
                <div id="progressBar" class="progress-bar" role="progressbar" style="width: 0%" aria-valuenow="0" aria-valuemin="0" aria-valuemax="100">0%</div>
            </div>
            <p id="statusText" class="mt-2"></p>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.4/dist/jquery.min.js"></script>
    <script>
        $(document).ready(function() {
            $('#uploadForm').submit(function(e) {
                e.preventDefault();
                const formData = new FormData(this);

                $.ajax({
                    url: '/upload/process',
                    type: 'POST',
                    data: formData,
                    contentType: false,
                    processData: false,
                    success: function(res) {
                        $('#progressSection').removeClass('d-none');
                        startProgressTracking(res.task_id);
                    },
                    error: function() {
                        alert('Upload failed. Please try again.');
                    }
                });
            });

            // Poll for progress updates every second
            function startProgressTracking(taskId) {
                const interval = setInterval(() => {
                    $.ajax({
                        url: `/upload/get-progress/${taskId}`,
                        type: 'GET',
                        success: function(res) {
                            $('#progressBar').css('width', `${res.progress}%`).text(`${res.progress}%`);
                            $('#statusText').text(`Current Status: ${res.status}`);

                            // Stop polling when task finishes
                            if (res.status === 'completed' || res.status === 'failed') {
                                clearInterval(interval);
                                if (res.status === 'failed') {
                                    alert('Processing failed. Check admin logs for details.');
                                }
                            }
                        },
                        error: function() {
                            clearInterval(interval);
                            alert('Failed to fetch progress.');
                        }
                    });
                }, 1000);
            }
        });
    </script>
</body>
</html>

• Install PhpSpreadsheet: Run composer require phpoffice/phpspreadsheet to handle Excel files.
• CLI Bootstrap: Make sure your cli.php file (Phalcon CLI entry point) is configured to connect to the database and load the CLI tasks.
• Permissions: Ensure the public/temp directory is writable by the web server and CLI user.
• Large Files: For very big Excel files, consider using PhpSpreadsheet's chunk reading to avoid memory limits.
• Cleanup: Add a cron job to delete old task_progress records and unused temp files.

内容的提问来源于stack exchange，提问作者khalid wedaa