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:

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

2. 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.

Answer 1

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.

Answer 2

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"