PHPExcel auto size column width

Question

I'm trying to auto size the columns of my sheet. I'm writing the file and in the end I try to resize all of my columns.

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('B1', 'test1111111111111111111111')
            ->setCellValue('C1', 'test1111111111111')
            ->setCellValue('D1', 'test1111111')
            ->setCellValue('E1', 'test11111')
            ->setCellValue('F1', 'test1')
            ->setCellValue('G1', 'test1');

foreach($objPHPExcel->getActiveSheet()->getColumnDimension() as $col) {
    $col->setAutoSize(true);
}
$objPHPExcel->getActiveSheet()->calculateColumnWidths();

The above code doesn't work. Doesn't change the column size to fit the text

UPDATE The writer I'm using $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

Mark Baker · Accepted Answer · 2013-05-26T18:01:26.827

227

If a column is set to AutoSize, PHPExcel attempts to calculate the column width based on the calculated value of the column (so on the result of any formulae), and any additional characters added by format masks such as thousand separators.

By default, this is an estimated width: a more accurate calculation method is available, based on using GD, which can also handle font style features such as bold and italic; but this is a much bigger overhead, so it is turned off by default. You can enable the more accurate calculation using

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

However, autosize doesn't apply to all Writer formats... for example CSV. You don't mention what writer you're using.

But you also need to identify the columns to set dimensions:

foreach(range('B','G') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
        ->setAutoSize(true);
}

$objPHPExcel->getActiveSheet()->getColumnDimension() expects a column ID.

$objPHPExcel->getActiveSheet()->getColumnDimensions() will return an array of all the defined column dimension records; but unless a column dimension record has been explicitly created (perhaps by loading a template, or by manually calling getColumnDimension()) then it won't exist (memory saving).

edited May 26 '13 at 18:01

answered May 26 '13 at 17:46

Mark Baker

209,507
32
346
385

Thank you so much. It works. It does not calculate the extra spacing created from the bold font, although that's expected (I've read it somewhere). Could you update your answer to include that too? – Alkis Kalogeris May 26 '13 at 17:59
1

If you need that degree of precision with typeface style such as bold or italic, then you need to use PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT, but it is a lot slower – Mark Baker May 26 '13 at 18:00
It's ok I don't care. Although I don't know how to do it. I'm using the 01simple-download-xls.php from the Test folder. Where do I add that line? Sorry for my complete noobness. I've just started to play with it. – Alkis Kalogeris May 26 '13 at 18:06
2

Also, If you don't want to iterate via column letters but indexes instead you can use the static method PHPExcel_Cell::stringFromColumnIndex($columnIndex) to get the column letter – MeatPopsicle Mar 03 '14 at 10:41
@MarkBaker Is there any method available to do with a single statement so that we can set width of columns given in a list. Currently I am using $activeSheetObj->getColumnDimension('G')->setWidth(35); the columns in the list may be of any order. – Rosa Mystica Dec 19 '17 at 09:57
The only option to provide a list is to loop through an array of column names is as shown in @TodStoychev's answer, iterating over that array – Mark Baker Dec 19 '17 at 10:29
In 2019 using PhpSpreadsheet you can do this: `Font::setAutoSizeMethod(Font::AUTOSIZE_METHOD_EXACT);` with `use PhpOffice\PhpSpreadsheet\Shared\Font;`. – Entity May 16 '19 at 18:34

score 64 · Answer 2 · answered Oct 06 '14 at 10:58

64

If you need to do that on multiple sheets, and multiple columns in each sheet, here is how you can iterate through all of them:

// Auto size columns for each worksheet
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {

    $objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));

    $sheet = $objPHPExcel->getActiveSheet();
    $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(true);
    /** @var PHPExcel_Cell $cell */
    foreach ($cellIterator as $cell) {
        $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
    }
}

answered Oct 06 '14 at 10:58

MrUpsidown

21,592
15
77
131

7

This is the better solution, since it doesn't require a special consideration for columns past Z. – Anthony Sep 17 '15 at 13:44
3

I can confirm this answer works flawlessly in PhpSpreadSheet as well. Thanks a ton! – Hissvard Oct 03 '18 at 07:26
1

Use `$worksheet->getColumnIterator()` to simplify things here. Refer to my answer – BARNZ Sep 03 '20 at 22:12

score 29 · Answer 3 · answered Jul 07 '14 at 12:19

29

Here a more flexible variant based on @Mark Baker post:

foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
        $phpExcelObject->getActiveSheet()
                ->getColumnDimension($col)
                ->setAutoSize(true);
    }

Hope this helps ;)

answered Jul 07 '14 at 12:19

Todor Todorov

2,503
1
16
15

16

