0

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);
        }

    }


}
Saroj Shrestha
  • 2,696
  • 4
  • 21
  • 45

1 Answers1

4

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);
    }
}
IGP
  • 14,160
  • 4
  • 26
  • 43
  • 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? – Saroj Shrestha Dec 22 '20 at 11:45
  • To my knowledge you can't. – IGP Dec 22 '20 at 18:07
  • 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. – Saroj Shrestha Dec 23 '20 at 14:29
  • 1
    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 Dec 23 '20 at 17:14
  • 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. – IGP Dec 24 '20 at 07:09
  • Hello Sir, Could you help me on this one https://stackoverflow.com/questions/65466300/cannot-use-object-of-type-app-exports-offettableexport-as-array-laravel-excel – Saroj Shrestha Dec 27 '20 at 15:18
  • Hello SIr, I tried using the second-mentioned method having `WithStyles`, but it shows `Interface 'Maatwebsite\Excel\Concerns\WithStyles' not found` And I checked on the vendor file and it looks like it isn't there. I am using v3.1.19 – Saroj Shrestha Dec 31 '20 at 13:27
  • That concern was not yet implemented in 3.1.19. run `composer update maatwebsite/excel` – IGP Dec 31 '20 at 18:44