Создание 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 4 ... 12 Следующие »

Автор: AS76, 27 января 2010 в 12:29
использует Firefox 3.5.7 на Windows XP
Спасибо за статью, аможно ли с помощью этой библиотеки создать Excel-файл содержащий рисунки?

Ответить
- Автор: web-junior, 27 января 2010 в 13:10
  использует Firefox 3.5.7 на Windows XP
  Да, конечно можно.
  Добавлять рисунки можно с помощью класса PHPExcel_Worksheet_Drawing. Вот пример:
  
  $iDrowing = new PHPExcel_Worksheet_Drawing();
  //берем рисунок
  $iDrowing->setPath(‘img.jpeg’);
  
  //устанавливаем ячейку
  $iDrowing->setCoordinates(‘A3′);
  
  //устанавливаем смещение X и Y
  $iDrowing->setOffsetX(50);
  $iDrowing->setOffsetY(50);
  
  //помещаем на лист
  $iDrowing->setWorksheet($objPHPExcel->getActiveSheet());
  
  Также есть класс PHPExcel_Worksheet_MemoryDrawing, с помощью которого можно вставлять ресурсы GD. Это делается с помощью метода setImageResource. Вот пример из документации:
  
  // Generate an image
  $gdImage = @imagecreatetruecolor(120, 20) or die(‘Cannot Initialize new GD image stream’);
  $textColor = imagecolorallocate($gdImage, 255, 255, 255);
  imagestring($gdImage, 1, 5, 5, ‘Created with PHPExcel’, $textColor);
  
  // Add a drawing to the worksheet
  $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
  $objDrawing->setName(‘Sample image’);
  $objDrawing->setDescription(‘Sample image’);
  $objDrawing->setImageResource($gdImage);
  $objDrawing->setRenderingFunction(
  PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
  $objDrawing->setMimeType(
  PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
  $objDrawing->setHeight(36);
  $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  
  Ответить
  - Автор: AS76, 2 февраля 2010 в 14:08
    использует Firefox 3.5.7 на Windows XP
    Спасибо, все получилось.
    Только непонятно вот что, при установке высоты изображения
    $objDrawing->setHeight(36);
    в каких единицах она указывается? Так же как и для столбцов? в символьных единицах?
    
    Как задать ширину? Так?
    $objDrawing->setWidth(36);
    Вообще с изменением размеров какие-то странности
    (не сохраняются пропорции делает, например 22% на 23%)
    Где можно посмотреть все возможные настройки изображений (в Word-файле документации я так понял указаны не все)
    
    Ответить
    - Автор: web-junior, 2 февраля 2010 в 15:10
      использует Firefox 3.5.7 на Windows XP
      По поводу единиц высоты и ширины точно сказать не могу. Но похоже, что все же в пикселах.
      
      Те странности с изменением размеров, которые вы заметили вполне объяснимы. При установки ширины или высоты происходит пропорциональное изменение ширины и высоты. Чтобы отключить эту функциональность нужно вызвать
      $objDrawing->setResizeProportional(false);
      ДО установки высоты или ширины
      
      Всех настроек и установок к сожалению нет. Часть есть в Word-файлах, часть в api-документации. Все остальное можно найти только непосредственно в коде.
      
      Ответить
Автор: tzi0, 9 февраля 2010 в 10:46
использует Firefox 3.5.7 на Windows XP
Привет. Может кто сталкивался с такой проблемкой: есть книга, в которой 2 листа : Worksheet1 и Worksheet2. Мне нужно в ячейку A2 листа Worksheet2 записать значение ячейки A1 листа Worksheet1:

require_once (“Classes/PHPexcel.php”);
require_once (“Classes/PHPexcel/IOFactory.php”);

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$worksheet1 = $objPHPExcel->getActiveSheet();
$worksheet1->setCellValue(‘A1′, “SomeValue”);

$worksheet2 = $objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$worksheet2 = $objPHPExcel->getActiveSheet();
$worksheet2->setCellValue(‘A2′,’=Worksheet1.A1′);

header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”test.xlsx”‘);
header(‘Cache-Control: max-age=0′);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007′);
$objWriter->save(‘php://output’);

В итоге, открывая файл test.xlsx, вижу там ошибку:
_raiseFormulaError message is Formula Error: An unexpected error occured

Может я неправильно описал формулу? Я уже второй день не могу решить эту проблему ( Версия PHPExcel – 1.7.2. Или это баг?

Ответить
- Автор: web-junior, 9 февраля 2010 в 12:16
  использует Firefox 3.5.7 на Windows XP
  Добрый день!
  На самом деле вы неверно ввели формулу. Обращение к ячейкам на других листах происходит через ! а не точку. Вот так =Лист1!A1.
  В вашем примере нужно вместо
  $worksheet2->setCellValue(‘A2′,’=Worksheet1.A1′);
  написать
  $worksheet2->setCellValue(‘A2′,’=Worksheet!A1′);
  
  Ответить
Автор: ser, 9 февраля 2010 в 16:47
использует Firefox 3.6 на Windows 7
Здравствуйте!
У меня проблема с записью русского текста в таблицу, ячейки куда должен помещаться русский текст остаются пустыми, при этом если в них записывать англ. текст то все нормально… Кто-нибудь сталкивался с проблемой?

Ответить
- Автор: web-junior, 9 февраля 2010 в 18:28
  использует Firefox 3.5.7 на Windows XP
  Добрый день!
  Дело в том, что бибилиотека PHPExcel понимает кириллицу, только кодированную в UTF-8. Если вы записываете в ячейки кириллицу в кодировке, отличной от UTF-8, то вам нужно перекодировать ее с помощью mb_string или iconv
  
  Ответить
  - Автор: Алексей В., 28 мая 2010 в 23:21
    использует Opera 10.52 на Windows XP
    Можно пример что-то я и так и сяк, может потому что уже поздно у нас тут и голова туго соображает…
    
    Ответить
    - Автор: web-junior, 30 мая 2010 в 11:42
      использует Firefox 3.5.9 на Windows XP
      Например, так
      
      1 2
      $aSheet->setCellValue('D27', mb_convert_encoding('Текст в cp1251','utf-8','windows-1251'));
      
      где $aSheet – это активный лист, а D27 нужная ячейка.
      
      Ответить
      - Автор: Алексей В., 30 мая 2010 в 15:36
        использует Opera 10.52 на Windows XP
        Большое спасибо!!!
Автор: Денис, 15 февраля 2010 в 20:25
использует Firefox 3.6 на Windows 7
Что то у меня не получается. Вроде все делал как вы. Я правильно понял надо скачать PHPExel и достаточно на сайт положить только папку Classes. Других библиотек не надо, я так понял она содержит в себе все. Далее скопировал ваш код для пробы но вот ошибки: Warning: Cannot modify header information – headers already sent by (output started at /home/www/proba.php:14) in /home/www/proba.php on line 56

Warning: Cannot modify header information – headers already sent by (output started at /home/www/proba.php:14) in /home/www/proba.php on line 57

Warning: Cannot modify header information – headers already sent by (output started at /home/www/proba.php:14) in /home/www/proba.php on line 58

Warning: fopen(output/) [function.fopen]: failed to open stream: Is a directory in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php on line 109

Fatal error: Uncaught exception ‘Exception’ with message ‘Can’t open output/. It may be in use or protected.’ in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php:111 Stack trace: #0 /home/www/PhpExcel/Classes/PHPExcel/Writer/Excel5.php(238): PHPExcel_Shared_OLE_PPS_Root->save(‘output/’) #1 /home/www/proba.php(59): PHPExcel_Writer_Excel5->save(‘output/’) #2 {main} thrown in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php on line 111

Что не так, куда копать, Подскажите пожалуйста!

Ответить
- Автор: web-junior, 15 февраля 2010 в 21:04
  использует Firefox 3.5.7 на Windows XP
  Для того, чтобы раскопать в чем ошибка, нужно понять, что значат те ошибки, которые пишет php.
  1) Ошибка
  Warning: Cannot modify header information – headers already sent by (output started at /home/www/proba.php:14) in /home/www/proba.php on line 56
  означает, что в 14 строке был вывод какого-то текста или другим способом были отправлены заголовки. Поэтому, найдя в 56-й строке код отсылки заголовков, php сообщает, что не может отослать заголовки, поскольку они уже были посланы. Уберите вывод заголовков в 14-й строке.
  2) Ошибка
  Warning: fopen(output/) [function.fopen]: failed to open stream: Is a directory in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php on line 109
  
  и связанная с ней
  
  Fatal error: Uncaught exception ‘Exception’ with message ‘Can’t open output/. It may be in use or protected.’ in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php:111 Stack trace: #0 /home/www/PhpExcel/Classes/PHPExcel/Writer/Excel5.php(238): PHPExcel_Shared_OLE_PPS_Root->save(‘output/’) #1 /home/www/proba.php(59): PHPExcel_Writer_Excel5->save(‘output/’) #2 {main} thrown in /home/www/PhpExcel/Classes/PHPExcel/Shared/OLE/OLE_Root.php on line 111
  
  говорит о том, что PHPExcel не может создать файл “output/” для сохранения. Видимо неверно введено имя файла в метод
  $objWriter->save(‘php://output’);
  В этот метод нужно передать имя сохраняемого или ‘php://output’ для вывода в браузер.
  
  Ответить
Автор: Денис, 15 февраля 2010 в 21:20
использует Firefox 3.6 на Windows 7
Убрал все, оставил только ваш первый код, стал ругаться на 34,35,36 строки. Удалил ошибок нет, теперь проблема с кодировкой. Вывел вопросики с картинками )).

