Column width is automatically adjusted in PHPExcel.

Problem

I’m trying to make my spreadsheet’s columns auto-size. I’m writing the file and trying to resize all of my columns at the end.

// 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 code above isn’t working. The column size is not changed to fit the text.

UPDATE $objWriter = PHPExcel IOFactory::createWriter($objPHPExcel, ‘Excel5’); $objPHPExcel = PHPExcel IOFactory::createWriter($objPHPExcel, ‘Excel5’);

Asked by Alkis Kalogeris

Solution #1

When a column is set to AutoSize, PHPExcel attempts to calculate the column width based on the column’s calculated value (i.e., the result of any formulas) and any additional characters supplied by format masks such as thousand separators.

This is an estimated width by default: a more exact calculation approach based on using GD, which can also handle font style attributes like bold and italic, is available, but it has a significantly higher overhead, thus it is turned off by default. You can make the calculation more precise by 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 say who you’re utilizing as a writer.

However, in order to establish dimensions, you must first identify the columns:

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

A column ID is expected by $objPHPExcel->getActiveSheet()->getColumnDimension().

$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).

Answered by Mark Baker

Solution #2

If you need to accomplish it on numerous sheets, each with multiple columns, follow these steps to iterate through them all:

// 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 by MrUpsidown

Solution #3

Based on @Mark Baker’s post, here’s a more flexible variant:

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

I hope this was helpful 😉

Answered by Todor Todorov

Solution #4

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

Answered by Nathan

Solution #5

You can’t use range() after column Z since it won’t function.

Simply use:

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

This should be done after you’ve finished writing your data, so the column iterator understands how many columns to go over.

Answered by BARNZ

Post is based on https://stackoverflow.com/questions/16761897/phpexcel-auto-size-column-width