Laravel Excel Style with CSS not working while exporting view

Question

I am trying to export view using laravel Excel 3.1. While the export is working, I am not being able to style it.

My laravel Export looks as:

<?php

namespace App\Exports;

use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Events\AfterSheet;

class MatrixExcelExport implements FromView, WithEvents
{
    use RegistersEventListeners;
    protected $data, $selected_data, $am_lists_only, $categories_list, $amenities_list, $affordable_list, $unit_type_list, $highlights_list, $non_unit;
    public function __construct($data, $selected_data, $am_lists_only, $categories_list, $amenities_list, $affordable_list, $unit_type_list, $highlights_list, $non_unit)
    {
        $this->data = $data;
        $this->selected_data = $selected_data;
        $this->am_lists_only = $am_lists_only;
        $this->categories_list = $categories_list;
        $this->amenities_list = $amenities_list;
        $this->affordable_list = $affordable_list;
        $this->unit_type_list = $unit_type_list;
        $this->highlights_list = $highlights_list;
        $this->non_unit = $non_unit;
    }

    public function view(): View
    {
        return view('admin.matrix._excel', [
            'data' => $this->data,
            'selected_data' => $this->selected_data,
            'am_lists_only' => $this->am_lists_only,
            'categories' => $this->categories_list,
            'amenities' => $this->amenities_list,
            'affordables' => $this->affordable_list,
            'unit_types' => $this->unit_type_list,
            'highlights' => $this->highlights_list,
            'non_unit' => $this->non_unit
        ]);
    }
}

And my _excel looks like this:

<?php
$impact = 0;
$negativeChanges = 0;
$positiveChanges = 0;
?>
<style>
    .strikethroughCell{
        text-decoration: line-through !important;
    }
    .table-text-center th,
    .table-text-center td{
        text-align: center !important;
    }
    .text-added{
        color: #3ec10d;
        font-weight: bold;
    }
    .text-updated{
        color: #1843f5 !important;
        font-weight: bold;
    }
