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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.