Php построение графиков excel
Иногда в рамках интернет проекта возникает потребность в визуализации данных. Представление данных в виде графиков и диаграмм дает возможность наглядно убедиться, например, в увеличении/уменьшении объёмов продаж в текущем году по сравнению с прошлым годом, количества посетителей и пр., вариантов великое множество.
Специально для этих целей, ребята из Asial Corporation, разработали библиотеку JPGraph. Это объектно-ориентированная библиотека для создания графики профессионального качестве при использовании минимум кода, написанная на PHP.
Используя JPGraph, можно создавать графики, диаграммы, captcha картинки и даже географические карты. В бесплатной версии библиотеки доступно большинство возможностей, но имеется также и профессиональная версия, позволяющая разработчику получить доступ к дополнительным функциям JPGraph.
Для работы с JPGraph вам понадобится PHP версии 4.3.1 и выше, а также установленная библиотека GD. Поддерживается как первая, так и вторая версия GD.
Разработчики библиотеки настоятельно рекомендуют не располагать библиотеку в директориях доступных извне, и я с ними полностью согласен.
Последнее, что нужно сделать, это убедиться, что GD библиотека подключена и работает. Это можно проверить, заглянув в вывод функции phpinfo().
Итак, библиотека установлена, попробуем создать простенький график.
Данный код при выполнении сгенерирует такое изображение:
Рассмотрим ещё неcколько типов графиков, которые предоставляет JPGraph (здесь я не буду приводить полный код примеров, а только новые строки).
Столбчатая диаграмма
JPGraph также предоставляет возможность генерировать 3D проекции.
Отобразим статистику использования браузеров на 1 сенября 2012 года в виде круговой диаграммы.
Во всех примерах для наглядности использовались данные, введенные вручную, но на практике вы можете использовать данные из любых источников, будь то база данных или текстовый файл.
JPGraph предоставляет ещё очень много инструментов для создания всевозможных графиков, например, комбинирование графиков или использование своих изображений для фонов или контрольных точек. Описание всех возможностей JPGraph потребовало бы написания целого ряда статей.
Подпишитесь
Оставьте адрес, и каждый месяц мы будем высылать свежую статью
о новых трендах в разработке програмного обеспечения.
После моей статьи « Как создавать документы Microsoft Word с помощью PHP » (с использованием возможностей взаимодействия в Windows) было довольно много комментариев, призывающих к чистой реализации PHP, то есть к использованию только файлов универсальной библиотеки PHP для управления файлами Office.
В этой статье мы увидим, как использовать библиотеку PHPExcel для предоставления функции «Экспорт в Excel» в веб-приложении, чтобы пользователь мог экспортировать данные в файл Excel 2007/2013 для дальнейшего анализа.
ПРИМЕЧАНИЕ. Существует несколько библиотек PHP, которые могут обеспечивать манипуляции с файлами в Excel (и Office). Используемая здесь библиотека называется PHPExcel, подмножество PHPOffice, которое можно клонировать здесь .
После этого урока мы получим:
- Лист с информацией об игре (дата игры, команды, счет, статус победа / поражение) моей любимой команды НБА – LA Lakers в сезоне 2013-14.
- Кнопка, которая будет экспортировать данные в файл Excel 2013.
- Этот файл Excel будет заполнен некоторыми дополнительными аналитическими данными и диаграммой, также сгенерированной PHP и Excel.
подготовка
Чтобы использовать PHPExcel, мы должны иметь версию PHP выше 5.2.0. Также необходимо включить 3 расширения PHP: php_zip (который необходим для работы с форматами Office 2007), php_xml и php_gd2 (необязательно, но требуется для автоматического расчета ширины столбца).
Далее установите библиотеку через Composer .
Конечно, мы должны иметь нашу базу данных и работать. Дамп данных для этого урока ( lakers.sql ) был загружен в lakers.sql связанный с этой статьей . Данные извлекаются с помощью простого оператора SQL: « select * from lakers » (всего 90 записей, в том числе 8 предсезонных и 82 игр регулярного сезона).
Кроме того, эта демонстрация использует Silex в качестве инфраструктуры MVC. Twig будет использоваться в качестве шаблонного движка. Убедитесь, что необходимые зависимости правильно указаны в вашем файле composer.json .
Индексный файл
index.php будет точкой входа для нашего приложения Silex. Будет определено два маршрута:
Маршрут '/' будет нашей точкой входа и отображает данные и кнопку «Экспорт». Маршрут '/export' будет выполнять процесс обработки, который фактически экспортируется в Excel. Обе функции заключены в пользовательский класс ( classExcel.php ). В оставшейся части этой статьи мы сосредоточимся на этом файле – или, точнее, на функции export и связанных с ней функциях, определенных в этом файле, и обсудим несколько важных аспектов манипулирования Excel с использованием библиотеки PHPExcel.
Приложение Excel и его метаданные
Когда мы нажимаем значок, чтобы запустить Excel, приложение Excel запускается. В обычных условиях он также будет содержать рабочую книгу с 3 (в Excel 2013, только 1) рабочими листами. Рабочий лист – это «холст», с которым мы играем. Это два самых важных термина в Excel. Другие важные термины могут включать в себя: ячейка, диапазон и т. Д.
Чтобы создать экземпляр файла Excel, мы используем:
Экземпляр приложения Excel обычно сопоставляется с физическим файлом Excel. У него есть собственные метаданные для описания файла, который мы создаем. Метаданные отображаются, когда мы «Alt-Enter» файл Excel (или щелкните правой кнопкой мыши на этом файле и выберите «Свойства»):
Свойства, показанные в диалоговом окне выше, будут иметь соответствующие методы setXXXX для установки этих свойств, где XXXX практически идентичен именам свойств, перечисленным в диалоговом окне:
Методы ( setXXXX ) достаточно setXXXX и довольно хорошо отображаются в диалоге «Свойства». Есть некоторые несоответствия в отображении, но они не слишком сложны для нас, чтобы установить соединение (например, «Авторы» будут сопоставлены с setCreator ).
Рабочий лист и популяция клеток
Рабочий лист – это, вероятно, объект, которым мы будем больше всего манипулировать: заполнение ячеек данными или формулами, применение стилей, выполнение фильтрации данных, вставка диаграммы и т. Д.
Чтобы получить ссылку на рабочий лист, мы используем:
Листы в рабочей книге всегда индексируются 0. Таким образом, 1-й (и до сих пор единственный) лист будет Sheet Zero. Имя этого листа по умолчанию всегда «Рабочий лист», и мы можем изменить его с setTitle метода setTitle .
Для заполнения ячейки / ячеек у нас есть как минимум два варианта:
- Для этих заголовков, заголовков и других описательных элементов мы будем заполнять их один за другим, используя метод setCellValue .
- Для структурированных данных, большая часть которых поступает из оператора select SQL, мы будем использовать метод fromArray .
Метод fromArray принимает 3 параметра:
1. источник данных в виде массива;
2. значение «заполнитель», если данные равны NULL;
3. ссылка на ячейку, чтобы начать заполнение (слева направо, затем вверх).
ПРИМЕЧАНИЕ. Когда мы используем PDO для извлечения данных, просто $res = $q->fetchAll(\PDO::FETCH_ASSOC); вызов заставит возвращенный набор данных результата содержать только связанный массив, без индекса. Если fetchall вызывается без опции PDO::FETCH_ASSOC , результирующий набор будет фактически содержать два набора идентичных данных, один в связанной форме массива, другой в индексированной форме, и будет создавать дубликаты в файле Excel при использовании fromArray .
Мы также можем захотеть стилизовать строку заголовка ( ID , сезон и т. Д.). Для этого у нас также есть два способа:
Одним из способов является использование некоторых методов get для извлечения объекта стиля, который мы хотим изменить, и его изменения. Мы делаем это для стиля «фоновой заливки».
Другой заключается в объявлении массива «style» и указании стилей, которые мы хотим изменить, и на что их изменить. Затем мы используем applyFromArray для применения стилей в пакете. Здесь мы изменили шрифт и выравнивание.
Оба метода поддерживают диапазон как свой параметр ( $header='a1:h1'; ), что очень удобно.
Наконец, мы можем захотеть отрегулировать ширину столбца, чтобы они соответствовали максимальной отображаемой длине в каждом столбце:
К сожалению, это не поддерживает параметр диапазона, поэтому мы используем цикл for чтобы это произошло.
Если мы сохраним файл сейчас – мы обсудим сохранение позже – мы увидим, что файл XLSX заполнен данными и правильно отформатирован:
Добавление другого листа и вставка формул
Я всегда использую отдельный лист для хранения исходных данных и как минимум еще один лист для отображения сводной и / или аналитической информации.
Чтобы вставить новый лист, мы делаем:
Метод addSheet принимает два параметра.
- $ews2 : экземпляр листа Excel, который мы должны вставить;
- $location : индекс этой таблицы. Таким образом, 0 означает, что он должен стать первым. -1 означает, что он должен быть последним.
Вставив рабочий лист, мы можем заполнить ячейки в этом рабочем листе как обычно и применить стили. В этом листе мы будем использовать формулы:
Видите ли, это ничем не отличается от того, что мы сделали в предыдущем разделе. Строка формулы аналогична той, которую мы введем в файл Excel для выполнения необходимых вычислений.
ПРИМЕЧАНИЕ. Обратите особое внимание на ссылку на ячейку ( G2:G91 ). Ленивый способ написания этой формулы – использовать такой диапазон, как G:G Это прекрасно работает, когда на листе нет графика. Если есть диаграмма, нотация G:G потерпит неудачу, выдав исключение.
Этот «сводный» лист выглядит так:
Показанный в ячейке B4 устанавливается с помощью следующего кода:
Пожалуйста, обратите внимание на некоторые проблемы с дизайном здесь. Для A1 я применил следующий стиль:
Результат показывает, что вес шрифта, размер шрифта и выравнивание применяются правильно. Слияние A1 и B1 в A1 также сделано правильно. Но метод setAutoSize терпит неудачу в этой объединенной ячейке. В результате эта ячейка ( A1 ) все еще сжата. Это означает, что автоматический расчет ширины не всегда будет работать. Ну, в общем, ничего страшного.
Картинка стоит тысячи слов
Всегда приятно иметь визуальное представление наших данных, поэтому диаграмма пригодится. В Excel есть богатый набор встроенных диаграмм, из которых мы можем выбирать. PHPExcel может использовать почти все из них. Первый график, который мы должны создать, это линейный график, показывающий взлеты и падения очков в каждой игре команды Лейкерс и ее противника.
Создание диаграммы – это длительная работа по кодированию, даже при поддержке библиотеки. Полный код этого процесса можно найти в addChart1 и addChart2 которые находятся в нашем файле classExcel.php . Я просто объясню ключевые шаги.
Метка ряда данных идентифицирует ряд данных, присваивая ему имя (метку). В нашем случае, если мы хотим показать результаты «Лейкерс» и их оппонента, мы смотрим на две метки: « Self Score и « Opponent Score . Их метки можно найти в D1 и E1 соответственно:
Метка серии данных на самом деле является экземпляром \PHPExcel_Chart_DataSeriesValues . Конструктор содержит четыре параметра:
- Тип. Для метки, без сомнения, это должно быть «String»;
- Источник. Это в D1 или E1 ;
- Формат. Обычно достаточно указать NULL, и будет использоваться формат по умолчанию;
- Граф. Сколько данных в источнике. Обычно должно быть 1.
- Метка значения оси X
Это идентифицирует метку для оси X. Например, в «2013-11-15» Лейкерс набрал 86, а их соперник – 89. « 2013-11-15 » – это метка для этих двух результатов. В нашем случае мы будем использовать столбец «Дата воспроизведения» от строки 2 до строки 91:
Конструктор такой же, как и параметры.
Мы будем использовать «Self Score» (столбец D) и «Opponent Score» (столбец E). Оба из ряда 2 в ряд 91.
После того, как у нас есть 3 вышеупомянутых критических переменных, мы можем установить Ряд данных. В Excel ряд данных содержит следующую информацию, необходимую для создания диаграммы:
- Тип диаграммы
- Группировка
- Количество значений ряда данных
- Метка серии данных
- Метка значения оси X
- Значения ряда данных
И конструктор вызывается просто передачей всех этих параметров:
Далее мы создадим область сюжета и легенду:
Область графика содержит макет диаграммы и ряд данных. В макете можно указать, будут ли диаграммы отображать значения, проценты и т. Д. Мы можем использовать NULL, чтобы использовать макет по умолчанию.
Легенда используется для визуального представления групп данных.
И вот, наконец, мы можем создать график:
Единственный новый параметр в этом конструкторе – это name диаграммы. «Chart1» будет достаточно хорош. Название диаграммы может быть создано с помощью:
После того, как диаграмма создана, мы устанавливаем ее положение и размер, устанавливая координаты ее левого верхнего и нижнего правого углов и вставляя ее в лист.
ПРИМЕЧАНИЕ. В большинстве случаев ссылка на ячейку не чувствительна к регистру, но, пожалуйста, используйте ЗАГЛАВНУЮ букву + число, если на листе есть диаграмма.
Сохранить файл
В качестве последнего шага мы сохраняем файл, чтобы пользователь мог его скачать:
Он использует фабричный шаблон для создания объекта записи для сохранения файла. Будет указан формат (в нашем случае мы используем формат «Excel2007»).
Убедитесь, что мы установили setIncludeCharts(true) в процессе сохранения, иначе диаграммы там не будет.
Теперь лист данных будет выглядеть так:
и увеличенный вид диаграммы. Он правильно расположен и имеет размеры:
Вывод
В этой статье мы продемонстрировали, как использовать чистый PHP и чистую PHP-библиотеку (PHPExcel) для управления Excel. Мы создали новый файл, заполнили данные, добавили новый лист и аналитические данные, вставили диаграмму и, наконец, сохранили файл для загрузки.
В общем, я нашел этот PHPExcel lib достойным внимания и легким в освоении. Его понимание кода в IDE может очень помочь нам при программировании.
Мы не рассмотрели другие общие функции в файле Excel – мы напишем дополнительную статью о них, если интерес будет достаточно высоким. Дайте нам знать!
Его официальная документация и примеры – хорошее место, где можно найти подробные объяснения использования API и рабочие фрагменты кода для общих задач. Прочитайте их полностью. Их можно найти в клонированном репо. Попробуйте эту библиотеку и дайте нам знать о ваших собственных случаях использования!
PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.
Установка библиотеки
С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:
- Excel 2007;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- HTML;
- PDF;
- CSV.
Импорт данных из PHP в Excel
Рассмотрим пример по формированию таблицы умножения.
Рассмотрим еще на примере три полезные инструкции:
- $sheet->getColumnDimension('A')->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
- $sheet->getColumnDimension('B')->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
- $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.
Также обратите внимание на следующие необходимые для работы с отчетом методы:
- Методы для вставки данных в ячейку:
- setCellValue([$pCoordinate = 'A1' [, $pValue = null [, $returnCell = false]]]) — принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
- setCellValueByColumnAndRow([$pColumn = 0 [, $pRow = 1 [, $pValue = null [, $returnCell = false]]]]) — принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
- getCell([$pCoordinate = 'A1']) — принимает в качестве параметра координату ячейки;
- getCellByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) — принимает два параметра в виде номеров столбца и строки ячейки.
Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.
Оформление отчета средствами PHP в Excel
Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.
Назначить стиль ячейке можно тремя способами:
- Использовать метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами:
- fill — массив с параметрами заливки;
- font — массив с параметрами шрифта;
- borders — массив с параметрами рамки;
- alignment — массив с параметрами выравнивания;
- numberformat — массив с параметрами формата представления данных ячейки;
- protection — массив с параметрами защиты ячейки.
- pCellStyle – данный параметр является экземпляром класса PHPExcel_Style;
- pRange – диапазон ячеек.
Заливка
Значением параметра fill является массив со следующими необязательными параметрами:
- type — тип заливки;
- rotation — угол градиента;
- startcolor — значение в виде массива с параметром начального цвета в формате RGB;
- endcolor — значение в виде массива с параметром конечного цвета в формате ARGB;
- color — значение в виде массива с параметром начального цвета в формате RGB.
Или можно использовать следующие методы:
Вставка изображений
Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:
- setPath([$pValue = '', [$pVerifyFile = true]]) — данный метод принимает два параметра. В качестве первого параметра указывается путь к файлу с изображением. А второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false).
- setCoordinates([$pValue = 'A1'])) — принимает на вход один параметр в виде строки с координатой ячейки.
- setOffsetX([$pValue = 0]) — принимает один параметр со значением смещения по X от левого края ячейки.
- setOffsetY([$pValue = 0]) — принимает один параметр со значением смещения по Y от верхнего края ячейки.
- setWorksheet([$pValue = null, [$pOverrideOld = false]]) — этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр класса PHPExcel_Worksheet (текущий активный лист). Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее – произойдет его перезапись и соответственно изображение удалится.
Код демонстрирующий алгоритм вставки изображения приведен ниже:
Вот так выглядит отчет со вставленным изображением:
Шрифт
В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:
- name — имя шрифта;
- size — размер шрифта;
- bold — выделять жирным;
- italic — выделять курсивом;
- underline — стиль подчеркивания;
- strike — перечеркнуть;
- superScript — надстрочный знак;
- subScript — подстрочный знак;
- color — значение в виде массива с параметром цвета в формате RGB.
Или воспользоваться следующими методами:
Рамка
В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:
- тип рамки — (top|bootom|left|right|diagonal|diagonaldirection);
- style — стиль рамки;
- color — значение в виде массива с параметром цвета в формате RGB.
Так же можно прибегнуть к использованию следующих методов:
$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080'))).
Выравнивание
Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:
- horizontal — константа горизонтального выравнивания;
- vertical — константа вертикального выравнивания;
- rotation — угол поворота текста;
- wrap — разрешить перенос текста;
- shrinkToFit — изменять ли размер шрифта при выходе текста за область ячейки;
- indent — отступ от левого края.
Или использовать следующие методы:
Формат представления данных
Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.
А можно и воспользоваться методом:
Защита ячеек
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:
- locked — защитить ячейку;
- hidden — скрыть формулы.
Или использовать следующие методы:
Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это тремя способами. Первый способ заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.
Далее мы применим созданный нами стиль к ячейкам excel.
Сейчас применим тот же стиль, но используя другую методику.
Вот что у нас получилось:
Для получения данных о стиле конкретной ячейки необходимо использовать один из следующих методов, который вернет экземпляра класса PHPExcel_Style:
- getStyleByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) – применяется если требуется обратиться к ячейке по числовым координатам. Методу необходимо передать два параметра в виде номеров столбца и строки ячейки;
- getStyle([pCellCoordinate = 'A1']) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.
А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):
Добавление комментариев
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:
- getFont() – возвращает объект класса для работы со шрифтами PHPExcel_Style_Font.
- setFont([$pFont = null]))]) – данному методу требуется передать в качестве параметра объект класса PHPExcel_Style_Font.
Вот какой комментарий мы должны получить:
Вставка ссылки
Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:
Чтение данных из Excel
Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.
С помощью библиотеки PHPExcel можно читать следующие форматы:
- Excel 2007;
- Excel 5.0/Excel 95;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- Symbolic Link;
- CSV.
Для работы нам понадобятся объекты двух классов:
- PHPExcel_Worksheet_RowIterator – используется для перебора строк;
- PHPExcel_Worksheet_CellIterator – используется для перебора ячеек.
Для демонстрации выведем данные из таблицы с информацией об автомобилях.
Пример чтения файла представлен ниже:
Первый вариант
Второй вариант
В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:
- getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
- getHighestRow() – возвращает количество занятых строк в активном листе.
Другие полезные методы
Возможностей по работе с отчетами формата excel с использованием PHP как мы видим, достаточно много. Но мы рассмотрим еще несколько полезных методов, которые могут оказаться весьма полезны в работе:
- getMergeCells() – с помощью данного метода принадлежащего классу PHPExcel_Worksheet можно получить информацию обо всех объединенных ячейках в листе;
- setPreCalculateFormulas([$pCellStyle = true]) – данный метод необходимо использовать если требуется произвести расчет формул в листе (он имеется у двух классов: PHPExcel_Writer_Excel5 и PHPExcel_Writer_Excel2007). В рассматриваемый метод передается параметр типа boolean: true или false (если передать значение true, то расчет формул произойдет перед сохранением файла автоматически, иначе расчета формул не последует). Использование данного метода может оказаться полезным если созданный файл потребуется загрузить, к примеру на Google Drive. Ведь в таком случае расчет формул не будет произведен автоматически указанным сервисом и здесь вся ответственность ложиться на нас;
- stringFromColumnIndex([$pColumnIndex = 0]) – данный метод позволяет определить по номеру столбца его символьное представление, для этого в качестве параметра необходимо передать его номер;
- columnIndexFromString([$pString = 'A']) – с помощью данного метода можно определить номер столбца по его символьному представлению, для этого в качестве единственного параметра необходимо передать его обозначение.
Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
After my article “How To Make Microsoft Word Documents with PHP” (using Interop capability under Windows), there were quite a few comments urging a pure PHP implementation, i.e., only using a universal PHP library to manipulate Office files.
In this article, we will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis.
NOTE: There are a few PHP libraries that can provide Excel (and Office) file manipulations. The lib we use here is called PHPExcel, a subset of PHPOffice, which can be cloned here.
Objectives
After this tutorial, we will get:
- A sheet showing the game information (date played, teams, score, win/lose status) of my favorite NBA team – LA Lakers, in its 2013-14 season.
- A button that will export the data into an Excel 2013 file.
- That Excel file will be populated with some additional analytic data and a chart also generated by PHP and Excel.
Let’s get started.
Preparation
To use PHPExcel, we must have PHP version above 5.2.0. There are also 3 PHP extensions to be enabled: php_zip (which is essential to operate Office 2007 formats), php_xml and php_gd2 (optional, but required for exact column width auto-calculation).
Next, install the library via Composer.
Of course, we should have our database up and running. The data dump for this tutorial ( lakers.sql ) has been uploaded to the repo associated with this article. The data is retrieved with a simple SQL statement: “ select * from lakers ” (total 90 records, including 8 pre-season and 82 regular season games).
Also, this demo uses Silex as the MVC framework. Twig will be used as the template engine. Make sure the necessary dependencies are correctly specified in your composer.json file.
The index file
index.php will be the entry point for our Silex application. There will be two routes defined:
Route '/' will be our entry point and display the data and the “Export” button. Route '/export' will do the back end handling process that actually exports to Excel. Both functions are wrapped in a user-defined class ( classExcel.php ). In the rest of this article, we will focus on this file – or more precisely, the export function and related functions defined in this file and discuss several important aspects of Excel manipulation using the PHPExcel library.
Excel application and its meta data
When we click the icon to launch Excel, the Excel application starts. Under normal circumstances, it will also contain a workbook with 3 (in Excel 2013, only 1) worksheets. The worksheet is the “canvas” we play with. These are the two most important terms in Excel. Other important terms may include: cell, range, etc.
To instantiate an Excel file, we use:
An Excel application instance usually is mapped to a physical Excel file. It has its own meta data to describe the file that we create. The meta data is shown when we “Alt-Enter” an Excel file (or right click on that file and choose “Properties”):
The properties shown in the above dialog will have corresponding setXXXX methods to set these properties, where XXXX is almost identical to the property names listed in the dialog:
The methods ( setXXXX ) are quite self explanatory and map to the “Properties” dialog quite well. There are some discrepancies in the mapping, but they are not too difficult for us to make the connection (e.g., “Authors” will be mapped to setCreator ).
Worksheet and cell population
The worksheet is probably the object that we’ll manipulate the most: populating cells with data or formulas, applying styles, doing data filtering, inserting a chart, etc.
To get the reference to a worksheet, we use:
The sheets in a workbook are always 0-indexed. So the 1st (and up to now the only) sheet will be Sheet Zero. The default name of this sheet is always “Worksheet” and we can change it with the setTitle method.
To populate a cell/cells, we have at least two options:
- For those headings, titles, other descriptive items, we will populate them one by one using setCellValue method.
- For structured data, most of which comes from an SQL select statement, we will use the fromArray method.
The fromArray method takes 3 parameters:
1. the data source, in array form;
2. a “filler” value in case the data is NULL;
3. a cell reference to start the filling (from left to right, then up to down).NOTE: When we use PDO to fetch the data, a simple $res = $q->fetchAll(\PDO::FETCH_ASSOC); call will force the returned result data set to contain an associated array only, without index. If fetchall is called without the option PDO::FETCH_ASSOC , the result set will actually contain two sets of identical data, one in associated array form, one in indexed form and will create duplicates in the Excel file when using fromArray .
We may also want to style the header row (ID, Season, etc). To do that, we also have two ways:
One way is to use some get methods to retrieve the style object that we want to change and change it. We do this for the “background fill” style.
The other is to declare a “style” array and specify the styles we want to change and what to change them to. Then, we use applyFromArray to apply the styles in a batch. Here we changed the font and the alignment.
Both methods support range as their parameter ( $header='a1:h1'; ), which is very convenient.
Finally, we may want to adjust the column width so they will fit the max displayed length in each column:
Unfortunately, this does not support a range parameter, so we use a for loop to make this happen.
If we save the file now – we will discuss saving later – we will see that the XLSX file is filled with the data and properly formatted:
Adding another sheet and inserting formulas
I always use a separate sheet to store the original data and at least one more sheet to display the summary and/or analytic information.
To insert a new worksheet, we do:
The addSheet method takes two parameters.
- $ews2 : the Excel worksheet instance that we are to insert;
- $location : the index of this worksheet. So 0 means it should become the 1st one. -1 means it should be the last one.
With the worksheet inserted, we can populate the cells in this worksheet as usual and apply styles. In this sheet, we will use formulas:
You see, it is no different from what we have done in the previous section. The formula string is just like the one we will input in an Excel file to perform the necessary calculation.
NOTE: Please pay special attention to the cell reference ( G2:G91 ). A lazy way of writing that formula is to use a range like G:G . This works fine when there is NO chart in the sheet. If there is a chart, the G:G notation will fail, throwing an exception.
This “Summary” sheet looks like this:
The % shown in cell B4 is set by the following code:
Please note some styling issues here. For A1 , I have applied the following style:
The result shows that the font weight, font size, and alignment are correctly applied. The merging of A1 and B1 into A1 is also done correctly. But, the setAutoSize method fails on this merged cell. The result is that this cell ( A1 ) is still squeezed. This means auto width calculation will not always work. Well, not a big deal, anyway.
A picture is worth a thousand of words
It is always nice to have a visual representation of our data, so the chart will come in handy. Excel has a rich set of built-in charts for us to choose from. PHPExcel can tap into almost all of these. The first chart we are to create is a line chart showing the ups and downs of the scores in each game of the Lakers team and its opponent.
Creating a chart is a lengthy coding job, even with the support of a library. The full code of this process can be found in the addChart1 and addChart2 methods that reside in our classExcel.php file. I will just explain the key steps.
A data series label identifies data series by giving it a name (label). In our case, if we want to show the scores of Lakers and their opponent, we are looking at two labels: Self Score and Opponent Score . Their labels can be found in D1 and E1 respectively:
A Data Series Label is actually a \PHPExcel_Chart_DataSeriesValues instance. The constructor contains four parameters:
- The type. For a label, no doubt it should be “String”;
- Source. It is in D1 or E1 ;
- Format. Normally, providing NULL is sufficient and the default format will be used;
- Count. How much data in the Source. It should normally be 1.
- X Axis Value Label
This identifies the label for the X Axis. For example, on “2013-11-15”, Lakers scored 86 and their opponent scored 89. The “2013-11-15” is the label for those two scores. For our case, we will use the “Date Played” column from row 2 to row 91:
The constructor is the same, so are the parameters.
We will use “Self Score” (column D) and “Opponent Score” (column E). Both are from row 2 to row 91.
After we have the above 3 critical variables, we can set up the Data Series. In Excel, a data series contains the following information essential to creating a chart:
- Chart Type
- Grouping
- Count of data series values
- Data Series Label
- X Axis Value Label
- Data Series Values
And the constructor is called by simply passing all these parameters:
Next, we will create the Plot Area and Legend:
A plot area contains a layout of the chart and the Data Series. The layout can specify whether the chart shall show values, percentages, etc. We can use NULL so that a default layout will be used.
A legend is used to provide a visual representation of the data groups.
And now, finally, we can create the chart:
The only new parameter in this constructor is the name of the chart. “chart1” will be good enough. A title of the chart can be created via:
After the chart is created, we set its position and size by setting its top-left and bottom right corners’ coordinate and insert it into the worksheet.
NOTE: Most of the time, a cell reference is case insensitive, but please use CAPITAL letter + number when there is a chart in the sheet.
Save the file
As the last step, we save the file so that the user can download it:
It uses a factory pattern to create a writer object to save the file. A format will be specified (we use “Excel2007” format in our case).
Be sure that we have setIncludeCharts(true) in the saving process, otherwise the chart won’t be there.
Remember when I said PHPExcel could tap into almost all chart types? One exception that this lib can’t do well in Excel 2013 is that it does not produce a usable pie chart. In our output.xlsx and our code, we have actually created a pie chart (done in addChart1 ) but when opening the output.xlsx , Excel 2013 will prompt an error. If we choose continue, the pie chart will be lost and only the line chart (done in addChart2 ) will be preserved. A bug report has already been filed into its Git repo.
Now the “Data” sheet will look like this:
and a zoom-in view of the chart. It is correctly positioned and sized:
Conclusion
In this article, we demonstrated how to use pure PHP and a pure PHP lib (PHPExcel) to manipulate Excel. We created a new file, populated the data, added in a new sheet and analytic data, inserted a chart, and finally saved the file for download.
In general, I found this PHPExcel lib worth trying and easy to learn. Its code insight in an IDE can help us a lot when programming.
We have not covered other common features in an Excel file – we’ll write a followup article on those if the interest is high enough. Let us know!
Its official documentation and examples are a nice place to find detailed API usage explanations and workable code snippets for common tasks. Read them thoroughly. They can be found in the cloned repo. Give this library a try and let us know of your own use cases!
Share This Article
Taylor is a freelance web and desktop application developer living in Suzhou in Eastern China. Started from Borland development tools series (C++Builder, Delphi), published a book on InterBase, certified as Borland Expert in 2003, he shifted to web development with typical LAMP configuration. Later he started working with jQuery, Symfony, Bootstrap, Dart, etc.
Построение графиков на PHP возможно двумя способами. Можно нарисовать самостоятельно или использовать какую-нибудь библиотеку. Я предлагаю научиться создавать графики самим. Во-первых, потому что хорошие библиотеки являются платными. Во-вторых, используя любую библиотеку, вы ограничены её функционалом. Лучше рисовать графики самостоятельно.
Для начала рассмотрим самый простой вариант. Есть массив, содержащий количество пользователей, которые зарегистрировались на сайте за 5 дней.
Выведем этот массив на страницу в виде графика. Сначала нарисуем оси координат.
3 - создаём изображение
9-13 - засечки на оси x
14-18 - цифры на оси x
19, 20 - засечки на оси y
21, 22 - цифры на оси y
Рисунок должен выглядеть так:
Чтобы нарисовать линию на графике, нужно найти на нём точки, по которым линия будет строиться. На практике можно сразу находить точку и рисовать по ней линию. Но чтобы пример был более понятным, мы разделим задачу на две части. Сначала найдём все точки, потом нарисуем по ним график. Для этого создадим двухмерный массив, в котором будут координаты x и y каждой точки. Для поиска координат нам нужна точка отсчёта. Это та точка, в которой пересекаются оси координат. В нашем рисунке она имеет координаты
Для определения положения каждой точки нужно использовать масштаб графика. В примере мы задали масштаб сами. По оси x для каждого дня создано 100 пикселей. Первый день находится на расстоянии 100 пикселей от начала отсчёта, второй на расстоянии 200 пикселей и так далее. Значит координата x
для первого дня: 50 + 100 = 150
для второго: 50 + 200 = 250.
По оси Y для каждых 10 пользователей выделено 100 пикселей. Значит для одного пользователя - 10 пикселей. Нужно учитывать, что у изображения ось Y направлена вниз, значит нужно не прибавлять значение к точке отсчёта, а вычитать. В первый день на сайте зарегистрировались 2 посетителя.
Координата для этого значения равна: 250 - (2 * 10) = 230
Для второго дня: 250 - (7 * 10) = 180
Таким образом, массив с координатами точек получается такой:
Теперь по ним построим график. Для этого запустим цикл, в котором будем рисовать линии по соседним точкам:
График выглядит так:
Мы рассмотрели самый простой вариант построения графика. Я заранее знал количество дней и максимальное число пользователей. Поэтому я сам выбрал масштаб графика. Но обычно эти данные могут быть разными и масштаб нужно расчитывать. И сам график выглядит немного иначе. Нам привычно, что оси координат начинаются с нуля. Но для удобства отображения это правило иногда не соблюдается. Ось X начинается не с 0, а с 1, чтобы слева от линии не было пустого пространства. А ось Y показывается так, чтобы на график поместились все значения. Например, если минимальное значение: 60, а максимальное: 70, то ось Y содержит именно такой диапазон. Рассмотрим создание такого графика. Создадим для этого новый скрипт.
В примере ось X имеет длину 520 пикселей. Её нужно разделить на одинаковые сегменты для каждого дня. Ось X начинаться не с 0, а с 1. Поэтому сегмента между 0 и 1 не будет и количество сегментов становится на 1 меньше. Если дней 5, то получится 4 сегмента. Формула расчёта такая:
длина сегмента = длина оси / (число дней - 1)
Число может получится дробное, поэтому результат нужно округлить в меньшую сторону.
570 - 50 это длина оси X . Она зависит от размеров изображения.
Ось Y делаем такой, чтобы поместились все значения. Нужно получить минимальное и максимальное значения. Они должны занять всю высоту графика. Зная диапазон, мы сможем рассчитать, сколько пикселей должно соответствовать одному пользователю на графике.
диапазон = максимальное - минимальное
длина сегмента = длина оси / диапазон
Полученное число также нужно округлить.
Сами оси рисуем, как в предыдущем примере:
Поставим засечки и напишем числа:
15 - цикл для каждого дня. Нулевого значения не будет
17 - координата X для засечки. 50 - это точка отсчёта
18 - для первого дня засечки не нужно, он находится в начале оси X
19 - рисуем засечку
20 - пишем цифру
На оси Y не будем ставить засечки для каждого сегмента. Поставим на максимуме и в середине:
22 - координата Y для засечки. 250 - это точка отсчёта. У изображений ось Y направлена вниз, поэтому не прибавляем к точке отсчёта, а вычитаем. При расчёте координаты Y всегда будем отнимать минимальное значение в массиве. Тогда график опуститься вниз, и минимальное значение на графике совпадёт с точкой отсчёта
23 - рисуем засечку
24 - число, которое нужно написать
25 - пишем число
Остальные цифры ставятся таким же способом.
Строим график. Координаты точек, по которым будем рисовать линии, определяются так:
x = точка отсчёта + номер дня * длина сегмента
y = точка отсчёта - (количество пользователей - минимальное значение) * длина сегмента
Читайте также: