UTF-8 : export de MYSQL vers EXCEL via php ?

**tavarlindar** · 11/01/2011, 00h32

Bonjour à tous,

Je cherche à améliorer une class php d’export de mysql vers excel.

Jusqu’à présent j’utilisais la class ci-dessous pour exporter le résultat de requête mysql vers excel sans problème. Migrant toute ma base de données en UTF-8 afin de gérer le chinois et le russe notamment, je n’arrive pas à exporter mes données correctement. Les caractères « exotiques » ne passent pas sous Excel.

Voici le code :
Dans la page php qui sert de déclencheur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
$reqExcel = "SELECT champ1, champ2, …, champ_n, …. FROM table" ;
$reqExcelCodee= urlencode($reqExcel);
echo "<br/><A HREF='ExportExcel.php?QueryExcel=$reqExcelCodee'>Export to excel</A>\n" ;

ExportExcel.php

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
require_once('include/sql2excel.class.php');
 
if (isSet($_GET['QueryExcel']))
{    
$query = stripSlashes($_GET['QueryExcel']);		
}
 
	//setup parameters for initiating Sql2Excel class instance
	//modify your mysql connection parameters & database name below:
 
	$db_host="localhost";
	$db_user=NOM;
	$db_pwd=PASSE;
	$db_dbname=BASE;
 
//initiating Sql2Excel class instance
	$excel=new Sql2Excel($db_host,$db_user,$db_pwd,$db_dbname);
	//Output excel file to user's browser
 
	$excel->ExcelOutput($query);
	echo"<h1>Exel Generate Completed!!</h1>";

sql2excel.class.php

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
<?
	/* 
	 * @version V1.0 2002/July/18 (c) Erh-Wen,Kuo (erhwenkuo@yahoo.com). All rights reserved.
	 * Released under both BSD license and Lesser GPL library license. 
	 * Whenever there is any discrepancy between the two licenses, 
	 * the BSD license will take precedence. 
	 *
	 * purpose: providing a easy method to write mysql sql content to excel file format and stream
	 *          the output to user's browser directly.
	 */
 
	include_once('include/excelgen.class.php');
	class Sql2Excel{
		var $db_host;
		var $db_user;
		var $db_pwd;
		var $db_name;
		var $db_query;
 
 
		/*Sql2Excel is class constructor
		  Return: None
		*/
		//function Sql2Excel($db_host,$db_user,$db_pwd,$db_name)
        function Sql2Excel($db_host="localhost",$db_user="",$db_pwd="",$db_name="")		
		{
			$this->db_host=$db_host;
			$this->db_user=$db_user;
			$this->db_pwd=$db_pwd;
			$this->db_name=$db_name;
 
			//connect mysql database
			$this->db_link=mysql_pconnect($this->db_host,$this->db_user,$this->db_pwd) or die("Mysql database connecion failed!");
		}//Sql2Excel() END
 
		/*ExcelOutput() is the key method to let this class works
		  This method won't work if you have not include "excelgen.class.php" well.
				  
		  Return: None
		*/
		function ExcelOutput($query="")
		{
			//connect mysql database
			mysql_select_db($this->db_name,$this->db_link);
 
			//get result
			$result=mysql_query($query);
			$numoffields=mysql_num_fields($result);
 
			// now we could construct Excel output
			$fieldstype=array();
 
			for($i=0;$i<$numoffields;$i++){
				$fieldstype[]=mysql_field_type($result,$i);
			}// for($i=0;...) END
 
			//create new instance of ExcelGen() class
			$excel = new ExcelGen("Sql2Excel");
 
			//initiate a counter for excel "ROW" counter
			$rowscounter=0;
 
			//write fields to excel
			for($i=0;$i<$numoffields;$i++){
				$fld=mysql_fetch_field($result,$i);
				$fldname=$fld->name;
				$excel->WriteText($rowscounter,$i,$fldname);
			}// for($i=0;...) END
 
			$rowscounter++;
 
			while($row=mysql_fetch_array($result)){
 
				//fetch each Cell($rowscounter,$colscounter) into Excel output stream
				for($colscounter=0;$colscounter<$numoffields;$colscounter++){
 
					//identify field type to descide how to write excel cell
					if(eregi("(int)",$fieldstype[$colscounter])){
						$excel->WriteNumber($rowscounter,$colscounter,$row[$colscounter]);
					}else{ 
						$excel->WriteText($rowscounter,$colscounter,$row[$colscounter]);
					}
				}//for($colscounter..) END
				$rowscounter++;
			}// while($row=mysql..) END
			$excel->SendFile();
			return;		
		}//ExcelOut() END		
	}//class Sql2Excel END
