4 сентября 2012 в 20:39

PHPExcel и большие файлы из песочницы

PHP*
PHPExcel — отличная библиотека с огромным функционалом по работе с форматами xls, xlsx. Можно считывать, записывать, менять форматирование, задавать формулы, а из xlsx можно и картинки вытаскивать.

На хабре уже был пост про эту библиотеку - Универсальное чтение ячеек в PHPExcel. Я остановлюсь только лишь на главном минусе PHPExcel — вечно памяти не хватает, все время сыпятся ошибки «Fatal error: Out of memory». Этот пост о том, как это обойти.

Чтение файла


Для чтения большого файла (~25 000 строк) я использовал комплексное решение.

Во-первых, считываем файл не целиком, а по несколько строк. Это делается так:

import_xls.php

<?php
require_once 'path/to/PHPExcel/IOFactory.php';

class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0;
private $_endRow = 0;

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;
}
}

session_start();

if ($_SESSION['startRow']) $startRow = $_SESSION['startRow'];
else $startRow = 13;

    $inputFileType = 'Excel5';
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $chunkSize = 20;
    $chunkFilter = new chunkReadFilter();

    while ($startRow <= 65000) {
     $chunkFilter->setRows($startRow,$chunkSize);
     $objReader->setReadFilter($chunkFilter);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($fileName);
     //Что-то с этими строками делаем
     $startRow += $chunkSize;
     $_SESSION['startRow'] = $startRow; 

    unset($objReader); 

    unset($objPHPExcel);

    }

    echo "The End";
    unset($_SESSION['startRow']);
?>


Собственно класс chunkReadFilter — это то, что нам нужно. Устанавливаем его в качестве фильтра для чтения файла, и файл будет загружаться не целиком, а лишь определенное количество строк.

Во-вторых, кроме него еще используем такую полезную опцию как ReadDataOnly. Как следует из названия, она позволяет не загружать форматирование документа, высвобождая место для данных.

И, в-третьих, конечно же используем unset(). Это так же поможет высвободить память.

Но помимо нехватки памяти возникает другая проблема. На большинстве shared-хостингов у php-скриптов помимо ограничения на использование памяти, еще стоит ограничение на время выполнения. И крайне вероятно, что этого времени хватать не будет. Лично я обошел эту проблему при помощи сессий и повторяющихся ajax запросов. В коде представленном выше вы уже увидели использование сессий и завершающее «The End».

А вот код клиентской части.

import_xls.html

<html>
    <head> 
    <title>Импорт прайс-листа</title>
      <script src="/media/js/jquery.js" type="text/javascript"></script>
      <script src="/media/js/import-xls.js" type="text/javascript"></script>
    </head>

    <body> 

    <h1>Импорт прайс-листа</h1>
    Подождите завершения импорта, не закрывайте данную страницу!
    <div id="progress-bar">
    </div>
    <div id="content">
    </div> 

    </body> 

</html>


import-xls.js

function repeat_import() {
	$.ajax({
			url: "/import_xls.php",
			timeout: 50000,
			success: function(data, textStatus){
						$("#progress-bar").append("I");
						if (data == "The End") {
							$("#content").html("<h2>Импорт завершен!</h2>");
						}
						else {
							$("#content").html("<p>" + data + "</p>");
							repeat_import();
						}		
					},
			complete: function(xhr, textStatus){
						if (textStatus != "success") {
							$("#progress-bar").append("I");
							repeat_import();		
						}
					}
	});
}

$(function (){
	repeat_import();
});


Т.е. мы отправляем ajax-запрос нашему скрипту import_xls.php, ждем ответа, и, если ответ нас не устраивает, посылаем новый ajax-запрос. Встречал еще в сети решение без использования AJAX — при помощи редиректа в самом php. Обработка файла делится на малое количество строк и после этого вставляется код:

header ("Location: import_xls.php");). 


Но лично мне больше нравится решение с AJAX, потому что тут можно легко и просто добавить прогресс-бар и какие-нибудь другие рюшечки и плюшечки. Кстати, внимательный читатель заметил, что в моем коде простейший прогресс-бар уже реализован. Дополнительный немаловажный момент: в решение с AJAX не нужно заранее знать сколько строк скрипт сможет обработать за один проход.

