Map query result with multiple headings using laravel excel

Question

I am exporting an excel using Laravel excel 3.1 by Maatwebsite. I want to map it with 2 headings and display it one below the other

I'm using WithHeadings, WithMapping, FromArray I'm getting the records, just the format that needs correction

<?php

namespace App\Exports;

use Modules\Program\Entities\Program;
use Modules\report\Entities\report;
use DB;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithMapping;

class reportPerDaySheet implements  FromArray, WithTitle, WithHeadings, ShouldAutoSize, WithMapping
{
    private $year;
    private $day;
    private $month;

    public function __construct(int $year, int $month, int $day)
    {
        $this->year = $year;
        $this->day = $day;
        $this->month  = $month;
    }


    public function array():array
    {    
        $reportOut = DB::table('reports')->join('programs','programs.program_id','=','reports.program_id')->whereMonth('report_dateofreport', $this->month)->whereday('report_dateofreport', $this->day)->get()->toArray();

        return $reportOut;
    }

    public function map($reportOut):array
    {
        return [
            [
                $reportOut->program_programname,
                $reportOut->program_projectlead,
                $reportOut->program_dse,
                $reportOut->program_extserviceprovider,
                $reportOut->program_programid,
                $reportOut->program_datatype,
            ],
            [
                $reportOut->report_id,
                $reportOut->report_date,
                $reportOut->report_url,
                $reportOut->report_username,
                $reportOut->report_reportertype,
                $reportOut->report_productname,
                $reportOut->report_verbatim,
                $reportOut->report_priority,
                $reportOut->report_aeraised,
            ]            
        ];
    }


    public function title(): string
    {
        return $this->day .' '. date("F", mktime(0,0,0,$this->month,1)) .' '. $this->year;
    }
    public function headings(): array
    {
        return[
            [
              'Program Name',
              'Project Lead',
              'DS&E Contact',
              'Name of external service provider',
              'Prepared By',
              'External Service Provider Contact Information',
              'Time Period Covered',
              'Program ID',
              'Date of Report',
              'Data Type',
              'Signature'
            ],
            [
                'Id',
                'Date of report',
                'Date',
                'URL',
                'Username',
                'Reporter Type',
                'Product Name',
                'Verbatim',
                'Priority',            
                'AE Raised',
                'User',
                'Date From',
                'Date Till',
                'Record Created At',
                'Record Updated At'
            ]                   
        ];
    }
}

Current Output:

Desired Output:

Are you sure you can have two heading rows? I've checked the documentation and I don't know if you can do that. https://docs.laravel-excel.com/3.1/imports/heading-row.html I would split my data in two sheets. — namelivia, Jun 20 '19 at 07:50
I'm already getting my data with two headings. The problem is to arrange it accordingly. https://docs.laravel-excel.com/3.1/exports/mapping.html I'm also using multiple sheets so cannot split it — Adil, Jun 20 '19 at 08:50

score 0 · Answer 1 · answered Jun 27 '19 at 09:45

0

In your situation it would be recommended to build the array including the headers within the array() method. Heading rows will always be displayed as the first x rows. (Depending on how many arrays you return)

answered Jun 27 '19 at 09:45

Patrick Brouwers

71
1

Sorry I'm a bit confused, can you help me with a basic example. Thanks – Adil Jul 01 '19 at 06:16

score 0 · Answer 2 · answered Oct 22 '19 at 11:52

I ran into a similar situation where i needed to add headings at certain rows, Think of the function map() as a foreach() it loops through all your data which means you can add a few checks in and add your headings as an additional row.

have a look at the laravel excel docs

Example of map function:

public function map($reportOut): array
{
    if($this->addHeadingRow == 1) {

        $this->addHeadingRow = 0; // for my use case I only want to add the heading once
        $map = [
            [], // add an empty array if you would like an spacer row
            [
                'heading 1',
                'heading 2',
                'and so on...'
            ]
        ];        
    }
    else{
        $map =  [
            $reportOut->program_programname,
            $reportOut->program_projectlead,
            $reportOut->program_dse,
            $reportOut->program_extserviceprovider,
            $reportOut->program_programid,
            $reportOut->program_datatype,
        ];
    }

    return $map;
}

Map query result with multiple headings using laravel excel

2 Answers2