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