Problem while reading large size .xls and .xlsx file

Topics: Developer Forum
Feb 19, 2015 at 2:31 PM
Edited Feb 20, 2015 at 7:04 AM
I am having problem while reading 3Mb data .xlsx file and same for 7Mb data .xls file, Is there any size limitations while reading file???
In my excel, I have 30,000 rows and 36 rows. Is there any solutions so that I can read up to 1 lack records or more then that..
In my project I have to import 10 lack records, but my code is not working for more than 29000 records.
up till 29000 records my code works good on my local, but greater then 29000 records stop working.
And also reading 29000 records takes too much time may be 25 min.
Can any one please give me solutions why this happen so????
And what should I do to resolve this?
below is my code, can any one suggest me what is wrong in my code????
Here is my code:
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
/ Set Include path to point at the PHPExcel Classes folder /
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');

/ Include PHPExcel_IOFactory /
include 'Classes/PHPExcel/IOFactory.php';

$inputFileName = 'files/30000rows.xls';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

/* Define a Read Filter class implementing PHPExcel_Reader_IReadFilter /
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
private $_startRow = 0;
private $_endRow = 0;
/**  Set the list of rows that we want to read  */
public function setRows($startRow, $chunkSize) {
    $this->_startRow    = $startRow;
    $this->_endRow      = $startRow + $chunkSize;
public function readCell($column, $row, $worksheetName = '') {
    if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
        return true;
    return false;
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
/ Create a new Reader of the type defined in $inputFileType /
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
echo '<hr />';
/ Define how many rows we want to read for each "chunk" /
$chunkSize = 1000;
//total rows in excel
$spreadsheetInfo = $objReader->listWorksheetInfo($inputFileName);
$totalRows = $spreadsheetInfo[0]['totalRows'];
/ Create a new Instance of our Read Filter /
$chunkFilter = new chunkReadFilter();
/ Tell the Reader that we want to use the Read Filter that we've Instantiated /
/ Loop to read our worksheet in "chunk size" blocks /
for ($startRow = 2; $startRow <= $totalRows; $startRow += $chunkSize) {
echo "in for loop<br>";
echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
/**  Tell the Read Filter, the limits on which rows we want to read this iteration  **/


$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize '  => '1000MB'
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);


$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
    die($cacheMethod . " caching method is not available" . EOL);
echo date('H:i:s') , " Enable Cell Caching using " , $cacheMethod , " method" , EOL;

/**  Load only the rows that match our filter from $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestColumn = $objWorksheet->getHighestColumn();
$sheetData = $objWorksheet->rangeToArray('A'.$startRow.':'.$highestColumn.($startRow + $chunkSize-1),null, false, false, true);
echo '<pre>';
echo '<br /><br />';
Really it is very much urgent for me.
please help me if any one can..
I have tried all possible solutions for this.
I will be very grateful if any one can help me.
Thanks in advance.