Setting autosize column phpExcel

Question

How do I make PHPExcel create the column width automatically I don't like having to go in by hand and stretch the columns witdth. I have looked at other examples but none work for me. Here is my code:

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Company Name");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Company Type");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "First Name");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "Last Name");
$objPHPExcel->getActiveSheet()->setCellValue('E1', "Position");
$objPHPExcel->getActiveSheet()->setCellValue('F1', "Email");

// Set outline levels

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1)
                                                       ->setVisible(false)
                                                       ->setCollapsed(true);

// Freeze panes

$objPHPExcel->getActiveSheet()->freezePane('A2');


// Rows to repeat at top

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);




try {

    $stmt3 = $DB->prepare('SELECT * FROM companies C INNER JOIN personalInfo PI ON C.CompanyName = PI.Company_id');
    $stmt3->execute();


} catch(PDOException $e) {  
    echo $e->getMessage();  
}

$info3 = $stmt3->fetchAll();


$i = 2;
foreach($info3 as $info) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $info['CompanyName'])
                                  ->setCellValue('B' . $i, $info['CompanyType'])
                                  ->setCellValue('C' . $i, $info['firstName'])
                                  ->setCellValue('D' . $i, $info['lastName'])
                                  ->setCellValue('E' . $i, $info['position'])
                                  ->setCellValue('F' . $i, $info['email']);
    $i++;
}


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file

$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo "Excel file has been created click <a href='Excel.xlsx'>HERE</a> to view it.";

Mark Baker · Accepted Answer · 2014-05-26T13:25:41.567

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

score 20 · Answer 2 · answered Jan 22 '14 at 15:07

20

    $nCols = 6; //set the number of columns

    foreach (range(0, $nCols) as $col) {
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);                
    }

answered Jan 22 '14 at 15:07

dap.tci

2,455
1
20
18

3

I think this answer is better because it handles column ranges that are greater than Z (e.g AB, etc.) - thanks! – Craig Nakamoto Jul 20 '15 at 15:44
@CraigNakamoto - My answer also handles column ranges greater than Z `for($col = 'A'; $col !== 'AC'; $col++) {` will set autosize for all columns from `A` to `AB` inclusive – Mark Baker Mar 07 '16 at 14:09
From my point of view, I think this answer is more natural because it iterates columns as numbers like rows do. Thanks @dap.tci – InfZero Jul 29 '16 at 21:41
this is actually correct answer for me when it come to dynamic because you can determine the number of your column header but you cannot determine data row, thanks is life saver men – Michael Mar 20 '17 at 23:35

score 0 · Answer 3 · answered Jan 12 '15 at 10:51

0

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

answered Jan 12 '15 at 10:51

Sadikhasan

18,365
21
80
122

Setting autosize column phpExcel

3 Answers3