setAutoSize comes into effect too late

Topics: Developer Forum, User Forum
Jul 15, 2011 at 10:45 PM
Edited Jul 21, 2011 at 10:51 AM

Hello everybody,

I'm having a problem with PHPExcel, which is in general a great piece of software. Consider the following example:

$objPHPExcel2->getActiveSheet()->setCellValue("A2", "3");
$objPHPExcel2->getActiveSheet()->setCellValue("B2", "456");
$objPHPExcel2->getActiveSheet()->setCellValue("A3", "34");
$objPHPExcel2->getActiveSheet()->setCellValue("B3", "56");
$objPHPExcel2->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
$objPHPExcel2->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
$objPHPExcel2->getActiveSheet()->setCellValue("A1", "A quite long string");
$objPHPExcel2->getActiveSheet()->setCellValue("B1", $objPHPExcel2->getActiveSheet()->getColumnDimension("A")->getWidth());
$objWriter2 = PHPExcel_IOFactory::createWriter($objPHPExcel2, "Excel2007");
$objWriter2->save($tempFileName);

There are two things that don't work as I expect them to work, perhaps I'm doing something wrong.

  1. In the resulting file column A should have a width corresponding to string "34", because that was the widest string in this column at time when setAutoSize was executed. Instead the column is as wide as a string "A quite long string".
  2. When I try to "getWidth" of column A, the result I get is -1, while I'd expect a value that is an effect of autosizing.

Is there a way to workaround these unexpected results? I'm using PHPExcel 1.7.6.

Jul 21, 2011 at 12:21 PM

OK,

I made some further tests, and I've found a workaround (although I still consider the behaviour described above a BUG).

Before inserting a "very long string", save the file and reload it. This will cause the "setAutoSize" to come into effect (i.e. column widths are computed and saved). After opening the file, the getWidth() method starts to work (returns a real value, not -1).

One additional bug I've found. My code does:

$objPHPExcel->getActiveSheet()->setTitle('A fancy sheet title');

and a file with such a title is successfully written, but not read:

Uncaught exception 'Exception' with message 'Invalid cell coordinate 'A FANCY SHEET TITLE'!A2'

 

Another bug: when setting setAutoSize(true); PHPExcel doesn't take into account an autofilter and its buttons that also take some width, thus making cells with autofilter too narrow. This can be easily worked-around once the above workaround is in place.

Any chance to have the three above bugs fixed?

Jul 26, 2012 at 3:29 AM

i have the same problem about setAutoSize()

 

if  getColumnDimension("A")->setAutoSize(true);

the cell"A1" didn't AutoSize

Coordinator
Jul 26, 2012 at 6:50 AM

Autosize is a very costly operation, especially if you've set exact mode, so it is only performed when absolutely necessary (ie when you actually save the file): setting setAutoSize(true) is merely telling PHPExcel that it should perform this calculation when the the file is saved. If you really want, you can force PHPExcel to calculate at any time after setting the setAutoSize(true) flags using the worksheet objects calculateColumnWidths() method.

You're right that autofilter is not taken into account.

 

How fancy is your worksheet title? It works with a heck of a lot, and 'A fancy sheet title' should not cause any problems. Please provide a working example  of a sheet title that causes thsi problem