Laravel 8中使用maatwebsite/excel导出带表头的动态原生查询结果

Hey there! Let's work through this Excel export challenge you've got with your custom stored queries in Laravel 8 and maatwebsite/excel. I've put together a step-by-step solution that should cover exactly what you need:

Step 1: Create a Custom Export Class #

First, let's build a dedicated export class that handles both your dynamic data and column headers. This class will leverage maatwebsite/excel's core interfaces to define the data source and header structure.

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

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

class CustomQueryExport implements FromArray, WithHeadings, WithStyles
{
    private $rawData;
    private $columnHeaders;

    // Inject data and headers via constructor for flexibility
    public function __construct(array $rawData, array $columnHeaders)
    {
        $this->rawData = $rawData;
        $this->columnHeaders = $columnHeaders;
    }

    // Convert stdClass objects (from DB::select) to arrays for Excel compatibility
    public function array(): array
    {
        return array_map(function ($record) {
            return (array) $record;
        }, $this->rawData);
    }

    // Define the column headers for the Excel sheet
    public function headings(): array
    {
        return $this->columnHeaders;
    }

    // Optional: Add styling for cleaner, more professional headers
    public function styles(Worksheet $sheet)
    {
        // Make the header row bold
        $sheet->getStyle('1')->getFont()->setBold(true);
        // Auto-size all columns to fit content neatly
        foreach ($this->columnHeaders as $index => $header) {
            $column = chr(65 + $index); // Convert index to column letter (A, B, C...)
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }
    }
}

Step 2: Add a Helper Method to Extract Headers from Your Query #

Next, we need to pull column headers directly from your parsed SQL query. This method will extract field aliases (like full_name or active) to use as user-friendly Excel column titles. Add this to your Export model:

// app/Models/Export.php
private function extractQueryHeaders(string $query): array
{
    // Extract the content between SELECT and FROM to get field definitions
    preg_match('/SELECT\s+(.*?)\s+FROM/i', $query, $matches);
    
    if (!isset($matches[1])) {
        return []; // Fallback if parsing fails
    }

    $headers = [];
    $fields = explode(',', trim($matches[1]));

    foreach ($fields as $field) {
        $field = trim($field);
        // Check if the field has an alias (using AS)
        if (stripos($field, ' as ') !== false) {
            // Split on "AS" and grab the alias part
            list(, $alias) = explode(' as ', strtolower($field));
            $headers[] = ucwords(trim($alias)); // Format alias to a readable title
        } else {
            // No alias? Use the raw field name, formatted nicely
            $headers[] = ucwords(trim($field));
        }
    }

    return $headers;
}

Step 3: Update Your Excel Method to Trigger the Export #

Now modify your excel method to tie everything together: handle placeholder replacement securely, fetch data, extract headers, and kick off the Excel download. Critical note: We'll use parameter binding instead of str_replace to avoid SQL injection risks!

// app/Models/Export.php
use App\Exports\CustomQueryExport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;

public function excel($params = [])
{
    // 1. Use parameter binding for security (update your stored query to use ? placeholders)
    // Example stored query: SELECT id, name as full_name, is_active as active FROM users WHERE my_field > ?;
    $data = DB::select($this->query, [$params['my_field']]);

    // 2. Extract headers from the stored query
    $headers = $this->extractQueryHeaders($this->query);

    // 3. Trigger the Excel download with a unique filename
    return Excel::download(
        new CustomQueryExport($data, $headers),
        "custom-export-{$this->id}.xlsx"
    );
}

Key Notes & Improvements #

• SQL Injection Protection: Using DB::select($query, $params) is far safer than direct string manipulation—never trust user input with raw regex replacement for SQL queries!
• Handling Complex Queries: If your queries include nested functions (like CONCAT(first_name, ' ', last_name) as full_name), the regex in extractQueryHeaders will still work, but you can tweak it for edge cases (like nested parentheses) if needed.
• Fallback Headers: If header parsing fails, you could add a fallback that pulls keys from the first $data object to ensure the export still works.

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