如何用Laravel-Maatwebsite实现Excel列求和导出并设置单元格只读

Got it, let's get your sum cells locked down properly! The key thing to remember with Excel cell protection is that cell locking only works when the worksheet itself is protected—by default, all cells are marked as locked, but it doesn't do anything until you turn on sheet protection.

Here's a step-by-step implementation using Maatwebsite Excel 3.x (the most common current version):

1. Use the Right Interfaces #

First, include the necessary interfaces in your export class to handle sheet protection and custom sheet configuration:

use Maatwebsite\Excel\Concerns\WithSheetProtection;
use Maatwebsite\Excel\Concerns\WithCustomSheets;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Worksheet\WorksheetCallback;

2. Configure Your Sheet & Protection #

In your export class, set up the sum formulas, lock the target cells, unlock editable cells, and enable sheet protection:

class YourReportExport implements WithSheetProtection, WithCustomSheets
{
    public function sheets(): array
    {
        return [
            'Report' => new class implements WorksheetCallback {
                public function configure(Worksheet $sheet)
                {
                    // Example: Set sum formulas for C1 to C10
                    for ($row = 1; $row <= 10; $row++) {
                        // Add your sum formula (you already had this part)
                        $sheet->setCellValue("C$row", "=SUM(A$row:B$row)");
                        
                        // Explicitly lock the sum cell (default is locked, but this ensures it)
                        $sheet->getStyle("C$row")->getProtection()->setLocked(true);
                    }

                    // Unlock columns A and B so users can edit them (critical!)
                    // If you skip this, ALL cells will be read-only when sheet is protected
                    $sheet->getStyle('A:B')->getProtection()->setLocked(false);

                    // Enable sheet protection (add a password if you want)
                    $sheet->getProtection()->setSheet(true);
                    // Optional: Set a password for protection
                    // $sheet->getProtection()->setPassword('your-secure-password');
                }
            }
        ];
    }

    // Optional: Define granular sheet protection permissions
    public function sheetProtection(): array
    {
        return [
            'sort' => false,
            'insertRows' => false,
            'formatCells' => false,
            'deleteColumns' => false,
            // Adjust these based on what users should be allowed to do
        ];
    }
}

Key Notes: #

• Sheet Protection is Mandatory: Without setSheet(true), the cell locking won't have any effect—Excel ignores locked cells unless the sheet is protected.
• Unlock Editable Cells: Since all cells are locked by default, you need to explicitly unlock any cells you want users to edit (like A and B in your case).
• Password is Optional: If you don't need password protection, just remove the setPassword line.
• Flexible Range Selection: Instead of looping through rows, you can target entire columns if applicable (e.g., $sheet->getStyle('C:C')->getProtection()->setLocked(true) for all of column C).

If You're Using FromView #

If you're generating your export from a Blade view, you can still add this logic by using the AfterSheet event:

use Maatwebsite\Excel\Concerns\FromView;
use Maatwebsite\Excel\Concerns\AfterSheet;

class YourReportExport implements FromView, AfterSheet
{
    public function view(): View
    {
        // Your view logic here
        return view('exports.report', ['data' => $yourData]);
    }

    public function afterSheet(Worksheet $sheet)
    {
        // Apply the same protection logic here
        $sheet->getStyle('C:C')->getProtection()->setLocked(true);
        $sheet->getStyle('A:B')->getProtection()->setLocked(false);
        $sheet->getProtection()->setSheet(true);
    }
}

That should do it! Your sum cells will be read-only, while users can still edit the A and B columns to update the calculations.

内容的提问来源于stack exchange，提问作者Sakthivel A R