Using Maatwebsite Excel 3.1 Export how to set background color for cells and font size for heading?

Question

I Am using Maatwebsite Excel 3.1 for export functionality. How can we set the background color for cells and and font size for headings?.Could you please help me to solve this?

Thank you!

Answer 1

Firstly, implement WithHeadings and add use RegistersEventListeners. This will allow you to use afterSheet method autowired to the event:

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

class BomExport implements FromArray, WithEvents
{
    use RegistersEventListeners;

    public static function afterSheet(AfterSheet $event)
    {
        // Add styling here
    }

    // ...
}

For alternative ways of placing your styling code, check the documentation.

In afterSheet method, you can access the underlaying library and its Worksheet object (\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet):

$sheet = $event->sheet->getDelegate();

Using that object, you can e.g.:

• Set font size, weight and color on the first row:

  $sheet->getStyle('1')->getFont()
      ->setSize(16)
      ->setBold(true)
      ->getColor()->setRGB('0000ff')
  

• Set background color on the second column:

  $sheet->getStyle('B')->getFill()
      ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
      ->getStartColor()->setARGB('FFFF0000');
  

• Set border thickness on a cell:

  $sheet->getStyle('D3')->getBorders()->getAllBorders()
      ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
  

• Set row height:

  $sheet->getRowDimension('1')->setRowHeight(26);
  

For more options, see the documentation.

Unfortunately, styling whole rows ($sheet->getStyle('1')) and columns ($sheet->getStyle('B')) doesn't work in Excel Mobile (version 16001.12325.20032.0) I had to use cell ranges ($sheet->getStyle('A1:Z1') or $sheet->getStyle('A1:A999'))

Putting it together:

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

class BomExport implements FromArray, WithEvents
{
    use RegistersEventListeners;

    public static function afterSheet(AfterSheet $event)
    {
        $sheet = $event->sheet->getDelegate();

        $sheet->getStyle('1')->getFont()->setSize(16);
        $sheet->getStyle('1')->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFFF0000');
        // ...
    }

    // ...
}