PHP script generating an Excel file taking too much memory

Question

I have a PHP script running using YII framework that goes through the database and creates and Excel sheet for export purposes. The data in the database is around 15K records now and increasing. The issue is that the script consumes a lot of memory and the excel file generated becomes around 45 MB. I can try to zip the file to compress it to the size which i dont think would be an issue but the script using more memory is my concern.

One way I was thinking of solving it was to process only 5k records at one time and then add all of them up to create the Excel file. I am not sure if this would be that way to go.

    public function actionExport() {

        Yii::import('ext.phpexcel.XPHPExcel');
        XPHPExcel::init();


// Create new PHPExcel object
        $objPHPExcel = new PHPExcel();



// Adding data
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'SN')
                ->setCellValue('B1', 'Full Name')
                ->setCellValue('C1', 'Gender')
                ->setCellValue('D1', 'Date Of Birth')
                ->setCellValue('E1', 'Products')


        $merchantModels = Merchant::model()->findAll();
        $i = 2; //starting after the header row
        foreach ($merchantModels as $model) {
            $merchantPModels = MerchantProducts::model()->findAll(array(
                'condition' => 'merchantID = :merchantID',
                'params' => array(':merchantID' => $model->id),
            ));
            $productStr= "";
            $pStr ="";
            $x = 0; // counter for the merchnat product Loop 

            foreach ($merchantModels as $MPmodel) {
                if($MPmodel->product !== null){
                    $pStr = $MPmodel->product->productName;
                }else{
                    $pStr = "";
                }


                if ($x == 0) { // First Object
                    $productStr = $pStr;
                }else{
                   $productStr = $productStr . "," . $pStr; 
                }

                $x++;
            }
             * 
             */


            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $i, $i - 1)
                    ->setCellValue('B' . $i, $model->fullName)
                    ->setCellValue('C' . $i, $model->gender)
                    ->setCellValue('D' . $i, $model->dateOfBirth)                        
                    ->setCellValue('E' . $i, $productStr);
            $i++;
        }
// Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Merchants');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $date = new DateTime('NOW');
        $date = $date->format('Y-m-d H:i:s'); // for example

        $this->render('export', array(
            'excelModel' => $objPHPExcel,
            'date' => $date,
        ));
    }

I think you need to be much more precise to allow us to help you. For instance: can you show us some code on how you are generating those files right now? — jsalonen, Aug 01 '13 at 12:51
We need more info, some code would be good. but without knowing if you are using libraries or frameworks to generate files, we cannot tell you what to change. — Anigel, Aug 01 '13 at 12:59
What about using CSV instead of Excel? It would probably save up memory. — jsalonen, Aug 01 '13 at 13:04
The easiest way by far to cut down the memory usage is to create a csv file as you can write out to that one line at a time and not need to store large complex data structures in memory. CSV files can be used in excel without problems, however you could convert the csv file to an excel file in one operation if required which could save on memory usage http://stackoverflow.com/questions/3874840/csv-to-excel-conversion — Anigel, Aug 01 '13 at 13:33
You are running an extra DB query for each row. That is terrible for performance - look into `JOIN`s and `GROUP_CONCAT` , which should allow you to fetch all the data you need in a single query (or at least to slice the list of merchants into decent sized pieces). If you still need more performance, skip Active Record and write a manual SQL query to fetch the data you need as plain arrays. — DCoder, Aug 01 '13 at 16:06
Now that I look at it again, it looks even worse than that... You're running two queries for each merchant, not one. 1) `$merchantPModels = MerchantProducts::model()->findAll(...)` runs a DB query to find all products. 2) unless you have some advanced trickery, `$MPModel->product` probably makes another DB query to fetch the product related to `$MPModel`. (Also, I would guess that `foreach ($merchantModels as $MPmodel)` needs to iterate over `$merchantPModels`, not `$merchantModels`.) You can see this in the application log, if you enable query logging. — DCoder, Aug 01 '13 at 16:59

score 1 · Answer 1 · answered Aug 02 '13 at 07:38

Instead of Merchant::model()->findAll() you should use a CDbDataReader. You will loose the convenience of ActiveRecord but it wont load all records into memory like you did before.

A data reader is returned from CDbCommand::query(). So you could do something like:

$command   = Yii::app()->db->createCommand();
$merchants = $command->query('SELECT * FROM merchants');
foreach($merchants as $merchant) {
    // Add to excel here
}

score 0 · Answer 2 · answered Aug 01 '13 at 13:04

0

I had also worked on a project to export data from MySQL to excel. In my generated XL sheet I have around 30 tabs and their are some complex formulas for different cell. I am using PHPExcel for that. But in my case I was generating the excel as a cron job because it was taking much time.

Here if you have some complex excel template then you can also save a designed template on server and then by the help of reader and writer class of PHPExcel you can read that template and write you data on it and save it in a new file.

I think this information will help you. Please let me know if you want any further clarification.

answered Aug 01 '13 at 13:04

Deepak Biswal

4,280
2
20
37

I am using phpexcel at the moment – BinCode Aug 01 '13 at 13:14
Great! So you can try to read from a excel template and write to it. That way it will be faster and you don't have to think about the formatted cells. – Deepak Biswal Aug 01 '13 at 13:17
So what you are suggesting is to tweak the code to load a template first and then add the new data?BUt the thing is my excel document is not complex at the moment it just has a one row header and all the other fields are populated from the database. – BinCode Aug 01 '13 at 13:24

PHP script generating an Excel file taking too much memory

2 Answers2