CODEWITHSUNDEEP
phpphpexcelphpspreadsheet
kunal
kunal

Reputation: 4248

How to middle align cell value in PhpSpreadsheet?

I want to align the cell value to the middle. My output looks like this:-

Current output

My expected output should be this:

Desired output

I want every column to be in the center. I tried the following code:

$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => [
            'argb' => '0070C0',
        ],
        'endColor' => [
            'argb' => '0070C0',
        ],
    ],
];

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

I tried all the other attributes like HORIZONTAL_CENTER, RIGHT, LEFT, JUSTIFY, etc. How can I do this properly?

Upvotes: 11

Views: 26238

Answers (2)

totymedli
totymedli

Reputation: 31182

Beyond the style array way you can also do it in the method chaining way:

$spreadsheet->getActiveSheet()->getStyle($cells)->getAlignment()->setHorizontal($align)

$spreadsheet->getActiveSheet()->getStyle($cells)->getAlignment()->setVertical($align);
  • $cells should be a single cell ('A1') or a range of cells ('A1:E4').
  • $align should be a constant of the \PhpOffice\PhpSpreadsheet\Style\Alignment class (or its string value) for the desired alignment.
    • for horizontal alignment:
      • Alignment::HORIZONTAL_GENERAL or 'general'
      • Alignment::HORIZONTAL_LEFT or 'left'
      • Alignment::HORIZONTAL_RIGHT or 'right'
      • Alignment::HORIZONTAL_CENTER or 'center'
      • Alignment::HORIZONTAL_CENTER_CONTINUOUS or 'centerContinuous'
      • Alignment::HORIZONTAL_JUSTIFY or 'justify'
      • Alignment::HORIZONTAL_FILL or 'fill'
      • Alignment::HORIZONTAL_DISTRIBUTED or 'distributed' (Excel2007 only)
    • for vertical alignment:
      • Alignment::VERTICAL_BOTTOM or 'bottom'
      • Alignment::VERTICAL_TOP or 'top'
      • Alignment::VERTICAL_CENTER or 'center'
      • Alignment::VERTICAL_JUSTIFY or 'justify'
      • Alignment::VERTICAL_DISTRIBUTED or 'distributed' (Excel2007 only)

The source of these is the source.

Upvotes: 8

esqew
esqew

Reputation: 44710

You're setting the wrong (and one too few) key(s) for the alignment setting. What you're attempting to achieve is the vertical and horizontal alignment of the text.

'alignment' => [
    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],

PhpSpreadsheet docs

Upvotes: 27

Related Questions