AutoSize column in a loop

Topics: Developer Forum, Project Management Forum, User Forum
Apr 9, 2010 at 9:56 PM

Is there anyway to AutoSize the columns in a loop?  I have some code below of what I was attempting.  Version 3 is the code that works.  I think that either Version 1 or 2 would be a good alternative.  Are you not able to put php vars or functions in the PHPExcel code?

I am trying to make a flexible exporter that can handle a variety of data, and was attempting to make a loop.  My only other option might be to run AutoSize on like 15 columns and it would just be a little extra work.

I am running Windows XP, PHP v5.2.12, and PHPExcel v1.7.2.

// Version 1
for ($col = 'A'; col <= 'I'; $i++) {
	$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}
// Version 2
for ($i = 0; $i <= 9; $i++) {
	$objPHPExcel->getActiveSheet()->getColumnDimension(chr(65+$i))->setAutoSize(true);
}
// Version 3
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

Coordinator
Apr 10, 2010 at 12:14 PM
heavenswrath19 wrote:

Is there anyway to AutoSize the columns in a loop?  I have some code below of what I was attempting.  Version 3 is the code that works.  I think that either Version 1 or 2 would be a good alternative.  Are you not able to put php vars or functions in the PHPExcel code?

Any of your three methods should work, though you may have some issues with the end test for your character incrementor.

I prefer to use for ($col = 'A'; $col != 'J'; $i++) which should work even when you have more than 26 columns. I assume you do have $col in your real code rather than col.

PHPExcel is no different to any standard PHP: values passed to a function or method can be variables, constants, quoted strings, etc. Only methods/functions that require parameters passed by reference preclude the use of constants or quoted strings. I'll run some tests with all three methods, but I'd expect any of the three to work.

Apr 12, 2010 at 7:30 PM
MarkBaker wrote:
heavenswrath19 wrote:

Is there anyway to AutoSize the columns in a loop?  I have some code below of what I was attempting.  Version 3 is the code that works.  I think that either Version 1 or 2 would be a good alternative.  Are you not able to put php vars or functions in the PHPExcel code?

Any of your three methods should work, though you may have some issues with the end test for your character incrementor.

I prefer to use for ($col = 'A'; $col != 'J'; $i++) which should work even when you have more than 26 columns. I assume you do have $col in your real code rather than col.

PHPExcel is no different to any standard PHP: values passed to a function or method can be variables, constants, quoted strings, etc. Only methods/functions that require parameters passed by reference preclude the use of constants or quoted strings. I'll run some tests with all three methods, but I'd expect any of the three to work.

Mark,

Thanks for the response, but I still seem to be struggling.  I did have the code correct in my code, but it still won't work.  Versions 1 and 2 both do not work on my example, even though Version 3 works fine.  I experience a long "loading" period and then nothing outputs with the first two versions, versus a short load time and the browser asks where I want to save my file.  I'm assuming it is creating an error, but I cannot figure it out.

// Version 4
for ($col = 'A'; $col != 'J'; $i++) {
	$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}
The code you referred to is not working for me either (Version 4).  It creates the same problems as Versions 1 and 2 above.  I am a Java programmer at heart, so I might be misunderstanding PHP somewhere, but I don't know what is causing the problem.  Could it be a problem with my PHP or PHPExcel versions?

Apr 12, 2010 at 8:34 PM

Nevermind, I wrote out Version 4 incorrectly in my code.

// Version 4 fixed
for ($col = 'A'; $col != 'J'; $col++) { $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); }
This works. Thanks again, Mark.  I kept changing it so much, I did not keep track of all of the changes.  To make it change then, I will simply change 'J' into a variable and have it be affected by the number of columns (or fields since I am using MYSQL).  So I guess just Version 2 doesn't work, which must be something related to the chr function I am guessing.