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);

Autosize, on the other hand, does not apply to all Writer formats, such as 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().

Unless a column dimension record has been deliberately created (for example, by loading a template or manually calling getColumnDimension()), $objPHPExcel->getActiveSheet()->getColumnDimensions() will return an array of all the defined column dimension records (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

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

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