Создание Excel-файлов с помощью PHPExcel

Главная > php> Создание Excel-файлов с помощью PHPExcel

Создание Excel-файлов с помощью PHPExcel

Коммерция, а в особенности торговля никогда не обходится без отчетности. Отчеты нужны для эффективного анализа текущего положения дел в бизнесе. В таблицы можно вводить различные данные: числа, текст, даты и т.д. Excel позволяет не просто выстраивать таблицы с данными. Он позволяет делать вычисления над данными таблиц. Кроме того, можно сортировать и фильтровать данные. Как только бизнес начал занимать просторы Интернета, сюда начали переносить все решения для ведения бизнеса на персональных компьютерах. Excel не стал исключением. Сегодня я хочу рассказать про очень удобную объектно-ориентированную библиотеку для работы с Excel-файлами, которая имеет название PHPExcel.

Библиотека
Скачать библиотеку можно со странички Downloads официального сайта. Текущая доступная версия имеет номер 1.7.1. На сайте доступно несколько различных архивов для скачивания.

PHPExcel 1.7.1 – вся библиотека со всей документацией (описанием и API);
PEAR PHPExcel 1.7.1 – библиотека в пакетах PEAR;
Documentation – документация (PHPExcel Developer Documentation);
Calculation function reference – документация по функциям Excel.

Рекомендую скачать всю библиотеку со всей документацией. Размер распакованных данных внушительный (ок. 76 Мб), но большая часть – это документация. Сама библиотека «весит» всего 6 (!) Мб, что по моим меркам тоже кажется достаточно большим. Но с другой стороны, библиотека проводит много преобразований элементов без сторонних компонентов, поэтому размер должен быть не маленьким.
Распространяется PHPExcel под лицензией LGPL, поэтому проблем с лицензией быть не должно (если это кому-то важно ).
Системные требования библиотеки тоже не маленькие:

PHP 5.2 или выше;
php_zip – расширение для работы с Zip-архивами;
php_xml – расширение для работы с XML;
php_gd2 – расширение для работы с рисунками
mb_string – расширения для работы с кодировками. Библиотека работает исключительно с UTF-8.

Быстрый старт
После распаковки архива получится папка PHPExcel, в которой находится папка Classes. Именно здесь располагаются файлы библиотеки. Чтобы не было проблем с путями библиотеки при внедрении в различные фреймворки, лучше всего установить путь к классам через set_include_path().
Вот пример создания небольшого Excel-файла.
excel.php

<?php
set_include_path(get_include_path() . PATH_SEPARATOR .
'PhpExcel/Classes/');
//подключаем и создаем класс PHPExcel
include_once 'PHPExcel.php';
$pExcel = new PHPExcel();
$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();
$aSheet->setTitle('Первый лист');
//устанавливаем данные
//номера по порядку
$aSheet->setCellValue('A1','№');
$aSheet->setCellValue('A2','1');
$aSheet->setCellValue('A3','2');
$aSheet->setCellValue('A4','3');
$aSheet->setCellValue('A5','4');
//названия сайтов
$aSheet->setCellValue('B1','Названия');
$aSheet->setCellValue('B2','http://www.web-junior.net');
$aSheet->setCellValue('B3','http://www.google.com');
$aSheet->setCellValue('B4','http://www.yandex.ru');
$aSheet->setCellValue('B5','http://www.twitter.com');
//мой личный рейтинг
$aSheet->setCellValue('C1','Рейтинг');
$aSheet->setCellValue('C2','100');
$aSheet->setCellValue('C3','99');
$aSheet->setCellValue('C4','90');
$aSheet->setCellValue('C5','85');
//устанавливаем ширину
$aSheet->getColumnDimension('B')->setWidth(25);
//отдаем пользователю в браузер
include("PHPExcel/Writer/Excel5.php");
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="rate.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
?>

В результате получается вот такой чудо-рейтинг.

Самый правильный рейтинг всех времен и народов

