Export a colum from Mysql

Nov 7, 2011 at 9:58 AM
Edited Nov 10, 2011 at 8:51 AM

Hi it is my first time here and i hope anybody can help me to fin a solution about my Problem.

I want to export a resultset of my queries, but it dosn't works with one Colum. It is the last while query, where i want to export in Excel in the column C row 7 the resultset of '$nombre_coupures'. There i have 43 records and export for this resultset begins at Cell C7 to C50.
I don't knox how to realise that, i try something and i have searced the Form here, but without success.
Here is my code:

 

<?php
include("inc/connexion.php"); 
mysql_connect($serverDB,$userDB,$passDB); 
mysql_select_db($databDB) or die ("La base des donn�es n'existe pas - Verifie le nom de la base des donn�es.");

$id_acores = $_GET['id'];
$date_demande 	= $_GET['ddemande'];
$code_acores 	= $_GET['codeAcore'];
$code_regate 	= $_GET['codeRegate'];

$abfrage0 = "SELECT DISTINCT acores.code_regate, acores.libelle, infos_bureau.telephone, infos_bureau.mail, demande_appro_devise.date_demande 
FROM demande_appro_devise, acores, infos_bureau, detail_demande_appro_devise WHERE acores.id_acores = '$id_acores' AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande AND demande_appro_devise.code_acores = acores.code_acores AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande"; $ergebnis0 = mysql_query($abfrage0) or die("Query failed with error: ".mysql_error()); if( mysql_numrows($ergebnis0) > 0) { $codeRegate = mysql_result($ergebnis0 , 0 , "code_regate"); $libBureau = mysql_result($ergebnis0 , 0 , "libelle"); $acoreTelephone = mysql_result($ergebnis0 , 0 , "telephone"); $acoreEmail = mysql_result($ergebnis0 , 0 , "mail"); $date_demande = mysql_result($ergebnis0 , 0 , "date_demande"); } else { $codeRegate = ""; $libBureau = ""; $acoreTelephone = ""; $acoreEmail = ""; $date_demande = ""; } // Test Bereich $abfrage1 = "select devises as 'devis' from details_coupures order by devises"; $ergebnis1 = mysql_query($abfrage1) or die("Query failed with error: ".mysql_error()); while($row = mysql_fetch_row($ergebnis1)) { $abfrage2 = "select count(coupure) as 'ncoupures' from details_coupures where devises = '".$row[0]."'"; $ergebnis2 = mysql_query($abfrage2) or die("Query failed with error: ".mysql_error()); $ncoupures = mysql_result($ergebnis2 , 0 , "ncoupures"); //echo '<tr><td>&nbsp;'.$row[0].'</td><td colspan="3">'; $abfrage3 = "select coupure from details_coupures where devises = '".$row[0]."' order by coupure"; $ergebnis3 = mysql_query($abfrage3) or die("Query failed with error: ".mysql_error()); //echo '<table border="0" width="240" cellspacing="0" cellpadding="0">'; while($row1 = mysql_fetch_row($ergebnis3)) { $abfrage4 = "SELECT DISTINCT detail_demande_appro_devise.quantite as 'quantite'
FROM demande_appro_devise, acores, infos_bureau, detail_demande_appro_devise
WHERE acores.id_acores = '"
.$id_acores."'
AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande
AND demande_appro_devise.code_acores = '"
.$code_acores."'
AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande
AND demande_appro_devise.date_demande = '"
.$date_demande."'
AND detail_demande_appro_devise.coupure = '"
.$row1[0]."'
AND demande_appro_devise.code_devise = '"
.$row[0]."' "; $ergebnis4 = mysql_query($abfrage4) or die("Query failed with error: ".mysql_error()); if( mysql_numrows($ergebnis4) > 0) { $nombre_coupures = mysql_result($ergebnis4 , 0 , "quantite"); } else { $nombre_coupures = "0"; } // echo '<tr><td width="80">&nbsp;'.$row1[0].'</td><td width="80">&nbsp;&nbsp;'.$nombre_coupures.'</td>
// <td width="80">&nbsp;&nbsp;&nbsp;'.( $nombre_coupures * $row1[0] ).'</td></tr>';
//echo '</tr>'; $mTotal = ( $nombre_coupures * $row1[0] ) + $mTotal; } //echo '</table></td>'; //echo '<td>&nbsp;'.$mTotal.'</td><td>&nbsp;</td><td>&nbsp;</td>'; $mTotal = 0; } //echo '</table>'; //Ende Testbereich /** PHPExcel */ require_once 'lib/phpexcel/Classes/PHPExcel.php'; require_once 'lib/phpexcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php'; /** PHPExcel_IOFactory */ require_once 'lib/phpexcel/Classes/PHPExcel/IOFactory.php'; /** PHPExcel_RichText */ require_once 'lib/phpexcel/Classes/PHPExcel/RichText.php'; /** PHPExcel_Drawing */ require_once 'lib/phpexcel/Classes/PHPExcel/Worksheet/Drawing.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("Unknown") ->setLastModifiedBy("Unknown") ->setTitle("Export Commandes de Devises") ->setSubject("Resultat") ->setDescription("Running Order Export") ->setKeywords("CMD") ->setCategory("Results"); //Header and Footer $objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing(); $objDrawing->setPath('lib/phpexcel/tests/images/logo.gif'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing,PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&R GESTION DES DEVISES - SCM'); /* $objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing(); $this->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G'); $objDrawing->setName('La Banque Postale'); $objDrawing->setPath('lib/phpexcel/tests/images/logo.gif'); $objDrawing->setHeight(33); $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&Gestion des devises!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); */ // Titre du Formulaire $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'COMMANDES DE DEVISES'); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(false); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB(f7f8f9); $objPHPExcel->getActiveSheet()->mergeCells('A1:G1'); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setRGB('3a62a1'); // Les Enregistrement Code_Regate $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); $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()->setCellValue('A2', 'Nom Du Bureau:'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Code Du Bureau:'); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Telephone:'); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Email:'); $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Date Operation:'); $objPHPExcel->getActiveSheet()->setCellValue('B2' . $abfrage, $libBureau); $objPHPExcel->getActiveSheet()->setCellValue('B3' . $abfrage, $codeRegate); $objPHPExcel->getActiveSheet()->setCellValue('B4' . $abfrage, $acoreTelephone); $objPHPExcel->getActiveSheet()->setCellValue('B5' . $abfrage, $acoreEmail); $objPHPExcel->getActiveSheet()->setCellValue('G2' . $abfrage, $date_demande); // Deuxieme Header avec 7 colonnes $objPHPExcel->getActiveSheet()->setCellValue('A6', 'DEVISES:'); $objPHPExcel->getActiveSheet()->setCellValue('B6', 'COUPURES:'); $objPHPExcel->getActiveSheet()->setCellValue('C6', 'NOMBRE TOTAL:'); $objPHPExcel->getActiveSheet()->setCellValue('D6', 'TOTAL COMMANDE:'); $objPHPExcel->getActiveSheet()->setCellValue('E6', 'COURS:'); $objPHPExcel->getActiveSheet()->setCellValue('F6', 'FOURNISSEUR:'); $objPHPExcel->getActiveSheet()->setCellValue('G6', 'CONTREVALEUR:'); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFont()->getColor()->setRGB(f7f8f9); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFont()->setBold(false); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFill()->getStartColor()->setRGB('3a62a1'); //$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'); //Les Devises $objPHPExcel->getActiveSheet()->setCellValue('A7', 'AUD'); $objPHPExcel->getActiveSheet()->setCellValue('A12', 'CAD'); $objPHPExcel->getActiveSheet()->setCellValue('A17', 'CHF'); $objPHPExcel->getActiveSheet()->setCellValue('A24', 'DKK'); $objPHPExcel->getActiveSheet()->setCellValue('A29', 'GBP'); $objPHPExcel->getActiveSheet()->setCellValue('A33', 'JPY'); $objPHPExcel->getActiveSheet()->setCellValue('A36', 'NOK'); $objPHPExcel->getActiveSheet()->setCellValue('A41', 'SEK'); $objPHPExcel->getActiveSheet()->setCellValue('A46', 'USD'); $objPHPExcel->getActiveSheet()->mergeCells('A7:A11'); $objPHPExcel->getActiveSheet()->mergeCells('A12:A16'); $objPHPExcel->getActiveSheet()->mergeCells('A17:A23'); $objPHPExcel->getActiveSheet()->mergeCells('A24:A28'); $objPHPExcel->getActiveSheet()->mergeCells('A29:A32'); $objPHPExcel->getActiveSheet()->mergeCells('A33:A35'); $objPHPExcel->getActiveSheet()->mergeCells('A36:A40'); $objPHPExcel->getActiveSheet()->mergeCells('A41:A45'); $objPHPExcel->getActiveSheet()->mergeCells('A46:A50'); //Les Coupures AUD $objPHPExcel->getActiveSheet()->setCellValue('B7', '5'); $objPHPExcel->getActiveSheet()->setCellValue('B8', '10'); $objPHPExcel->getActiveSheet()->setCellValue('B9', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B10', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B11', '100'); //Les Coupures CAD $objPHPExcel->getActiveSheet()->setCellValue('B12', '5'); $objPHPExcel->getActiveSheet()->setCellValue('B13', '10'); $objPHPExcel->getActiveSheet()->setCellValue('B14', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B15', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B16', '100'); //Les Coupures CHF $objPHPExcel->getActiveSheet()->setCellValue('B17', '10'); $objPHPExcel->getActiveSheet()->setCellValue('B18', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B19', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B20', '100'); $objPHPExcel->getActiveSheet()->setCellValue('B21', '200'); $objPHPExcel->getActiveSheet()->setCellValue('B22', '500'); $objPHPExcel->getActiveSheet()->setCellValue('B23', '1000'); //Les Coupures DKK $objPHPExcel->getActiveSheet()->setCellValue('B24', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B25', '100'); $objPHPExcel->getActiveSheet()->setCellValue('B26', '200'); $objPHPExcel->getActiveSheet()->setCellValue('B27', '500'); $objPHPExcel->getActiveSheet()->setCellValue('B28', '1000'); //Les Coupures GBP $objPHPExcel->getActiveSheet()->setCellValue('B29', '5'); $objPHPExcel->getActiveSheet()->setCellValue('B30', '10'); $objPHPExcel->getActiveSheet()->setCellValue('B31', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B32', '50'); //Les Coupures JPY $objPHPExcel->getActiveSheet()->setCellValue('B33', '1000'); $objPHPExcel->getActiveSheet()->setCellValue('B34', '5000'); $objPHPExcel->getActiveSheet()->setCellValue('B35', '10000'); //Les Coupures NOK $objPHPExcel->getActiveSheet()->setCellValue('B36', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B37', '100'); $objPHPExcel->getActiveSheet()->setCellValue('B38', '200'); $objPHPExcel->getActiveSheet()->setCellValue('B39', '500'); $objPHPExcel->getActiveSheet()->setCellValue('B40', '1000'); //Les Coupures SEK $objPHPExcel->getActiveSheet()->setCellValue('B41', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B42', '50'); $objPHPExcel->getActiveSheet()->setCellValue('B43', '100'); $objPHPExcel->getActiveSheet()->setCellValue('B44', '500'); $objPHPExcel->getActiveSheet()->setCellValue('B45', '1000'); //Les Coupures USD $objPHPExcel->getActiveSheet()->setCellValue('B46', '1'); $objPHPExcel->getActiveSheet()->setCellValue('B47', '5'); $objPHPExcel->getActiveSheet()->setCellValue('B48', '10'); $objPHPExcel->getActiveSheet()->setCellValue('B49', '20'); $objPHPExcel->getActiveSheet()->setCellValue('B50', '50'); //Doesn't Work !!! //Row Output Total numbers //$objPHPExcel->getActiveSheet()->setCellValue('C7,C'. $abfrage, $nombre_coupures); //$col = ('C'); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow('C7:C'. $abfrage, $nombre_coupures); // Rename sheet - $objPHPExcel->getActiveSheet()->setTitle(''. $codeRegate); $objPHPExcel->getActiveSheet()->getTabColor()->setRGB('3a62a1'); //Set active sheet index to the first sheet, so Excel opens this as the first sheet // Rename sheet // $objPHPExcel->setActiveSheetIndex(0); // redirect output to client browser header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="export_scm.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/cdd/'. $codeRegate .'.xls'); ?>

