27 ноя 2011 в 15:08

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

Ожидает приглашения

Дано:

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.

Ссылка на оригинальный тред на форуме разработчиков

Хабы:

Чулан