(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: habrahabr

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

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

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 07.02.2012 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
ESET Secure Authentication newsale for 5 user, лицензия на 1 год
ABBYY FineReader 14 Standard Full
FastCube FMX Single License
Quest Software. TOAD for Oracle Edition
ABBYY Lingvo x6 Многоязычная Домашняя версия, электронный ключ
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
СУБД Oracle "с нуля"
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
Новые материалы
Новости мира 3D-ускорителей
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Написание программ для микроконтроллеров AVR, PIC, ARM, STM32 (25)
Напишу любую программу на любом искусственном языке. Профессиональный программист. Основная...
 
Разработка устройств на микроконтроллерах (39)
Профессиональный программист. Основная специализация: МИКРОКОНТРОЛЛЕРЫ, АССЕМБЛЕР для любых...
 
Пишу программы на заказ для студентов (254)
Пишу для студентов на с, с++, паскаль в средах ms visual studio, qt, builder, borland c, delphi....
 
Ремонт холодильников (1)
Если нужно починить срочно холодильник в Харькове то советую обращаться в эту...
 
Разработка программ базы данных (60)
Написание прикладных компьютерных программ (базы данных) на заказ. Разработка корпоративных...
 
 
 



    
rambler's top100 Rambler's Top100