Write a new file if the limit is reached or disconnect/unset (memory problem)

Aug 24, 2012 at 10:04 PM
Edited Aug 24, 2012 at 10:37 PM

Hello,

I've to write more than 370 columns and 7000 cells into a file so I've a memory problem.

Maybe is there a solution that automatically write a new file or a new worksheet if the limit of 2000 cells are reached and  a new file for the next 2000 starts.

Or is there another solution???

May with disconnect but how should I implement it into my code ??

$objPHPExcel->disconnectWorksheets();
     unset($objPHPExcel);

Below my current code which I use but sometimes I've a memory problem.

Please help me thank you.

 

<?php

/** require the PHPExcel file 1.0 */
    require 'Classes/PHPExcel.php';

/** Set Memory Limit 1.0 */
    ini_set("memory_limit","1000M");

/** Caching to discISAM 1.0*/
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 'dir'  => '/usr/local/tmp'
                      );
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

/** connection with the database 1.0 */
    $dbhost = "localhost";
    $dbuser = "user";
    $dbpass = "pw";
    $dbname = "mydb";
    mysql_connect($dbhost,$dbuser,$dbpass);
    mysql_select_db($dbname);

/** Query 1.0 */
    $query = "SELECT * FROM tblpsrmonth";

    if ($result = mysql_query($query) or die(mysql_error())) {
/** Create a new PHPExcel object 1.0 */
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()->setTitle('Technical Data');
   }  
  
/** Loop through the result set 1.0 */
    $rowNumber = 1;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
          $col++;
       }
       $rowNumber++;
}
   
/** Create Excel 2007 file with writer 1.0 */
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Technical.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
exit;

?>
Coordinator
Aug 25, 2012 at 1:11 AM
frank1973 wrote:

Hello,

I've to write more than 370 columns and 7000 cells into a file so I've a memory problem.

Or is there another solution???

The only real solution is either to increase your memory, or to use cell caching, or both.

The drawback with a pure PHP script for writing Excel workbooks is that it's subject to the memory limitations defined in php.ini. Cell caching is one way of reducing the effect of that limitation, but the limit is still there.

frank1973 wrote:

Hello,

I've to write more than 370 columns and 7000 cells into a file so I've a memory problem.

Maybe is there a solution that automatically write a new file or a new worksheet if the limit of 2000 cells are reached and a new file for the next 2000 starts.

There isn't any automatic solution to do this. If you simply switched to a new worksheet, it would still use PHP memory for that sheet, although each sheet is separately cached when you're using cell caching. If you change to a different workbook, then you can clear the first workbook from memory before creating a second blank workbook and continuing there.

 

Aug 25, 2012 at 9:50 AM

Hello Mark,

the second solution seems for me the best.

But how to do it to change to a different workbook, that I can clear the first workbook from memory before creating a second blank workbook and continuing there.

Please help me how to implement it into my code.

Thanks.