Создание 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

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

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

2leep.com

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

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

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

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

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

1 2 3 ... 12 Следующие »

Автор: someone, 5 января 2010 в 11:44
использует Shiretoko 3.5.7pre на Ubuntu 8.04
На самом деле лицензия очень важна любому нормальному разработчику и нормальному заказчику.
А если кто-то этот момент не учитывает, то либо делает чисто для себя, либо подставляет и себя и заказчика, либо просто идио.. ладно, просто некомпетентен.

Уж извините меня.

Ответить
- Автор: web-junior, 5 января 2010 в 11:58
  использует Firefox 3.5.6 на Windows XP
  Спасибо вам, за то, что просветили.
  А то я думал: писать о лицензии или не писать?
  На самом деле текст лицензии распространяется вместе библиотекой, поэтому кому нужно, тот нашел бы ее.
  
  Ответить
Автор: Dreamer, 12 января 2010 в 16:01
использует Firefox 3.0.14 на Windows XP
Здравствуйте!
А как-то можно сделать следующее: записать уже в ранее созданный файл данные, там чтоб выполнилась формула(в соответствующих ячейках она уже записана) и затем прочитать всю информацию из файла .xls. Может есть какой-то способ.
Или может кто знает как перенести формулу из одного xls файла в другой xls файл? Можно ли как-то извлечь формулу из xls- файла?

Нашёл
* phpexcelreader
* Spreadsheet_Excel_Writer
и пакет PEAR OLE
Задал такой же вопрос – сказали невозможно, а с помощью этой библиотеки можно?

Подскажите пожалуйста

Ответить
- Автор: web-junior, 12 января 2010 в 16:32
  использует Firefox 3.5.6 на Windows XP
  Добрый день!
  С помощью этой библиотеки достаточно просто читать xls-файлы.
  Для чтения можно использовать метод PHPExcel_IOFactory::load(), который находится в файле PHPExcel/IOFactory.php. Он возвращает объект класса PHPExcel, с которым можно работать как показано выше.
  
  Ответить
Автор: Dreamer, 12 января 2010 в 17:15
использует Firefox 3.0.14 на Windows XP
Спасибо за подсказку. У меня проблема возникла когда функцией load подгружаешь xls-файл, изменяю данные, а потом в этот же файл пробую сохранить пишет что файл уже открыт. Есть какае-то функция типа close для закрытия файла? Я не делал $excelReader->setReadDataOnly(true). Может надо сделать?

Ответить
Автор: Dreamer, 12 января 2010 в 17:26
использует Firefox 3.0.14 на Windows XP
С сохранением разобрался. setReadDataOnly с ней получилось. Но формула которая была в ячейках 3-й строки пропала. Можно ли при извлечении из xls-файла извлекти саму формулу, а не результат. Потому как при записи она пропадает.

Ответить
- Автор: web-junior, 12 января 2010 в 17:56
  использует Firefox 3.5.6 на Windows XP
  Есть более простой способ записи, который не требует установки setReadDataOnly у PHPExcel_Reader
  Вот пример:
  
  set_include_path(get_include_path() . PATH_SEPARATOR . ‘PhpExcel/Classes/’);
  
  include(“PHPExcel.php”);
  include_once ‘PHPExcel/IOFactory.php’;
  $objPHPExcel = PHPExcel_IOFactory::load(“template2.xls”);
  $objPHPExcel->setActiveSheetIndex(0);
  $aSheet = $objPHPExcel->getActiveSheet();
  
  $aSheet->setCellValue(‘D23′,$index[0]);
  $aSheet->setCellValue(‘E23′,$index[1]);
  //… здесь делаем какие-то записи
  
  //создаем объект класса-писателя
  include(“PHPExcel/Writer/Excel5.php”);
  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  //сохраняем в тот-же файл
  $objWriter->save(‘template2.xls’);
  
  Ответить
Автор: Dreamer, 12 января 2010 в 18:14
использует Firefox 3.0.14 на Windows XP
Спасибо за объяснения. Проблема в том что когда мы создаём объект new PHPExcel_Reader_Excel5 мы теряем формулы. Мне надо чтоб было так существует xls-файл, в некоторых ячейках есть формулы. Надо записать в этот файл данные, а формулы чтоб выполнились, а уже потом извлечь данные. А так мы теряем формулы

