Php Excel set whole column data alignment not working

Question

I am Using this code For E column data set to right align but Its not showing me effect

$objPHPExcel->getActiveSheet()
    ->getStyle('E')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

instead of 'E' if i write E6 then it display E6 cell data to right.

$objPHPExcel->getActiveSheet()
    ->getStyle('E6')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

score 50 · Accepted Answer · answered Nov 09 '12 at 12:18

50

You're correct: row and column styles aren't supported by PHPExcel.

Cell styling is, but you can also set style by a range of cells:

$objPHPExcel->getActiveSheet()
    ->getStyle('E1:E256')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

answered Nov 09 '12 at 12:18

Mark Baker

209,507
32
346
385

but as i search and found in some example of other site and they provide the example with whole column alignment. Thats why i was confused. I was thinking that i am missing something.Thank you for your reply. – pankil thakkar Nov 10 '12 at 06:13
@pankil - If you have an example of setting a row or column style (e.g. alignment), then please provide it; and I'll stand corrected – Mark Baker Jan 25 '13 at 21:08
The answer by Jijesh Cherrai is a better answer. – Kellen Stuart Mar 05 '16 at 16:02
@Mark Baker Seems you can help me again. Look at this : https://stackoverflow.com/questions/51733202/how-can-i-set-text-align-right-in-the-column-on-the-laravel-excel-maatwebsite – moses toh Aug 08 '18 at 23:43
FWIW, `PHPExcel_Style_Alignment::HORIZONTAL_RIGHT` is `"right"`. I suppose it's possible that the constant will change but it's probably safe, more readable and easier to remember `setHorizontal('right')` – But those new buttons though.. Oct 26 '20 at 18:42

score 11 · Answer 2 · answered Oct 13 '15 at 08:40

Since nobody explained how to style an entire column, which was part of the question, here's the code:

$lastrow = $objPHPExcel->getActiveSheet()->getHighestRow();

$objPHPExcel->getActiveSheet()
        ->getStyle('E1:E'.$lastrow)
        ->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

Jijesh Cherayi · Answer 3 · 2015-08-20T09:30:48.840

8

Try this code. It works well.And I have confirmed.

 $activeSheet = $phpExcelObject->getActiveSheet();
    //..
    //...
     $activeSheet->getStyle("E")
                 ->getAlignment()
                 ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

This code align the column E in horizontal right

edited Aug 20 '15 at 09:30

answered Sep 18 '14 at 07:28

Jijesh Cherayi

1,111
12
15

1

This is the best answer. – Kellen Stuart Mar 05 '16 at 16:02
@KolobCanyon - Does this answer actually work? It doesn't on any of the PHPExcel code that I work with – Mark Baker Mar 05 '16 at 16:09
@MarkBaker Yes, I added screen shots here: http://stackoverflow.com/questions/35816644/format-the-cells-for-entire-column-in-php-excel – Kellen Stuart Mar 05 '16 at 16:31
If it works, how come you're posting a question saying that it doesn't work? – Mark Baker Mar 05 '16 at 16:34
@MarkBaker Both your answer and Jijesh Cherrai formats a range of cells within a column, but not the whole column itself. I'm asking a question about the implications of this. Technically that means it does work. I said this answer was better because you don't need to specify the range of cells. I think that is justified. – Kellen Stuart Mar 05 '16 at 16:37
1

Except that it doesn't work (whether it should or not is a different matter)..... believe me, I think I'd know if it was possible, I wrote the code in the first place – Mark Baker Mar 05 '16 at 16:41
1

I tried this with version 1.8 and it in fact did not work. Does not apply any formatting to any cells since "E" isn't treated as a range. – dctucker Dec 14 '16 at 00:38

score 0 · Answer 4 · answered May 28 '13 at 00:51

0

I have confirmed this as well while trying to apply certain number formats to colums: you cannot apply a style to a column - getStyle('E'), you must specify the range - getStyle('E1:E50').

$objPHPExcel->getActiveSheet()->fromArray($row_array, NULL, 'A2');
$rows = count($row_array);
$objPHPExcel->getActiveSheet()->getStyle('C2:C'.$rows)->getNumberFormat()->setFormatCode('000000000');

This code will left-pad the numbers in column C with zeros

answered May 28 '13 at 00:51

Jimbo

499
4
5

it's been a while but I just bumped into this question and this works **$activeSheet->getStyle('A')->getAlignment()->setHorizontal('right');** – Scaramouche Aug 14 '15 at 16:53
this too **$activeSheet->getStyle('A')->getFill()->setFillType('solid')->getStartColor()->setARGB('00FF0000');**. I'm starting to think maybe it's been added to later versions since 2013. Yes, I think that's it; Jijesh's post is correct and from 2014 – Scaramouche Aug 14 '15 at 16:58

Php Excel set whole column data alignment not working

4 Answers4

Linked