How to export a big result from a mysql query utf-8 to excel ?

Topics: Developer Forum, User Forum
Feb 13, 2012 at 2:03 PM


Well, first, sorry for my English and sorry also for my low level in program.

I post here, because it's my last chance to find a solution. I have spent days and days on it.

I'm looking for solution that will export a mysql query formated in utf-8 into Excel.

My code (below) is working for small result. But when I try to export a result of 28 columns x 60000 rows, it doesn't work.

May be that my code is not the best also. I know that there are many post about lack of memory also. Before, 1 year ago, as far as I can remember, when the result was too big, I had a error message about memory. Today, the message is that The file cannot be found or nothing appears.

Anyway, is someone have a general code that take a query in argument that works well ?

The server uses PHP Version 5.2.5. I cannot modify parameters.



/** 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 My TABLE WHERE id_contact<'2500' ";

$resultat = mysql_query($reqExcel);


$entete_champs =1;


function col2chr($a){



           return strtoupper(chr($a+96));  


           while($a > 26){


               $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("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 2007 openxml php")

                                                                                                       ->setCategory("Test result file");



// Add some data


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




// Rename sheet



// Set active sheet index to the first sheet, so Excel opens this as the first sheet



// Redirect output to a client’s web browser (Excel5)

header('Content-Type: application/');

header('Content-Disposition: attachment;filename="01simple.xls"');

header('Cache-Control: max-age=0');


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





Feb 13, 2012 at 2:46 PM

are you aware that ph 5.2 has been end of life for some time now?


Check your error logs, because I suspect it is still an out of memory error. With this amount of data, I would suspect your php script to use somewhere around 8GB of memory.

Feb 13, 2012 at 3:11 PM
Edited Feb 13, 2012 at 9:14 PM

Hello Borft,

Well, I have no choice about php version.  Even if you would suspect my php script to use somewhere around 8GB of memory, what can I do ?

I try to add this code, but it doesn't improve the problem :

// Initiate cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '8GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Maybe I can ask to the manager of the php server to do something, but what ? What can I ask ?

Feb 14, 2012 at 11:00 PM
Edited Feb 14, 2012 at 11:09 PM

You must enable cell caching before instantiating the PHPExcel object.. the lines commented out in your original code appear after the

$objPHPExcel = new PHPExcel();



When working with very large files, phpTemp caching may not be sufficient, because it stil retains an index to the cells "in memory". APC, WinCache or memCache only hold a "cell exists" index "in memory", so they offer the best memory usage, though slower execution speed. The latest SVN code introduces SqLite as a caching option, which is the best memory usage because it holds no "in memory" index at all, but again at a cost in execution speed. There's a summary of some of the caching methods at the bottom of this thread.

Note that your col2chr($a) function is already available in PHPExcel (and faster when columns exceed Z) as PHPExcel_Cell::stringFromColumnIndex($a)