Problème d'exportation BDD Excel

par **LongJohnson** » 04 mars 2015, 12:47

Bonjour, je sollicite votre aide afin de m'aidé à comprendre pourquoi mon code d'exportation ne fonctionne pas. Enfait il y'en a deux l'un fonctionne ( celui qui va chercher dans la BBD "thèse" ) et l'autre non ( celui qui vas chercher dans la BBD "livres" , il me renvoi systematiquement une page excel vide. ) Je tien a précisé que d'autre script d'exportation fonctionne sur la BDD livre.

Pouvez vous m'aider , comparer les deux ? je ne comprend pas. De plus ce n'est pas moi qui est écrit le code de base et je suis débutant. Merci.

Le code qui fonctionne :

<?php 
set_time_limit (0);
function stripAccents($string)
{
	return strtr($string,'àáâãäçèéêëìíîïñòóôõöùúûüýÿÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝ',
'aaaaaceeeeiiiinooooouuuuyyAAAAACEEEEIIIINOOOOOUUUUY');
}
$nbfeuilles = 0;
 // selectionner la base de données 
$host = 'localhost';
$user = 'root';
$pass = 'gauthier';
$db = 'bibliotheque';
mysql_connect ($host,$user,$pass) or die ('Erreur : '.mysql_error() );
mysql_select_db($db) or die ('Erreur :'.mysql_error());

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once './Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();



// Set properties
$objPHPExcel->getProperties()->setCreator("Matthieu Verriest")
							 ->setLastModifiedBy("Matthieu Verriest")
							 ->setTitle("Office 2007 XLS Test Document")
							 ->setSubject("Office 2007 XLS Test Document")
							 ->setDescription("Test document for Office 2007 XLS, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("xls file");
	

$liste = explode(',',$_POST['liste']);

foreach($liste AS $utilisateur)
{

	$reponse=mysql_query("SELECT id,ref,titre,auteur,theme,lieu,annee,date FROM theses where stockage like \"%$utilisateur%\" ORDER BY ref");
	$total = mysql_num_rows($reponse);	

$user = stripAccents($utilisateur);
	
$date_courante = getdate();
$mois = $date_courante['mon'];
$mjour = $date_courante['mday'];
$annee = $date_courante['year'];

$styleArray = array( 'font' => array( 'bold' => true,'size'=>16, 'underline' => PHPExcel_Style_Font::UNDERLINE_NONE),);
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->applyFromArray($styleArray);

	if($total)
	{
	// Add some data
  
		$objPHPExcel->createSheet();

		$objPHPExcel->setActiveSheetIndex($nbfeuilles)
					->setCellValueByColumnAndRow(1, 1, "IMPRESSION DES THESES EMPRUNTEES PAR  $user  DU  :  $mjour / $mois / $annee")
					->setCellValueByColumnAndRow(0, 3, 'Ref')
					->setCellValueByColumnAndRow(1, 3, 'Titre')
					->setCellValueByColumnAndRow(2, 3, 'Auteur');
					//->setCellValueByColumnAndRow(3, 3, 'Theme');
					//->setCellValueByColumnAndRow(4, 3, 'lieu')
					//->setCellValueByColumnAndRow(5, 3, 'Annee Edition');

	//$objPHPExcel->getActiveSheet()->getStyle('A2:' . 
    //$objPHPExcel->getActiveSheet()->getHighestColumn() . 
   // $objPHPExcel->getActiveSheet()->getHighestRow()
	//)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	//->getFont()->setBold(false);
		$i=4;

		while($data = mysql_fetch_array($reponse))
		{
			$objPHPExcel->setActiveSheetIndex($nbfeuilles)	
					->setCellValueByColumnAndRow(0, $i, $data['ref']) 
					->setCellValueByColumnAndRow(1, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data["titre"]))
					->setCellValueByColumnAndRow(2, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['auteur']));
					//->setCellValueByColumnAndRow(3, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['theme']));
					//->setCellValueByColumnAndRow(4, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['lieu']))
					//->setCellValueByColumnAndRow(5, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['annee']));

$style2 = array( 'font' => array( 'bold' => false,'size'=>14, 'underline' => PHPExcel_Style_Font::UNDERLINE_NONE),);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($style2);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $i)->applyFromArray($style2);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $i)->applyFromArray($style2);
//$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $i)->applyFromArray($style2);
					
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('7');
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('100');
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('70');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('15');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('20');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('15');
	
	$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
	
	$objPHPExcel->getActiveSheet()->getStyle('A3:' . 
    $objPHPExcel->getActiveSheet()->getHighestColumn() . 
    $objPHPExcel->getActiveSheet()->getHighestRow()
	)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
			$i++;
		}
		
		// Rename sheet			
		$objPHPExcel->getActiveSheet()->setTitle($user);	
					
		$nbfeuilles++;
	}	
}




