CODEWITHSUNDEEP
phpzend-framework
opHasNoName
opHasNoName

Reputation: 20736

PHPExcel Memory Problems - any more ideas?

Iam trying to write an Excel2007 File with Zend Framework and PHPExcel. I know i could increase the PHP Memory Limit.. but is there no other way?

This is my Code so far, it works well with 5000 Rows and 77 Columns but i need 12000 rows :-) Memory is set to 128MB

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

    $db = Zend_Db_Table_Abstract::getDefaultAdapter();
    $phpExcel = new PHPExcel();

    // set headers
    $select = $db->select();
    $select->from('IMPORT')->limit(1);
    $data = $select->query()->fetchAll();
    $columns = array_keys($data[0]);
    $phpExcel->setActiveSheetIndex(0);
    $colCNT = 1;

    foreach ($columns as $column) {
        $letter = Nc_Utils::getNameFromNumber($colCNT);
        $phpExcel->getActiveSheet()->SetCellValue($letter . '1', $column);
        $colCNT++;
    }

    unset($select);
    unset($data);

    $sql = 'SELECT * FROM IMPORT LIMIT 7000';
    $stmt = $db->query($sql);

    $rowCNT = 2;
    while($rows = $stmt->fetch()){
        $phpExcel->getActiveSheet()->fromArray($rows, null, 'A'.$rowCNT);
        $rowCNT++;
    }  

    unset($rowCNT);
    unset($select);
    unset($db);

    // MEMORY USAGE = 4 MB!

    $phpExcelWrite = new PHPExcel_Writer_Excel2007($phpExcel);
    $phpExcelWrite->setUseDiskCaching(true);
    $phpExcelWrite->save(str_replace('.php', '.xls', __FILE__));

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 13878925 bytes) in /Applications/MAMP/htdocs/phpexcel/library/PHPExcel/Shared/XMLWriter.php on line 102

Upvotes: 2

Views: 8533

Answers (4)

albertski
albertski

Reputation: 2632

I ran into some memory problems and updating to the latest version of PHPExcel improved performance for me.

Upvotes: 1

velcrow
velcrow

Reputation: 6516

Have you tried PHP_XLSXWriter?

It is a simple, lightweight XLSX writer library in php. Though not as fully featured as PHP_Excel, it was designed to solve some of the memory and CPU usage issues, often faced by PHP_Excel data exports.

Upvotes: 1

Ivan Hušnjak
Ivan Hušnjak

Reputation: 3503

I have tried to use PhpExcel but found out the same problems as you. I've tried all the proposals to reduce memory consumption (the link Klinky posted in his answer) but got only 25-30% improvement. I even thought about creating CSV output instead of excel.

Now I am using PEAR's Spreadsheet_Excel_Writer and it works fine for me with roughly the same amount of data (11k rows 62 columns), though it takes quite some time to generate the file.

It is not as powerful as PhpExcel, but as I see in your example you are not using it to style cells and write functions, right?

Example:

$workbook = new Spreadsheet_Excel_Writer();
$workbook->send($filename . '.xls');
$workbook->setVersion(8); // excel 8
// Create worksheet
$worksheet =& $workbook->addWorksheet('submissions');
$worksheet->setInputEncoding('UTF-8');
// Write some data on Sheet 1
$row = 0;
$worksheet->write($row, 0, 'Poster #ID');
$worksheet->write($row, 1, 'Surname');
$worksheet->write($row, 2, 'Firstname');
$worksheet->write($row, 3, 'Country');
$worksheet->write($row, 4, 'E-mail');
$worksheet->write($row, 5, 'All authors');
$worksheet->write($row, 6, 'Institutions');
$worksheet->write($row, 7, 'Abstract topic');
$worksheet->write($row, 8, 'Abstract title');
foreach ($records as $rec_id => $rec_data) {
    $row++;
    $worksheet->write($row, 0, 'P-' . $rec_data['id']);
    $worksheet->write($row, 1, $rec_data['submitter_surname']);
    $worksheet->write($row, 2, $rec_data['submitter_firstname']);
    $worksheet->write($row, 3, $rec_data['submitter_country']);
    $worksheet->write($row, 4, $rec_data['submitter_email']);
    $worksheet->write($row, 5, $rec_data['authors']);
    $worksheet->write($row, 6, $rec_data['institutions']);
    $worksheet->write($row, 7, $rec_data['abstract_topic']);
    $worksheet->write($row, 8, $rec_data['abstract_title']);
}
$workbook->close(); // output

Upvotes: 1

Klinky
Klinky

Reputation: 2092

The PHPExcel website has a discussion on possible ways to reduce memory consumption:

Upvotes: 3

Related Questions