0

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.

CrisAlfie
  • 111
  • 13
  • why don't you import the excel file into a database, this will get rid of your issue easily. Otherwise, keep increasing the mem to accommodate your giant object size, but this is not the right solution, just a solution – ATechGuy Mar 10 '17 at 20:42
  • I am seeking to upload the excel files to a SQL database engine (Not defined yet), but still data would have to entered with some changes or filters which I still would require to be able to edit or change using PHP. – CrisAlfie Mar 10 '17 at 22:05
  • ok. my advice would be this: dump the excel into a db as is, once in the db then you can use php to edit and filter any needed data. In the mean time you can just increase your mem till it works, but this is not a good solution and may bog down your server. – ATechGuy Mar 10 '17 at 22:08

2 Answers2

1

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.

Onur Kucukkece
  • 1,708
  • 1
  • 20
  • 46
1

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!

Community
  • 1
  • 1
Adrien
  • 1,929
  • 1
  • 13
  • 23