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>

Answer 2

The bug you are facing now is not related to the library you are using.

You are using an HTML table for each relation item inside a master table containing all the stuff you need to display. It cannot work well because of that. Not all web browsers are supporting bad formed HTML documents, and their supports are varying much from one version to the other one.

Write proper HTML: Use DIVs elements to build an smart visual webpage, and display only the relations inside TABLEs elements, so they will not be partially hidden or misdisplayed as we can see here.

So to structure your HTML, if you use Bootstrap as an example, you can use a row for headers, and a row per item. For each relation item, you can put them inside a table, they will be displayed correctly.