Laravel-excel: Missing data in XLSX export

[lucacattide]

Prerequisites

• [ ] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• [x] Checked that your issue isn't already filed.
• [x] Checked if no PR was submitted that fixes this problem. -> maybe related to this one but not sure

Versions

• PHP version: 7.2.1
• Laravel version: 5.6
• Package version: 3.0

Description

Exporting data via FormQuery + WithHeadings + ShouldAutoSize results in a partial populated file. Some columns not being included in file.

Steps to Reproduce

1. Implements the Export;
2. Implements FormQuery + WithHeadings + ShouldAutoSize;
3. Call the export with xlsx extension;

Expected behavior:

File exported including full data

Actual behavior:

I'm trying to export a full xlsx file, but for some reason some columns are not being included.
Here's a screen from my DB - the red line indicates non-included column:

This is the xlsx produced:
items.xlsx

Follows the code implementation:
ItemExport.php

<?php

namespace App\Exports;

use App\Item;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ItemExport implements FromQuery, WithHeadings, ShouldAutoSize
{
    // Abilita l'esportazione senza facade
    use Exportable;

    public function query()
    {
        return Item::query();
    }

    public function headings(): array
    {
        return [
            'id',
            'codice',
            'data_acquisto',
            'serial',
            'labeled',
            'estensione_garanzia',
            'stato',
            'data_dismissione',
            'note',
            'componente_id',
            'tipologia_id',
            'condizione_id',
            'locazione_id',
            'fornitore_id',
            'parent_id',
            'created_at',
            'updated_at',
            'deleted_at'
        ];
    }
}

ItemController.php

<?php

// Definizione Namespace
namespace App\Http\Controllers\API;

use App\Item;
use App\Exports\ItemExport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Resources\Item as ItemResource;
use Carbon\Carbon;

class ItemController extends Controller
{
[...]
/**
     * Esporta gli elementi in formato CSV o XLS
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function esporta(Request $request)
    {
        $items = ItemResource::collection(Item::all());
        $intestazione = [
            'id',
            'codice',
            'data_acquisto',
            'serial',
            'labeled',
            'estensione_garanzia',
            'stato',
            'data_dismissione',
            'note',
            'componente_id',
            'tipologia_id',
            'condizione_id',
            'locazione_id',
            'fornitore_id',
            'parent_id',
            'created_at',
            'updated_at',
            'deleted_at'
        ];

        // Controllo formato
        if ($request->formato == 'csv') {
            // CSV
            [...]
        } else {
            // Excel
            return (new ItemExport)->download('items.xlsx');
        }
    }
[...]
}

Additional Information

Actually the controller is invoked with a normal synchronous request - simple link/no AJAX - from a Vue component. I tried to remove too WithHeadings, ShouldAutoSize, rolling back to simplest FormCollection export but nothing changed.
Maybe I'm missing something?

Thanks in advance for help.

[patrickbrouwers]

Hey @lucacattide ! By default we export only "visible" eloquent attributes. This means that we ignore relationships and hidden fields (e.g. password). This explains why you don't see those columns in your export. The best thing to do is to use the WithMapping concern, so you can explicitly define what needs to be in your export.

[lucacattide]

It worked. Many thanks.

[patrickbrouwers]

Great!

[MLouis]

Hi @lucacattide ! Could you show me an example of how you did it ? In my Export class I can't access the relashionships

<?php

namespace App\Exports;

use App\Contact;
use App\Cart;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\Exportable;

class ContactsExport implements FromQuery, WithMapping, WithHeadings
{
    use Exportable;

    public function __construct(Cart $cart)
    {
        $this->cart = $cart;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function query()
    {
        /* I use data from $this->cart to build my query, but I simplified and tested without it for the example */
        return Contact::query()->with(['rubric', 'area']);
    }

    public function map($contact): array
    {
        return [
            $contact->rubric['name'], // blank cells, $contact->rubric is null, and $contact->rubric() makes the job fails
            $contact['name']
        ];
    }

    public function headings(): array
    {
        return [
            'Rubrique',
            'Nom'
        ];
    }
}

Here is my Contact model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'rubric_id', 'area_id', 'name', 'address', 'postal_code', 'city', 'phone_number', 'fax_number', 'email',
    ];

    public function rubric()
    {
        return $this->belongsTo('App\Rubric');
    }

    public function area()
    {
        return $this->belongsTo('App\Area');
    }
}

and my Rubric model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Rubric extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
    ];

    /**
     * Return this rubric's contacts
     *
     * @return App/Contact
     */
    public function contacts()
    {
        return $this->hasMany('App\Contact');
    }
}

[lucacattide]

Hi @MLouis, here as I proceeded

<?php
// Definizione Namespace
namespace App\Exports;

use App\Item;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ItemExport implements FromQuery, WithHeadings, WithMapping, ShouldAutoSize
{
    // Abilita l'esportazione senza facade
    use Exportable;

    /**
     * Data fetching
     *
     * @return void
     */
    public function query()
    {
        return Item::query();
    }

    /**
     * Imposta l'intestazione della scheda
     *
     * @return array
     */
    public function headings(): array
    {
        return [
            'id',
            'codice',
            'data_acquisto',
            'serial',
            'labeled',
            'estensione_garanzia',
            'stato',
            'data_dismissione',
            'note',
            'componente_id',
            'tipologia_id',
            'condizione_id',
            'locazione_id',
            'fornitore_id',
            'parent_id',
            'created_at',
            'updated_at',
            'deleted_at'
        ];
    }

    /**
    * Imposta i contenuti della scheda
    *
    * @var Item $item
    */
    public function map($item): array
    {
        return [
            $item->id,
            $item->codice,
            $item->data_acquisto,
            $item->serial,
            $item->labeled,
            $item->estensione_garanzia,
            $item->stato,
            $item->data_dismissione,
            $item->note,
            // 1:n relation foreign keys
            $item->componente_id,
            $item->tipologia_id,
            $item->condizione_id,
            $item->locazione_id,
            $item->fornitore_id,
            $item->parent_id,
            //
            $item->created_at,
            $item->updated_at,
            $item->deleted_at
        ];
    }
} 

As you can see - and as suggested by @patrickbrouwers - in my case I'm querying all the Item table, but in map function I'm extracting the foreign keys directly.

Hope it'll help you.

[MLouis]

Thanks for the reply @lucacattide !

Sorry, I read the issue too quickly, your problem was to retrieve foreign keys from your model, not the related models themselves. I'll have to try something different.

[lucacattide]

There, I use the foreign keys to fetch data from a _Vue_ component in order to populate the .xls. Item is the belongsTo side of the relation. If I understood, you have to do the opposite - retreive the hasMany side. I suggest you to try anyway, maybe by using the IDs something could happen.

[MLouis]

I facepalmed myself, as you said @lucacattide the solution was to select() the rubric_id and area_id for the with() to work.

[...]
class ContactsExport implements FromQuery, WithMapping, WithHeadings
{
    [...]
    public function query()
    {
        return Contact::query()
            ->select('rubric_id', 'area_id', 'name')
            ->with(['rubric:id,name', 'area:id,name']);
    }

    public function map($contact)
    {
        return [
            $contact->rubric->name,
            $contact->area->name,
            $contact->name,
        ];
    }
}

Thanks again for the suggestion !

[lucacattide]

You're welcome. Glad you've found it. ;)