</style>
<table class="table-text-center">
    <thead>
        <tr>
            <th rowspan="2">Bldg</th>
            <th rowspan="2">Unit</th>
            <th rowspan="2">Floor</th>
            <th rowspan="2">Stack</th>
            @foreach($selected_data as $k => $v)
                <th colspan="{{count($v['amenities'])}}">
                    {{$v['category_name']}}
                </th>
            @endforeach
        </tr>
        <tr>
            @foreach($am_lists_only as $ak => $av)
                <th title="{{ $av }}">
                    <?php
                        if (strlen($av) > 15){
                            $av = substr($av, 0, 12) . '...';
                        }
                    ?>
                    {{ $av }}
                </th>
            @endforeach
        </tr>
    </thead>
    <tbody>
    @foreach($data as $k => $v)
        <?php
        if(count($v) == 0){
            continue;
        }
        $unit_cell_status  = '';
        $unit_note_class = '';
        $avail_status = '';

        if(trim($v[0]->unit_note) != ''){
            $unit_note_class = 'unit_note';
        }
        if(isset($v[0]->avail_status)){
            $avail_status = str_replace(' ','-',strtolower($v[0]->avail_status));
            if(!in_array($avail_status,$highlights)){
                $avail_status = '';
            }
        }
        $unit_type_id = NULL;
        if(!empty($v[0]->unit_type_id)){
            $unit_type_id = $v[0]->unit_type_id;
        }
        $unit_type_class = (in_array($unit_type_id, $unit_types))?"td-unit-type":"";

        ?>
        <tr id="ur_{{$v[0]->unit_id}}">
            <td data-search="{{ $v[0]->building_number }}">{{ $v[0]->building_number }}</td>
            <td data-search="{{ $v[0]->unit_id }}" data-unitid="{{$v[0]->unit_id}}" id="unitCell_{{$v[0]->unit_id}}" class="td-unit {{ $unit_note_class }} {{ $avail_status }} {{ $unit_type_class }}" xonclick="editUnit(1, {{ $v[0]->unit_id }}, {{ $v[0]->building_id }}); return false;">{{ $v[0]->unit_number }}</td>
            <td data-search="{{ $v[0]->floor }}">{{ $v[0]->floor }}</td>
            <td data-search="{{ $v[0]->stack }}">{{ $v[0]->stack }}</td>
            @foreach($am_lists_only as $ak => $av)
                <?php
                $amenity_val = "";
                $deleted_class = "";
                $negativeClass = "";
                $affordable = false;
                $text_class = '';
                $strikethrough_class = '';
                foreach($v as $vk => $vv){
                    if($ak == $vv->amenity_id){
                        if( (isset($categories[0]) && $categories[0] === "-1") || !empty(in_array($vv->category_id, $categories)) || !empty(in_array($vv->amenity_id, $amenities))) {
                            if(empty($vv->uav_deleted_at)){
                                $impact += $vv->amenity_value;
                                if($vv->av_status == 2){
                                    $text_class = 'text-updated';
                                    if($unit_cell_status == ''){
                                        $unit_cell_status = $text_class;
                                    }

                                    if($vv->initial_amenity_value != $vv->amenity_value){
                                        $diff = $vv->amenity_value - $vv->initial_amenity_value  ;
                                        if($diff > 0){
                                            $positiveChanges += $diff;
                                        }else{
                                            $negativeChanges += abs($diff);
                                        }
                                    }
                                }

                                if($vv->uav_status == 1){
                                    $text_class = 'text-added';
                                    $unit_cell_status = $text_class;
                                    if($vv->amenity_value != 0){
                                        if($vv->amenity_value > 0){
                                            $positiveChanges += abs($vv->amenity_value);
                                        }else{
                                            $negativeChanges += abs($vv->amenity_value);
                                        }
                                    }
                                }

                            }else{
                                $deleted_class = "td-deleted";
                                if($vv->amenity_value != 0){
                                    if($vv->amenity_value > 0){
                                        $negativeChanges += abs($vv->amenity_value);
                                    }else{
                                        $positiveChanges += abs($vv->amenity_value);
                                    }
                                }
                                $strikethrough_class = 'strikethroughCell';
                            }
                            $show_sum = true;
                            if($affordable == false && in_array($vv->amenity_id, $affordables) && empty($vv->uav_deleted_at)){
                                $affordable = true;
                            }
                            $amenity_val = $vv->amenity_value;
                            if($amenity_val < 0){
                                $amenity_val = "(".abs($amenity_val).")";
                                $negativeClass = 'text-negative';
                            }
                            if($deleted_class != ""){
                                $amenity_val = "<del>".$amenity_val."</del>";
                            }
                        }else{
                            if(empty($vv->uav_deleted_at)){
                                if($vv->av_status == 2){
                                    if($vv->initial_amenity_value != $vv->amenity_value){
                                        $diff = $vv->amenity_value - $vv->initial_amenity_value  ;
                                        if($diff > 0){
                                            $positiveChanges += $diff;
                                        }else{
                                            $negativeChanges += abs($diff);
                                        }
                                    }
                                }
                                if($vv->uav_status == 1){
                                    if($vv->amenity_value != 0){
                                        if($vv->amenity_value > 0){
                                            $positiveChanges += abs($vv->amenity_value);
                                        }else{
                                            $negativeChanges += abs($vv->amenity_value);
                                        }
                                    }
                                }
                            }else{
                                if($vv->amenity_value != 0){
                                    if($vv->amenity_value > 0){
                                        $negativeChanges += abs($vv->amenity_value);
                                    }else{
                                        $positiveChanges += abs($vv->amenity_value);
                                    }
                                }
                            }
                        }
                    }
                }
//                $str = ['<del>','</del>'];
//                $rplc =['-','-'];
//                $search_val = str_replace($str,$rplc,$amenity_val);

                ?>
                <td data-am_id="{{$ak}}" class="{{$strikethrough_class}} {{($affordable == true)?'affordable-unit':''}} {{$deleted_class}} {{$negativeClass}} {{ $text_class }}">
                    {!! $amenity_val !!}
                </td>
            @endforeach
        </tr>
    @endforeach
    </tbody>
</table>

Well, I want to center the text of all cells and want to add strikethrough to the text of those td with class strikethroughCell

Update:

It looks like I could change font Color with inline CSS, but I have yet to find a work around for line-through css.

Update 2