?>

excelgen.class.php

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
<?
	/* 
	 * @version V1.0 2002/July/18 (c) Erh-Wen,Kuo (erhwenkuo@yahoo.com). All rights reserved.
	 * Released under both BSD license and Lesser GPL library license. 
	 * Whenever there is any discrepancy between the two licenses, 
	 * the BSD license will take precedence. 
	 *
	 * purpose: This class is used to generate simple MS Excel file (xls) via
	 * PHP & stream to user's browser.
	 * Credits: This class is based on Erol Ozcan's psxlsgen
	 *          Excel library functions.
	 */
 
	class  ExcelGen {
	      var  $excel_data;      	// a buffer for store excel stream data
	      var  $excel_filename;     // excel filename
 
 
	     // Default constructor
	     function  ExcelGen($excel_filename='excelgen',$excel_wksheetname='david')
	     {
		$this->excel_data="";		             // a buffer for store excel stream data
		$this->excel_filename=$excel_filename;	     // excel filename
 
		$this->ExcelStart();
	     }
 
	     // start of the excel file
	     function ExcelStart()
	     {
	     	// start of excel file header
	     	$this->excel_data = pack( "vvvvvv", 0x809, 0x08, 0x00,0x10, 0x0, 0x0 );
     	     }
 
	     // end of the excel file
	     function ExcelEnd()
	     {
	       $this->excel_data .= pack( "vv", 0x0A, 0x00 );
	     }
 
	     // write a Number (double) into cell(row, col)
	     function WriteNumber( $row, $col, $value )
	     {
	        $this->excel_data .= pack( "vvvvv", 0x0203, 14, $row, $col, 0x00 );
	        $this->excel_data .= pack( "d", $value );
	     }
 
	     // write a text into cell(Row,Col)
	     function WriteText( $row, $col, $value )
	     {
	        $len = strlen( $value );
	        $this->excel_data .= pack( "v*", 0x0204, 8 + $len, $row, $col, 0x00, $len );
	        $this->excel_data .= $value;
	     }
 
	     // send generated xls as stream file
	     function SendFile()
	     {
	     	//Close Excel File         
	        $this->ExcelEnd();
	        header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT");
	        header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
	        header ( "Pragma: no-cache" );
	        header ( "Content-type: application/octet-stream; name=$this->excel_filename.xls" );
	        header ( "Content-Disposition: attachment; filename=$this->excel_filename.xls"); 
	        header ( "Content-Description: PHP ExcelGen Class" );
	        print $this->excel_data;
	     }
 
	} // end of the class ExcelGen() class
?>

A partir du code initial, j'ai essayé de nombreuses modifications sans succès à savoir :
1) encoder tous les fichier en UTF-8
2) rajouter pour les requêtes sql de mysql_query("SET NAMES utf8");
3) modifier le header ( "Content-type: application/octet-stream; name=$this->excel_filename.xls" ); en précisant que c'était de l'utf8, que c'était du excel, etc...

Dans tous les cas, j'arrive à exporter vers excel, mais les catactères chinois ou russe voire les caractères accentués ne passe jamais correctement selon les modifications.

J'ai l'impression qu'excel s'attend à recevoir des data en iso classique en non pas en UTF-8.

Qui peut m'aider s'il vous plait ?

Par avance merci.

Tavar

Pour info : les datas sont enregistrées en caractères chinois, russe, ou autre (français !, anglais, ..) dans la base.

**tavarlindar** · 12/02/2012, 20h04

Bonjour,

Après avoir passé des jours et des jours à chercher et tester, je n'ai malheureusement trouver aucune solution à ce besoin pourtant simple : exporter le résultat d'une requête (mysql utf-8) vers excel.

J'ai testé PHPExcel, sans succès. Avec PHPExcel, je suis limité par l'exportation de petits fichiers. J'ai besoin d'exporter un résultat de 60000 lignes environ et de 20 colonnes.

Je n'ai pas accès à PEAR.

Bref, je suis preneur de toute solution.

Avec PHPExcel : voici où j'en suis (ce code fonctionne pour de petites extractions)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
/** PHPExcel */
 
require_once 'ClassesPHPExcel/PHPExcel.php';
 
require_once ("include/ParametresConnexion.php");
$db = mysql_connect(SERVEUR, NOM,PASSE) or die(mysql_error());
mysql_select_db(BASE,$db) or die(mysql_error());
mysql_query("SET NAMES utf8");
 
$reqExcel = "SELECT * FROM maTable"; 
$resultat = mysql_query($reqExcel); 
 
$numoffields=mysql_num_fields($resultat);
$entete_champs =1;
 
