Reading the first row of a large excel file takes to much time

Topics: Developer Forum
Aug 26, 2012 at 1:35 PM

Hello all, Im trying to read the first row of a large excel file (4Mb). The file only have 18 columns and 18119 rows (it can have more rows). This is my code:

 

require_once sfConfig::get('sf_lib_dir') . '/vendor/PHPExcel/PHPExcel/IOFactory.php';

$objPHPExcel = PHPExcel_IOFactory::load(sfConfig::get('sf_upload_dir') . '/' . $filename);
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();

$list = array();
$row = 1;    
$columnsToRead = 18; 

for ($column = 0; $column < $columnsToRead; $column++) {
  $value = $objWorksheet->getCellByColumnAndRow($column, $row)->getValue();
   $list[$column] = $value;
}
 ...

  But it takes a lot of time loading the file. Increasing the memory of php is not an alternative. Is there any efficent way to achive this?. I only want the first row, the rest of the data I dont care because I will store the file and I will process it with java. 

 

Thanks in advance.

Aug 26, 2012 at 6:06 PM
Edited Aug 26, 2012 at 6:44 PM

I tried with this:

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
	private $_startRow = 0;

	private $_endRow = 0;

	private $_columns = array();

	public function __construct($startRow, $endRow, $columns) {
		$this->_startRow	= $startRow;
		$this->_endRow		= $endRow;
		$this->_columns		= $columns;
	}

	public function readCell($column, $row, $worksheetName = '') {
		if ($row >= $this->_startRow && $row <= $this->_endRow) {
			if (in_array($column,$this->_columns)) {
				return true;
			}
		}
		return false;
	}
}

$inputFileName = './sampleData/ejemplo.xls';
$filterSubset = new MyReadFilter(1,1,range('A','O'));
$objReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
$objReader->setReadFilter($filterSubset);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet(0);

$list = array();
$row = 1;  
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$columnsToRead = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($column = 0; $column < $columnsToRead; $column++) {
     $value = $objWorksheet->getCellByColumnAndRow($column, $row)->getValue();
     $list[$column] = $value;
     echo $value.'<br>';
}

But it takes 15seg to read it, thats a lot of time for a web user. Is there another thing that I can use for minimize the time of load?. 

Any help will be apreciated.

Aug 27, 2012 at 8:13 PM
Edited Aug 27, 2012 at 8:13 PM

I've created a website that attempts to read spreadsheet files using PHPExcel in the most efficient way possible.

It does NOT do anything for writing a spreadsheet file, it's geared for reading and displaying the contents of spreadsheet files.

I use it for testing my code in reading large spreadsheet files.

I can zip it up and you can use it to see if it reads the files faster than your current method.

If it does you can dissect my code an incorporate what you need into your own projects.

If you want the website code, just email me at schir1964@gmail.com and I'll set things up so you can download the code.

- Christopher Mullins

Aug 27, 2012 at 9:02 PM

Thank you Christopher, I already sent you the email. ;)

Aug 27, 2012 at 11:22 PM
Edited Aug 27, 2012 at 11:23 PM

I just got to thinking, that a large portion of that 15 second delay might just be the file being uploaded to the website for processing.

If that is the case, only a faster connection across the internet will solve the issue.

Just a thought.

- Christopher Mullins