Image size/aspect ratio saved incorrectly

Topics: Developer Forum, User Forum
Jul 22, 2009 at 12:41 AM

Hi,

I am having a problem with images that get modified in size as I save to a file.  And I can't figure out why it's doing it or how to make the writer to stop re-scaling automatically.

Basically, I have a banner image (60 x 600 pixels) in gif format and created a template file in .xlsx format.  Then I have a separate script to load the file and save it as .xls format.  When I open the template file in .xlsx format using MS EXcel 2007, the image looked fine and looking at the properties of the image showed as follows:

Height: 0.63", Width: 6.25",  Scale Height: 100%, Scale Width: 100%

However, the image in .xls file looked fuzzy.  the properties of the image showed as follows:

Height: 0.62", Width: 6.24",  Scale Height: 98%, Scale Width: 100%

 

Changing the default cell size or the default font style and size also appear to affect the image size as well.  I'd appreciate if you could share some hints or suggestions dealing with this problem.

Thanks!

 

Developer
Jul 22, 2009 at 6:20 AM

Yes, I recognize the problem you are describing. Image scaling is difficult with xls files. The difficulty arises in this file format, because what is stored is the upper-left and bottom-right position of the image. From these numbers the scaling is derived. This is not very practical when one wants to create a 3rd party xls reader/writer because these will then have to know how to calculate the exact pixel width and height of each cell in the grid (not easy).

I will do some more testing and see if I can improve the Excel5 reader/writer so image scaling becomes more accurate, perhaps at the end of this week. Will post back here.

Jul 23, 2009 at 4:06 PM

Thank you for your response, Koyama-san.

I now understand the reason why it is doing the re-scaling.  Does knowing actual image size helps solving this issue easier?  If so, PHPExcel could read it off of the image itself.  I understand there are so many different image formats out there, but just a thought.

For example:

This is the first 10 bytes of my banner image in gif format, size: 600 x 60

 

47 49 46 38 39 61 58 02 3c 00 ....

 

And here is the detail of the 10 bytes:

47 49 46 38 39 61  ==> GIF89a (first 6 bytes)

58 02  ==> 0x258 = 600 (next 2 bytes)

3c 00  ==> 0x3c = 60 (next 2 bytes)

 

Thanks!

Developer
Jul 25, 2009 at 3:40 AM

>> Does knowing actual image size helps solving this issue easier?

Well, that part is not really the problem. One can do like you are showing, or one can use the PHP function getimagesize() to retrieve image pixel size. The tricky part is finding out the width and height of the cells in the worksheet.

I did some minor adjustments. Image scale should be working ok with Excel5 writer as long as one uses Calibri 11 as the default font for the workbook. Later we should make sure that image scaling works correctly also for other default fonts, but as mentioned it is tricky.

Sep 2, 2009 at 5:51 PM

I've just encountered this problem.

Images are being stretched on the horizonal x-axis.

I tried explicitly defining the size $objDrawing->setHeight(50); $objDrawing->setWidth(100); but this didn't have any effect.

As a temporary kludge I shrunk the width of the original image so the final result looks sort of ok.

If you compare the "ouput image width" to the "input imagewidth" and work out the % stretch is it possible to factor this in to your scaling?

Thanks

Developer
Sep 2, 2009 at 6:04 PM

@sparkyhd: Are you using the latest source code? There could be improvements in latest source code compared to PHPExcel 1.7.0. Do you have a sample file showing the problem? I think this may get fixed soon. We have some improved algorithms for determining column width that just have to be checked into the source code.

If you upload the Excel file somewhere or send email to me I can check if it will get better: erik at phpexcel dot net

Sep 2, 2009 at 8:40 PM
Edited Sep 2, 2009 at 8:53 PM

Yes 1.7.0

See http://www.top5000contacts.com/includes/phpexcel/Tests/sample2.php

and the image http://www.top5000contacts.com/includes/phpexcel/images/top5000-logo-2.jpg

Regards

 

Developer
Sep 2, 2009 at 8:49 PM

Broken links?

Sep 2, 2009 at 8:53 PM

Sorry - local pc instead of server - I've edited the links above

 

Developer
Sep 2, 2009 at 9:11 PM
Edited Sep 2, 2009 at 9:11 PM

I see the problem now. It was what I expected. PHPExcel_Writer_Excel5 does not yet deal perfectly with the full range of font sizes when positioning images. For example you have used Arial 8 as default workbook font. Arial 10, or Calibri 11 might give better results.

Anyway, since it is a bug in PHPExcel it should be fixed. I will see how I can tweak it, but it requires a little work. Perhaps I can look at this in the weekend. Will post back here.

Developer
Sep 8, 2009 at 2:44 AM

>> Images are being stretched on the horizonal x-axis.

@sparkyhd: If you try with source code release tomorrow from here:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

some of this should have been fixed here:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10599

Sep 9, 2009 at 8:14 AM

Hi Koyama - I've tried the new version but don't think it's made any difference?

Developer
Sep 9, 2009 at 12:40 PM

>> Hi Koyama - I've tried the new version but don't think it's made any difference?

