PhpSpreadsheet how to export html with sub headers?

Question

ref: https://github.com/PHPOffice/PhpSpreadsheet/issues/1608

What is the expected behavior?

Without Events

What is the current behavior?

What are the steps to reproduce?

the issue is based on Maatwebsite/Laravel-Excel which internally use PhpSpreadsheet https://github.com/Maatwebsite/Laravel-Excel/issues/2784

<table style="width:100%" border="1">
    <thead>
        <tr>
            // ...
            <th style="text-align: center;" colspan="4">{{ __('Items') }}</th>
            // ...
        </tr>
        <tr>
             // ...
        </tr>
    </thead>

    @foreach ($models->cursor() as $model)
        <tbody>
            <tr>
                // ...

                <td colspan="4">
                    <table style="width:100%">
                        @foreach ($model->relation as $item)
                            <tr>
                                // ...
                            </tr>
                        @endforeach
                    </table>
                </td>

                // ...
            </tr>
        </tbody>
    @endforeach
</table>

// this works perfectly with single headers, but not with sub ones
public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->getRowDimension(1)->setRowHeight(30);

                $header = $sheet->getStyle('A1:' . $sheet->getHighestDataColumn() . '1');
                $header->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $header->getFont()->setBold(true);
                $header->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
                $header->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

                $other = $sheet->getStyle('A2:' . $sheet->getHighestDataColumn() . $sheet->getHighestRow());
                $other->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

                foreach ([$header, $other] as $item) {
                    $item->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
                    $item->getAlignment()->setWrapText(true);
                }
            },
        ];
    }

any help is appreciated & if u need any more info plz ask.

Answer 1

Sub headers or <tr> inside <thead> is valid HTML and should work fine with PhpSpreadsheet/Laravel-Excel. However, multiple <tbody> isn't valid/legal according to the HTML spec, and nested tables aren't supported by Laravel-Excel.

As I've mentioned in the comments, we've been using Laravel-Excel in our application, which uses a pretty similar layout, and works fine with multiple rows in head. So the real issue is with multiple or nested body. I suggest to follow the approach we've used. You can achieve the same layout with proper rowspan and colspan (we're doing count for calculating that).

Edit:

As requested, here's an example in HTML with sample data according to your layout, and below is the blade view with loops.

<table border="1">
  <thead>
    <tr>
        <th rowspan="2">Client Name</th>
        <th rowspan="2">Total</th>
        <th colspan="4">{{ __('Items') }}</th>
        <th rowspan="2">Creation Date</th>
    </tr>
    <tr>
        <th>Title</th>
        <th>Price</th>
        <th>Quantity</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    @foreach($customers as $customer)
        @php
            $count = count($customer->items);
            $i = 0;
        @endphp

        @foreach($customer->items as $item)
            <tr>
                @if($i == 0)
                    <td rowspan="{{ $count }}">{{ $customer->name }}</td>
                    <td rowspan="{{ $count }}">{{ $customer->total }}</td>
                @endif

                <td>{{ $item->title }}</td>
                <td>{{ $item->price }}</td>
                <td>{{ $item->qty }}</td>
                <td>{{ $item->total }}</td>
            
                @if($i == 0)
                    <td rowspan="{{ $count }}">{{ $customer->date }}</td>
                @endif
            </tr>
            @php
                $i++;
            @endphp
        @endforeach
    @endforeach
  </tbody>
</table>