CODEWITHSUNDEEP
phpphpexcel
CrisAlfie
CrisAlfie

Reputation: 111

Increase max size of memory allocation with PHPExcel

I am currently wanting to just read data from an xslx excel file, only display of data is what I am currently seeking but the excel files will always contain 30k+ rows. My current code is the following:

<?php
ini_set('memory_limit', '1024M'); // or you could use 1G
/**
 * PHPExcel
 *
 * Copyright (c) 2017
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    ##VERSION##, ##DATE##
 */

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('America/Guatemala');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('America/Guatemala');

/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/phpexcel/PHPExcel/IOFactory.php';

require_once 'phpexcel/PHPExcel.php';
$before = memory_get_usage(true);
$xls_reader = PHPExcel_IOFactory::load('excelfile.xlsx');
$after = memory_get_usage(true);
unset($xls_reader);
printf("usage before loading xlsx file %s\nusage after loading xlsx file %s\ndelta usage %s", $before, $after, $after - $before);

?>

Please DO NOT mark this question as duplicate because I have already tried with every answer posted on previous questions similar to this one but none seem to work.

I have tried with cell caching, and also with ini_set('memory_limit', '1024M'); and with ini_set('memory_limit', '-1'); and I still get the exact same error Fatal error: Out of memory (allocated 2097152) (tried to allocate 132604370 bytes) in C:\MAMP\htdocs\dclean\phpexcel\PHPExcel\Reader\Excel2007.php on line 315.

When changing the memory_limit to 1024, allocated amount of data changes but same error is displayed.

All I am trying to achieve is a way to work with data from excel, but the biggest challenge at the moment is being able to load 30K+ rows from an excel file. I am currently using PHPExcel but any alternative is welcome as long as the "working with a lot of rows" condition is present.

Thanks in advance for your answers.

Upvotes: 0

Views: 2507

Answers (2)

Adrien
Adrien

Reputation: 1947

Look at this post for PHPExcel alternatives: https://stackoverflow.com/a/3931142/4499987

Since you're looking for a memory efficient alternative, I recommend you using Spout (https://github.com/box/spout). It only uses 10MB, regardless of the spreadsheet size and reading will be way faster than with PHPExcel!

Upvotes: 1

Onur Kucukkece
Onur Kucukkece

Reputation: 1758

If possible I would suggest you to check some tools for converting excel data to json like Excel to JSON

You would have lot more chance with reading json data.

Hope it helps.

Upvotes: 1

Related Questions