Получается что ваш код только выводит в браузере, не сохраняет?

Ответить
- Автор: Денис, 15 февраля 2010 в 21:23
  использует Firefox 3.6 на Windows 7
  Щас закачал на платный хостинг там этих ошибок нет что это может быть?
  
  Ответить
- Автор: web-junior, 15 февраля 2010 в 21:51
  использует Firefox 3.5.7 на Windows XP
  проблема с кодировками из-за того, что PHPExcel работает исключительно с UTF-8. Данные обязательно должны быть в утф.
  
  Ответить
Автор: Денис, 16 февраля 2010 в 08:01
использует Firefox 3.6 на Windows 7
У меня страница в кодировке utf-8. Может гдето в коде указать кодировку создаваемого файла. Как сделать чтобы он сохранялся на сайте?

Ответить
- Автор: web-junior, 16 февраля 2010 в 09:28
  использует Firefox 3.5.7 на Windows XP
  Указывать в коде кодировку создаваемого Excel-файла не нужно. PHPExcel создает его только в своей внетренней кодировке.
  Но данные, которые вы устанавливаете в ячейки должны быть в кодировке utf-8.
  
  >>Как сделать чтобы он сохранялся на сайте?
  Если нужно, чтобы создаваемый Excel-файл не выводился в браузер, а сохранялся на сайте, нужно в
  $objWriter->save(‘php://output’);
  вместо ‘php://output’ написать имя файла, в который вы хотите сохранить данные. Убедитесь, что папка, в которую вы собираетесь сохранять доступна для записи.
  
  Ответить
Автор: Денис, 16 февраля 2010 в 12:41
использует Firefox 3.6 на Windows 7
Да сохраняет в нормальной кодировке, отлично. Но странно почему ругается на header и в браузере не правильная кодировка.

Ответить
- Автор: web-junior, 16 февраля 2010 в 13:04
  использует Firefox 3.5.7 на Windows XP
  Ищите, где у вас происходит вывод. Это может быть текст до <?php, может быть BOM при перекодировании в утф, может быть session_start, а может быть где-то header выводит заголовки.
  
  Ответить
Автор: Hows, 27 февраля 2010 в 13:07
использует Firefox 3.5.7 на Ubuntu 9.10
Интересная штуковина, интересно, а в опен оффисе также работать будет ?

Ответить
- Автор: web-junior, 27 февраля 2010 в 13:58
  использует Firefox 3.6 на Windows XP
  Должно по идее также работать, ведь ОО нормально работает с обычными xls-файлами.
  
  Ответить