Разъяснения
Теперь по порядку. Класс PHPExcel является ядром библиотеки. Он хранит в себе все листы (Sheet) книги Excel.
Для установки активного листа (тот с которым будем работать), нужно вызвать метод setActiveSheetIndex() и передать ему индекс листа по порядку. Индексация начинается с нуля, поэтому первый лист будет иметь индекс 0, второй – 1, третий – 2 и т.д.
Метод getActiveSheet() вернет активный лист. Это объект класса PHPExcel_Worksheet. Поэтому код

$aSheet = $pExcel->getActiveSheet();

Запишет в переменную $aSheet активный лист.
Класс PHPExcel_Worksheet имеет множество методов для работы с листом, ячейками, строками, столбцами и др.
Для установки значения ячейки нужно вызвать метод setCellValue() активного листа.

setCellValue( [string $pCoordinate = 'A1'],
[mixed $pValue = null])

В метод передаются два параметра:

string $pCoordinate – номер ячейки – нумерация идет также как и в Excel, напр. A1, C5, D8 и т.д.
mixed $pValue – значение, которое устанавливается в эту ячейку.

Тот столбец, в котором у нас вписаны сайты, получается слишком узким, поэтому нужно установить ширину для столбца B. Столбец мы можем получить с помощью метода getColumnDimension() активного листа ($aSheet)

PHPExcel_Worksheet_ColumnDimension getColumnDimension(
[string $pColumn = 'A'])

В качестве параметра передаем ему строку с именем нужного столбца (в нашем случае это B). Метод вернет объект столбца. Столбец представлен классом PHPExcel_Worksheet_ColumnDimension. Сейчас нас интересует только один из его методов. А именно setWidth().

PHPExcel_Worksheet_ColumnDimension setWidth(
[double $pValue = -1])

В качестве единственного параметра передаем устанавливаемую ширину столбца.

Ширина (и высота) в Excel может измеряться тремя способами:

внутренняя ширина в символьных единицах (напр. 8,43. Этот вид чаще всего отображается в Excel);
полная ширина в пикселях (напр. 64 pixels);
полная ширина в символьных единицах (напр. 9,140625, -1 указывает на то, что ширина отключена).

Библиотека PHPExcel работает с третьим видом меры, поскольку этот вид хранится во всех версиях Excel.
Теперь нужно сохранить наше творение. Для сохранения доступно несколько классов. Каждый сохранит по-своему, в своем формате. Доступны форматы Excel5, Excel2007, HTML, PDF и др. Эти классы расположены в папке PHPExcel/Writer/ библиотеки. Они имеют название PHPExcel_Writer_*, где вместо * вставляем соответствующий формат.
Сохранение происходит следующим образом: в конструктор «классу-записывателю» передается объект класса PHPExcel, т.е. нашей книги. После чего нужно вызвать метод save(), которому передается имя файла.

void save($pFilename = null)

В этот файл будет записана Excel-книга в соответствующем формате. Если в качестве имени файла передать строку ‘php://output’, то файл не будет сохранен, а будет выведен в браузер.

Украшательства
Теперь рассмотрим пример работы со стилями. Прежде всего со шрифтами.
excel.php

…
//настройки для шрифтов
$baseFont = array(
	'font'=>array(
		'name'=>'Arial Cyr',
		'size'=>'10',
		'bold'=>false
	)
);
$boldFont = array(
	'font'=>array(
		'name'=>'Arial Cyr',
		'size'=>'10',
		'bold'=>true
	)
);
//и позиционирование
$center = array(
	'alignment'=>array(
		'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical'=>PHPExcel_Style_Alignment::VERTICAL_TOP
	)
);
//установим жирный шрифт для заголовков
//и заодно отцентрируем
$aSheet->getStyle('A1')->applyFromArray($boldFont)
->applyFromArray($center);
$aSheet->getStyle('B1')->applyFromArray($boldFont)
->applyFromArray($center);
$aSheet->getStyle('C1')->applyFromArray($boldFont)
->applyFromArray($center);
//и основной шрифт для всех остальных
for($i=2;$i<6;$i++){
	$aSheet->getStyle('A'.$i)->applyFromArray($baseFont);
	$aSheet->getStyle('B'.$i)->applyFromArray($baseFont);
	$aSheet->getStyle('C'.$i)->applyFromArray($baseFont);
}
…

