Search code examples
phpphpexcel

PHPExcel column A:C wont auto size width


So I have implemented this code, to generate my excel:

$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //Default = whole workbook center vertical
$tsc = array_keys($data);
for ($i=0; $i < count($tsc); $i++) {
    if ($i != 0) {
        $objPHPExcel->createSheet($i);
    }
    //Active sheet
    $objPHPExcel->setActiveSheetIndex($i);

    $tsc_data = explode(";", $tsc[$i]); //[0] = tsc name - [1] = tsc id
    //Columns merging
    $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))
    //Adding cell text
    ->SetCellValue('A'.$table_row.'', 'Sr. No.ddddddddddddddddddddddd')
    ->SetCellValue('B'.$table_row.'', 'Component namedddddddddddddddddddddddddddddddddddd')
    ->SetCellValue('C'.$table_row.'', 'Featuresddddddddddddddddddddddd')
    ->SetCellValue('D'.$table_row.'', $tsc_data[0])
    ->SetCellValue('D'.($table_row+3), 'HW Detailsddddddddddddddddddddddd')
    ->SetCellValue('E'.($table_row+3), 'Test Case ID/ Test Stepsddddddddddddddddddddddd')
    ->SetCellValue('F'.($table_row+3), 'Expectedddddddddddddddddddddddd')
    ->SetCellValue('G'.($table_row+3), 'Resultddddddddddddddddddddddd')
    ->SetCellValue('H'.($table_row+3), 'Remarkddddddddddddddddddddddd');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$table_row.':H'.($table_row+3))->applyFromArray($bold_text_12);
    foreach(range('A','H') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
            ->setAutoSize(true);
    }
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('php://output');

And as you can see I do have the part which auto size the columns

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

This works but only for columns D:H not A:C, and I Dont know why?

You can see here that the columns have auto sized when I open the xml file, but not for A:C

enter image description here

Can any of you see what is blocking this, because to me it should work, but dosn't?

EDIT

I can see that the merging of A:C makes the auto sizeing stop, so if i remove them, the lines does auto size again...

//These lines
->mergeCells('A'.$table_row.':A'.($table_row+3))
->mergeCells('B'.$table_row.':B'.($table_row+3))
->mergeCells('C'.$table_row.':C'.($table_row+3))
//Do I insert the text into the cells right????
->SetCellValue('A'.$table_row.'', 'Sr. No.ddddddddddddddddddddddd')
->SetCellValue('B'.$table_row.'', 'Component namedddddddddddddddddddddddddddddddddddd')
->SetCellValue('C'.$table_row.'', 'Featuresddddddddddddddddddddddd')

NOTE: $table_row is default = 1, so 'A'.$table_row.':A'.($table_row+3) is A1:A4


Solution

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