Laravel Excel, style only certain row values

I have built an array that I'm dumping out to an excel file using Laravel Excel which is an PHPExcel wrapper basically, for laravel

I successfully have this exporting my data, but I'm wondering how I can make the row for these values

    $excelRows[] = [
          $group->group_code,
          $group->group_name,
          $group->group_desc,
        ];

have bold font and background color

My code:

    $excelRows = [];

    foreach($prices->groups as $group){ 

        $excelRows[] = [
          $group->group_code,
          $group->group_name,
          $group->group_desc,
        ];

        foreach($group->skus as $sku){

            $row = [
              $sku->sku_info->frame->frame_code,
              strip_tags(html_entity_decode($sku->sku_info->frame->desc)),
            ];

            foreach ($sku->sku_info->covers as $covers) {
              $row[] = $covers->cover_code;
              $row[] = $covers->color_code;
              $row[] = $covers->color_name; 
            }

            $row[] = $sku->sku_info->upc;

            foreach($sku->prices as $price => $amount){
                $row[] = $amount;
            }

            $excelRows[] = $row;
        }
    }

    $name = 'FileExport';

    $build = Excel::create($name, function ($excel) use ($excelRows) {

        $excel->setTitle('File Export');

        $excel->sheet('File Export', function ($sheet) use ($excelRows) {

            $sheet->fromArray($excelRows);

            // bold the column headers
            $sheet->cell('A1:Z1', function ($cells) {
                $cells->setFontWeight('bold');
            });

            // set the width for the columns that are used 
            $sheet->setWidth('A', 10);
            $sheet->setWidth('B', 24);
            $sheet->setWidth('C', 20);
            $sheet->setWidth('D', 12);
            $sheet->setWidth('E', 10);
            $sheet->setWidth('F', 16);
            $sheet->setWidth('G', 16);
            $sheet->setWidth('H', 16);
            $sheet->setWidth('I', 16);
            $sheet->setWidth('J', 16);
            $sheet->setWidth('K', 16);

        });

    })->download('xlsx');

So an example in my excel is below with an * on the two rows that I would want bold and with color

    123  |  Test Category One  |  This is a test category *
    1234 |  Description |  DTL1 | 12 | color | DTL2 | 12 | catDetails | 1234567 | 450.00
    1234 |  Description |  DTL1 | 12 | color | DTL2 | 12 | catDetails | 1234567 | 370.00
    321  |  Test Category Two  |  This is another Test category *
    1234 |  Description |  DTL1 | 12 | color | DTL2 | 12 | catDetails | 1234567 | 470.00
    1234 |  Description |  DTL1 | 12 | color | DTL2 | 12 | catDetails | 1234567 | 440.00

Talinon

7 years ago

Level 51

There are a few ways you could go about this.. here's one possible way:

Build an array that holds the row numbers for each group:



  $excelRows = [];

    foreach($prices->groups as $group){ 

        $excelRows[] = [
          $group->group_code,
          $group->group_name,
          $group->group_desc,
        ];

    // add the row number to the array  
    $skuHeaderRows[] = count($excelRows);

    ...

}

After the sheet is populated with fromArray(), style the group category lines:


$excel->sheet('File Export', function ($sheet) use ($excelRows) {

            $sheet->fromArray($excelRows);

            // bold the column headers
            $sheet->cell('A1:Z1', function ($cells) {
                $cells->setFontWeight('bold');
            });

    foreach ($skuHeaderRows as $row)
    {

        $sheet->row($row, function($cells) {
            // apply bold
            $cells->setFontWeight('bold');
            // apply background colour
            $cells->setBackground('#FF0000');
        });

    }


...
})->download('xlsx');

Please or to participate in this conversation.