Completely switched to array method.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use DB;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class MatrixExport implements FromArray, WithHeadings, WithStrictNullComparison, WithEvents
{
    use RegistersEventListeners;
    protected $data, $selected_data, $am_lists_only, $categories_list, $amenities_list, $affordable_list, $unit_type_list, $highlights_list, $non_unit;
    static $negativeClassArr, $deletedClassArr, $addedClassArr, $updatedClassArr, $mergeCellsArr;
    public function __construct($data, $selected_data, $am_lists_only, $categories_list, $amenities_list, $affordable_list, $unit_type_list, $highlights_list, $non_unit, $negativeClassArr = [], $deletedClassArr = [], $addedClassArr = [], $updatedClassArr = [], $mergeCellsArr = [])
    {
        $this->data = $data;
        $this->selected_data = $selected_data;
        $this->am_lists_only = $am_lists_only;
        $this->categories_list = $categories_list;
        $this->amenities_list = $amenities_list;
        $this->affordable_list = $affordable_list;
        $this->unit_type_list = $unit_type_list;
        $this->highlights_list = $highlights_list;
        $this->non_unit = $non_unit;

//        $this->negativeClassArr = $negativeClassArr;
//        $this->deletedClassArr = $deletedClassArr;
//        $this->addedClassArr = $addedClassArr;
//        $this->updatedClassArr = $updatedClassArr;
//        $this->mergeCellsArr = $mergeCellsArr;

        self::$negativeClassArr = $negativeClassArr;
        self::$deletedClassArr = $deletedClassArr;
        self::$addedClassArr = $addedClassArr;
        self::$updatedClassArr = $updatedClassArr;
        self::$mergeCellsArr = $mergeCellsArr;
    }

    public function array(): array
    {
        $ex_data = $item = [];
        $impact = 0;
        $negativeChanges = 0;
        $positiveChanges = 0;
        self::$negativeClassArr = self::$deletedClassArr = [];

        $row = 3;
        foreach($this->data as $k => $v){
            if(count($v) == 0){
                continue;
            }
            $unit_cell_status  = '';

            $item = [
                'Bldg'  => $v[0]->building_number,
                'Unit'  => $v[0]->unit_number,
                'Floor' => $v[0]->floor,
                'Stack' => $v[0]->stack
            ];
            $col = 5;
            foreach ($this->am_lists_only as $ak => $av) {

                $colName  = $this->getNameFromNumber($col);

                $amenity_val = "";
                $deleted_class = "";
                $negativeClass = "";
                $affordable = false;
                $text_class = '';
                $font_color = '#000000';
                $font_weight = 'normal';
                $strikethrough_class = '';
                foreach($v as $vk => $vv){

                    if($ak == $vv->amenity_id){
                        if( (isset($this->categories_list[0]) && $this->categories_list[0] === "-1") || !empty(in_array($vv->category_id, $this->categories_list)) || !empty(in_array($vv->amenity_id, $amenities))) {
                            if(empty($vv->uav_deleted_at)){

                                $impact += $vv->amenity_value;
                                if($vv->av_status == 2 && $vv->uav_status == 0){
                                    $text_class = 'text-updated';
//                                    $font_color = '#1843f5';
//                                    $font_weight = 'bold';
                                    if($vv->initial_amenity_value != $vv->amenity_value){
                                        $diff = $vv->amenity_value - $vv->initial_amenity_value  ;
                                        if($diff > 0){
                                            $positiveChanges += $diff;
                                        }else{
                                            $negativeChanges += abs($diff);
                                        }
                                    }
                                }

                                if($vv->uav_status == 1){
                                    $text_class = 'text-added';
//                                    $font_color = '#3ec10d';
//                                    $font_weight = 'bold';
                                    if($vv->amenity_value != 0){
                                        if($vv->amenity_value > 0){
                                            $positiveChanges += abs($vv->amenity_value);
                                        }else{
                                            $negativeChanges += abs($vv->amenity_value);
                                        }
                                    }
                                }

                                if($text_class === "text-added"){
                                    self::$addedClassArr[] = $colName.$row;
                                }

                                if($text_class === "text-updated"){
                                    self::$updatedClassArr[] = $colName.$row;
                                }

                            }else{
                                $deleted_class = "td-deleted";
                                if($vv->amenity_value != 0){
                                    if($vv->amenity_value > 0){
                                        $negativeChanges += abs($vv->amenity_value);
                                    }else{
                                        $positiveChanges += abs($vv->amenity_value);
                                    }
                                }
//                                $strikethrough_arr[] = 'strikethroughCell';
                            }

                            $show_sum = true;
                            if($affordable == false && in_array($vv->amenity_id, $this->affordable_list) && empty($vv->uav_deleted_at)){
                                $affordable = true;
                            }
                            $amenity_val = $vv->amenity_value;
                            if($amenity_val < 0){
                                $amenity_val = "(".abs($amenity_val).")";
//                                $negativeClass = 'text-negative';
//                                $font_color = '#dc3545';
                                self::$negativeClassArr[] = $colName.$row;
                            }
                            if($deleted_class != ""){
                                self::$deletedClassArr[] = $colName.$row;
//                                $amenity_val = "<del>".$amenity_val."</del>";
                            }
                        }
                    }
                }
                //$col 4
                $item[$av] = $amenity_val;
                $col++;
            }
            $ex_data[] = $item;
            $row++;
        }
        return $ex_data;
    }

    public function headings(): array
    {
        $commonn_header_arr = ['Bldg', 'Unit', 'Floor', 'Stack'];
        $this->selected_data;
        $cat_header = [];
        $col = 5;
        $row = 1;
        foreach($this->selected_data as $v){
//            pe($this->selected_data);
//            $cat_header = $v['category_name'];
            $cnt = count($v['amenities']);
            for($i=0; $i<$cnt; $i++){
                $cat_header[] = $v['category_name'];
            }
            if($cnt > 1){
                $colNameStart = self::getNameFromNumber($col).$row;
                $col = $col + $cnt - 1;
                $colNameEnd = self::getNameFromNumber($col).$row;
                self::$mergeCellsArr[] = $colNameStart.':'.$colNameEnd;
            }
            $col += 1;
        }
        $first_row_header = array_merge($commonn_header_arr, $cat_header);
        $second_row_header = array_merge($commonn_header_arr, $this->am_lists_only);
//        return $second_row_header;
//        pe(self::$mergeCellsArr);
        return [
            $first_row_header,
            $second_row_header
        ];
    }

    public function getNameFromNumber($num) {
        $numeric = ($num - 1) % 26;
        $letter = chr(65 + $numeric);
        $num2 = intval(($num - 1) / 26);
        if ($num2 > 0) {
            return self::getNameFromNumber($num2) . $letter;
        } else {
        return $letter;
        }
    }

//    public function styles(Worksheet $sheet)
//    {
//        return [
//            // Style the first row as bold text.
//            1    => ['font' => ['bold' => true]],
//
//            // Styling a specific cell by coordinate.
//            'B2' => ['font' => ['italic' => true]],
//
//            // Styling an entire column.
//            'C'  => ['font' => ['size' => 16]],
//        ];
//    }

    public static function afterSheet(AfterSheet $event)
    {

        $negativeStyle = [
            'fill' => array(
                'color' => array('rgb' => 'dc3545')
            )
        ];
        // Create Style Arrays
        $addedStyle = [
            'font' => [
                'bold'  => true,
                'color' => array('rgb' => '3ec10d')
            ]
        ];
        $updatedStyle = [
            'font' => [
                'bold'  => true,
                'color' => array('rgb' => '1843f5')
            ]
        ];
        $negativeStyle = [
            'font' => [
                'bold'  => true,
                'color' => array('rgb' => 'dc3545')
            ]
        ];

        $strikethrough = [
            'font' => ['strikethrough' => true],
        ];

        // Get Worksheet
        $active_sheet = $event->sheet->getDelegate();

        // Apply Style Arrays
//        $active_sheet->getParent()->getDefaultStyle()->applyFromArray($default_font_style);
        $q = $event->sheet;
        foreach(self::$mergeCellsArr as $v){
            $q->mergeCells($v);
        }

        foreach(self::$negativeClassArr as $v){
            $active_sheet->getStyle($v)->applyFromArray($negativeStyle);
        }
        foreach(self::$addedClassArr as $v){
            $active_sheet->getStyle($v)->applyFromArray($addedStyle);
        }
        foreach(self::$updatedClassArr as $v){
            $active_sheet->getStyle($v)->applyFromArray($updatedStyle);
        }
        foreach(self::$deletedClassArr as $d){
            $active_sheet->getStyle($d)->getFont()->setStrikethrough(true);
        }

    }


}