// 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.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Theso_totale_par_utilisateur.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
	exit;

Le code qui ne fonctionne pas :

<?php 
set_time_limit (0);
function stripAccents($string)
{
	return strtr($string,'àáâãäçèéêëìíîïñòóôõöùúûüýÿÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝ',
'aaaaaceeeeiiiinooooouuuuyyAAAAACEEEEIIIINOOOOOUUUUY');
}
$nbfeuilles = 0;
 // selectionner la base de données 
$host = 'localhost';
$user = 'root';
$pass = 'gauthier';
$db = 'bibliotheque';
mysql_connect ($host,$user,$pass) or die ('Erreur : '.mysql_error() );
mysql_select_db($db) or die ('Erreur :'.mysql_error());

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once './Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();



// Set properties
$objPHPExcel->getProperties()->setCreator("Matthieu Verriest")
							 ->setLastModifiedBy("Matthieu Verriest")
							 ->setTitle("Office 2007 XLS Test Document")
							 ->setSubject("Office 2007 XLS Test Document")
							 ->setDescription("Test document for Office 2007 XLS, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("xls file");
	

$liste = explode(',',$_POST['liste']);

foreach($liste AS $utilisateur)
{
$reponse=mysql_query("SELECT id,ref,titre,auteur,theme,lieu,annee FROM livres where stockage like \"%$utilisateur%\" ORDER BY ref");
	$total = mysql_num_rows($reponse);	

$user = stripAccents($utilisateur);
	
$date_courante = getdate();
$mois = $date_courante['mon'];
$mjour = $date_courante['mday'];
$annee = $date_courante['year'];

$styleArray = array( 'font' => array( 'bold' => true,'size'=>16, 'underline' => PHPExcel_Style_Font::UNDERLINE_NONE),);

$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->applyFromArray($styleArray);

	if($total)
	{
	// Add some data
  
		$objPHPExcel->createSheet();

		$objPHPExcel->setActiveSheetIndex($nbfeuilles)
					->setCellValueByColumnAndRow(1, 1, "IMPRESSION DES LIVRES EMPRUNTES PAR  $user  DU  :  $mjour / $mois / $annee")
					->setCellValueByColumnAndRow(0, 3, 'Ref')
					->setCellValueByColumnAndRow(1, 3, 'Titre')
					->setCellValueByColumnAndRow(2, 3, 'Auteur');
					//->setCellValueByColumnAndRow(3, 3, 'Theme')
					//->setCellValueByColumnAndRow(4, 3, 'ISBN')
					//->setCellValueByColumnAndRow(5, 3, 'Annee Edition');

	//$objPHPExcel->getActiveSheet()->getStyle('A2:' . 
    //$objPHPExcel->getActiveSheet()->getHighestColumn() . 
    //$objPHPExcel->getActiveSheet()->getHighestRow()
	//)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	//->getFont()->setBold(false);
		$i=4;

		while($data = mysql_fetch_array($reponse))
		{
			$objPHPExcel->setActiveSheetIndex($nbfeuilles)	
					->setCellValueByColumnAndRow(0, $i, $data['ref']) 
					->setCellValueByColumnAndRow(1, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data["titre"]))
					->setCellValueByColumnAndRow(2, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['auteur']));
					//->setCellValueByColumnAndRow(3, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['theme']))
					//->setCellValueByColumnAndRow(4, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['isbn']))
					//->setCellValueByColumnAndRow(5, $i, iconv("ISO-8859-15//TRANSLIT","UTF-8",$data['annee']));
					
$style2 = array( 'font' => array( 'bold' => false,'size'=>14, 'underline' => PHPExcel_Style_Font::UNDERLINE_NONE),);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $i)->applyFromArray($style2);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $i)->applyFromArray($style2);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $i)->applyFromArray($style2);
//$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $i)->applyFromArray($style2);

	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('7');
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('100');
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('70');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('15');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('20');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('15');


	$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
	
	$objPHPExcel->getActiveSheet()->getStyle('A3:' . 
    $objPHPExcel->getActiveSheet()->getHighestColumn() . 
    $objPHPExcel->getActiveSheet()->getHighestRow()
	)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
		
			$i++;
		}
		
		// Rename sheet			
		$objPHPExcel->getActiveSheet()->setTitle($user);	
					
		$nbfeuilles++;
	}	
}




// 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.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="biblio_totale_par_utilisateur.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

	exit;

Merci d'avance pour votre aide.

Problème d'exportation BDD Excel

Répondre

Étendre la vue Revue du sujet : Problème d'exportation BDD Excel

Problème d'exportation BDD Excel