Вот так выглядят изменения

Чудо-рейтинг меняет облик

Для настройки стилей ячейки есть класс PHPExcel_Style. Добраться до него можно из активного листа ($aSheet), вызвав метод getStyle()

PHPExcel_Style getStyle( [string $pCellCoordinate = 'A1'])

В качестве параметра передается строка с именем ячейки, для которой извлекается класс стилей. У класса стилей есть чудесный метод, который позволяет вынести все настройки в массив. Этот метод называется applyFromArray(). В качестве параметра ему передается массив с различными настройками. Для установки различных видов стилевых параметров нужно использовать различные секции в массиве.

font – используется для настроек шрифтов ячейки;
alignment – используется для настроек позиционирования;
color – используется для настроек цвета ячейки;
fill – используется для задания заливки ячейки
и другие, подробнее см. документацию.

Формулы
Все Excel-файлы поддерживают формулы, для вычисления значений ячеек. PHPExcel тоже поддерживает добавление формул в ячейки. К сожалению в формулах содержится недостаток библиотеки PHPExcel. Все функции формул на английском языке. Как называются функции можно посмотреть в документации. Установить формулу можно так
excel.php

….
$aSheet->setCellValue('B6','Всего:')->getStyle('B6')
->applyFromArray($boldFont)->applyFromArray($center);
//формула для вычисления суммы балов
//наших сайтов
$formul = '=SUM(C2:C5)';
$aSheet->getCell('C6')->setDataType(
PHPExcel_Cell_DataType::TYPE_FORMULA)->setValue($formul);
…

Вместе мы набрали много баллов

Второй способ попроще

...
$aSheet->setCellValue('B6','Всего:')->getStyle('B6')
->applyFromArray($boldFont)->applyFromArray($center);
//формула для вычисления суммы балов
//наших сайтов
$formul = '=SUM(C2:C5)';
$aSheet->setCellValue('C6',$formul);
...

т.е. через все тот-же setCellValue()

На основе всего вышеизложенного можно уверенно заявить, что библиотека PHPExcel обладает достаточно мощными средствами для создания таблиц Excel и сохранения этих таблиц в различные форматы файлов.

Читайте все статьи цикла:

Создание Excel-файлов с помощью PHPExcel
Чтение Excel-файлов с помощью PHPExcel
Часто задаваемые вопросы по PHPExcel
Кириллические формулы в PHPExcel

Популярность: 100%

Интересное из других блогов:

2leep.com

И не забывайте комментировать статью.

Метки: Excel, php, PHPExcel, xls

php, Технологии | web-junior 4 Января 2010 года

Добавляйся в группу во вконтакте, чтобы самым первым узнавать все новости сайта

Отзывов: 233 на «Создание Excel-файлов с помощью PHPExcel»

« Предыдущие 1 ... 6 7 8 9 10 ... 12 Следующие »

Автор: TOTO, 10 января 2011 в 19:13
использует Firefox 3.6.13 на Windows XP
Может кому понадобится – столкнулся с проблемой надо было создавать excel файлы в цикле, выскакивала ошибка: не могу переопределить PHPExcel_Writer_Excel5, решил заменив строку include(“PHPExcel/Writer/Excel5.php”); на include_once (“PHPExcel/Writer/Excel5.php”);

Ответить
- Автор: web-junior, 10 января 2011 в 19:22
  использует Firefox 3.6.12 на Windows 7
  Здравствуйте.
  Спасибо за комментарий. Ошибка достаточно интересная и скорее всего связана с инструкциями в коде. Я бы попытался решить ее, вынеся include(“PHPExcel/Writer/Excel5.php”) за цикл, чтобы каждый раз не подключался один и тот же файл бесчисленное количество раз.
  
  Ответить