You need to style it using the PhpSpreadsheet methods.

IGP
– IGP

2020-12-22 06:06:17 +00:00
Commented Dec 22, 2020 at 6:06 — IGP
– IGP, Commented Dec 22, 2020 at 6:06
Could you show me the way?

Saroj Shrestha
– Saroj Shrestha

2020-12-22 06:37:02 +00:00
Commented Dec 22, 2020 at 6:37 — Saroj Shrestha
– Saroj Shrestha, Commented Dec 22, 2020 at 6:37

IGP · Accepted Answer · 2020-12-22 07:21:23Z

5

+50

As I commented, for more complicated styles you need to use PhpSpreadsheet's styling methods. More info on the phpspreadsheet's docs.

The way I did it was by using the events to get the underlying phpspreadsheet classes.

use Maatwebsite\Excel\Concerns\FromView;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class MatrixExcelExport implements FromView, WithEvents
{
    use RegistersEventListeners;

    public function __construct(...) { ... }

    public function view(): View
    {
        return view('admin.matrix._excel', [...]);
    }

    public static function afterSheet(AfterSheet $event)
    {
        // Create Style Arrays
        $default_font_style = [
            'font' => ['name' => 'Arial', 'size' => 10]
        ];

        $strikethrough = [
            'font' => ['strikethrough' => true],
        ];

        // Get Worksheet
        $active_sheet = $event->sheet->getDelegate();

        // Apply Style Arrays
        $active_sheet->getParent()->getDefaultStyle()->applyFromArray($default_font_style);

        // strikethrough group of cells (A10 to B12) 
        $active_sheet->getStyle('A10:B12')->applyFromArray($strikethrough);
        // or
        $active_sheet->getStyle('A10:B12')->getFont()->setStrikethrough(true);

        // single cell
        $active_sheet->getStyle('A13')->getFont()->setStrikethrough(true);
    }
}

