Weird memory problem

Topics: User Forum
Nov 17, 2010 at 4:13 PM

Hi all. First of all I want to congratulate you on a great project.I've been using PHPExcel for a few months and worked great so far.

Lately however i had to export a large amount of data from a mysql database to a spreadsheet and if i select a large period of time ( the user selects a begin and end date for the data ) the script runs out of memory. I upgraded my hosting to a VPS server and increased the max memory to 256Mb. I calculated that 1day required about 50mb of memory (12x2000 entries), and so I was hoping that 4 days wouldn't be a problem.

The result was unexpected since i got the following error Fatal error: Out of memory (allocated 38010880) (tried to allocate 1572864 bytes) in /home/.../public_html/test/reports/excel/classes/PHPExcel/Worksheet.php on line 942

Different caching ... same result, same limit.

It's a weird limit and I can't figure out why. I've also enabled ini_set() to be sure and allocated 256M at the beggining of the script but got the same result. This is the code from the script :

<?php
include ('user_validator.php');
require_once 'classes/PHPExcel.php';
require_once 'classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("RDC")
    ->setLastModifiedBy("SMDC")
    ->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");

$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$begin_date = $_SESSION['ciel_report_begin_date'];
$end_date = $_SESSION['ciel_report_end_date'];
$location = $_SESSION['ciel_report_location'];

global $search_operators;
if ($location == 'incorrect') { header("Location: ../ciel_report.php"); }
if ($location == 'entire_company') { $search_operators = mysql_query("SELECT * FROM users WHERE user_type = 'operator' AND deleted = '0' ORDER BY name"); }
if (substr($location, 0, 1) == 'a') { $search_operators = mysql_query("SELECT * FROM users WHERE user_type = 'operator' AND area_id = '".substr($location,1)."' AND deleted = '0' ORDER BY name");}
if (substr($location, 0, 1) == 'o') { $search_operators = mysql_query("SELECT * FROM users WHERE id = '".substr($location,1)."' ORDER BY name");}

$count = 0;
$counter = 0;