This does only work until Z, because `range('A', 'AB')` will just return one item named 'A'. So using `range()` in this case is NOT a good idea! – Michael Jul 23 '14 at 14:23
8

This works though: for ($i = 'A'; $i != $phpExcelObject->getActiveSheet()->getHighestColumn(); $i++) { $worksheet->getColumnDimension($i)->setAutoSize(TRUE); } – Nathan Apr 28 '16 at 19:44
Do not use this. Please see my answer that uses the `$sheet->getColumnIterator()` – BARNZ Sep 03 '20 at 22:09

score 20 · Answer 4 · edited Apr 28 '16 at 21:06

20

for ($i = 'A'; $i !=  $objPHPExcel->getActiveSheet()->getHighestColumn(); $i++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}

edited Apr 28 '16 at 21:06

Matt S

14,976
6
57
76

answered Apr 28 '16 at 19:46

Nathan

24,586
4
27
36

4

good answer but should be `$i <= $objPHPExcel->getActiveSheet()->getHighestColumn()` otherwise last column doesn't get sized – But those new buttons though.. Mar 23 '18 at 05:23

BARNZ · Answer 5 · 2021-09-03T23:44:33.697

20

Do not use range() it wont work beyond column Z.

Simply use:

$sheet = $spreadsheet->getActiveSheet();
foreach ($sheet->getColumnIterator() as $column) {
    $sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
}

Do this after you have written your data so that the column iterator knows how many columns to iterate over.

edited Sep 03 '21 at 23:44

answered Sep 03 '20 at 22:04

BARNZ

1,239
13
15

dmi3x · Answer 6 · 2014-08-04T11:06:30.187

11

This is example how to use all columns from worksheet:

$sheet = $PHPExcel->getActiveSheet();
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells( true );
/** @var PHPExcel_Cell $cell */
foreach( $cellIterator as $cell ) {
        $sheet->getColumnDimension( $cell->getColumn() )->setAutoSize( true );
}

edited Aug 04 '14 at 11:06

answered Aug 04 '14 at 11:00

dmi3x

121
1
3

3

Please add some context around your answer to explain how it solves the problem – Andrew Stubbs Aug 04 '14 at 11:04

score 10 · Answer 7 · edited Feb 19 '19 at 14:45

10

for phpspreadsheet:

$sheet = $spreadsheet->getActiveSheet(); // $spreadsheet is instance of PhpOffice\PhpSpreadsheet\Spreadsheet

foreach (
    range(
         1, 
         Coordinate::columnIndexFromString($sheet->getHighestColumn(1))
    ) as $column
) {
    $sheet
          ->getColumnDimension(Coordinate::stringFromColumnIndex($column))
          ->setAutoSize(true);
}

edited Feb 19 '19 at 14:45

long

3,692
1
22
38

answered Dec 04 '18 at 06:40

beck kuo

101
1
2

```foreach (range('A', $sheet->getHighestDataColumn()) as $column) {$sheet->getColumnDimension($column)->setAutoSize(true);}``` – Wesley Abbenhuis Jul 03 '19 at 06:52
3

@WesleyAbbenhuis your comment will only work when there are no more than 26 columns. When the highest column is for example: 'AH' the range function will not work properly. – Sander Van Keer Mar 23 '20 at 09:00

score 6 · Answer 8 · answered Sep 20 '16 at 11:58

This code snippet will auto size all the columns that contain data in all the sheets. There is no need to use the activeSheet getter and setter.

// In my case this line didn't make much of a difference
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
// Iterating all the sheets
/** @var PHPExcel_Worksheet $sheet */
foreach ($objPHPExcel->getAllSheets() as $sheet) {
    // Iterating through all the columns
    // The after Z column problem is solved by using numeric columns; thanks to the columnIndexFromString method
    for ($col = 0; $col <= PHPExcel_Cell::columnIndexFromString($sheet->getHighestDataColumn()); $col++) {
        $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
}

Gabi Dj · Answer 9 · 2017-11-04T18:26:38.023

In case somebody was looking for this.

The resolution below also works on PHPSpreadsheet, their new version of PHPExcel.

// assuming $spreadsheet is instance of PhpOffice\PhpSpreadsheet\Spreadsheet
// assuming $worksheet = $spreadsheet->getActiveSheet();
foreach(range('A',$worksheet->getHighestColumn()) as $column) {
    $spreadsheet->getColumnDimension($column)->setAutoSize(true);
}

Note: getHighestColumn() can be replaced with getHighestDataColumn() or the last actual column.

What these methods do:

getHighestColumn($row = null) - Get highest worksheet column.

getHighestDataColumn($row = null) - Get highest worksheet column that contains data.

getHighestRow($column = null) - Get highest worksheet row

getHighestDataRow($column = null) - Get highest worksheet row that contains data.

score 3 · Answer 10 · answered Nov 07 '19 at 15:17

// Auto-size columns for all worksheets
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    foreach ($worksheet->getColumnIterator() as $column) {
        $worksheet
            ->getColumnDimension($column->getColumnIndex())
            ->setAutoSize(true);
    } 
}

