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?

Restaurateur

Answer 1

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.)

Gwendolin

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! – Restaurateur

If you're exporting all the columns of the table: https://mcmap.net/q/389519/-how-to-select-all-column-name-from-a-table-in-laravel – Gwendolin

how do i must call the headings method in the controller? – Belize

@Gwendolin 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? – Greylag

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. – Gwendolin

use Exportable; seems unnecessary – Fatima

Answer 2

<?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',
        ];
    }
}

Figwort

It only worked with :array for me, probably because of a newer version. – Nudd

Answer 3

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

Rollerskate

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. – Corrincorrina

This should be the accepted answer, so simple and dynamic! – Deanery

Answer 4

<?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"];
    }
}

Territorialize

Answer 5

<?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'
            ];
        }
    }

Yager

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. – Startling

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. – Irick

Answer 6

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.

Volsung

Answer 7

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

Hourihan