Laravel中使用maatwebsite包设计Excel/CSV并优化导出数据显示

Hey Gabrielle! Let's work through your two questions step by step—first the export formatting issue you're currently stuck on, since you shared your code and the problem you're seeing, then we'll cover general Excel design tips with the Maatwebsite package.

Fixing Your CSV Export: Adding Titles & Correcting Data Structure #

Looking at your code, there are two key issues causing the raw, unlabeled data output:

1. You wrapped the query result in an extra array with $items=array($items);—this nests your collection inside another array, leading to weird formatting.
2. You didn’t define a header row to label each column of data.

Here’s the corrected version of your export method, with comments explaining the changes:

public function export() {
    // Fetch your data as a collection (no need to wrap it in an extra array)
    $items = DB::table('userinformations')
        ->join('users', 'userinformations.user_id', '=', 'users.id')
        ->select('userinformations.fname','userinformations.lname','users.email')
        ->where('userinformations.payment_status',1)
        ->get();

    Excel::create('items', function($excel) use($items) {
        $excel->sheet('ExportFile', function($sheet) use($items) {
            // Step 1: Define your header labels
            $headers = ['First Name', 'Last Name', 'Email'];
            // Add the header row to the first line of the sheet
            $sheet->row(1, $headers);

            // Step 2: Populate data starting from row 2
            $currentRow = 2;
            foreach ($items as $item) {
                $sheet->row($currentRow, [
                    $item->fname,
                    $item->lname,
                    $item->email
                ]);
                $currentRow++;
            }

            // Alternative shorter approach:
            // Convert the collection to a 2D array, add headers to the top, then use fromArray
            // $data = $items->toArray();
            // array_unshift($data, $headers);
            // $sheet->fromArray($data);
        });
    })->export('csv');
}

This will output a CSV with a clear header row at the top, and each user’s data aligned under the correct title.

General Tips for Designing Excel/CSV Files with Maatwebsite/Laravel-Excel #

Once you’ve fixed the basic export, here are some common design techniques to make your files more professional:

1. Use Export Classes (Recommended for Clean Code) #

Instead of writing all logic in the controller, create dedicated export classes. This makes your code reusable and easier to maintain.

Example UsersExport class:

// app/Exports/UsersExport.php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromCollection, WithHeadings
{
    public function collection()
    {
        // Return your query result directly
        return DB::table('userinformations')
            ->join('users', 'userinformations.user_id', '=', 'users.id')
            ->select('userinformations.fname','userinformations.lname','users.email')
            ->where('userinformations.payment_status',1)
            ->get();
    }

    // Define your header labels here
    public function headings(): array
    {
        return [
            'First Name',
            'Last Name',
            'Email'
        ];
    }
}

Then call it in your controller:

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

public function export()
{
    return Excel::download(new UsersExport, 'items.csv');
}

2. Style Excel Files (For .xlsx, Not CSV) #

CSV files don’t support styling, but if you’re exporting to Excel (.xlsx), you can add formatting like bold headers, background colors, and adjusted column widths:

Add the WithStyles interface to your export class:

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class UsersExport implements FromCollection, WithHeadings, WithStyles
{
    // ... collection() and headings() methods ...

    public function styles(Worksheet $sheet)
    {
        // Style the header row (row 1) with bold text and an orange background
        return [
            1 => [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => 'FFA500']
                ]
            ],
        ];

        // You can also set column widths directly:
        // $sheet->getColumnDimension('A')->setWidth(20);
        // $sheet->getColumnDimension('C')->setWidth(35);
    }
}

3. Add Merged Cells & Custom Titles #

For more polished Excel reports, you can merge cells for a main title:

// Inside your export class's styles() method or sheet closure
$sheet->mergeCells('A1:C1');
$sheet->setCellValue('A1', 'Paid User Information');
$sheet->getStyle('A1')->getFont()->setSize(16)->setBold(true);

内容的提问来源于stack exchange，提问作者Gabrielle-M