PHPExcel / PhpSpreadsheet

https://github.com/PHPOffice/PHPExcel

https://github.com/PHPOffice/PhpSpreadsheet#want-to-contribute

Несколько недель назад мне пришлось работать с файлами 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

Установка PHPExcel

Убедитесь что на вашем сервере стоит 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 определенно стоит обратить внимание, т.к. он может работать со многими форматами.