AutoSize columns

Apr 2, 2010 at 6:56 AM

Hello,

got strange behaviour of autosize property in excel 2007 (WIN XP).

 

Later i was using office xp, but need to see more than 65k rows in Excel, so switched to 2007 version.

Problem is:

If i generate excel file with Excel5 writer, AutoSize property seems to be ignored, so when i open downloaded file, column widths are not correct. Was fine in excel xp (office xp). The width was okey and bit wider than widest column content. But in 2007 it is not, widths are incorrect.

If i generate excel file with Excel2007 writer i get "corrupt content & recovery" message.

I'm using php 5.2.13 version of dot.deb for lenny debian.

According to phpinfo():

zip

Zip enabled
Extension Version $Id: php_zip.c 293036 2010-01-03 09:23:27Z sebastian $
Zip version 1.8.11
Libzip version 0.9.0

 

zlib

ZLib Support enabled
Stream Wrapper support compress.zlib://
Stream Filter support zlib.inflate, zlib.deflate
Compiled Version 1.2.1.1
Linked Version 1.2.3.3

My code :

 

	require_once '../lib/Classes/PHPExcel.php';
	require_once '../lib/Classes/PHPExcel/IOFactory.php';
	
	$objPHPExcel = new PHPExcel();
		
	
	$objPHPExcel->getActiveSheet(0)->setCellValue('A1', 'date');
	$objPHPExcel->getActiveSheet(0)->setCellValue('B1', 'number');
	$objPHPExcel->getActiveSheet(0)->setCellValue('C1', 'num');
	$objPHPExcel->getActiveSheet(0)->setCellValue('D1', 'make');
	$objPHPExcel->getActiveSheet(0)->setCellValue('E1', 'me');
	$objPHPExcel->getActiveSheet(0)->setCellValue('F1', 'quantity');
	$objPHPExcel->getActiveSheet(0)->setCellValue('G1', 'what');
	$objPHPExcel->getActiveSheet(0)->setCellValue('H1', 'user');

	
	$i = 2;
	
	while ($excl = mysql_fetch_assoc($excel)){
	$objPHPExcel->getActiveSheet(0)->setCellValue('A'.$i, $date['when']);
		
	$objPHPExcel->getActiveSheet(0)->setCellValue('B'.$i, $excl['number']);
	$objPHPExcel->getActiveSheet(0)->setCellValue('C'.$i, 'NUM');
	$objPHPExcel->getActiveSheet(0)->setCellValue('D'.$i, $excl['make']);
		
	$objPHPExcel->getActiveSheet(0)->getCell('E'.$i)->setValueExplicit($excl['me'], PHPExcel_Cell_DataType::TYPE_STRING);
	
		
	$objPHPExcel->getActiveSheet(0)->setCellValue('F'.$i, $excl['quant']);
		
	$objPHPExcel->getActiveSheet(0)->setCellValue('G'.$i, $excl['what']);
	$objPHPExcel->getActiveSheet(0)->setCellValue('H'.$i, $excl['user']);
		
	
	$i++;
	}
	
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('G')->setAutoSize(true);
	$objPHPExcel->getActiveSheet(0)->getColumnDimension('H')->setAutoSize(true);		
			
$objPHPExcel->setActiveSheetIndex(0);
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
	header('Content-Disposition: attachment;filename="myfile.xlsx"');
	header('Cache-Control: max-age=0');
	header("Content-Transfer-Encoding: binary\n");
	$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
	$objWriter->setOffice2003Compatibility(true);
	$objWriter->save('php://output');


The code for outputting Excel5 writer's output is from manual, so as for 2007, i don't quote it here.

Maby i'm missing updated version of something or doing something wrong?

Using PHPExcel 42439 from snapshots.

Thanks.

 

Developer
Apr 5, 2010 at 11:13 AM

There was a temporary problem in the snapshots where calculation of column width was skipped in Excel5 writer. Can you try again with latest source code:

http://phpexcel.codeplex.com/SourceControl/list/changesets

Not sure what the problem is with Excel2007. Can you try to remove this line and see if it gets better:

$objWriter->setOffice2003Compatibility(true);
Apr 21, 2010 at 4:34 PM

Hello,

 

with current latest Excel5 seems to be ok, Excel2007 is still "broken" (wants to recover file contents )