Laravel Excel Export: Formatting and Styling Cells

Laravel Excel Export: Formatting and Styling Cells

Laravel-Excel package is great for exporting data. But not a lot of info there about formatting Excel cells – widths, word wraps, fonts etc. So when I encountered this in a client’s project, I decided to write this article with a few tips on this topic.

Default Laravel Export

Let’s create a dummy project with Users table and default Laravel Auth. Then we create 10 random users. Actually, Laravel comes with pre-built Factory class for User model, so all we need to do is to create a UsersTableSeeder.php

PHP

1

2

3

4

5

6

7

8

9

classUsersTableSeederextendsSeeder

{

publicfunctionrun()

{

factory(App\User::class,10)->create();

}

}

Then, we require Laravel Excel package:

PHP

1

2

3

composer requiremaatwebsite/excel

Now, we export all users to Excel. To do that, we create a separate class app/Exports/UsersExport.php:

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

namespaceApp\Exports;

useApp\User;

useMaatwebsite\Excel\Concerns\FromCollection;

useMaatwebsite\Excel\Concerns\WithHeadings;

classUsersExport implementsFromCollection,WithHeadings

{

publicfunctioncollection()

{

returnUser::all();

}

publicfunctionheadings():array

{

return[

'#',

'Name',

'Email',

'Created at',

'Updated at'

];

}

}

Finally, let’s use it from Controller:

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

namespaceApp\Http\Controllers;

useApp\Exports\UsersExport;

useMaatwebsite\Excel\Facades\Excel;

classExportControllerextendsController

{

functionexport()

{

returnExcel::download(newUsersExport,'users.xlsx');

}

}

Here’s the visual result:

It does the job, exports the data. But does it look good and readable? Far from it. So let’s tweak it a little.

It just takes whatever cell range we pass, and changes the styles.
Here’s where we need to dig deeper and look at the package which is the base of Laravel Excel. It’s actually based on PHPSpreadsheet package. So let’s take a look at its documentation, and specifically section Recipes.

Some examples from there:

Wrap text:

PHP

1

2

3

4

$spreadsheet->getActiveSheet()->getStyle('A1:D4')

->getAlignment()->setWrapText(true);

Default style of a worksheet:

PHP

1

2

3

4

$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');

$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

Styling borders and applying styles from array:

PHP

1

2

3

4

5

6

7

8

9

10

11

12

$styleArray=[

'borders'=>[

'outline'=>[

'borderStyle'=>\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,

'color'=>['argb'=>'FFFF0000'],

],

],

];

$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

You can find more examples in that recipes section, just apply it to your individual needs.

Beautiful article, but in the case of importing a spreadsheet file using this package how do we catch errors, let’s say an invalid file was uploaded, also in the case where we expect the uploaded file to have some named headings how do we check if the headings are there? Thanks