PHPExcel / PhpSpreadsheet

web, script, PHPExcel

Несколько недель назад мне пришлось работать с файлами Excel в формате BIFF8 (Excel 97), т.к. заказчик не согласен был на экспорт данных в CSV. Spreadsheet_Excel_Writer вместе с Spreadsheet_Excel_Reader очень хорошо помогали мне раньше, но только с BIFF5. Поддержка таблиц BIFF8 долгое время была большой проблемой. поэтому я стал искать альтернативы.

После недолгих поисков, я наткнулся на PHPExcel, который поддерживает множество форматов чтения и записи с помощью одного API. Он распространяется по GNU Lesser General Public License? что дает вам право использовать его свободно в коммерческих продуктах. Форматы чтения и записи не ограничиваются файлами Excel. В этой статье я дам краткий обзор о того, что вы можете делать с помощью этой библиотеки.

Форматы чтения

Excel 2007 (SpreadsheetML)
BIFF5 (Excel 5.0/Excel 95
BIFF8 (Excel 97 и более поздние)
PHPExcel Serialized Spreadsheet
Symbolic Link
CSV

Форматы записи

Excel 2007 (SpreadsheetML)
BIFF8 (Excel 97 и более поздние)
PHPExcel Serialized Spreadsheet
PDF
HTML
CSV

Убедитесь что на вашем сервере стоит PHP 5.2.0 или старше и установлены следующие расширения: php_zip, php_xml и php_gd2. Вs можете скачать архив с PHPExcel с сайта проекта. Можно так же установить PEAR-пакет.

После распаковки скачанного архива, вы найдете папку Tests, в которой содержатся несколько демонстрационных файлов. Когда вы захотите использовать эту библиотеку, взгляните на эти примеры, чтобы познакомиться методами работы с PHPExcel.

Теперь вы готовы к созданию вашего первого файла Excel. После создания экземпляра класса, устанавливаются некоторые метаданные:

require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
// устанавливаем метаданные
$objPHPExcel->getProperties()->setCreator("PHP")
->setLastModifiedBy("Алексей")
->setTitle("Office 2007 XLSX Тестируем")
->setSubject("Office 2007 XLSX Тестируем")
->setDescription("Тестовый файл Office 2007 XLSX, сгенерированный PHPExcel.")
->setKeywords("office 2007 openxml php")
->setCategory("Тестовый файл");
$objPHPExcel->getActiveSheet()->setTitle('Демо');

Как вы можете видеть, практически везде в классе используется паттерн Fluent Interface (цепочка вызовов).

Имена методов говорят сами за себя. В результате выполнения этого кода создастся объект PHPExcel c метаданными, но без наполнения, кроме листа под названием "Демо". При создании объекта, первый лист создастся автоматически. Конечно, вы можете добавить еще листы, если это необходимо. Сейчас в качестве активного листа выбран первый. Этот лист Excel или Open Office покажут первым при открытии файла. Добавим "Привет, мир!" в таблицу:

  $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A1', 'Привет')
              ->setCellValue('B1', 'Мир!');

Обращаться к ячейкам можно через координаты Excel, например, "С3". Это не очень удобно, когда надо перебрать содержимое большого количества ячеек. К счастью есть другой метод для того чтобы обратиться к ячейке - с помощью координат X и Y:

setCellValueByColumnAndRow($column, $row, $value) Заметьте, что нумерация строк начинается с 1, то есть координаты ячейки "A1" будут (0,1). Последний шаг - сохраняем файл Excel на диск. Для этого надо создать объект для записи, используя PHPExcel_IOFactory. Второй параметр определяет формат файла.

  require_once 'PHPExcel/IOFactory.php';
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  // Если вы хотите, то можете сохранить в другом формате, например, PDF:
  //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
  $objWriter->save('MyExcel.xslx);

Есть методы для изменения стиля, формата, слияния и проверки ячеек. Для более подробной информации - смотрите документацию.

Чтение таблицы PHPExcel так же способен читать данные из Excel файлов. В реальной программе это может быть использовано, например, для импорта списка продукции. Это может быть полезно, если вы хотите предложить клиенту загрузку непосредственно Excel файла, а не экспорт через CSV. Так как PHPExcel может открыть оба этих формата, вы можете предложить оба способа импорта, без особых усилий.

Чтение из файла так же просто как и запись в него. Вновь используется класс IOFactory для доступа к файлу:

  require_once 'PHPExcel/IOFactory.php';
  $objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");

Использование итератора листов позволяет вам получить структуру файла. Следующий пример показывает, как это можно сделать:

  require_once 'PHPExcel/IOFactory.php';
  $objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");
  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
  {
      $worksheetTitle     = $worksheet->getTitle();
      $highestRow         = $worksheet->getHighestRow(); // например, 10
      $highestColumn      = $worksheet->getHighestColumn(); // например, 'F'
      $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
      $nrColumns = ord($highestColumn) - 64;
      echo "<br>В таблице ".$worksheetTitle." ";
      echo $nrColumns . ' колонок (A-' . $highestColumn . ') ';
      echo ' и ' . $highestRow . ' строк.';
      echo '<br>Данные: <table border="1"><tr>';
      for ($row = 1; $row <= $highestRow; ++ $row)
      {
          echo '<tr>';
          for ($col = 0; $col < $highestColumnIndex; ++ $col) 
          {
              $cell = $worksheet->getCellByColumnAndRow($col, $row);
              $val = $cell->getValue();
              $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
              echo '<td>' . $val . '<br>(Тип ' . $dataType . ')</td>';
          }
          echo '</tr>';
      }
      echo '</table>';
  }

Используя всего три строки кода можно легко конвертировать файлы из одного формата в другой:

  $objPHPExcel = PHPExcel_IOFactory::load("XMLTest.xml");
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save('covertedXml2Xlsx.xlsx');

Другое интересное решение - использовать бизнес-логику из Excel файла. Вместо повторного создания кода, вы можете выполнить код сохраненный в Excel.

Так же возможно (а еще и быстрее, чем создание с нуля) загрузить файл Excel, а затем добавить в него данные. Базовый файл можно хранить как шаблон и использовать, заполняя нужными данными. А когда меняется макет - просто менять шаблон.

PHPExcel очень требователен к памяти. Для одной ячейки надо около 1КБ памяти. При работе с большими таблицами, вы можете столкнуться с определенными трудностями. Тем не менее, разработчики позаботились об этом предоставив механизм кэширования: CachedObjectStorageFactory. Что говорит об этом руководство:

Механизм кэширования ячеек позволяет PHPExcel манипулировать объектами потреблять меньше памяти в PHP, используя жесткий диск, APC, memcache или Wincache. Это позволяет работать с большими таблицами используя небольшие объемы памяти, хоть и платя за это скоростью обращения к ячейкам.

PHPExcel не умеет создавать диаграммы - пока что. Это пока в планах, но работа над ними уже ведется. Возможно в следующем релизе можно будет создавать диаграммы.

PHPExcel имеет большой набор методов, по настоящему нужных программисту. Документация хорошо структурирована и большое число демонстрационных файлов позволяет легко найти как нужно использовать класс. Кроме этого файлы документированы с помощью PHPDocBlocks, так что при использовании IDE, таких как ZendStudio или NetBeans, вы будуте получать подсказки по использованию методов классов.

В заключение: на PHPExcel определенно стоит обратить внимание, т.к. он может работать со многими форматами.

PHPExcel / PhpSpreadsheet

Установка PHPExcel

Создание таблицы

Небольшое замечание

В будущем

Заключение