How can you include column headers when exporting Eloquent to Excel in Laravel?

Title: Incorporating Column Headers in Laravel Eloquent Excel Exports

Body: We’ve recently been asked how to include column headers when exporting Eloquent models as an Excel file using Laravel Excel in Laravel applications. Currently, the output shows only data rows without any relevant information about their fields. This blog post will guide you through adding column headers to your generated files.

Let’s first review our current code:

1. Web Route:

   Route::get('/excel/release', 'ExcelController@create')->name('Create Excel');

2. Eloquent Model and Query:

   class ProductExport implements FromQuery
    {
        use Exportable;
   
        public function __construct(int $id)
        {
            $this->id = $id;
        }
   
        public function query()
        {
            return ProductList::query()->where('id', $this->id);
        }
    }

3. Controller:

   public function create(Request $request) {
   
    # Only alowed tables
    $alias = [
        'product_list' => ProductExport::class
    ];
   
    # Ensure request has properties
    if(!$request->has('alias') || !$request->has('id'))
        return Redirect::back()->withErrors(['Please fill in the required fields.'])->withInput();
   
    # Ensure they can use this
    if(!in_array($request->alias, array_keys($alias)))
        return Redirect::back()->withErrors(['Alias ' . $request->alias . ' is not supported'])->withInput();
   
    # Download
    return (new ProductExport((int) $request->id))->download('iezon_solutions_' . $request->alias . '_' . $request->id . '.xlsx');
   

}

Now, to add column headers:

1. Import the appropriate libraries:

   use Maatwebsite\Excel\Facades\Excel;
    use Illuminate\Support\Collection;

2. Create a View class for your Eloquent model and add use Exportable; at the beginning:

   class ProductExport implements FromQuery, WithHeadings
   

public function __construct(int $id)
{
    $this->id = $id;
}

public function query()
{
    return ProductList::query()->where('id', $this->id);
}

} Here, we’ve added the WithHeadings trait to our ProductExport class. This trait allows us to add column headers when exporting data as an Excel file.

3. Change your controller:

   public function create(Request $request) {
   
    # Only alowed tables
    $alias = [
        'product_list' => ProductExport::class
    ];
   
    # Ensure request has properties
    if(!$request->has('alias') || !$request->has('id'))
        return Redirect::back()->withErrors(['Please fill in the required fields.'])->withInput();
   
    # Ensure they can use this
    if(!in_array($request->alias, array_keys($alias)))
        return Redirect::back()->withErrors(['Alias ' . $request->alias . ' is not supported'])->withInput();
   
    # Download
    return (new ProductExport((int) $request->id))->download('iezon_solutions_' . $request->alias . '_' . $request->id . '.xlsx');
   

} After adding the WithHeadings trait, you can use the collection() method to add column headers to your Excel file. The collection method takes an array of column titles as its first parameter and a callback function with the data as the second one. It then returns a new instance of Collection, which is used to create the Excel file.

Here’s a simplified example for adding column headers:

return (new ProductExport((int) $request->id))->download('iezon_solutions_' . $request->alias . '_' . $request->id . '.xlsx'), function($query, Collection $collection) {
    $collection->map(function($product) use ($columns) {
        return array_combine($columns, $product); // create an associative array with column names as keys and respective data as values
    });

    $headers = ['ID', 'Cost', 'Created At'];

    $collection->prependRow(0, $headers); // add the headers to our collection
});

This implementation uses map() to create an associative array with column names and their respective data. Afterwards, it prepends a new row with the desired headers at index 0 using prependRow(). Finally, pass the updated collection back to the download() method along with your original query object.

By following these steps and making use of Laravel’s powerful Eloquent capabilities, you can easily generate Excel files containing column headers for your data exports.