Center all text in PHPSpreadsheet and make the cells expand to fill up with context

Question

I struggle a bit to make all the cells set the context to center and to autoexpand so it doesnt overlap each other.

So what I try to do is:

Set the info in every cell to be centered since it makes better for printing to PDF/etc.
Make the cells expand based on how much text there is in the cell. I dont want the the information in A to go over in cell B.

Ive tried this code but it doesnt seem to work:

$styleArray = array(
            'borders' => array(
                'outline' => array(
                    'style' => Alignment::HORIZONTAL_CENTER,
                ),
            ),
        );

        $sheet ->getStyle('A1:D30')->applyFromArray($styleArray);

Tho if I do it for one single cell (center context), it works. Did it like this:

$sheet->setCellValue('A2', $activitiesCount)->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

For the cells expanding tho I havent found a solution for that to try yet.

I would love to do both on all my cells with only 1 command if possible.

score 42 · Accepted Answer · answered Aug 29 '18 at 07:31

42

This would do the trick:

For autosizing (cell automatic expansion) do this:

$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);

NOTE

You have to do this individually for each column as the getColumnDimension method can only accept one column as it's parameter.

You have already figured out the horizontal alignment, but it is worth noting that you can set the alignment of more than one column using one command.

$sheet->getStyle('A:B')->getAlignment()->setHorizontal('center');

As for setting the cells values, I would prefer you do it separately from anything that has to do with formatting and styling just for the purpose of separation of concerns and readability.

Cheers.

answered Aug 29 '18 at 07:31

Chukwuemeka Inya

2,575
1
17
23

1

Thank you! This seem to work. Only thing I can point out is that $sheet->getStyle('A:B')->getAlignment()->setHorizontal('center'); doesnt seem to work as intended, but if I take 'A1:A10' for example, then it works for A1-A10. 'A:B' doesnt seem to be accepted. – stAMy Aug 29 '18 at 08:45
Cool...though `$sheet->getStyle('A:B')->getAlignment()->setHorizontal('center');` works for me. I am using `version 1.4;`... You? – Chukwuemeka Inya Aug 29 '18 at 09:21
In my composer.lock file it says phpoffice/phpspreadsheet: 1.0... maybe thats why. Thought it would update with composer update tho. Thanks! – stAMy Aug 29 '18 at 09:32
Note that this doesn't work for ods files... would you know what is the work around for this? Thanks. – Gellie Ann Nov 08 '20 at 13:08

score 4 · Answer 2 · answered Oct 01 '19 at 17:23

For autosizing a range of columns:

foreach(range('A','Z') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

Set the info in every cell to be centered:

$alignment_center = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER;

foreach($sheet->getRowIterator() as $row) {
    foreach($row->getCellIterator() as $cell) {
        $cellCoordinate = $cell->getCoordinate();
        $sheet->getStyle($cellCoordinate)->getAlignment()->setHorizontal($alignment_center);
    }
}

These work for:

"phpoffice/phpspreadsheet": "^1.6"

score 0 · Answer 3 · answered Jun 02 '20 at 17:18

Set the info in every cell to be centered since it makes better for printing to PDF/etc.

As $sheet->getStyle('A:B') didn't work with me so here is an alternative.

 $lastColumn = $sheet->getHighestColumn();
 $lastRow = $sheet->getHighestRow();
 $sheet->getStyle("A1:$lastColumn$lastRow")->applyFromArray($styleArray); // "A1:$lastColumn$lastRow" this is basically means apply style from the first cell to last cell (last column)

Make the cells expand based on how much text there is in the cell. I dont want the the information in A to go over in cell B.

$sheet->getColumnDimension('B')->setAutoSize(true);

you can also loop on columns and apply autosize to every column.

Center all text in PHPSpreadsheet and make the cells expand to fill up with context

3 Answers3