Запись файла


Для записи файла в формате xls величиной так же около 25 000 строк крайне полезно использовать следующий код:

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


Можно еще поиграться с методами кеширования. Помимо кеширования во временной директории php еще поддерживается memcache:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array( 'memcacheServer' => 'localhost', 'memcachePort' => 11211, 'cacheTime' => 600 );


А так же cache_to_discISAM.

UPDATE
Прошу у всех прощения за то, что код тут был представлен ужасно. Сразу не разобрался с подсветкой синтаксиса на Хабре (видимо, позднее время суток сказалось) и поторопился. Впредь не буду торопиться и буду умнее.

UPDATE2
Переделал JavaScript. Теперь он посылает запрос по таймауту в случае, если сервер не ответил.
Михаил Паршин @MParshin
карма
3,0
рейтинг 0,0
Пользователь
Похожие публикации
Самое читаемое Разработка

Комментарии (18)

  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      Извините. Встроенным хабровским тегом source нормально?
  • +11
    <source lang="php"></source>
    • +1
      Спасибо. Исправил.
  • 0
    Спасибо! Как раз кстати. Ну, конкретно с этой проблемой ещё не столкнулся, ибо пока работал только с файлами на 12-13 тыс. строк, но не помешает на будущее.
    • +1
      Надеюсь, что моя статья Вам поможет.
  • 0
    Могу добавить, что лично я сталкивался с файлами куда больше и, например, кеширование в memcached уже не спасало, по скольку все-рано не хватало памяти.

    В последней версии библиотеки есть кеширование в sqllite — весьма медленно, но надежно и спасает от всех проблем с памятью.
    • 0
      Интересно. При случае поэкспериментирую.
  • 0
    Столкнулся с проблемой нехватки времени на выполнение скрипта. На хостинге было всего 10 секунд. PHPExcel не успевал сделать
    $objPHPExcel = $objReader->load($fileName);
    Пришлось использовать какую-то библиотеку в два файла (что-то типа Excel Reader). Она за 10 секунд успевает прочитать файл и обработать 5 строчек.
    • 0
      Excel Reader хорошая штука, жаль что не работает с форматом *.xlsx
  • 0
    Для чтения можно воспользоваться консольной утилитой xlhtml, преобразовать в CSV, и читать построчно, в итоге мало кода, быстро работает, малый расход памяти, из минусов — требует разрешения использования функции exec().
  • 0
    А у меня часто вставала проблема не с чтением больших Excel файлов а с их формированием. PHPExcel потребляет слишком много памяти и времени для создания большого Excel файла. Так что я отказался от его использования в подобных случаях и формирую XML ODF файл вручную, а он прекрасно читается Excel-м.

    В принципе это не так уж и страшно. Во первых есть хорошее описание этого формата и его майкросовтовского расширения, а во вторых можно просто сохранить шаблон Экселя в виде XML, а потом сделать свой файл о образу и подобию.

  • 0
    После того как у меня начались проблемы с памятью и скоростью начал искать другое решения и нашел решение модулем к php, как следствие скорость работы и проблемы с памятью пропали само расширение тут github.com/iliaal/php_excel оно использует библиотеку libxl она конечно платная но того стоит, будут вопросы пишите…
  • 0
    1. почему именно 13? else $startRow = 13;
    2. unset($objReader); в цикле, на следующей итерации цикла объект не найден $objReader
    3. if ($_SESSION['startRow']) тут добавить isset
  • 0
    Вместо "//Что-то с этими строками делаем" рекомендую следующую конструкцию, пропускающую пустые строки в начале

    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator($startRow, $chunkSize);
    foreach($rowIterator as $row) {
        $cellIterator = $row->getCellIterator();
        $col=Array('idx'=>$row->getRowIndex());
        foreach($cellIterator as $cell)
            $col[$cell->getColumn()] = $cell->getCalculatedValue();
    }
    

  • 0
    PHPExcel жрет память десятками мегабайт даже с настройками кэширования в файл!
    Эта библиотека — настоящий кошмар разработчика.
    Рекомендую сразу отказаться от нее.
    • 0
      Есть ли другая библиотека для работы с xls файлами и такая же функциональная?

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.