Ответить
Автор: web-junior, 12 января 2010 в 18:43
использует Firefox 3.5.6 на Windows XP
А не могли бы вы выложить где-нибудь свой код и xls-файл, чтобы можно было увидеть вашу проблему наглядно?

Ответить
Автор: Dreamer, 12 января 2010 в 20:13
использует Firefox 3.0.8 на Windows XP
Если очень кратко. Всё что нужно для решения задачи которую я описал – это извлечь из ячейки файла xls саму формулу. Потому как прописать в ячейку формулу понятно как, а вот как извлечь формулу.
Выложить пока не знаю где.
Для примера и для простоты
файл template.xls
Колонки A и B пустые
C1 => ‘=A1+B1′, C2 => ‘=A2+B2′, … C10 => ‘=A10+B10′

файл php
include_once ‘Classes/PHPExcel/Reader/Excel5.php’;
$excel = new PHPExcel_Reader_Excel5();
$excel = $excel->load(‘sheets/binary2.xls’);
$excel->setActiveSheetIndex(0);
$aSheet = $excel->getActiveSheet();
for ($i=1; $isetCellValue(‘A’.$i, $i);
$aSheet->setCellValue(‘B’.$i, $i+2);
}
include(“Classes/PHPExcel/Writer/Excel5.php”);
$objWriter = new PHPExcel_Writer_Excel5($excel);
$objWriter->save(‘sheets/binary2.xls’);

Надо чтобы в ячейках столбца C было
С1 => 4, C2 => 6, … , с10 => 22
То есть выполнилась формула А+B=C для каждой строки и результат записался в ячейки столбца С.
Дальше я уже знаю что сделать. Открыть ещё раз на чтение файл и извлечь все ячейки и отобразить в браузере

Ответить
- Автор: web-junior, 12 января 2010 в 20:37
  использует Firefox 3.5.6 на Windows XP
  Если вам нужна сама формула ячейки, то у класса PHPExcel_Cell (это собственно сама ячейка) есть метод getValue. Вот пример
  ….
  for($i=1;$i< =10;$i++){
  echo $aSheet->getCell(‘C’.$i)->getValue().”\r\n”;
  }
  …
  цикл выведет все содержимое столбца С. Если там формулы, то он выведет их в виде
  =A1+B1
  =A2+B2
  …
  =A10+B10
  Если вам нужно значение, которое расчитывается по этой формуле, то вместо getValue() нужно вызывать метод getCalculatedValue(). Поэтому можно и не сохранять файл, а просто пройтись по столбцу С, после установки данных и вызвать у каждой ячейки метод getCalculatedValue()
  
  А выложить код с xls-файлом можно архивом на любом файлообменнике, их много сейчас развелось
  
  Ответить
  - Автор: Dreamer, 12 января 2010 в 21:15
    использует Firefox 3.0.8 на Windows XP
    http://depositfiles.com/files/27klpji1j
    
    echo $aSheet->getCell(‘C’.$i)->getValue() и getCalculatedValue() -эти методы выводят значение. А первоначально там 0. Такое выражение не выводится =A1+B1. Наверно не всё так просто. Может от версии зависит Excel?
    
    Ответить
    - Автор: web-junior, 12 января 2010 в 21:44
      использует Firefox 3.5.6 на Windows XP
      Да, действительно, в вашем xls-файле формулы не выводят ничего. Наверняка это зависит от версии Excel. Вот попробуйте мой файл. В нем все должно быть ок.
      В какой программе создавали свой xls-файл?
      
      Ответить
Автор: Dreamer, 12 января 2010 в 22:05
использует Firefox 3.0.8 на Windows XP
Cпасибо. Да у вас действительно всё правильно выводит. Возможно что я создавал файл с помощью этих классов phpexcelreader Spreadsheet_Excel_Writer, поэтому оно и не работало.
Это я уже разберусь. Ещё раз спасибо за помощь

Ответить
- Автор: web-junior, 12 января 2010 в 22:08
  использует Firefox 3.5.6 на Windows XP
  Не за что.
  Обращайтесь еще – с удовольствием помогу, чем смогу.
  
  Ответить