Мне по работе часто приходится импортировать 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);
Спасибо за внимание!
Ссылки по теме
