Универсальное чтение ячеек в 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);

Спасибо за внимание!

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