Универсальное чтение ячеек в PHPExcel

    Привет, Хабр!
    Мне по работе часто приходится импортировать Excel-документы средствами PHP.
    Для этого я использую библиотеку PHPExcel, которая на сегодняшний день является весьма удобным инструментом.
    Но есть несколько «скользких» моментов, связанных с чтением данных из ячеек, о которых я хочу рассказать хабрачитателям, а также разобрать функцию, решающую эти проблемы.

    1. Адресация ячеек


    Адресовать ячейки при работе с excel можно разными способами:
    • колонка и ряд в виде строки: «A1»
    • колонка буквой, ряд числом: («A», 1)
    • колонка и ряд числом: (1, 1)

    Первый способ удобнее для статических ячеек, а второй и третий для циклов.

    Но в PHPExcel нет универсальной функции получения ячейки любым из этих способов, есть только отдельные функции. Что ж, исправляем это упущение:
    public function getCellValue($cellOrCol, $row = null)
    {
    	//column set by index
    	if(is_numeric($cellOrCol)) {
    	    $cell = $this->activeSheet->getCellByColumnAndRow($cellOrCol, $row);
    	} else {
    	    $lastChar = substr($cellOrCol, -1, 1);
    	    if(!is_numeric($lastChar)) { //column contains only letter, e.g. "A"
    	       $cellOrCol .= $row;
    	    } 
    	    
    	    $cell = $this->activeSheet->getCell($cellOrCol);
    	}
    	$val = $cell->getValue();
    	return $val;
    }
    

    Сразу оговорюсь, что в приводимых мной примерах кода есть ссылки на $this, т.к. это методы моего класса-обертки над PHPExcel. В этом кусочке реализуются все три способа получения объекта ячейки.

    2. Объединение ячеек


    При чтении объединенных ячеек, PHPExcel возращает пустое значение для всех, кроме первой.
    Т.е. для рисунка ниже значения B3 и C3 будут пустыми строками:



    Мне всегда было неудобно такое поведение.
    Гораздо удобнее (и логичнее!) для любой объединенной ячейки возвращать общее для них значение «mergedvalue».
    Для этого при запросе значения нужно пройтись по всем объединенным диапазонам листа, и если заданная ячейка попадает в диапазон, то вернуть первую:
    $this->mergedCellsRange = $this->activeSheet->getMergeCells();
    foreach($this->mergedCellsRange as $currMergedRange) {
    if($cell->isInRange($currMergedRange)) {
    	$currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange);
    		$cell = $this->activeSheet->getCell($currMergedCellsArray[0][0]);
    		break;
    	}
    }
    


    3. Даты


    Как известно, Excel хранит даты как число дней с 1 января 1900 года. Поэтому при чтении ячейки B2 на скриншоте выше мы получим бесполезное 41044. Но есть и хорошая новость — в PHPExcel присутствует удобная функция PHPExcel_Shared_Date::ExcelToPHP(), которая превращает дату в формат php.
    Осталось только применить эту функцию в нужный момент:
    $val = $cell->getValue();
    if(PHPExcel_Shared_Date::isDateTime($cell)) {
         $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val)); 
    }
    


    4. Формулы


    В большинстве случаев стандартная функция $cell->getValue() корректно обрабатывает формулы и возвращает рассчитанное значение. Но бывают ситуации, когда формула ссылается на несуществующий лист или другой файл, который локально хранится у того, кто отправил вам excel-документ. Тогда getValue() возвратит ошибку, хотя визуально в экселе вы можете увидеть верное значение, если не было пересчета листа. Дело в том, что Excel сохраняет oldCalculatedValue, которое и используется, если не пересчитывать лист. На рисунке выше я показал это в ячейке B4 — она отображает старое значение, хотя ссылка в ней нерабочая.
    PHPExcel, к счастью, тоже умеет хранить старое значение формулы. Это удобно использовать, когда getValue() не смогла отработать и вернула не значение, а саму формулу (первый символ "="):

    $val = $cell->getValue();
    if((substr($val,0,1) === '=' ) && (strlen($val) > 1)){
        $val = $cell->getOldCalculatedValue();
    }
    


    Результат


    В итоге мы получили функцию, которая позволяет универсально считывать значения ячеек:
     public function getCellValue($cellOrCol, $row = null, $format = 'd.m.Y')
        {
            //column set by index
            if(is_numeric($cellOrCol)) {
                $cell = $this->activeSheet->getCellByColumnAndRow($cellOrCol, $row);
            } else {
                $lastChar = substr($cellOrCol, -1, 1);
                if(!is_numeric($lastChar)) { //column contains only letter, e.g. "A"
                   $cellOrCol .= $row;
                } 
                
                $cell = $this->activeSheet->getCell($cellOrCol);
            }
            
            //try to find current coordinate in all merged cells ranges
            //if find -> get value from head cell
            foreach($this->mergedCellsRange as $currMergedRange){
                if($cell->isInRange($currMergedRange)) {
                    $currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange);
                    $cell = $this->activeSheet->getCell($currMergedCellsArray[0][0]);
                    break;
                }
            }
    
            //simple value
            $val = $cell->getValue();
            
            //date
            if(PHPExcel_Shared_Date::isDateTime($cell)) {
                 $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val)); 
            }
            
            //for incorrect formulas take old value
            if((substr($val,0,1) === '=' ) && (strlen($val) > 1)){
                $val = $cell->getOldCalculatedValue();
            }
    
            return $val;
        }
    


    Тест


    Для проверки прочитаем эксель со скриншота двумя способами: стандартной getValue (#1) и с использованием вышеописанной функции (#2):



    Тест #1:

    Тест #2:

    Как видно, во втором случае все считалось корректно.

    Ложка дегтя


    Важно отметить, что использование пунктов 2, 3 и 4 работает только в режиме ReadDataOnly = false. Это режим PHPExcel по умолчанию, когда он считывает всю мета-информацию о книге. Подходит для стандартных небольших документов, например счета, накладные и.т.д.
    Включение ReadDataOnly = true может потребоваться для громоздких файлов, когда нужны только значения ячеек. По моей практике, такие файлы содержат отформатированные таблицы и там такой функционал не нужен.

    Установка режима чтения в PHPExcel делается так:
    $objReader = PHPExcel_IOFactory::createReaderForFile($filename);
    $objReader->setReadDataOnly(false);
    $this->PHPExcel = $objReader->load($filename);         
    

    Спасибо за внимание!
    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 29
    • +1
      То что надо. Спасибо!
      • +1
        Большое спасибо, скорее всего скоро мне придется распарсивать большие прайсы, и это должно пригодиться!
        • +1
          только будьте готовы, что либа весьма прожорлива к памяти, и к сожалению никак не лечится. (я про большие прайсы)
          • 0
            В некоторых проектах, где приходится парсить большие Excel-файлы, мне пришлось отказаться jn PHPExcel в пользу менее удобных и универсальных, но зато более шустрых и менее прожорливых библиотек.
            • +1
              Да, у нас тоже с большими файлами проблемы были.
              Из менее удобных, но шустрых, которые я использовал:
              — пировский Spreadsheet_Excel_Writer
              phpexcelreader
            • 0
              Спасибо за совет, я надеюсь все будет ок, т.к. у нас в компании свой сервер стоит.
              На крайний случай, будем в ручную готовить прайсы и разбивать.
              • +4
                Кеширование включайте, помогает. По крайней мере при создании экселевских файлов — но думаю и при чтении будет работать.

                1. $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
                2. PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
                • +1
                  Файл в 6к строк и 5 колонок, солидно помогает unset, если нужно только читать файл:
                  $reader = new PHPExcel_Reader_Excel5();
                  $pe = $reader->load('data.xls');
                  echo 'Memory usage: ', showMemUsage();
                  
                  unset($reader);
                  echo 'Memory usage: ', showMemUsage();
                  
                  $sheet = $pe->getActiveSheet();
                  

                  в результате
                  Memory usage: 20.78 Mb
                  Memory usage: 15 Mb
                  

                  При ограничении в 32 Мб почти 6 Мб освободившейся памяти — не мало. Мне же требовалось проводить над данными некие манипуляции и писать их в новый файл. Вот этих 6 Мб и мне как раз и не хватало при записи :)
                  • 0
                    у меня на вашей 2 строчке вылетало
                    ~ 50k строк 6 колонок
                    можно посмотреть в конструктор, что оно там делает. не удивительно )
              • +1
                И Вам спасибо большое за пост.
                • +1
                  Порадовали пункты объединение ячеек и формулы. Спасибо вам большое.
                  • 0
                    Одно время хотел сделать на работе мини базу данных, чтоб удобнее было манипулировать данными (вместо екселей). ох и намучился я с импортированием екселей методами РНР! Мегабайт по 20-30 плюс куча формул и блокировок в книге и все что можно отваливается и не работает. Склоняюсь к импортированию в Аксес. Там наверно будет проще, да и доставать данные наверно будет проще.
                    • 0
                      Сколько перепробовали библиотек, всегда одно и то же. В больших файлах вылазит ошибка, сообщающая о недостатке памяти. На шаред хостингах обрабатываются файлы до 1000 строк, поэтому мы забросили это дело. Сейчас все наши обработчики работают на Python.
                      • 0
                        Тема актуальная, может напишите статью?
                        • 0
                          ошибка при чтении или при записи? Не надо пользоваться шаред хостингом для подобных задач :) VPS-ы недорогие нынче.
                          • 0
                            Ошибка при записи. А VPS не для всех выход. Некоторым накладно его оплачивать и тем более следить за ним, если на фирме нет специалиста.
                            • 0
                              я выше дал кусок кода с решением проблемы экспорта больших объемов данных. А насчет VPS — у меня для моих клиентов стоят VPS разделенные на несколько клиентов — и людям получается недорого, и мне с бубном плясать не надо вокруг шаред хостинга.
                        • –4
                          Какой кошмар, читаем файлы, созданные одним злом, с помощью второго.
                          • 0
                            по работе приходится создавать большие ексели. Два листа по 40000 строк и 10 колонок. таких два с половиной листа. памяти уходит очень много. даже с кешированием на диск c помощью PHPExcel_CachedObjectStorageFactory.
                            вывод памяти после записи каждой строчки умиляет)
                            • 0
                              не пробовали писать в xml (или даже html) и потом сохранять с xls расширением?
                              • 0
                                обязателен строгий формат c листами внутри.
                                • 0
                                  а, ну тогда да.
                                  Если бы только листы в одной книге — то может быть создавать их отдельно, а потом собрать в один файл через PHPExcel.
                            • +1
                              Я нашел самое лучшее средство для чтения Excel, это утилита xlhtml, через которую можно сконвертировать даже огромные файлы без расхода памяти (в отличие от других решений) в обычный текстовый, например CSV, с которым уже и работать из PHP.

                              exec('xlhtml -xp:0 -csv import.xls > /tmp/out.csv'); // выбрать первый лист
                              $csv = fopen('/tmp/out.csv', 'r');
                              while (($row = fgetcsv($csv)) !== FALSE) {
                                  foreach ($row as $col) {
                                      echo $col;
                                  }
                              }
                              fclose($csv);
                              


                              Ну а для выдачи данных в формате Excel проще всего формировать HTML таблицу, и отдать пользователю с заголовками:
                              header('Content-Type: application/vnd.ms-excel');
                              header('Content-Disposition: attachment; filename=export.xls');
                              
                              • 0
                                Выглядит привлекательно.
                                А как xlhtml обрабатывает формулы, даты и объединенные ячейки?
                                • 0
                                  Показывает только результат вычислений, сами формулы кому-нибудь нужны? Даты не знаю как обрабатывает, нужно будет посмотреть, скорее всего так же, что видим на экране, то и получим.
                                  • 0
                                    Именно результат и нужен :)
                                    спасибо, попробуем!
                              • 0
                                А как решили проблему с часовым поясом?
                                PHPExcel_Shared_Date::ExcelToPHP добавляет текущую разницу к GMT. При московском соответственно 4 часа.
                                Прикажете вычитать их из полученной даты?
                                • 0
                                  А сразу и отвечу — использовать ее совместно с gmdate() вместо date().
                                  • 0
                                    А еще, эта сволочь режет время из полученной даты.

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