function col2chr($a){
 
        $a=$a+1; 
		if($a<27){
            return strtoupper(chr($a+96));   
        }else{
            while($a > 26){
                $b++;
                $a = $a-26;               
            }                  
            $b = strtoupper(chr($b+96));   
            $a = strtoupper(chr($a+96));               
            return $b.$a;
        }
    } 
 
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
 
 
//$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
//$cacheSettings = array( ' memoryCacheSize ' => '4GB');
//PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
 
 
// Set properties
$objPHPExcel->getProperties()->setCreator("TOTO")
							 ->setLastModifiedBy("TOTO")
							 ->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 2007 openxml php")
							 ->setCategory("Test result file");
 
 
//Entête de colonne
 
for ($entete_champs=0; $entete_champs<$numoffields;$entete_champs++){
	$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($entete_champs,1, mysql_field_name($resultat,$entete_champs));
	$lettre_colonne = col2chr($entete_champs).'1';
	$lettre_colonne = $entete_champs.'1';
}
 
$i = 2; // on démarre à la ligne 2, car ligne 1 correspond au entete de colonne 
while($row = mysql_fetch_array($resultat))
{
  for ($j=0; $j<$numoffields;$j++){
   $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j,$i, $row[$j]);   
   }$i++;
}
 
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
 
// 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 (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

**buggen25** · 12/02/2012, 20h40

Bonjour,
Je pense que PHPExcel code en iso effectivement, donc pour le traitement des accents faut utiliser quelquechose qui ressemble a ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
$index = 1;
while($row = mysql_fetch_array($result))
 {
 $objPHPExcel->setActiveSheetIndex(0)
			 ->setCellValue('A'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Colonne1']))
			 ->setCellValue('B'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Colonne2']))
			 ->setCellValue('C'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Colonne3']))
			 ->setCellValue('D'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Colonne4']))
			 ->setCellValue('E'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Rue']))
			 ->setCellValue('F'.(string)$index, iconv("ISO-8859-1//TRANSLIT","UTF-8",$row['Colonne5']));
			 $index = $index + 1;
 }

**tavarlindar** · 12/02/2012, 20h54

Tout d'abord, merci à vous buggen25 pour votre réponse. Je me sens moins seul !

Le problème (à priori) n'est pas que PHPExcel http://www.phpexcel.net code ou non en iso, c'est qu'on se retrouve vite avec de problèmes de mémoire côté serveur. J'arrive parfaitement à exporter du chinois, russe et autres langues.

Il y a plein de post à ce sujet sur le site consacrée à cette class. Malheureusement (pour moi) c'est tout en anglais et cela dépasse mes compétences techniques.
A priori, beaucoup de personnes ont rencontré le même problème que moi.
Après que je code mal et que je n’optimise pas l'usage ce cette class, c'est certainement possible.
Là où je suis désespéré, c'est qu'avec mon premier exemple http://www.phpclasses.org/package/64...to-Excel-.html, j'arrive à exporter n'importe quoi, pas de limitation au niveau de la taille de la requête, mais cette class ne gère pas l'utf-8.
PHPExcel gère l'utf-8 mais ne permet pas d'exporter n'importe quelle requête (taille)

**tavarlindar** · 16/02/2012, 21h52

Up !

Avant de capituler, je pose une dernière fois la question à la communauté française :

Comment faites vous pour exporter vers excel un requête impliquant une base en utf-8 et un résultat conséquent (60000 lignes au moins).

Je n'ai rien trouvé à ce jour sur la toile ... Il doit bien avoir une solution.

Par avance mille merci à celui ou ceux qui pourront m'aider.

**glen1789** · 17/02/2012, 13h12

Bonjour,

Je n'ai pas eu ce problème personnellement car j'utilises PHPExcel avec des petits fichiers (moins de 1000 lignes), mais de ce que j'ai pu lire dans les formus le problème avec PHPExcel est le dépassement mémoire.
Ce lien donne de bonnes infos sur la consommation mémoire : http://phpexcel.codeplex.com/discuss...tName=phpexcel
Donc il existe quatres solutions:
- augmenter la limite mémoire de php (dans le php.ini sachant qu'elle est à 128 MB de base je crois et que ils ont l'air d'être dépassés pour 80000 cellules)
-il est possible de réduire l'utilisation de la mémoire en utilisant un principe de cache

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

-une combinaison des deux précédentes
-la méthode qui permet de traiter n'importe quelle taille de fichier mais qui prend un temps considérable, tout est expliqué ici : http://phpexcel.codeplex.com/discuss...tName=phpexcel

UTF-8 : export de MYSQL vers EXCEL via php ? [MySQL]

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager