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

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

Hello,

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

$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("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';

               //$objPHPExcel->getActiveSheet()->getStyle($lettre_colonne)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

}

 

$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;

 

 

Feb 13, 2012 at 1: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 2:11 PM
Edited Feb 13, 2012 at 8: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 ?

Coordinator
Feb 14, 2012 at 10:00 PM
Edited Feb 14, 2012 at 10: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();

line.

 

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)