When using PHPExcel, why is the autosize stretching my image?

Question

I am creating a spreadsheet using the PHPExcel Class. I have several columns of data. I am using the autosize method in order to have them take the size of the longest data value in each column.

EDIT - Here is my full code to show more details:

require_once '../../Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

// Adding values for Header Row
$objPHPExcel->getActiveSheet()
            ->setCellValue('A3', '#')
            ->setCellValue('B3', 'Name')
            ->setCellValue('C3', 'Email')
            ->setCellValue('D3', 'Phone')
            ->setCellValue('E3', 'Address')
            ->setCellValue('F3', 'Date Registered');

//Setting column values to the Listers Spreadsheet  
$counter = 4;
$entries_num = 1;          
//while($listers_export_row = $listers_export_result->fetch_assoc()) {
while($listers_export_row = $listers_export_result->fetch(PDO::FETCH_ASSOC)) {
    //Adding Numbers to Listers Spreadsheet (Column A)
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$counter, $entries_num);

    //Adding Names to Listers Spreadsheet (Column B)
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$counter, $listers_export_row['first_name'].' '.$listers_export_row['last_name']);

    //Adding Email to Listers Spreadsheet (Column C) 
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$counter, $listers_export_row['email']);

    //Adding Phone to Listers Spreadsheet (Column D)
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$counter, '('.$listers_export_row['phone_prefix'].')'.$listers_export_row['phone_first'].'-'.$listers_export_row['phone_last']);

    //Adding Address to Listers Spreadsheet (Column E) 
    $address = $listers_export_row['address'];
        if($listers_export_row['apt'] != '') { $address .= ' '.$listers_export_row['apt']; }
    $address .= ', '.$listers_export_row['city'].', '.$listers_export_row['state'].' '.$listers_export_row['zip_1'];

    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$counter, $address);

    //Adding Date Registered to Listers Spreadsheet (Column F)  
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$counter, date('m/d/y',$listers_export_row['date_created']));

    $counter++;
    $entries_num++;
}

// Forces the spreadsheet to take the size of the longest value             
for ($col = 'A'; $col != 'G'; $col++) { //Runs through all cells between A and E and sets to autosize
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}

// Add a drawing to the worksheet

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setResizeProportional(false);
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('../images/crl_logo.png');
$objDrawing->setHeight(35);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Accounts - '.date('m.d.y',time()).'.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

This is what is occuring (It should only be as wide as the first column): enter image description here

I have removed the autosize method and it takes the correct size, so I know it is affecting the size of the image.

How can I remove the autosize from the image but leave it applied for the columns of data?

Please provide a bit more detail, e.g. which Writer? Is it the same for all writers? What are the precise details of image placement? (you're showing name, description, path and worksheet, but not showing any values for coordinates, height, width, offsets, etc) or are these being left as defaults? — Mark Baker, Aug 03 '13 at 08:14
Thanks for the reply Mark. I am using the Excel5 writer. I just tested it with Excel2007 and that works fine. Do you have any idea why it wouldn't work on Excel5? — zeckdude, Aug 04 '13 at 02:01
You might find out more by using a step debugger. Perhaps the Excel5 writer is ignoring that proportional property or Excel5 uses different codes? As this is open source, I'd say based on your discovery that a specific writer seems to ignore the setting, using let's say xdebug might help to reveal more information more quickly which might even lead to a fix in case this is a flaw or a missing feature so far for that writer. — hakre, Aug 06 '13 at 09:09

score 6 · Answer 1 · answered Mar 10 '16 at 11:34

6

I had the same problem, and I've found a workaround. Just before rendering document, manually init calculation, read adjusted width of the column, and set it manually.

$sheet = $this->phpExcelObject->getActiveSheet();
$sheet->calculateColumnWidths();
$columnDimension = $sheet->getColumnDimension('B');
$columnDimension->setAutoSize(false)->setWidth($columnDimension->getWidth());

answered Mar 10 '16 at 11:34

rolacja

2,381
1
13
12

1

This really worked. Should be marked as the correct answer. – Marco Mannes Nov 21 '19 at 19:03

score 1 · Answer 2 · answered Aug 05 '13 at 12:53

have you tried to set fixed image width.

Or to set the Offset of the image to set the correct position.

PHPExcel_Worksheet_BaseDrawing::setOffsetX()
PHPExcel_Worksheet_BaseDrawing::setOffsetY()

more details see: http://www.cmsws.com/examples/applications/phpexcel/Documentation/API/PHPExcel_Worksheet/PHPExcel_Worksheet_Drawing.html

score 0 · Answer 3 · answered Aug 23 '19 at 09:10

Had the same issue in PHPSpreadsheet when creating files using writerType xls, the images would automatically stretch as soon as you set the cells to autosize. No luck with common suggestions like setResizeProportional, setWidthAndHeight, setOffsetX, etc.

Changing it to xslx (Excel2007) immediately solved the image problem. However, when opening the generated file, an error message would appear:

We found a problem with some content in <filename>.xlsx. Do you want us to try and 
recover as much as we can? If you trust the source of this workbook, click Yes.

As answered by alex over at PhpSpreadsheet is corrupting files, this can be solved by adding a die after calling save, such as:

...
$writer->save('php://output');
die;

Hopefully this will help anyone running into the same issue.

When using PHPExcel, why is the autosize stretching my image?

3 Answers3