Pull to refresh

Обработка больших XLS-файлов в PHP

Lumber room
Awaiting invitation
Дано:

1 xls-файл размером скажем 133 мегабайта
2 Ограничение потребления памяти на сервере, скажем 128 мегабайт (вполне реальная величина на простеньком shared-хостинге)

Необходимо:

Распарсить xls-файл, и сделать с данными что угодно, например записать в базу.

Инструментарий:

замечательная библиотека PHPExcel

Мы не будем говорить о том, что можно пользоваться xml, csv и другими более дружелюбными форматами, мы смотрим обработку именно xls-файла

Если кто уже работал с библиотекой PHPExcel, тот знает, что она весьма прожорлива на память.
Но проблема решается, причем довольно просто, решение написано на официальном форуме, и ссылка на него есть прямо из документации, которую правда не все читают.

Ниже расположен пример кода, который обработает большой xls-файл:
<?
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 = '') { 
        //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow 
        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { 
            return true; 
        } 
        return false; 
    } 
}

$file =  'somefile.xls';
set_time_limit(1800);
ini_set('memory_liit', '128M');
/*	some vars	*/
$chunkSize = 2000;		//размер считываемых строк за раз
$startRow = 2;			//начинаем читать со строки 2, в PHPExcel первая строка имеет индекс 1, и как правило это строка заголовков
$exit = false;			//флаг выхода
$empty_value = 0;		//счетчик пустых знаений
/*	some vars	*/
if (!file_exists($file)) {
	exit();
}
require_once 'phpexcel/PHPExcel.php';

$objReader = PHPExcel_IOFactory::createReaderForFile($file);
$objReader->setReadDataOnly(true);

$chunkFilter = new chunkReadFilter(); 
$objReader->setReadFilter($chunkFilter); 
//внешний цикл, пока файл не кончится
while ( !$exit ) 
{
	$chunkFilter->setRows($startRow,$chunkSize); 	//устанавливаем знаечние фильтра
	$objPHPExcel = $objReader->load($file);		//открываем файл
	$objPHPExcel->setActiveSheetIndex(0);		//устанавливаем индекс активной страницы
	$objWorksheet = $objPHPExcel->getActiveSheet();	//делаем активной нужную страницу
	for ($i = $startRow; $i < $startRow + $chunkSize; $i++) 	//внутренний цикл по строкам
	{
		$value = trim(htmlspecialchars($objWorksheet->getCellByColumnAndRow(0, $i)->getValue()));		//получаем первое знаение в строке
		if ( empty($value) )		//проверяем значение на пустоту
			$empty_value++;			
		if ($empty_value == 3)		//после трех пустых значений, завершаем обработку файла, думая, что это конец
		{	
			$exit = true;	
			continue;		
		}	
		/*Манипуляции с данными каким Вам угодно способом, в PHPExcel их превеликое множество*/
	}
	$objPHPExcel->disconnectWorksheets(); 				//чистим 
	unset($objPHPExcel); 						//память
	$startRow += $chunkSize;					//переходим на следующий шаг цикла, увеличивая строку, с которой будем читать файл
}


Обычная работа с PHPExcel заключается в том, что мы открываем xls-файл и далее идет цикл по строкам.
В нашем случае, файл мы открываем в цикле, выставляя на каждой итерации диапазон строк, который мы будем считывать.

Как видно, в самом начале мы определяем класс chunkReadFilter, с двумя методами setRows и readCell, первый из которых и позволяет нам читать файл по частям.

Что делать со считанными строками дальше, дело сугубо Ваше: писать напрямую в базу, писать во временные таблицы, что-то вычислять — все зависит от конкретной задачи.

Также стоит учесть, что при большом объеме файла мы упремся в timeout выполнения скрипта, потому лучше запускать его фоном, например через exec().

Данный пример примечателен только фильтром на количество считываемых строк. Метод while ( !$exit ) не претендует на 100% правильность, это просто часть реально рабочего скрипта, а я еще продолжаю познавать PHPExcel.

Ссылка на оригинальный тред на форуме разработчиков
Tags:
Hubs:
You can’t comment this post because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.