Create more sheets

Topics: Developer Forum, User Forum
Jan 4, 2012 at 10:32 AM
Edited Jan 18, 2012 at 2:36 PM

Hello

I have a question about looping sheets in one Workbook.
The export in excel works fine with one only sheet, but when i have more recordset from the same db query then i want to get for every recordset on sheet in a workbook.

 

	mysql_connect($serverDB,$userDB,$passDB); 
	mysql_select_db($databDB) or die ("No connection");

	$id_acores = $_GET['id'];
	$regstock 	= $_GET['regstock'];
	$code_acores 	= $_GET['codeAcore'];

	require_once 'lib/phpexcel/Classes/PHPExcel.php';
	require_once 'lib/phpexcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
	require_once 'lib/phpexcel/Classes/PHPExcel/IOFactory.php';
	require_once 'lib/phpexcel/Classes/PHPExcel/RichText.php';
	require_once 'lib/phpexcel/Classes/PHPExcel/Worksheet/Drawing.php';

	$objPHPExcel = new PHPExcel();

	$objPHPExcel->getProperties()->setCreator("Test")
						 ->setLastModifiedBy("Test")
						 ->setTitle("Export")
						 ->setSubject("Resultat")
						 ->setDescription("Running Order Export")
						 ->setKeywords("RGD")
						 ->setCategory("Results");
	//Only a Codesnippet
	$abfrage0 = "SELECT DISTINCT acores.id_acores ...";
	$ergebnis0 = mysql_query($abfrage0) or die("Query failed with error: ".mysql_error());
	...

	while
	($abfrage0 = mysql_fetch_array($ergebnis0))			 
	{
	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Nom Du Bureau:');
	$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Code Du Bureau:');
	$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Nom du Caissier:');
	$objPHPExcel->getActiveSheet()->setCellValue('A7', 'MOTIF DE LA REGULARISATION:');
	$objPHPExcel->getActiveSheet()->setCellValue('E2', 'Date Bureau:');
	$objPHPExcel->getActiveSheet()->setCellValue('B2' . $abfrage, $libBureau);
	$objPHPExcel->getActiveSheet()->setCellValue('B3' . $abfrage, $codeRegate);
	$objPHPExcel->getActiveSheet()->setCellValue('B4', '');
	$regdate = formatdateslash($regstock);
	$objPHPExcel->getActiveSheet()->setCellValue('F2' . $abfrage, $regdate);
	$objPHPExcel->getActiveSheet()->setCellValue('A8' . $abfrage, $motif_regularisation);
	$objPHPExcel->getActiveSheet()->mergeCells('A7:F7');
	$objPHPExcel->getActiveSheet()->mergeCells('A8:F8');

	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$objWorksheet->setTitle(''. $codeRegate);
	}
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="export_rgd_total.xls"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

	$objWriter->save('php://output');

	//$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
	$objWriter->save('export/rgd/'. $codeRegate .'.xls');

 

I try with a while to looping all recordset and to get one sheet for one recordset.

The query works fine, tested with phpmyadmin.

In this example i get 4 recordsets and the export create 4 Worksheet in one Workbook, but

1. only the first worksheets has has one recordset the other three worksheets are empty

2. The name of the sheets are the variable of '$codeRegate', who is everytime different., but he repeat everytime the first $codeRegate, so when the first $coderegate calls 01400 then i get for the second sheet 01400 1 and not 75480.


I f you have any idea?

Thanks in advance

Achillix

Jan 18, 2012 at 2:33 PM

Hello again.

Is there no body who has an idea?

Please help me

 

THX

 

Achillix

Jan 18, 2012 at 2:40 PM

I think you have to change the active sheet to the newly added sheet. Otherwise your sql result rows are all added to the first worksheet.

 

btw, german var names, english messages, with french data... nice ;)

Jan 18, 2012 at 2:47 PM

I am german but i work in France.

THX for your reply i will try them.

greetings

 

Achillix

Jan 19, 2012 at 8:27 AM

Many Thanks for your help.
I have change the code from:

	while
	($abfrage0 = mysql_fetch_array($ergebnis0))			 
	{

	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$objWorksheet->setTitle(''. $codeRegate);
	$objWorksheet->getTabColor()->setRGB('f98933');
		...
	$objPHPExcel->getActiveSheet()->setCellValue('B2' . $abfrage, $libBureau);
	$objPHPExcel->getActiveSheet()->setCellValue('B3' . $abfrage, $codeRegate);
	$objPHPExcel->getActiveSheet()->setCellValue('B4', '');
	$objPHPExcel->getActiveSheet()->setCellValue('B5' . $abfrage, $acoreTelephone);
	$objPHPExcel->getActiveSheet()->setCellValue('B6' . $abfrage, $acoreEmail);
	...
	}

to:

	while
	($abfrage0 = mysql_fetch_array($ergebnis0))			 
	{

	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$objWorksheet->setTitle(''. $codeRegate);
	$objWorksheet->getTabColor()->setRGB('f98933');
	...
	$objWorksheet->setCellValue('B2' . $abfrage, $libBureau);
	$objWorksheet->setCellValue('B3' . $abfrage, $codeRegate);
	$objWorksheet->setCellValue('B4', '');
	$objWorksheet->setCellValue('B5' . $abfrage, $acoreTelephone);
	$objWorksheet->setCellValue('B6' . $abfrage, $acoreEmail);
	...
	}
and now he create the sheets and he put the data inside.
But now i have other problems.

1. In every sheet i get the same recordset(the first recordset only from my query)
2. He increment everytime the same Sheetname like 01400, 01400 1. But it must be 01400, 75480.

Many thanks for your help and if you have any idea what i do wrong with the two points above?

Cheers

Achillix

Jan 19, 2012 at 8:54 AM

the resultrow from your query is stored in $abfrage0, however, in the while loop you use $abfrage instead, which possibly contains the result of a different query ;)

Jan 19, 2012 at 1:28 PM
Edited Jan 19, 2012 at 1:30 PM

You are right, i'm blind, sorry
It works now.

	while
	($abfrage0 = mysql_fetch_assoc($ergebnis0))			 
	{

	$cr 		= 	$abfrage0['code_regate'];
	$lib 		= 	$abfrage0['libelle'];
	$tel 		= 	$abfrage0['telephone'];
	$email 		= 	$abfrage0['mail'];
	$dreg 		= 	$abfrage0['date_regularisation'];
	$astock 	= 	$abfrage0['ancien_stock'];
	$nstock 	= 	$abfrage0['nouveau_stock'];
	$motif	 	= 	$abfrage0['motif_regularisation'];

	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$objWorksheet->setTitle(''. $cr);
	$objWorksheet->getTabColor()->setRGB('f98933');
	...
	$objWorksheet->setCellValue('B2' , $lib);
	$objWorksheet->setCellValue('B3' ,  $cr);
	$objWorksheet->setCellValue('B4', '');
	$objWorksheet->setCellValue('B5' , $tel);
	$objWorksheet->setCellValue('B6' , $email);
	...


But and that is my last question, why he creates at the beginning an empty sheet calls 'Worksheet'. How can i remove them?
I tried:
    $objWorksheet->removeSheetByIndex(0);
but doesn't works
Thanks you very much for your help

cheers

Achillix

Jan 19, 2012 at 1:38 PM

I think it creates a default sheet at the beginning because you cannot have an empty workbook. I would say that $objPHPExcel->removeSheetByIndex(0); should work though ;)

Jan 19, 2012 at 1:48 PM

Yes it works, it was my mistake, i have put it in a while before ;-)

Many Thanks for your help

cheers

achillix