Phpexcel Auto Size Column Width

PHPExcel auto size column width

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

Setting autosize column phpExcel

As described in section 4.6.28 of the developer documentation, entitled Setting a column's width:

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

This has to be set individually for each column, so to set it for all of columns A through F, use a loop

for($col = 'A'; $col !== 'G'; $col++) {
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension($col)
        ->setAutoSize(true);
}

phpExcel doesn't set column width auto

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... CSV files do not support any formatting, just data, so column width (which is formatting) cannot be applied when you write a CSV file

PHP SpreadSheet can't find the function to auto size column width

Don't use range(). It won't work when cells beyond Z.
Use instead

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

How to PHPExcel set auto-columns width

First potential problem may be that you're working with column letters.
PHP's incrementor operation will work with column letters, so if $i is 'A', then $i++ will give 'B', and if $i is 'Z' than $i++ will give 'AA'; but you can't use <= as your comparator, as 'AA' is <= 'Z' when executed as a straight comparison.

Instead of

for($i = $fromCol; $i <= $toCol; $i++) {

use

$toCol++;
for($i = $fromCol; $i !== $toCol; $i++) {

To add the 5% margin after calling $sheet->calculateColumnWidths() do:

for($i = $fromCol; $i !== $toCol; $i++) {
    $calculatedWidth = $sheet->getColumnDimension($i)->getWidth();
    $sheet->getColumnDimension($i)->setWidth((int) $calculatedWidth * 1.05);
}

PHPExcel column A:C wont auto size width

Found a solution, so I've put the merging part at the bottom of my code now, and right before I do that, I auto size all columns, and turn it off again, so the merging won't recalculate the width:

foreach(range('A','H') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
$objPHPExcel->getActiveSheet()->calculateColumnWidths();

// Set setAutoSize(false) so that the widths are not recalculated
foreach(range('A','H') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(false);
}
//Columns merging (HAS TO BE AT THE END ELSE AUTO SEIZE WONT WORK)
$objPHPExcel->getActiveSheet()->setTitle(substr($tsc_data[0],0,30))
->mergeCells('A'.$table_row.':A'.($table_row+3))
->mergeCells('B'.$table_row.':B'.($table_row+3))
->mergeCells('C'.$table_row.':C'.($table_row+3))
//merge area
->mergeCells('D'.$table_row.':H'.($table_row+2));

Setting width of spreadsheet cell using PHPExcel

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.

Autofit Columns in PHPExcel then add large title text without messing up widths

I found out how to do it myself by looking at another stackoverflow question (How to PHPExcel set auto-columns width).

If I call $sheet->calculateColumnWidths(); after the autosize, then the 'getwidth' call will return a valid width, and my code will work....

    // added line...
    $sheet->calculateColumnWidths();

    //original code...
    $titlecolwidth = $sheet->getColumnDimension('B')->getWidth();
    $sheet->getColumnDimension('B')->setAutoSize(false);
    $sheet->getColumnDimension('B')->setWidth($titlecolwidth);

---

Related Topics