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

Question

I am trying to allow users to download Excel, using Laravel Excel files with product information. My current web route looks like this:

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

My current Export looks like this:

class ProductExport implements FromQuery
{
    use Exportable;

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

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

My current controller looks like this:

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');
}

When I head over to https://example.com/excel/release?alias=product_list&id=1 this executes correctly and returns an excel file. However, there is no column headers for the rows. The data comes out like so:

1   150 1   3       2019-01-16 16:37:25 2019-01-16 16:37:25     10

However, this should contain column headers like ID, cost etc... How can I include the column headers in this output?

miken32 · Accepted Answer · 2021-08-17T17:09:09.913

47

According to documentation you can change your class to use the WithHeadings interface, and then define the headings function to return an array of column headers:

<?php
namespace App;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ProductExport implements FromQuery, WithHeadings
{
    use Exportable;

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

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

    public function headings(): array
    {
        return ["your", "headings", "here"];
    }
}

This works with all export types (FromQuery, FromCollection, etc.)

edited Aug 17 '21 at 17:09

answered Jan 17 '19 at 23:18

miken32

42,008
16
111
154

Thanks for this! Is there a way I can use Eloquent to get the columns in the database dynamically? I'll accept as soon as i can! – Jaquarh Jan 17 '19 at 23:20
2

If you're exporting all the columns of the table: https://stackoverflow.com/a/37157879/1255289 – miken32 Jan 17 '19 at 23:22
how do i must call the headings method in the controller? – Luis Alfredo Serrano Díaz May 25 '20 at 00:52
@miken32 Is there any way to pass the array to headings from `query` functions or at least update it there and make it available on headings. Actually, I need to add Headings only if there are data on that column. So, is there any workaround for that? – Saroj Shrestha Dec 20 '20 at 11:47
It's a function. You can do whatever you want with it @SarojShrestha. Look at the code for the class, it's likely the data is a property you can get to. – miken32 Dec 20 '20 at 16:14
**use Exportable;** seems unnecessary – Farhan Ibn Wahid Jan 02 '21 at 11:34

score 22 · Answer 2 · answered Nov 04 '19 at 09:47

<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use DB;
class LocationTypeExport implements FromCollection,WithHeadings
{
    public function collection()
    {
        $type = DB::table('location_type')->select('id','name')->get();
        return $type ;
    }
     public function headings(): array
    {
        return [
            'id',
            'name',
        ];
    }
}

It only worked with :array for me, probably because of a newer version. — rafael.js, Jun 10 '20 at 18:10

score 7 · Answer 3 · edited Feb 19 '22 at 18:36

You can combine this with array_keys to dynamically get your column headers:

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

class ProductExport implements FromQuery, WithHeadings
{
    use Exportable;

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

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

    public function headings(): array
    {
        return array_keys($this->query()->first()->toArray());
    }
}

If you're using it with a collection, you can do so like the following:

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

class ProductExport implements FromCollection, WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        // for selecting specific fields
        //return ProductList::select('id', 'product_name', 'product_price')->get();
        // for selecting all fields
        return ProductList::all();
    }

    public function headings(): array
    {
        return $this->collection()->first()->keys()->toArray();
    }
}

When exporting my user table I needed to use: `array_keys($this->collection()->first()->toArray());` even though that’s using `FromCollection` as it’s a Collection of Models. — Ric, Jul 18 '22 at 11:08

score 1 · Answer 4 · answered Jul 25 '20 at 12:20

<?php

namespace App\Exports;

use App\Models\UserDetails;
use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class CustomerExport implements FromCollection, WithHeadings
{
   
    public function collection()
    {
        return UserDetails::whereNull('business_name')
        ->select('first_name','last_name','mobile_number','dob','gender')
        ->get();
    }

   
    public function headings() :array
    {
        return ["First Name", "Last Name", "Mobile","DOB", "Gender"];
    }
}

score 0 · Answer 5 · edited Sep 17 '21 at 06:52

0

<?php
    
    namespace App\Exports;
    
    use App\Models\StudentRegister;
    use Maatwebsite\Excel\Concerns\FromCollection;
    use Maatwebsite\Excel\Concerns\WithHeadings;
    
    class StudentExport implements FromCollection, WithHeadings
    {
        /**
        * @return \Illuminate\Support\Collection
        */
        public function collection()
        {
           
            return StudentRegister::select('name','fname','mname','gender','language','address')->get();
        }
    
        public function headings(): array
        {
            //Put Here Header Name That you want in your excel sheet 
            return [
                'Name',
                'Father Name',
                'Mother Name',
                'Gender',
                'Opted Language',
                'Corresponding Address'
            ];
        }
    }

edited Sep 17 '21 at 06:52

Suraj Rao

29,388
11
94
103

answered Sep 17 '21 at 06:48

QUTBUDDIN KHAN

9
1

3

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. – cursorrux Sep 17 '21 at 07:49
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 17 '21 at 08:52

score 0 · Answer 6 · edited Jul 30 '23 at 11:19

I am exporting from Collections and I wanted to generate headings automatically from the column names. The following code worked for me!

public function headings(): array
{
    return array_keys($this->collection()->first()->toArray());
}

If you want to manually write the column names return an array with the column names.

And don't forget to implement the WithHeadings interface

Thanks @Ric's comment.

score -1 · Answer 7 · answered Nov 17 '20 at 07:05

This code works for me

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

class NewsletterExport implements FromCollection, WithHeadings
{
    public function headings(): array
    {
        return [
            'Subscriber Id',
            'Name',
            'Email',
            'Created_at',
        ];
    }

    public function collection()
    {
        return Newsletter::where('isSubscribed', true)->get(['id','name','email','created_at']);
    }
}

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

7 Answers7

Linked