Автор: Лёха, 10 января 2011 в 22:35
использует Internet Explorer 8.0 на Windows XP
Здравствуйте, помогите пожалуйста, ломаю голову с прошлого года – каменный цветок не выходит. Знаком с PHP на 10-12% (Обучаюсь в процесе).
Проблема следующая: В PHP из формы HTML прилетают 4 числовые переменные (X,Y,Z,H), данные из которых затем надо добавить в ячейки экселя. После “вагона” просчёта в экселе числовые данные из ячейки с результатом отправить в браузер или переменную. Экселевский файл закрыть без сохранения изменений.
Подскажите как это можно осуществить?
PHPexcel установил “чудо-рейтинг” открылся без проблем.
Быть может есть какие заготовки подобных скриптов.
Понимаю что физически код должен быть не большим, но дальше простого открытия файла эксель пока не ушёл.

Ответить
- Автор: web-junior, 11 января 2011 в 21:13
  использует Firefox 3.6.13 на Windows 7
  Здравствуйте.
  Я так понимаю, вы хотите использовать эксель для того, чтобы произвести вычисления над 4-мя переменными, которые получаете из формы? Почему нельзя произвести вычисления посредством PHP? Поверьте, это будет намного быстрее и эффективнее.
  
  Ответить
  - Автор: Лёха, 12 января 2011 в 11:25
    использует Internet Explorer 8.0 на Windows XP
    Дело в том, что изначально я так и хотел поступить, но после открытия формул в экселе я был немного в шоке. Экселевская программа многофункциональна, расчитывает результат сразу для 8 типов продукции, расчёт производится на 4 связанных листах, для расчёта вводится более 20 параметров.
    Для меня важны только 4 параметра, остальные данные будут введенны заранее в ексель, перед загрузкой на хостинг, поэтому необходимо после расчёта на хосте не сохранять изменения в экселе.
    Вот поэтому я и подумал что этот вариант будет наиболее приемлем.
    Надеюсь на вашу помощь.
    
    Ответить
    - Автор: web-junior, 12 января 2011 в 12:19
      использует Firefox 3.6.13 на Windows 7
      В этом случае вам поможет другая статья, про чтение и запись в Excel-файлы
      
      Ответить
    - Автор: shafl, 12 января 2011 в 14:45
      использует Opera 11.00 на Windows XP
      php не менее многофункционален, может производить практически любые расчёты, без особой нагрузки на хостинг. И мне интересно на хостинге будет производиться расчёт в Excel? Как я понимаю расчёты в файле xls происходят именно при сохранении файла(выдачи в браузер), а до этого всё содержится в коде php.
      
      Ответить
      - Автор: Лёха, 12 января 2011 в 23:31
        использует Internet Explorer 8.0 на Windows XP
        Я в этом очень мало понимаю, но как мне кажется PhpExcel – библиотека не детская, соответственно разработчики были обязаны предусмотреть функцию “прогона”, расчёта до завершения кода пхп, для последующих операций над результатом. Наверное как то так . Вопрос: корректно ли обрабатываются формулы ссылающиеся на данные в другом листе книги эксель?
      - Автор: shafl, 13 января 2011 в 05:42
        использует Opera 11.00 на Windows XP
        Ну на самом деле не обязаны, и наверно не сделали. Может быть автор статьи пояснит этот момент? php создаёт файл со специальной разметкой и заголовками excel, не открывает саму программу для создания, ведь так? и будут ли проводится вычисления без сохранения(выдачи в браузер) самого файла?
Автор: Лёха, 13 января 2011 в 11:20
использует Internet Explorer 8.0 на Windows XP
Я уже говорил что мало что в этом понимаю, а рассуждаю чисто по логике вещей.
Если описанное выше невозможно, то быть может можно сохранить временный xls на сервере, далее открыть его, произвести необходимые действия с полученным результатом, потом времянку удалить?

