Excel Displaying Results Incorrectly

Topics: Developer Forum, Project Management Forum, User Forum
May 17, 2011 at 8:13 AM

Hello All,

I have been using PHPExcel for quite some time now for a client of mine. I have a report written in php that he runs and he often needs to export it to excel, therefore PHPExcel was the ideal solution.

However the reporting database has grown, and therefore so has excel. I have noticed that when excel creates a workbook with over 150 rows, its starts to go a bick haywire. Basically misplacing columns. there are 11 columns and after row 150ish it starts to put column 11 in column 1, column 10 in column 11, etc..

If anyone can help me with this I would be very grateful, as I am getting a lot of pressure from my client and I cannot seem to figure a solution.

Below is the code I am using:

 

<?php
										

/*
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.6, 2011-02-27
 */

/** Error reporting */
error_reporting(E_ALL);
$q=$_POST["my_hidden_data"];
if($q!=="")
{
	$r=$_POST["my_hidden_row"];
	
	$qst=strip_tags($q);
	$data=explode(",",$qst);
	//$data=strip_tags($_data);
	date_default_timezone_set('Africa/Johannesburg');

	/** PHPExcel*/ 
	require_once 'DA/Classes/PHPExcel.php';
	//require_once '../Classes/PHPExcel/IOFactory.php';


	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

	// Set properties
	$objPHPExcel->getProperties()->setCreator("WeExcel")
								 ->setLastModifiedBy("zedd")
								 ->setTitle("Report")
								 ->setSubject("Client Report")
								 ->setDescription("Report for Client.")
								 ->setKeywords("office 2007 openxml php")
								 ->setCategory("DB Report");


	/* Add some data */
	$x=0;
	for ($i = 1; $i <=$r; $i++) 
	{
		$objPHPExcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('B' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('C' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('D' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('E' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('F' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('G' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('H' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('I' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('J' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		$objPHPExcel->getActiveSheet()->getCell('K' . $i)->setValueExplicit($data[$x],PHPExcel_Cell_DataType::TYPE_STRING);
		$x=$x+1;
		
		//$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $data[$x]);
	}
		$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);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
	//foreach($data as $cll)
		//$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $data[$x]);
		
	// Rename sheet
	$objPHPExcel->getActiveSheet()->setTitle('Report');


	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);


	// Redirect output to a client�s web browser (Excel2007)
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="Report"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	/*$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2003XML');
	$objWriter->save('php://output');
	$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
	$objWriter->save("Report.xls");*/

	exit;

}
else
{
echo "You have not entered any data. <br />Please enter an ID Number and try again. <br />Or alternatively re-try downloading the report.";
}

?>