How to middle align cell value in PhpSpreadsheet?

Question

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

My expected output should be this:

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?

probably a few reasons why you could have recieved a downvote: (a) you haven't provided the definitions of key variables (`$template['start_from']`, `$template['start_to']`, `$attribute[]`, etc.), and (b) lack of percieved research effort (the docs are freely available and describe specifically how to achieve this) — esqew, Mar 13 '19 at 12:01
actually these are my database values if i add more description they will try to add only approiate info — kunal, Mar 13 '19 at 12:03

esqew · Accepted Answer · 2019-03-13T12:19:54.517

24

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}

edited Mar 13 '19 at 12:19

answered Mar 13 '19 at 11:57

esqew

42,425
27
92
132

i used for testing purpose i have reoved still same output – kunal Mar 13 '19 at 11:59
1

@kunal - Did you _remove_ the `alignment` property or did you _change_ it to be `vertical` instead of `horizontal` as this answer suggests? – M. Eriksson Mar 13 '19 at 12:00
1

@kunal - You might need to set both `horizontal` and `vertical` as centered. – M. Eriksson Mar 13 '19 at 12:02
1

kunal - Please see updated answer with @MagnusEriksson's suggestion – esqew Mar 13 '19 at 12:04

score 6 · Answer 2 · answered Mar 29 '21 at 23:49

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.

How to middle align cell value in PhpSpreadsheet?

2 Answers2