CODEWITHSUNDEEP
phpspreadsheet
Stephen Shaw
Stephen Shaw

Reputation: 41

Using PhpSpreadsheet, how do you set style for an entire sheet (not the whole spreadsheet)

Using PhpSpreadsheet I want to get (or set) the style for an entire sheet (not the whole spreadsheet) I know the syntax for doing that for the whole spreadsheet $spreadsheet->getDefaultStyle()->getFont()->setSize(100);

and for a single cell $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(100);

but the same function for a whole sheet fails $spreadsheet->getActiveSheet()->getStyle()->getFont()->setSize(100);

What is the correct syntax for styling a whole sheet?

Upvotes: 1

Views: 1940

Answers (2)

Sotar
Sotar

Reputation: 1

What worked for me, was using

$sheet->calculateWorksheetDimension();

which returns a string, which you can use to get the range encompassing your whole sheet. Like this, to use the original example:

$range = $sheet->calculateWorksheetDimension();
$spreadsheet->getActiveSheet()->getStyle($range)->getFont()->setSize(100);

Note: I was using LaravelExcel (which is a Laravel PHPSpreadsheet implementation), but the method exists in the original library, as seen here: https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Worksheet-Worksheet.html#method_calculateWorksheetDimension

Upvotes: 0

WolF
WolF

Reputation: 11

I also wanted to set a style for whole active sheet. After quick looking into code i think there is no such possibility at the moment. I ended up utilizing the implemented "conversion check":

// Convert '1:3' to 'A1:XFD3'
$coordinate = self::pregReplace('/^(\d+):(\d+)$/', 'A${1}:XFD${2}', $coordinate);

As i was already counting rows when writing cells i therefore ended up calling e.g.:

$spreadsheet->getActiveSheet()->getStyle("1:$row")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

There also are some other "conversion checks" which could come in handy:

// Convert 'A' to 'A:A'
$coordinate = self::pregReplace('/^([A-Z]+)$/', '${1}:${1}', $coordinate);

// Convert '1' to '1:1'
$coordinate = self::pregReplace('/^(\d+)$/', '${1}:${1}', $coordinate);

// Convert 'A:C' to 'A1:C1048576'
$coordinate = self::pregReplace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $coordinate);

Upvotes: 1

Related Questions