PhpSpreadsheet how to export html with sub headers?

user_3574919 0
Viewed 1266

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

What is the expected behavior?

html

Without Events

enter image description here

What is the current behavior?

html

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.

laravel-excel phpspreadsheet laravel php
2 Answers

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>
user_32093810

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.

user_3904620
Related