while ($result_operator = mysql_fetch_array($search_operators)) {

for ($i = $begin_date; $i <= $end_date; $i++) {
$current_date = $i;

$search_cash_book = mysql_query("SELECT * FROM cash_book WHERE date = '".$current_date."' AND user_id = '".$result_operator['id']."'");
$result_cash_book = mysql_fetch_array($search_cash_book);

$cash_book_id = $result_cash_book['id'];
$formated_date = substr($current_date, 0, 4).''.substr($current_date, 5, 2).''.substr($current_date, 8, 2);
$counter += 1;

//aparate
        if ($result_cash_book['slot_machines_in'] <> 0) {
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['slot_machines_in_no'])
            ->setCellValue('F'.$count, $result_operator['association_account'])
            ->setCellValue('G'.$count, 'INCASARI APARATE (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['slot_machines_in'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['slot_machines_in_no'])
            ->setCellValue('F'.$count, '462.4')
            ->setCellValue('G'.$count, 'INCASARI APARATE (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['slot_machines_in'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI APARATE (ASOCIERE)');
        }
        if ($result_cash_book['slot_machines_out'] <> 0) {
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['slot_machines_out_no'])
            ->setCellValue('F'.$count, $result_operator['association_account'])
            ->setCellValue('G'.$count, 'PLATI PREMII APARATE (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['slot_machines_out'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['slot_machines_out_no'])
            ->setCellValue('F'.$count, '461.3')
            ->setCellValue('G'.$count, 'PLATI PREMII APARATE (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['slot_machines_out'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATI PREMII APARATE (ASOCIERE)');
        }
//rulete
        if ($result_cash_book['roulettes_in'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['roulettes_in_no'])
            ->setCellValue('F'.$count, $result_operator['association_account'])
            ->setCellValue('G'.$count, 'INCASARI RULETA (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['roulettes_in'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['roulettes_in_no'])
            ->setCellValue('F'.$count, '462.6')
            ->setCellValue('G'.$count, 'INCASARI RULETA (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['roulettes_in'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI RULETA (ASOCIERE)');
        }
        if ($result_cash_book['roulettes_out'] <> 0) { 
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['roulettes_out_no'])
            ->setCellValue('F'.$count, $result_operator['association_account'])
            ->setCellValue('G'.$count, 'PLATI PREMII RULETA (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['roulettes_out'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book['roulettes_out_no'])
            ->setCellValue('F'.$count, '461.6')
            ->setCellValue('G'.$count, 'PLATI PREMII RULETA (ASOCIERE)')
            ->setCellValue('H'.$count, $result_cash_book['roulettes_out'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATI PREMII RULETA (ASOCIERE)');
        }

//pariuri
$search_bets = mysql_query("SELECT * FROM bets WHERE date = '".$current_date."' AND user_id = '".$result_operator['id']."'");
$result_bets = mysql_fetch_array($search_bets);
       
        //sportive
        if ($result_bets['in_sport'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_sport_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'INCASARI PARIURI SPORTIVE')
            ->setCellValue('H'.$count, $result_bets['in_sport'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_sport_no'])
            ->setCellValue('F'.$count, '462.1')
            ->setCellValue('G'.$count, 'INCASARI PARIURI SPORTIVE')
            ->setCellValue('H'.$count, $result_bets['in_sport'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI PARIURI SPORTIVE');
        }
        //caini
        if ($result_bets['in_dogs'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_dogs_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'INCASARI CURSE CAINI')
            ->setCellValue('H'.$count, $result_bets['in_dogs'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_dogs_no'])
            ->setCellValue('F'.$count, '462.2')
            ->setCellValue('G'.$count, 'INCASARI CURSE CAINI')
            ->setCellValue('H'.$count, $result_bets['in_dogs'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI CURSE CAINI');
        }
        //live
        if ($result_bets['in_live'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_live_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'INCASARI PARIURI LIVE')
            ->setCellValue('H'.$count, $result_bets['in_live'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_live_no'])
            ->setCellValue('F'.$count, '462.5')
            ->setCellValue('G'.$count, 'INCASARI PARIURI LIVE')
            ->setCellValue('H'.$count, $result_bets['in_live'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI PARIURI LIVE');
        }
        //loto
        if ($result_bets['in_loto'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_loto_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'INCASARI PARIURI LOTO')
            ->setCellValue('H'.$count, $result_bets['in_loto'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_loto_no'])
            ->setCellValue('F'.$count, '462.8')
            ->setCellValue('G'.$count, 'INCASARI PARIURI LOTO')
            ->setCellValue('H'.$count, $result_bets['in_loto'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI PARIURI LOTO');
        }
        //reviste
        if ($result_bets['in_magazines'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_magazines_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'INCASARI VANZARI REVISTE')
            ->setCellValue('H'.$count, $result_bets['in_magazines'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['in_magazines_no'])
            ->setCellValue('F'.$count, '462.3')
            ->setCellValue('G'.$count, 'INCASARI VANZARI REVISTE')
            ->setCellValue('H'.$count, $result_bets['in_magazines'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'INCASARI VANZARI REVISTE');
        }
        //impozit
        if (($result_bets['in_sport_tax'] + $result_bets['in_dogs_tax'] + $result_bets['in_live_tax'] + $result_bets['in_loto_tax']) <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, '')
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'IMPOZIT RETINUT PREMII')
            ->setCellValue('H'.$count, $result_bets['in_sport_tax'] + $result_bets['in_dogs_tax'] + $result_bets['in_live_tax'] + $result_bets['in_loto_tax'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, '')
            ->setCellValue('F'.$count, '446.3')
            ->setCellValue('G'.$count, 'IMPOZIT RETINUT PREMII')
            ->setCellValue('H'.$count, $result_bets['in_sport_tax'] + $result_bets['in_dogs_tax'] + $result_bets['in_live_tax'] + $result_bets['in_loto_tax'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'IMPOZIT RETINUT PREMII');
        }
        //plati sportive
        if ($result_bets['out_sport'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_sport_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'PLATI PREMII PARIURI SPORTIVE')
            ->setCellValue('H'.$count, $result_bets['out_sport'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_sport_no'])
            ->setCellValue('F'.$count, '461.1')
            ->setCellValue('G'.$count, 'PLATI PREMII PARIURI SPORTIVE')
            ->setCellValue('H'.$count, $result_bets['out_sport'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATI PREMII PARIURI SPORTIVE');
        }
        //plati caini
        if ($result_bets['out_dogs'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_dogs_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'PLATI PREMII CURSE CAINI')
            ->setCellValue('H'.$count, $result_bets['out_dogs'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_dogs_no'])
            ->setCellValue('F'.$count, '461.2')
            ->setCellValue('G'.$count, 'PLATI PREMII CURSE CAINI')
            ->setCellValue('H'.$count, $result_bets['out_dogs'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATI PREMII CURSE CAINI');
        }
        //plati live
        if ($result_bets['out_live'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_live_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'PLATA PREMII PARIURI LIVE')
            ->setCellValue('H'.$count, $result_bets['out_live'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_live_no'])
            ->setCellValue('F'.$count, '461.5')
            ->setCellValue('G'.$count, 'PLATA PREMII PARIURI LIVE')
            ->setCellValue('H'.$count, $result_bets['out_live'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATA PREMII PARIURI LIVE');
        }
        //plati loto
        if ($result_bets['out_loto'] <> 0) {
        $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_loto_no'])
            ->setCellValue('F'.$count, $result_operator['bets_account'])
            ->setCellValue('G'.$count, 'PLATA PREMII PARIURI LOTO')
            ->setCellValue('H'.$count, $result_bets['out_loto'])
            ->setCellValue('I'.$count, 'C')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
    $count += 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets['out_loto_no'])
            ->setCellValue('F'.$count, '461.8')
            ->setCellValue('G'.$count, 'PLATA PREMII PARIURI LOTO')
            ->setCellValue('H'.$count, $result_bets['out_loto'])
            ->setCellValue('I'.$count, 'D')
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, 'PLATA PREMII PARIURI LOTO');
        }
   
$search_cash_book_transfers = mysql_query("SELECT * FROM transfers WHERE source = 'cash_book' AND source_id = '".$result_cash_book['id']."'");
$verify_cash_book_transfers = mysql_num_rows($search_cash_book_transfers);
if ($verify_cash_book_transfers <> 0){
while ($result_cash_book_transfers = mysql_fetch_array($search_cash_book_transfers))
    {
      $primary_account = $result_operator['primary_account'];
     
      $count += 1; $type = '';
      if ($result_cash_book_transfers['transfer_subtype'] == 'out') {$type = 'C';} else {$type = 'D';}
      $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book_transfers['transfer_no'])
            ->setCellValue('F'.$count, $primary_account)
            ->setCellValue('G'.$count, transfer_names($result_cash_book_transfers['transfer_type'],$result_cash_book_transfers['transfer_subtype']).' '.$result_cash_book_transfers['explanation'])
            ->setCellValue('H'.$count, $result_cash_book_transfers['value'])
            ->setCellValue('I'.$count, $type)
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
           
      $count += 1; $type = '';
      if ($result_cash_book_transfers['transfer_subtype'] == 'out') {$type = 'D';} else {$type = 'C';}
      $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_cash_book_transfers['transfer_no'])
            ->setCellValue('F'.$count, transfer_accounts($result_cash_book_transfers['transfer_type']))
            ->setCellValue('G'.$count, transfer_names($result_cash_book_transfers['transfer_type'],$result_cash_book_transfers['transfer_subtype']).' '.$result_cash_book_transfers['explanation'])
            ->setCellValue('H'.$count, $result_cash_book_transfers['value'])
            ->setCellValue('I'.$count, $type)
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, transfer_names($result_cash_book_transfers['transfer_type'],$result_cash_book_transfers['transfer_subtype']).' '.$result_cash_book_transfers['explanation']);
       
    }}
   
$search_bets_transfers = mysql_query("SELECT * FROM transfers WHERE source = 'bets' AND source_id = '".$result_bets['id']."'");
$verify_bets_transfers = mysql_num_rows($search_bets_transfers);
if ($verify_bets_transfers <> 0){
while ($result_bets_transfers = mysql_fetch_array($search_bets_transfers))
    {
      $primary_account = $result_operator['primary_account'];
     
      $count += 1; $type = '';
      if ($result_bets_transfers['transfer_subtype'] == 'out') {$type = 'C';} else {$type = 'D';}
      $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets_transfers['transfer_no'])
            ->setCellValue('F'.$count, $primary_account)
            ->setCellValue('G'.$count, transfer_names($result_bets_transfers['transfer_type'],$result_bets_transfers['transfer_subtype']).' '.$result_bets_transfers['explanation'])
            ->setCellValue('H'.$count, $result_bets_transfers['value'])
            ->setCellValue('I'.$count, $type)
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, '')
            ->setCellValue('L'.$count, 'CASA IN LEI');
           
      $count += 1; $type = '';
      if ($result_bets_transfers['transfer_subtype'] == 'out') {$type = 'D';} else {$type = 'C';}
      $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$count, $counter)
            ->setCellValue('B'.$count, 'RC')
            ->setCellValue('C'.$count, $formated_date)
            ->setCellValue('D'.$count, '')
            ->setCellValue('E'.$count, $result_bets_transfers['transfer_no'])
            ->setCellValue('F'.$count, transfer_accounts($result_bets_transfers['transfer_type']))
            ->setCellValue('G'.$count, transfer_names($result_bets_transfers['transfer_type'],$result_bets_transfers['transfer_subtype']).' '.$result_bets_transfers['explanation'])
            ->setCellValue('H'.$count, $result_bets_transfers['value'])
            ->setCellValue('I'.$count, $type)
            ->setCellValue('J'.$count, '')
            ->setCellValue('K'.$count, $result_operator['buget_code'])
            ->setCellValue('L'.$count, transfer_names($result_bets_transfers['transfer_type'],$result_bets_transfers['transfer_subtype']).' '.$result_bets_transfers['explanation']);
       
    }}
}}

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(75);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(75);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('XIMPORT');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

header("Location: ../ciel_report_result.php");
?>

Coordinator
Nov 18, 2010 at 8:23 AM

"Fatal error: Out of memory (allocated 38010880) (tried to allocate 1572864 bytes)" gives an indication of the memory limit, because 38010880 bytes + 1572864 bytes exceeds that limit. In this case 39,583,744 bytes, or about 37.75MB. This suggests that your PHP memory limit is less than that rather than 256MB. phpinfo() or php.ini should tell you the actual limit. Some ISP's disable the ability to increase PHP memory, so you could try doing a phpinfo() after setting the memory limit to see if it has changed.

Nov 18, 2010 at 7:53 PM

Thanks for the answer, however i've checked php.ini, and info.php and they both say that memory limit is 256mb, got it up to 512mb but got the exact same error. The provider double checked all the settings and found no reason why the script should run out at 38mb ... I'm just lost at this point :)

Is there a way i can set the memory limit within the PHPExcel classes?

Coordinator
Nov 18, 2010 at 8:57 PM
pallsony wrote:

Thanks for the answer, however i've checked php.ini, and info.php and they both say that memory limit is 256mb, got it up to 512mb but got the exact same error. The provider double checked all the settings and found no reason why the script should run out at 38mb ... I'm just lost at this point :)

Is there a way i can set the memory limit within the PHPExcel classes?

All I can say is what I can interpret from the error message, which suggests the 37.75MB. PHPExcel is a PHP script, so it is subject to the memory limitations of PHP, it has no independent memory management capabilities that allows it to exceed the PHP limits. You could modify PHPExcel to include a call to ini_set('memory_limit', '512M'); but it would be no different to issuing the same function call within your own script.

You could also enable cell caching within PHP, which makes better use of the available memory, but at a cost in speed. This is described in the Documentation, and needs to be set before you instantiate your PHPExcel object.

Nov 30, 2010 at 3:42 PM

Hey, i've just talked to the server administrator and he said that the problem is not from the memory limit but from the file size of the generated file. He said that when the resulted .xlsx file reaches about 20mb the script stops. What's weird is that it should be more than a few mb and i've tripled checked for any infinite loops and there aren't any. Is there a limit to the maximum file size resulted, or perhaps for the space required to generate such a file?

Coordinator
Nov 30, 2010 at 9:37 PM
pallsony wrote:

Is there a limit to the maximum file size resulted, or perhaps for the space required to generate such a file?

It would seem unusual for a disk size limitation to trigger a memory error, but I'm still completely mystified by this. You might perhaps try with the latest SVN code, which reduces the memory overheads.

PHPExcel does not impose any restrictions on the size of files saved. Do you have any disk quotas enabled that might impose such a restriction?