Setting width of spreadsheet cell using PHPExcel

Question

I'm trying to set the width of a cell in an Excel document generated with PHPExcel with:

$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setWidth('10');
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setAutoSize(false);

but that does not works.

What is the method that I need to call here?

score 200 · Accepted Answer · edited Feb 09 '17 at 11:10

200

It's a subtle difference, but this works fine for me:

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);

Notice, the difference between getColumnDimensionByColumn and getColumnDimension

Also, I'm not even setting AutoSize and it works fine.

edited Feb 09 '17 at 11:10

Muhammad Hassaan

7,296
6
30
50

answered Aug 13 '11 at 11:45

James John McGuire 'Jahmic'

11,728
11
67
78

How can I set the wordwrap property to particular column. It would be useful for me if you know. Thanks! – Talk2Nit Dec 01 '14 at 07:02
5

It's been a while since I have worked with PHPExcel, but I believe you want 'setWrapText'. example: $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setWrapText(true); – James John McGuire 'Jahmic' Dec 02 '14 at 04:00
Jahmic is right. AutoSize is set to false on all columns by default, so it's not necessary to do it with code. – imelgrat Jun 06 '18 at 20:12

score 13 · Answer 2 · answered Oct 16 '15 at 12:52

13

setAutoSize method must come before setWidth:

$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setAutoSize(false);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setWidth('10');

answered Oct 16 '15 at 12:52

Rolland

679
5
10

2

AutoSize:false is required in order to set a width – user3711851 Sep 13 '16 at 10:18
2

getColumnDimensionByColumn() expects a 0-based column index, not a column name. (from the Worksheet class: "@param string $pColumn Numeric column coordinate of the cell") – userfuser Aug 11 '17 at 04:43

score 9 · Answer 3 · edited Feb 08 '15 at 21:22

9

hi i got the same problem.. add 0.71 to excel cell width value and give that value to the

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);

eg: A Column width = 3.71 (excel value)

give column width = 4.42

will give the output file with same cell width.

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4.42);

hope this help

edited Feb 08 '15 at 21:22

Rizier123

58,877
16
101
156

answered Feb 08 '15 at 21:05

King Alawaka

521
4
3

score 8 · Answer 4 · answered Mar 15 '18 at 07:31

8

autoSize for column width set as bellow. It works for me.

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

answered Mar 15 '18 at 07:31

Du Luong

124
1
4

score 5 · Answer 5 · answered Nov 05 '15 at 19:35

5

Tha is because getColumnDimensionByColumn receives the column index (an integer starting from 0), not a string.

The same goes for setCellValueByColumnAndRow

answered Nov 05 '15 at 19:35

shurbks

51
1
1

Magor Menessy · Answer 6 · 2017-12-22T14:50:46.397

5

This worked for me:

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

be sure to add setAuzoSize(false), before the setWidth(); as Rolland mentioned

edited Dec 22 '17 at 14:50

answered Dec 22 '17 at 14:47

Magor Menessy

381
4
13

Please don't add duplicate answer like Rolland have already defined – M Khalid Junaid Dec 22 '17 at 14:49
1

yeah but it's working with getColumnDimension and not getColumnDimensionByColumn – Magor Menessy Dec 22 '17 at 19:20
@MKhalidJunaid You might want to check again. It's not even close to being a duplicate answer, since it implements completely other methods. – I try so hard but I cry harder Apr 30 '21 at 20:18

score 2 · Answer 7 · answered Jun 16 '21 at 02:59

2

This way is much easier to adjust the size of the columns automatically.

foreach (range('A', 'I') as $letra) {            
            $spreadsheet->getActiveSheet()->getColumnDimension($letra)->setAutoSize(true);
}

answered Jun 16 '21 at 02:59

Carlos Alberto Gonzalez

159
2
5

score 1 · Answer 8 · answered Aug 14 '14 at 08:32

The correct way to set the column width is by using the line as posted by Jahmic, however it is important to note that additionally, you have to apply styling after adding the data, and not before, otherwise on some configurations, the column width is not applied

Setting width of spreadsheet cell using PHPExcel

8 Answers8

Linked