Memory Problem

Topics: Developer Forum, User Forum
Jun 6, 2011 at 1:34 AM
Edited Jun 6, 2011 at 5:11 PM

Hi all,

 

First, lets me say thanks for the great work on PHPExcel !! It sure helped me a lot.

 

Now, i´m really in need os some assitance: im creating an sort of backup routine for a project, need to pick some data on the DB and write do a XLS document.I limited the query to 500 entries at a time, what means that i have to put 500 lines per time on the worksheet. The great problem is when  load the file: even with a filter telling the chunk i want to work, it look like that returns the entire table. So, even limiting the numbers of line, i keep getting memory leak everytime, and i only have about 40 MB to work with this script.

 

So i am asking if there is a workaround this problem. I searched this forum for a solution, encountered how to create a filter, but everytime i call the load method, it keeps returning the entire worksheet.

I´ll post the part of code that i use the class:

//this part i found in this forum, if i recall it right. Credits to the original poster. this part is the filter creation

class MyReadFilter implements PHPExcel_Reader_IReadFilter{
        private $min;
        private $max;
        public function setRows($startRow, $chunkSize) {
            $this->min    = $startRow;
            $this->max      = $this->min + $chunkSize;
        }     
        public function readCell( $column, $row, $worksheetName = '') {
            if ($row == 1 || $row >= $this->min || $row <= $this->max) {
                return true;
        }
        return false;
    }
    }

//... and now my code, adapting the code from the same source

// $k, in this code, is a iterator that I use to calculate position, the "500" hardcoded in the code below is my chunk per iteration

                $objReader = PHPExcel_IOFactory::createReader("Excel5");
                $worksheetNames = $objReader->listWorksheetNames("./excel/$file_name.xls");
                $objReader->setLoadSheetsOnly($worksheetNames[0]);

                // here is the delimiter of the chunk of data i want to manipulate, write in the worksheet
                $chunkFilter->setRows(((($k-1)*500)+2),(((($k-1)*500)+2)+499));
                $objReader->setReadFilter($chunkFilter);
                $objReader->setReadDataOnly(true);
                $excel = $objReader->load("./excel/$nome_arquivo.xls");

Any help is greatly appreciated, thanks in advance !!

 

PS: The version im working with is 1.7.6, on PHP 5.3 -  Sorry for the lack of information

Jun 6, 2011 at 5:21 PM
Edited Jun 6, 2011 at 5:31 PM

Here is my implementation of the read filter using a custom method I added to the readers.

http://phpexcel.codeplex.com/discussions/259971

This implementation reads the current maximum memory to estimate the chunk size.

Addendum: I just realized that you are creating spreadsheets instead of reading them. My implementation only works with reading spreadsheets.

- Christopher Mullins

Jun 6, 2011 at 7:41 PM

Just a heads up, 40MB is not valid for this library. I was battling this earlier, and for large workbooks I have assigned 500MB and upto 1GB of RAM for the script when it needs it. Just assign more RAM, only sollution.

Jun 7, 2011 at 1:42 AM

I read your code, and gonna make sure to use it sometime to test and so. Thanks for the reply and for the ( great ) example about reading the worksheet.

 

Best Regards

Jun 7, 2011 at 1:44 AM

About the RAM, gonna contact the web host and see what i can do, but i think that i can´t do much... Thanks for the reply, gonna have it in consideration.