score 2 · Answer 11 · answered Jan 12 '15 at 10:52

2

foreach(range('B','G') as $columnID)
{
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

answered Jan 12 '15 at 10:52

Sadikhasan

18,365
21
80
122

score 2 · Answer 12 · answered Mar 18 '16 at 12:14

Come late, but after searching everywhere, I've created a solution that seems to be "the one".

Being known that there is a column iterator on last API versions, but not knowing how to atuoadjust the column object it self, basically I've created a loop to go from real first used column to real last used one.

Here it goes:

//Just before saving de Excel document, you do this:

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

//We get the util used space on worksheet. Change getActiveSheet to setActiveSheetIndex(0) to choose the sheet you want to autosize. Iterate thorugh'em if needed.
//We remove all digits from this string, which cames in a form of "A1:G24".
//Exploding via ":" to get a 2 position array being 0 fisrt used column and 1, the last used column.
$cols = explode(":", trim(preg_replace('/\d+/u', '', $objPHPExcel->getActiveSheet()->calculateWorksheetDimension())));

$col = $cols[0]; //first util column with data
$end = ++$cols[1]; //last util column with data +1, to use it inside the WHILE loop. Else, is not going to use last util range column.
while($col != $end){
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);

    $col++;
}

//Saving.
$objWriter->save('php://output');

score 2 · Answer 13 · answered Oct 04 '16 at 22:42

If you try to iterate with for ($col = 2; $col <= 'AC'; ++ $col){...}, or with foreach(range('A','AC') as $col) {...} it will work for columns from A to Z, but it fails pass the Z (Ex. iterate between 'A' to 'AC').

In order to iterate pass 'Z', you need to convert the column to integer, increment, compare, and get it as string again:

$MAX_COL = $sheet->getHighestDataColumn();
$MAX_COL_INDEX = PHPExcel_Cell::columnIndexFromString($MAX_COL);
    for($index=0 ; $index <= $MAX_COL_INDEX ; $index++){
    $col = PHPExcel_Cell::stringFromColumnIndex($index);

    // do something, like set the column width...
    $sheet->getColumnDimension($col)->setAutoSize(TRUE);
}

With this, you easy iterate pass the 'Z' column and set autosize to every column.

score 1 · Answer 14 · edited Jun 08 '16 at 07:56

1

you also need to identify the columns to set dimensions:

foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject
        ->getActiveSheet()
        ->getColumnDimension($col)
        ->setAutoSize(true);
}

edited Jun 08 '16 at 07:56

Shabbir Dhangot

8,954
10
58
80

answered Jun 08 '16 at 07:37

Bhavik Hirani

1,996
4
28
46

Prashant Khanderia · Answer 15 · 2016-08-31T11:04:36.763

0

$col = 'A';
while(true){
    $tempCol = $col++;
    $objPHPExcel->getActiveSheet()->getColumnDimension($tempCol)->setAutoSize(true);
    if($tempCol == $objPHPExcel->getActiveSheet()->getHighestDataColumn()){
        break;
    }
}

edited Aug 31 '16 at 11:04

answered Aug 31 '16 at 10:51

Prashant Khanderia

5
3

5

Please reformat your answer and think about this: A **good answer** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – B001ᛦ Aug 31 '16 at 10:56

score 0 · Answer 16 · edited Feb 22 '17 at 09:22

0

For Spreedsheet + PHP 7, you must write instead of PHPExcel_Cell::columnIndexFromString, \PhpOffice\PhpSpreadsheet\Cell::columnIndexFromString. And at the loop is a mistake, there you must < not work with <=. Otherwise, he takes a column too much into the loop.

edited Feb 22 '17 at 09:22

Peon

7,902
7
59
100

answered Oct 19 '16 at 10:37

GSI One

9
2

score 0 · Answer 17 · answered Jan 05 '22 at 15:55

Try this, i solved like this.

$Sheet = $excel->getActiveSheet();
$lABC1 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$lABC2 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

for($I = 0; $I < count($lABC1); $I++):
  $Sheet->getColumnDimension($lABC1[$I])->setAutoSize(true);
  for($J = 0; $J < 6; $J++){
    $Sheet->getColumnDimension($lABC2[$J].$lABC1[$I])->setAutoSize(true);
  }
endfor;

PHPExcel auto size column width

17 Answers17

Linked