После моей статьи « Как создавать документы 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. Будет определено два маршрута:
$app->get('/', function () use ($app) { $c=new trExcel\Excel(); return $c->index($app); }); $app->post('/export', function () use ($app) { $c=new trExcel\Excel(); return $c->export($app); });
Маршрут '/' будет нашей точкой входа и отображает данные и кнопку «Экспорт». Маршрут '/export' будет выполнять процесс обработки, который фактически экспортируется в Excel. Обе функции заключены в пользовательский класс ( classExcel.php ). В оставшейся части этой статьи мы сосредоточимся на этом файле — или, точнее, на функции export и связанных с ней функциях, определенных в этом файле, и обсудим несколько важных аспектов манипулирования Excel с использованием библиотеки PHPExcel.
Приложение Excel и его метаданные
Когда мы нажимаем значок, чтобы запустить Excel, приложение Excel запускается. В обычных условиях он также будет содержать рабочую книгу с 3 (в Excel 2013, только 1) рабочими листами. Рабочий лист — это «холст», с которым мы играем. Это два самых важных термина в Excel. Другие важные термины могут включать в себя: ячейка, диапазон и т. Д.
Чтобы создать экземпляр файла Excel, мы используем:
$ea = new \PHPExcel(); // ea is short for Excel Application
Экземпляр приложения Excel обычно сопоставляется с физическим файлом Excel. У него есть собственные метаданные для описания файла, который мы создаем. Метаданные отображаются, когда мы «Alt-Enter» файл Excel (или щелкните правой кнопкой мыши на этом файле и выберите «Свойства»):
Свойства, показанные в диалоговом окне выше, будут иметь соответствующие методы setXXXX для установки этих свойств, где XXXX практически идентичен именам свойств, перечисленным в диалоговом окне:
$ea->getProperties() ->setCreator() ->setTitle('PHPExcel Demo') ->setLastModifiedBy() ->setDescription('A demo to show how to use PHPExcel to manipulate an Excel file') ->setSubject('PHP Excel manipulation') ->setKeywords('excel php office phpexcel lakers') ->setCategory('programming') ;
Методы ( setXXXX ) достаточно setXXXX и довольно хорошо отображаются в диалоге «Свойства». Есть некоторые несоответствия в отображении, но они не слишком сложны для нас, чтобы установить соединение (например, «Авторы» будут сопоставлены с setCreator ).
Рабочий лист и популяция клеток
Рабочий лист — это, вероятно, объект, которым мы будем больше всего манипулировать: заполнение ячеек данными или формулами, применение стилей, выполнение фильтрации данных, вставка диаграммы и т. Д.
Чтобы получить ссылку на рабочий лист, мы используем:
$ews = $ea->getSheet(0); $ews->setTitle('Data');
Листы в рабочей книге всегда индексируются 0. Таким образом, 1-й (и до сих пор единственный) лист будет Sheet Zero. Имя этого листа по умолчанию всегда «Рабочий лист», и мы можем изменить его с setTitle метода setTitle .
Для заполнения ячейки / ячеек у нас есть как минимум два варианта:
- Для этих заголовков, заголовков и других описательных элементов мы будем заполнять их один за другим, используя метод
setCellValue. - Для структурированных данных, большая часть которых поступает из оператора
selectSQL, мы будем использовать методfromArray.
$ews->setCellValue('a1', 'ID'); // Sets cell 'a1' to value 'ID $ews->setCellValue('b1', 'Season'); ... //Fill data $ews->fromArray($data, ' ', 'A2');
Метод fromArray принимает 3 параметра:
1. источник данных в виде массива;
2. значение «заполнитель», если данные равны NULL;
3. ссылка на ячейку, чтобы начать заполнение (слева направо, затем вверх).
ПРИМЕЧАНИЕ. Когда мы используем PDO для извлечения данных, просто $res = $q->fetchAll(\PDO::FETCH_ASSOC); вызов заставит возвращенный набор данных результата содержать только связанный массив, без индекса. Если fetchall вызывается без опции PDO::FETCH_ASSOC , результирующий набор будет фактически содержать два набора идентичных данных, один в связанной форме массива, другой в индексированной форме, и будет создавать дубликаты в файле Excel при использовании fromArray .
Мы также можем захотеть стилизовать строку заголовка ( ID , сезон и т. Д.). Для этого у нас также есть два способа:
$header = 'a1:h1'; $ews->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00ffff00'); $style = array( 'font' => array('bold' => true,), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,), ); $ews->getStyle($header)->applyFromArray($style);
Одним из способов является использование некоторых методов get для извлечения объекта стиля, который мы хотим изменить, и его изменения. Мы делаем это для стиля «фоновой заливки».
Другой заключается в объявлении массива «style» и указании стилей, которые мы хотим изменить, и на что их изменить. Затем мы используем applyFromArray для применения стилей в пакете. Здесь мы изменили шрифт и выравнивание.
Оба метода поддерживают диапазон как свой параметр ( $header='a1:h1'; ), что очень удобно.
Наконец, мы можем захотеть отрегулировать ширину столбца, чтобы они соответствовали максимальной отображаемой длине в каждом столбце:
for ($col = ord('a'); $col <= ord('h'); $col++) { $ews->getColumnDimension(chr($col))->setAutoSize(true); }
К сожалению, это не поддерживает параметр диапазона, поэтому мы используем цикл for чтобы это произошло.
Если мы сохраним файл сейчас — мы обсудим сохранение позже — мы увидим, что файл XLSX заполнен данными и правильно отформатирован:
Добавление другого листа и вставка формул
Я всегда использую отдельный лист для хранения исходных данных и как минимум еще один лист для отображения сводной и / или аналитической информации.
Чтобы вставить новый лист, мы делаем:
$ews2 = new \PHPExcel_Worksheet($ea, 'Summary'); $ea->addSheet($ews2, 0); $ews2->setTitle('Summary');
Метод addSheet принимает два параметра.
-
$ews2: экземпляр листа Excel, который мы должны вставить; -
$location: индекс этой таблицы. Таким образом, 0 означает, что он должен стать первым. -1 означает, что он должен быть последним.
Вставив рабочий лист, мы можем заполнить ячейки в этом рабочем листе как обычно и применить стили. В этом листе мы будем использовать формулы:
$ews2->setCellValue('b2', '=COUNTIF(Data!G2:G91, "W")-COUNTIF(Data!G2:G9, "W")'); $ews2->setCellValue('b3', '=COUNTIF(Data!G2:G91, "L")-COUNTIF(Data!G2:G9, "L")'); $ews2->setCellValue('b4', '=b2/(b2+b3)');
Видите ли, это ничем не отличается от того, что мы сделали в предыдущем разделе. Строка формулы аналогична той, которую мы введем в файл Excel для выполнения необходимых вычислений.
ПРИМЕЧАНИЕ. Обратите особое внимание на ссылку на ячейку ( G2:G91 ). Ленивый способ написания этой формулы — использовать такой диапазон, как G:G Это прекрасно работает, когда на листе нет графика. Если есть диаграмма, нотация G:G потерпит неудачу, выдав исключение.
Этот «сводный» лист выглядит так:
Показанный в ячейке B4 устанавливается с помощью следующего кода:
$ews->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
Пожалуйста, обратите внимание на некоторые проблемы с дизайном здесь. Для A1 я применил следующий стиль:
$ews2->setCellValue('a1', 'Lakers 2013-2014 Season'); $style = array( 'font' => array('bold' => true, 'size' => 20,), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,), ); $ews2->mergeCells('a1:b1'); $ews2->getStyle('a1')->applyFromArray($style); $ews2->getColumnDimension('a')->setAutoSize(true);
Результат показывает, что вес шрифта, размер шрифта и выравнивание применяются правильно. Слияние A1 и B1 в A1 также сделано правильно. Но метод setAutoSize терпит неудачу в этой объединенной ячейке. В результате эта ячейка ( A1 ) все еще сжата. Это означает, что автоматический расчет ширины не всегда будет работать. Ну, в общем, ничего страшного.
Картинка стоит тысячи слов
Всегда приятно иметь визуальное представление наших данных, поэтому диаграмма пригодится. В Excel есть богатый набор встроенных диаграмм, из которых мы можем выбирать. PHPExcel может использовать почти все из них. Первый график, который мы должны создать, это линейный график, показывающий взлеты и падения очков в каждой игре команды Лейкерс и ее противника.
Создание диаграммы — это длительная работа по кодированию, даже при поддержке библиотеки. Полный код этого процесса можно найти в addChart1 и addChart2 которые находятся в нашем файле classExcel.php . Я просто объясню ключевые шаги.
- Метки серии данных
Метка ряда данных идентифицирует ряд данных, присваивая ему имя (метку). В нашем случае, если мы хотим показать результаты «Лейкерс» и их оппонента, мы смотрим на две метки: « Self Score и « Opponent Score . Их метки можно найти в D1 и E1 соответственно:
$dsl=array( new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$D$1', NULL, 1), new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$E$1', NULL, 1), );
Метка серии данных на самом деле является экземпляром \PHPExcel_Chart_DataSeriesValues . Конструктор содержит четыре параметра:
- Тип. Для метки, без сомнения, это должно быть «String»;
- Источник. Это в
D1илиE1; - Формат. Обычно достаточно указать NULL, и будет использоваться формат по умолчанию;
- Граф. Сколько данных в источнике. Обычно должно быть 1.
- Метка значения оси X
Это идентифицирует метку для оси X. Например, в «2013-11-15» Лейкерс набрал 86, а их соперник — 89. « 2013-11-15 » — это метка для этих двух результатов. В нашем случае мы будем использовать столбец «Дата воспроизведения» от строки 2 до строки 91:
$xal=array( new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$F$2:$F$91', NULL, 90), );
Конструктор такой же, как и параметры.
- Значения ряда данных
Мы будем использовать «Self Score» (столбец D) и «Opponent Score» (столбец E). Оба из ряда 2 в ряд 91.
$dsv=array( new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$D$2:$D$91', NULL, 90), new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$E$2:$E$91', NULL, 90), );
После того, как у нас есть 3 вышеупомянутых критических переменных, мы можем установить Ряд данных. В Excel ряд данных содержит следующую информацию, необходимую для создания диаграммы:
- Тип диаграммы
- Группировка
- Количество значений ряда данных
- Метка серии данных
- Метка значения оси X
- Значения ряда данных
И конструктор вызывается просто передачей всех этих параметров:
$ds=new \PHPExcel_Chart_DataSeries( \PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv)-1), $dsl, $xal, $dsv );глубинный$ds=new \PHPExcel_Chart_DataSeries( \PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv)-1), $dsl, $xal, $dsv );
Далее мы создадим область сюжета и легенду:
$pa=new \PHPExcel_Chart_PlotArea(NULL, array($ds)); $legend=new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
Область графика содержит макет диаграммы и ряд данных. В макете можно указать, будут ли диаграммы отображать значения, проценты и т. Д. Мы можем использовать NULL, чтобы использовать макет по умолчанию.
Легенда используется для визуального представления групп данных.
И вот, наконец, мы можем создать график:
$chart= new \PHPExcel_Chart( 'chart1', $title, $legend, $pa, true, 0, NULL, NULL ); $chart->setTopLeftPosition('K1'); $chart->setBottomRightPosition('M5'); $ews->addChart($chart);в$chart= new \PHPExcel_Chart( 'chart1', $title, $legend, $pa, true, 0, NULL, NULL ); $chart->setTopLeftPosition('K1'); $chart->setBottomRightPosition('M5'); $ews->addChart($chart);-$chart= new \PHPExcel_Chart( 'chart1', $title, $legend, $pa, true, 0, NULL, NULL ); $chart->setTopLeftPosition('K1'); $chart->setBottomRightPosition('M5'); $ews->addChart($chart);-$chart= new \PHPExcel_Chart( 'chart1', $title, $legend, $pa, true, 0, NULL, NULL ); $chart->setTopLeftPosition('K1'); $chart->setBottomRightPosition('M5'); $ews->addChart($chart);
Единственный новый параметр в этом конструкторе — это name диаграммы. «Chart1» будет достаточно хорош. Название диаграммы может быть создано с помощью:
$title=new \PHPExcel_Chart_Title('Any literal string');
После того, как диаграмма создана, мы устанавливаем ее положение и размер, устанавливая координаты ее левого верхнего и нижнего правого углов и вставляя ее в лист.
ПРИМЕЧАНИЕ. В большинстве случаев ссылка на ячейку не чувствительна к регистру, но, пожалуйста, используйте ЗАГЛАВНУЮ букву + число, если на листе есть диаграмма.
Сохранить файл
В качестве последнего шага мы сохраняем файл, чтобы пользователь мог его скачать:
$writer = \PHPExcel_IOFactory::createWriter($ea, 'Excel2007'); $writer->setIncludeCharts(true); $writer->save('output.xlsx');-$writer = \PHPExcel_IOFactory::createWriter($ea, 'Excel2007'); $writer->setIncludeCharts(true); $writer->save('output.xlsx');
Он использует фабричный шаблон для создания объекта записи для сохранения файла. Будет указан формат (в нашем случае мы используем формат «Excel2007»).
Убедитесь, что мы установили setIncludeCharts(true) в процессе сохранения, иначе диаграммы там не будет.
Помните, когда я говорил, что PHPExcel может использовать почти все типы графиков? Единственное исключение, которое эта библиотека не может сделать хорошо в Excel 2013, — то, что она не производит пригодную круговую диаграмму. В нашем output.xlsx и нашем коде мы фактически создали addChart1 диаграмму (сделано в addChart1 ), но при открытии output.xlsx Excel 2013 выдаст сообщение об ошибке. Если мы выберем продолжить, круговая диаграмма будет потеряна, и будет сохранена только линейная диаграмма (выполненная в addChart2 ). Отчет об ошибке уже внесен в репозиторий Git.
Теперь лист данных будет выглядеть так:
и увеличенный вид диаграммы. Он правильно расположен и имеет размеры:
Вывод
В этой статье мы продемонстрировали, как использовать чистый PHP и чистую PHP-библиотеку (PHPExcel) для управления Excel. Мы создали новый файл, заполнили данные, добавили новый лист и аналитические данные, вставили диаграмму и, наконец, сохранили файл для загрузки.
В общем, я нашел этот PHPExcel lib достойным внимания и легким в освоении. Его понимание кода в IDE может очень помочь нам при программировании.
Мы не рассмотрели другие общие функции в файле Excel — мы напишем дополнительную статью о них, если интерес будет достаточно высоким. Дайте нам знать!
Его официальная документация и примеры — хорошее место, где можно найти подробные объяснения использования API и рабочие фрагменты кода для общих задач. Прочитайте их полностью. Их можно найти в клонированном репо. Попробуйте эту библиотеку и дайте нам знать о ваших собственных случаях использования!