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

phpexcel

I am creating a spreadsheet using PHPExcel. I want to add the data, then set the column widths with setAutoSize(true)then I want to add a title to the page that is inevitably larger than its column. The problem I'm having is that the column autosizes to the width of the title, even if I add it after the setAutoSize(true) call.

I've tried setting autosize back to false for that column, that just reverts the column back to its default width. I've tried setting auto size to true, getting the column's width, then set auto size to false, then set the column's width. This just sets the width to the aforementioned default. Below is a snippet of my code…

    while($row = $this->getRow()){

        ++$currentrow;
        for($i = 0;$i < count($row); $i++){
                $sheet->setCellValueByColumnAndRow($i + 1, $currentrow,$row[$i]);
        }
    }

    // now that we have put all the data in the spreadsheet, auto fit the columns...

    for($i = 1;$i <= $this->columnCount; $i++){

        // this bit converts an integer into an excel column (such as 2 = 'B' or 28 = 'AB')
        if($i + 64 > 90){
            $col = "A" . chr($i + 38);
        }else{
            $col = chr($i + 64);
        }

        $sheet->getColumnDimension($col)->setAutoSize(true);  
        /* this is causing the column containing the title to autosize
        to the title's width after the title is added further down the code.*/

    }

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

    // Add the heading...

    if(isset($heading)){    
        $sheet->setCellValueByColumnAndRow(1, 2,$heading);  
        $sheet->getCellByColumnAndRow(1,2)->getStyle()->getFont()->setSize(20);
    }

When creating spreadsheets manually in excel, this type of thing is easy to acheive – add the data, perform autofit columns, then add the title. The column containing the title stays at its previously set width (before the title was added)

Is there a way to acheive the same using phpexcel?

I did have a look through the documentation, but couldn't find something that autofits the column but then leaves it alone once the fit has been performed.

Best Answer

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 Topic