I hope anybody can Help me

cheers

Achillix

Nov 7, 2011 at 8:23 PM

Well.... setCellValue define the value of ONE cell, so you can't use a address like 'C7,Cx', with a comma and 'x' coming from nowhere (as far as i can see $abfrage isn't defined).

setCellValueByColumnAndRow want three parameters : a column (integer), a row (integer) and the value.

You need only to increment the row, so, use what you want, using a counter :

$Row=7;
[...]setCellValue('C'.$Row, $nombre_coupures);

or

[...]setCellValueByColumnAndRow(2, $Row, $nombre_coupures);

$Row++;

Note : in PHPExcel, column index is zero based, row index is 1 based. ('A1' : 0,1).
In your script, you don't write value in the loop, you got only the last $nombre_coupures...
I wrote hardcoded values, easy to read in a snippet, but using some constants/variables is better, of course.

Nov 8, 2011 at 10:25 AM
Edited Nov 10, 2011 at 8:52 AM

Hi

At first - thank you for your Answer and your Help.
I have changed the line with the problem

 

require('date_demande_appdev_form_query2.php');
$col=2;
$Row=7;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $Row, $nombre_coupures);
$Row++;

but i get only the last record of my resultset in the export


Here the part of date_demande_appdev_form_query2.php

<?php
$abfrage1 	= "select distinct devises from details_coupures order by devises";
$ergebnis1 	= mysql_query($abfrage1) or die("Query failed with error: ".mysql_error());
while($row = mysql_fetch_row($ergebnis1))
{
$abfrage2 		= "select count(coupure) as 'ncoupures' from details_coupures where devises = '".$row[0]."'";
$ergebnis2 		= mysql_query($abfrage2) or die("Query failed with error: ".mysql_error());
$ncoupures	 	= mysql_result($ergebnis2 , 0 , "ncoupures");
	
	//echo '<tr id="tdformcol"><td id="formGlob" width="70" >&nbsp;'.$row[0].'</td><td colspan="3" id="formGlob" width="200">';
	$abfrage3 	= "select coupure from details_coupures where devises = '".$row[0]."' order by coupure";
	$ergebnis3 	= mysql_query($abfrage3) or die("Query failed with error: ".mysql_error());
		//echo '<table border="0" width="200" cellspacing="0" cellpadding="0">';
	while($row1 = mysql_fetch_row($ergebnis3))
	{
	$abfrage4 = " SELECT DISTINCT detail_demande_appro_devise.quantite
			FROM demande_appro_devise, acores, infos_bureau, detail_demande_appro_devise 
			WHERE acores.id_acores = '".$id_acores."' 
			AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande 
			AND demande_appro_devise.code_acores = '".$code_acores."' 
			AND demande_appro_devise.date_demande = '".$date_demande."' 
			AND detail_demande_appro_devise.coupure = '".$row1[0]."' 
			AND demande_appro_devise.code_devise = '".$row[0]."' ";
	$ergebnis4 	= mysql_query($abfrage4) or die("Query failed with error: ".mysql_error());
	if( mysql_numrows($ergebnis4) > 0)
	{
		$nombre_coupures	 	= mysql_result($ergebnis4 , 0 , "quantite");
	}
	else
	{
		$nombre_coupures	 	= "0";
	}
	//echo "$nombre_coupures";
	//echo '<tr id="formcol"><td id="formcol" width="100">&nbsp;'.$row1[0].'</td>
<td id="formcol" width="80">&nbsp;&nbsp;'.$nombre_coupures.'</td>
<td id="formcol" width="40">&nbsp;&nbsp;&nbsp;'.( $nombre_coupures * $row1[0] ).'</td></tr>';
//echo '</tr>'; $mTotal = ( $nombre_coupures * $row1[0] ) + $mTotal; } //echo '</table></td>'; //echo '<td id="formGlob" width="100">&nbsp;'.$mTotal.'</td>
<td id="formGlob" width="130">&nbsp;</td><td id="formGlob" width="130">&nbsp;</td>';
$mTotal = 0; } ?>

How you can see, i have HTML Code desactivated, because with HTML i get a crypted exported Excelfile .




I don't know what or where i can change something to get all records in the colum "NOMBRE"
If you have another Idea?

Thanks in advanced

Achillix

Nov 8, 2011 at 6:47 PM

If you don't have moved your line, i'm not suprised. You need to have something like :

0) getting your parameters, open your db connection, require needed files and the like.

1) Create your Excel workbook, define counter start value.

2 ) Execute your query.

3) Loop on each record, doing additional tasks needed

3b) In the loop, when your data is "ready to show", set the cell with your data

3c) Increment your row counter, preparing for the next data.

4)(here or after 1) Add static labels, font, color, etc. to your workbook.

5) "Write" the Workbook on the user browser.

If you have some trouble, comment the save('php://output') and add some brutal echo to display the target cell address and value, you must have something like C7, C8,..., C50 and 0,0,...1,21,...displayed in your loop.

Nov 10, 2011 at 8:53 AM

It works now.

THX