Ответить

Автор: web-junior, 13 января 2011 в 11:52

использует

Firefox 3.6.13 на

Windows 7

shafl, PHPExcel действительно не открывает программу Excel и никак с ней “не сотрудничает” до выдачи в браузер. Но библиотека настолько хороша, что без проблем делает вычисления формул в ячейках. Для этого у класса ячейки PHPExcel_Cell есть метод getCalculatedValue, который вернет значение ячейки, рассчитанное по формуле, находящейся в этой ячейке.
Простой пример. Создадим пустой файл tmp.xls. В ячейку C1 впишем формулу =СУММ(A1;B1), которая вычислит сумму ячейки A1 и B1. Сохраним файл.
Напишем простой код, который вставит данные в ячейки A1 и B1, и выведет значение ячейки C1.

<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
include_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';
 
            $objPHPExcel = PHPExcel_IOFactory::load('tmp.xls');
 
 
 
            $objPHPExcel->setActiveSheetIndex(0);
            $aSheet = $objPHPExcel->getActiveSheet();
	    $aSheet->setCellValue('A1',10);
	    $aSheet->setCellValue('B1',20);
            var_dump($aSheet->getCell('C1')->
getCalculatedValue());
?>

У меня этот код выводит float(30)
Т.е. судя по всему такие вычисления, по заранее установленным формулам вполне возможны. Хотя я вполне согласен с вами, что PHP имеет достаточно хороший функционал, чтобы справиться с вычислениями без PHPExcel.

Ответить

Автор: Лёха, 13 января 2011 в 12:10
использует Internet Explorer 8.0 на Windows XP
Огромное спасибо. Всё оказалось проще чем я думал.

Ответить
Автор: Лёха, 14 января 2011 в 01:29
использует Internet Explorer 8.0 на Windows XP
Здравствуйте, с новым годом (старым). Подскажите, почему xls не открывается (сервер выдаёт ошибку 500) если в одной из ячеек проставлена формула которая ссылается на данные ячейки другова листа? Как можно обойти проблему?

Ответить

Автор: Сергей, 16 января 2011 в 16:56

использует

Google Chrome 8.0.552.237 на

Windows 7

Вопрос такой уже битые сутки бюсь… Как узнать цвет ячейки(прочитать) Подскажите пожалуйста..

Ответить

Автор: web-junior, 18 января 2011 в 20:54

использует

Firefox 3.6.13 на

Windows 7

Заливку можно получить так:

<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
include_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load('tmp.xls');
 
 
$objPHPExcel->setActiveSheetIndex(0);
$aSheet = $objPHPExcel->getActiveSheet();
$start = $aSheet->getStyle('B2')->getFill()
->getStartColor()->getRgb();
$end = $aSheet->getStyle('B2')->getFill()
->getEndColor()->getRgb();
echo 'Start: '.$start.', end: '.$end;
?>

Ответить

Автор: Людмила, 27 января 2011 в 09:38
использует Google Chrome 8.0.552.237 на Windows XP
Спасибо вам большое! Очень полезный материал! Как раз то что мне сейчас нужно!)

Ответить

Автор: Александр, 31 января 2011 в 09:14
использует Firefox 3.6.13 на Fedora 14
Здравствуйте еще раз. Возник вопрос следующего характера: подскажите пож-та, как выгружать данные сразу в формате ячейки дата? Если выгружать вот-так: $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 01.01.2010);, то дата получается как текстовое поле, при изменении ее в самой таблице, все равно дата остается такой же(текстовой). Как быть?

Ответить

« Предыдущие 1 ... 6 7 8 9 10 ... 12 Следующие »

RSS-лента комментариев. Адрес для трекбека

Ваш отзыв

« Долой регулярные выражения или новый способ проверки данных

Чтение Excel-файлов с помощью PHPExcel »

web-junior.net

Все для начинающего веб-программиста