Привет, Хабр! На работе мне часто приходится импортировать документы Excel с помощью PHP. Для этого я использую библиотеку PHPExcel , что на сегодняшний день является очень удобным инструментом.
Но есть несколько «скользких» вопросов, связанных со чтением данных из ячеек, о которых я хочу рассказать читателям хабра, а также проанализировать функцию, решающую эти проблемы.
1. Адресация ячеек
Существуют разные способы обращения к ячейкам при работе с Excel:- столбец и строка в виде строки: «A1»
- столбец по букве, ряд по номеру: («А», 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; }
это методы моего класса-обертки над PHPExcel. В этом фрагменте реализованы все три способа получения объекта ячейки.
2. Объединение ячеек
При чтении объединенных ячеек PHPExcel возвращает пустое значение для всех, кроме первого .Те.
для картинки ниже значения B3 и C3 будут пустыми строками: 
Мне всегда было неприятно такое поведение.
Гораздо удобнее (и логичнее!), чтобы любая объединенная ячейка возвращала для них общее значение.
«объединенное значение» .
Для этого при запросе значения нужно пройтись по всем объединенным диапазонам листа, и если указанная ячейка попадает в диапазон, то вернуть первый: $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-> получить значение() правильно обрабатывает формулы и возвращает рассчитанное значение.Но бывают ситуации, когда формула ссылается на несуществующий лист или другой файл, хранящийся локально у человека, отправившего вам документ Excel. Затем getValue() вернет ошибку, хотя визуально в Excel можно увидеть правильное значение, если лист не был пересчитан.
Дело в том, что Excel сохраняет OldCalculatedValue , который используется, если лист не пересчитывается.
На картинке выше я показал это в ячейке B4 — там отображается старое значение, хотя ссылка в ней битая.
PHPExcel, к счастью, тоже умеет хранить старое значение формулы.
Это удобно использовать, когда получить значение() не смог сработать и вернул не значение, а саму формулу (первый символ "="): $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;
}
Тест
Для проверки прочитаем Excel со скриншота двумя способами: стандартным getValue( #1 ) и используя указанную выше функцию ( #2 ):Тест №1:
Тест №2:
Как видите, во втором случае все было признано правильным.
Ложка дегтя
Важно отметить, что использование пунктов 2, 3 и 4 работает только в режиме Реаддатаонли = ложь .Это режим PHPExcel по умолчанию, в котором он считывает всю метаинформацию о книге.
Подходит для стандартных небольших документов, таких как счета-фактуры, счета-фактуры и т. д. Включение Реаддатаонли = истина может потребоваться для объемных файлов, когда это необходимо только значения ячеек .
На моей практике такие файлы содержат отформатированные таблицы и такой функционал там не нужен.
Установка режима чтения в PHPExcel делается так: $objReader = PHPExcel_IOFactory::createReaderForFile($filename);
$objReader->setReadDataOnly(false);
$this->PHPExcel = $objReader->load($filename);
Спасибо за внимание!
Теги: #phpexcel #php #php
-
Как Выровнять Пирамиду Смерти
19 Oct, 24 -
Почему, А Главное, Куда Люди Уходят Из Ит?
19 Oct, 24