Or you could also follow laravel excel's styling guide

The WithStyles concerns allows styling columns, cells and rows. This might be useful when you want to make the heading row bold.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class InvoicesExport implements WithStyles
{
    public function styles(Worksheet $sheet)
    {
        return [
            // Style the first row as bold text.
            1    => ['font' => ['bold' => true]],

            // Styling a specific cell by coordinate.
            'B2' => ['font' => ['italic' => true]],

            // Styling an entire column.
            'C'  => ['font' => ['size' => 16]],
        ];
    }
}

For the contents of the styles array, please refer to the PhpSpreadsheet docs(opens new window)

If you prefer the fluent syntax for styling cells, you can do it as follows:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class InvoicesExport implements WithStyles
{
    public function styles(Worksheet $sheet)
    {
        $sheet->getStyle('B2')->getFont()->setBold(true);
    }
}

answered Dec 22, 2020 at 7:21

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Saroj Shrestha Over a year ago

Seems to be working, but how I can pass the cell position dynamically from _excel view?. On _excel view there you can see td-deleted Is there any way to pass that cell position to aftersheet function?

IGP Over a year ago

To my knowledge you can't.

Saroj Shrestha Over a year ago

I have switched from view method to array method and passes the array value using the global variable. I have updated it with a new code. 1. Could You please check if it is the optimized way, (as I am running multiple foreach). 2 How to center text for whole sheets. Once its completed I will mark it as solved.

IGP Over a year ago

I don't know if it's the most optimized but it might be faster than using a view. to center text for whole sheets you could use the set it on the defaultStyle. $active_sheet->getParent()->getDefaultStyle()->applyFromArray($centered_text); where

$centered_text = ['alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER]];

(Here it's both aligned vertically and horizontally)

IGP Over a year ago

Glad it helped. PhpSpreadsheet is not the most agreeable library to work with (and maybe even not the most efficient when it comes to just reading and writing spreadsheet files) but it is the most complete in terms of functionality as far as I know.

|

Collectives™ on Stack Overflow

Laravel Excel Style with CSS not working while exporting view

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related