CODEWITHSUNDEEP
phplaravel-5.7laravel-excel
Vannakpanha Mao
Vannakpanha Mao

Reputation: 69

How to map one to many relationships with Laravel excel 3.1 as a row

I have followed the document maatwebsite version 3.1 and I'm struggling with mapping all question choices into a row with a question when export as excel. Here's what I did in App\Exports\QuestionExcel.php:

<?php

namespace App\Exports;

use App\Question as Question;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMapping;

class QuestionExcel implements FromCollection, WithHeadings, WithMapping
{
    use Exportable;
    /*
        Question $question
    */
    private $i = 1;

    public function collection()
    {
        return Question::with('level','questionType','questionChoices')->get();
    }

    public function map($question): array
    {
        // $question 

        return [
            $this->i++,
            $question->description,
            $question->questionType->name,
            $question->level->name,
            $question->duration,
            $question->questionChoices->map(function ($choice){
                              return $choice->label;
                           }),
        ];
    }


    public function headings(): array
    {
        return [
            'No',
            'Question',
            'Type',
            'Level',
            'Duration (s)',
        ];
    }
}

In App\Http\Controllers\ExcelController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Maatwebsite\Excel\Excel;
use App\Exports\QuestionExcel;
use App\Level as Level;

class ExcelController extends Controller
{
    private $excel;

    public function __construct(Excel $excel)
    {
        $this->excel = $excel;
    }

    public function export_questions($level_id){
       $level = Level::find($level_id);
       $level_name = str_replace(' ', '_', $level->name);
       return (new QuestionExcel)->download($level_name.'_question_exports.xlsx');
    }
}

The result The result of excel

Please anyone help me to get result like this? Expected result

Upvotes: 2

Views: 8164

Answers (1)

Vannakpanha Mao
Vannakpanha Mao

Reputation: 69

Finally, I found the solution by using From View

In App\Exports\QuestionExcel.php

    <?php

    namespace App\Exports;

    use App\Question as Question;
    use Illuminate\Contracts\View\View;
    use Maatwebsite\Excel\Concerns\FromView;
    use Maatwebsite\Excel\Concerns\ShouldAutoSize;
    // for applying style sheet
    use Maatwebsite\Excel\Concerns\WithEvents;
    use Maatwebsite\Excel\Events\AfterSheet;
    Use \Maatwebsite\Excel\Sheet;

    class QuestionExcel implements FromView, WithEvents, ShouldAutoSize
    {
        // use Exportable;
        /*
            Question $question
        */
        public function __construct($level_id)
        {
            $this->level_id = $level_id;
        }


        public function view(): View
        {
            $questions = Question::where('level_id',$this->level_id)->with(['level','questionType','questionChoices'])->withCount('questionChoices');
            return view('excels.question_exports', [
                'questions' => $questions->get(),
                'option_column' =>  $questions->orderBy('question_choices_count','DESC')->first()
                // option_column, will return the largest number of question choices so we can create dynamic header in table
            ]);
    }

}

In views\excels.question_exports.blade.php

<html>
    <head>
    </head>
    <body>
        <table>
            <tr>
                <th rowspan="2">No</th>
                <th rowspan="2">Question</th>
                <th rowspan="2">Type</th>
                <th rowspan="2">Level</th>
                <th rowspan="2">Duration (s)</th>
                @for($i=1; $i<= $option_column->question_choices_count; $i++)
                    <th colspan="3">Option {{ $i}}</th>
                @endfor
            </tr>
            <tr>
                @for($i=1; $i<= $option_column->question_choices_count; $i++)
                    <th>Answer</th>
                    <th>Set correct</th>
                    <th>Explanations</th>
                @endfor 
            </tr>
            @foreach($questions as $index => $question)
            <tr>
                <td>{{ ($index+1) }}</td>
                <td>{{ $question->description }}</td>
                <td>{{ $question->questionType->name }}</td>
                <td>{{ $question->level->name }}</td>
                <td>{{ $question->duration }}</td>
                @foreach($question->questionChoices as $choice)
                    <td>{{ $choice->label }}</td>
                    <td>{{ $choice->is_correct?1:0 }}</td>
                    <td>{{ $choice->choice_explaination }}</td>
                @endforeach
            </tr>
            @endforeach
        </table>
    </body>
</html>

In App\Http\Controllers\ExcelController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Maatwebsite\Excel\Excel;
use App\Exports\QuestionExcel;
use App\Level as Level;

class ExcelController extends Controller
{
    private $excel;

    public function __construct(Excel $excel)
    {
        $this->excel = $excel;
    }

    public function export_questions($level_id){
        $level_name = Level::find($level_id)->name;
        return \Excel::download(new QuestionExcel($level_id), str_replace(" ","_",$level_name)."_question_exports.xlsx");
    }
}

Result of excel

Upvotes: 3

Related Questions