It's squeezed in the vertical direction, but this bug with horizontal squeezing disappeared, right?

I will see if I can solve the bug with vertical squeezing at the end of this week. Will post back here.

Developer
Sep 19, 2009 at 1:19 AM

Vertical squeeze/stretch of image should be fixed:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10661

Download latest source code tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Sep 23, 2009 at 11:05 AM
Edited Sep 23, 2009 at 11:07 AM

Hi Koyama

Vertical squeeze is correct but still horizontally strecthing (which give same end result as vertical squeeze)

See http://www.top5000contacts.com/images/stretch.jpg

Regards

Developer
Sep 23, 2009 at 12:58 PM

@sparkyhd:

Ah ok, I see the problem now. PHPExcel is using an approximation to calculate the pixel column width for columns with an explicitly set width. In work item 10599 I had only considered columns with no explicit width. Will see if I can get this solved later this week. Will post back here.

Developer
Sep 24, 2009 at 12:09 AM

Should be fixed in tomorrows source code:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Sep 25, 2009 at 4:49 PM

Perfect!

Thanks for your persistence.

Regards

Oct 23, 2009 at 6:16 PM

Hi,

I was excited to try out the 1.7.0 to see the better image aspect ratio saved as excel 5 format.

Sadly, the result I see with this new version is worse than before and stretched even more.  I may be doing something wrong and I'd appreciate if you could take a look at my code snipet below.  I'd appreciate any suggestions you can think of to fix or ease this stretching problem. Thank you.  :-)

I have a gif image which size is 60(h) x 574(w), 96 dpi, 8 bit color depth.

 

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

// Set default font for the worksheet
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Test Invoice');

// Add a drawing to the worksheet
echo date('H:i:s') . " Add a drawing to the worksheet\n";
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Test Logo');
$objDrawing->setDescription('Test Logo');
$objDrawing->setPath('test_invoice.gif');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
exit("Done");

 

Developer
Oct 23, 2009 at 11:03 PM

>> I was excited to try out the 1.7.0 to see the better image aspect ratio saved as excel 5 format.

You will need to use latest source code:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Can you try that?

Oct 26, 2009 at 9:41 PM

Thanks for the suggestion.

I have been trying to download the latest revision, but it kept stalling on me.  :-(  As soon as  I successfully download it, I'll give it a shot and will post the result.

 

 

Oct 27, 2009 at 4:34 PM

Hi, Koyama-san,

I've managed to download the latest code (29173) successfully.  And indeed the banner looks good now.

Thank you!

 

Oct 28, 2009 at 7:16 AM

Hi,koyama:

       I have a problem with images that the size will change when you modify the cell size. I can't find a propertie to protect the image size. But there have been a propertie for this in open office and microsoft office, could you add this in phpexcel?

      Thanks!

 

Developer
Oct 29, 2009 at 3:31 AM
kevingan wrote:

Hi,koyama:

       I have a problem with images that the size will change when you modify the cell size. I can't find a propertie to protect the image size. But there have been a propertie for this in open office and microsoft office, could you add this in phpexcel?

      Thanks!

For a start Excel5 writer has just been fixed so it behaves like "Move but don't size with cells" like one would expect.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10849

It sound like this is what you want. If we need an explicit method to control this property we can perhaps look at this later.

Nov 2, 2009 at 4:22 PM
Edited Nov 2, 2009 at 4:27 PM
koyama wrote:

>> I was excited to try out the 1.7.0 to see the better image aspect ratio saved as excel 5 format.

You will need to use latest source code:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Can you try that?

Hi Koyama,

I have just tried build 29234, and still seem to have the image size problem...it is out by a few millimetres, but enough to make it blurry in Excel.
I have uploaded the Excel file here, if it will help: http://stashbox.org/682807/1_Water_Level_.xls

The MNS problem I had has been resolved though :)

Thanks

Developer
Nov 2, 2009 at 4:38 PM

@gridder: I looked at the file you linked to and I see that the image is blurred.

To figure out what the problem is, can you say how this file was created? Was this file created using an xls template, i.e. a read/write of an existing xls file, or did you insert the image using PHPExcel methods? If you can upload the template xls file and/or link to the original, unblurred image, then we can look for a solution.

Nov 3, 2009 at 9:03 AM

Thanks for having a look.  The file was fully created with PHPExcel, using the $excel->getActiveSheet()->setCellValueByColumnAndRow() function.  I have uploaded the image here: http://stashbox.org/683776/water_level.png

The code used to insert the image is:

 

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Water_Level');
$objDrawing->setDescription('Water_Level');
$objDrawing->setPath(''.$_SESSION['graph_temp_path'].'/water_level.png');
$objDrawing->setHeight(450);
$objDrawing->setCoordinates('A'.$rowindex);
$objDrawing->setWorksheet($excel->getActiveSheet());

 

Thanks again.

Developer
Nov 4, 2009 at 3:32 AM

@gridder: Bug was identified:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10877

Download latest source code from here tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Nov 4, 2009 at 10:40 AM

Great work!  Its working perfectly now.  Thanks.

