Big bug with comments

Topics: Developer Forum, Project Management Forum, User Forum
May 3, 2012 at 3:18 PM

Hi,

I have big bug in php excel when I create a document with 4 sheets.

for example:

 


include("header.php");
include("menu.php");
include("connection.php");


require_once '../Classes/PHPExcel.php';	

function age ($dateNaiss)
{
list($year,$month,$day) = explode("-",$dateNaiss);
$year_diff = date("Y") - $year;
$month_diff = date("m") - $month;
$day_diff = date("d") - $day;
if ($month_diff < 0) $year_diff--;
elseif (($month_diff==0) && ($day_diff < 0)) $year_diff--;
return $year_diff;
}

function DateForm($date)
{
	list($year,$month,$day) = explode("-",$date);
	return "$day-$month-$year";
}
	
	// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2010 openxml php")
							 ->setCategory("Test result file");



$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);



$objPHPExcel->getActiveSheet()->getStyle('A')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('B')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('C')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('D')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('E')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('F')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('G')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('H')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('I')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('J')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('K')->getFont()->setSize(14);


$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A2', "Date Entr�e")
            ->setCellValue('B2', "Nom")
            ->setCellValue('C2', "Situation administrative")
            ->setCellValue('D2', "Sortie")
            ->setCellValue('E2', "Hotel");


$sql="SELECT f.idFamille,f.nomFamille,f.nbEnfants,f.Couples,h.libelle,s.libelle
FROM famille f, hotel h, situationadministrative s
WHERE f.pec=1
AND s.IdSituationAdmi=SituationAdmi
And h.idHotel=hotel
And paiement=1
And f.Archivage=0";


$requete=mysql_query($sql) or die (mysql_error());
$i=3;
while($val=mysql_fetch_array($requete))
{
	$j=1;
	$id=$val[0];
	$sitfam="";
	$color="";
	$couple=$val[3];
	$enfant=$val[2];
	$tabAgeEnfant=array();
	if($couple==0)
			$couple=2;
	$nb=$couple+$enfant;
	if($couple==2 && $nb>2)
	{
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FF90EE90');
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		$sitfam="famille avec enfants";
	}
	
	else if($nb==2 && $couple==2)
	{
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FFFFFF66');
		$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		$sitfam="couple sans enfants";
	}
	else if($nb==1 && $couple==1)
	{
		$sql1="select sexe,DateNaiss from personne where lienfamille='$id' ";
		$requete1=mysql_query($sql1) or die (mysql_error());
		$val2=mysql_fetch_array($requete1);
		if($val2[0]=="homme")
		{
			$sitfam="Homme seul";
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FF87CEEB');
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		}
		else
		{
			$sitfam="Femme seule";
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FFFFC0CB');
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		}
	}
	
	else
	{
		$sql1="select sexe,DateNaiss from personne where lienfamille='$id' ";
		$max=18;
		$sexe="";
		$requete1=mysql_query($sql1) or die (mysql_error());
		while($val2=mysql_fetch_array($requete1))
		{
			$age=age($val2[1]);
			
			if($max<$age)
			{
				$max=$age;
				$sexe=$val2[0];
			}
				
			
		}
		
		if($sexe=="homme")
		{
			$sitfam="homme avec enfant(s)";
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FF9933FF');
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		}
		
		else
		{
			$sitfam="femme avec enfant(s)";
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFill()->getStartColor()->setARGB('FFFFCC00');
			$objPHPExcel->getActiveSheet()->getStyle("A$i:E$i")->getFont()->setSize(14);
		}
		
	}
	
	$sql1="select DateEntree,DateSortieHotel,dateNaiss from personne where lienfamille='$id' and nombreNuit>3";
	$requete1=mysql_query($sql1) or die (mysql_error());
	while($val1=mysql_fetch_array($requete1))
	{
		
		if(substr($val1[1],0,4)>= date("Y") && substr($val1[1],5,2)>=date("m") && substr($val1[1],8,2)>=date("d"))
		{
			$j=0;
			$dateEnt=DateForm($val1[0]);
			$dateSort=DateForm($val1[1]);
			$tabAgeEnfant[]=age($val1[2]);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".$i, "$dateEnt");
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue("D".$i, "$dateSort");
			
		}
		else 
		{
			$j=1;
		}
	}
	if($j==0)
	{
		$hotel=$val[4];
		$admi=$val[5];
		$nom=$val[1];
		
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue("E".$i, "$hotel");
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".$i, "$admi");
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".$i, "$nom");
		
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->setAuthor('Famille');
		$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('B'.$i)->getText()->createTextRun('Famille:');
		$objCommentRichText->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->setWidth('200pt');
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->setHeight('200pt');
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->setMarginLeft('150pt');
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->getFillColor()->setRGB('EEEEEE');
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->getText()->createTextRun("\r\n");
		$objPHPExcel->getActiveSheet()->getComment('B'.$i)->getText()->createTextRun(" composition: $sitfam \n enfants age: \n ");
		
		foreach($tabAgeEnfant as $valeur)
		{
			$age=$valeur;
			$objPHPExcel->getActiveSheet()->getComment('B'.$i)->getText()->createTextRun(" $age ans  \n ");
			echo "\n $age \n";
		}
		
		$i++;
	}
}






// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('PRISE EN CHARGE URGENCE');



$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('FIN PEC URGENCE');

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setTitle('PRISE EN CHARGE CG');

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setTitle('FIN PEC CG');

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


// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));


// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

header("location:./excel_nomee.xlsx" );


 

 

with this code in the first sheet I don't have a problem, but if I copy this code in the over sheet that I create I have an XML errors with excel and comment in the first page are missing.

 

Thx for your attention and sorry for my english