Aug 30, 2010 at 3:53 PM
Edited Aug 30, 2010 at 3:56 PM

Hi,

Could it be that this bug was reintroduced in version 1.7.4? I was using version 1.7.3c and my images in Excel (logo) looked good. I upgraded to 1.7.4 and the image is now stretched to cell width?

This is the code I use to include the image:

 

if(is_file('imagepath')) {
  $objDrawing = new PHPExcel_Worksheet_Drawing();
  $objDrawing->setName($author . ' Logo');
  $objDrawing->setDescription($author . ' Logo');
  $objDrawing->setPath('imagepath');
  $objDrawing->setCoordinates('A1');
  $objDrawing->getShadow()->setVisible(true);
  $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
}

Edit: I'm saving the file as an Excel 2003 file (.xls)

I downgraded back to 1.7.3c in the meantime and the image is shown in correct aspect ratio's.

Any ideas?

Coordinator
Sep 1, 2010 at 1:20 PM
UwICTPartner wrote:

Could it be that this bug was reintroduced in version 1.7.4? I was using version 1.7.3c and my images in Excel (logo) looked good. I upgraded to 1.7.4 and the image is now stretched to cell width?

 downgraded back to 1.7.3c in the meantime and the image is shown in correct aspect ratio's.

Any ideas?

 No ideas at the moment, but it is possible. I'll see if I can identify the problem.

Jan 17, 2011 at 4:16 PM

@Developer Team

I came across the same problem. In comparing the version 1.7.3c and 1.7.4, I noticed that something is missing in the code. I have added it to version 1.7.5 and the problem was fixed. I have only change this line:

$escher = new PHPExcel_Shared_Escher(); to $escher = $this->_escher;


File: Classes\PHPExcel\Writer\Excel5\Worksheet.php function _writeMsoDrawing()


// create intermediate Escher object
$escher = new PHPExcel_Shared_Escher();

// dgContainer
$dgContainer = new PHPExcel_Shared_Escher_DgContainer();

// set the drawing index (we use sheet index + 1)
$dgContainer->setDgId($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1);
$escher->setDgContainer($dgContainer);

// spgrContainer
$spgrContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer();
$dgContainer->setSpgrContainer($spgrContainer);

// add one shape which is the group shape
$spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
$spContainer->setSpgr(true);
$spContainer->setSpType(0);
$spContainer->setSpId(($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1) << 10);
$spgrContainer->addChild($spContainer);

// add the shapes

// outer loop is for determining BSE index
$blipIndex = 0; // 1-based index to BstoreContainer

$countShapes = 0; // count number of shapes (minus group shape), in this sheet

foreach ($this->_phpSheet->getParent()->getAllsheets() as $sheet) {
	foreach ($sheet->getDrawingCollection() as $drawing) {
		++$blipIndex;

		if ($sheet === $this->_phpSheet) {
			++$countShapes;

			// add the shape
			$spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();

			// set the shape type
			$spContainer->setSpType(0x004B);

			// set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
			$spId = $countShapes
				| ($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1) << 10;
			$spContainer->setSpId($spId);

			// keep track of last spId
			$lastSpId = $spId;

			// set the BLIP index
			$spContainer->setOPT(0x4104, $blipIndex);

			// set coordinates and offsets, client anchor
			$coordinates = $drawing->getCoordinates();
			$offsetX = $drawing->getOffsetX();
			$offsetY = $drawing->getOffsetY();
			$width = $drawing->getWidth();
			$height = $drawing->getHeight();

			$twoAnchor = PHPExcel_Shared_Excel5::oneAnchor2twoAnchor($this->_phpSheet, $coordinates, $offsetX, $offsetY, $width, $height);

			$spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
			$spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
			$spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
			$spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
			$spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
			$spContainer->setEndOffsetY($twoAnchor['endOffsetY']);

			$spgrContainer->addChild($spContainer);
		}
	}
}

// set last shape index
$dgContainer->setLastSpId($lastSpId);


Apr 14, 2011 at 11:18 AM

Seems like this issue occurs in PHPExcel 1.7.5 and 1.7.6, my image is incorrectly resized when generating an Excel 5 worksheet.

Jun 11, 2011 at 2:43 AM

I had the same issue in 1.7.6 . When using Excel5 writer, images are a bit wider than expected. Any updates on this bug would be helpful.

Thanks!

Jun 13, 2011 at 7:49 PM

Guys, would really appreciate at least status on this issue.  Would be nice to know, if there are any workarounds that we can do in order to show images correctly and/or expected timeline to fix this issue. 

Thanks again in advance for the help.

Jun 14, 2011 at 7:46 AM

@revdev

to fix is

Change this line:

// create intermediate Escher object
$escher = new PHPExcel_Shared_Escher();

to:

// create intermediate Escher object
$escher = $this->_escher;

in File: Classes\PHPExcel\Writer\Excel5\Worksheet.php -> Function: _writeMsoDrawing()

Dec 14, 2011 at 6:48 PM

I had the same isssue of bad ratio.

I solved my issue simply in changing the output from save to 2007 file to output to browser directly !