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

Автор: Александр, 1 сентября 2010 в 15:15
использует Google Chrome 5.0.375.127 на Windows XP
Здравствуйте! Очень полезная информация, формирование excel работает, а вот как zip-овать? В документации по этому поводу ничего нет. Не могли бы Вы привести пример?

Ответить
Автор: Людмила, 20 сентября 2010 в 20:55
использует Google Chrome 6.0.472.62 на Windows XP
Здравствуйте! Подскажите пожалуйста, а можно ли скрывать не нужные ячейки файла Excel? У меня постоянно изменяемое количество полей, которое нужно вывести в файл. Помогите, что мне делать?

Ответить
- Автор: shafl, 21 сентября 2010 в 14:52
  использует Opera 10.62 на Windows XP
  Cредствами php можно реализовать
  
  Ответить
  - Автор: Людмила, 21 сентября 2010 в 14:54
    использует Google Chrome 6.0.472.62 на Windows XP
    А не подскажите как?
    
    Ответить
    - Автор: shafl, 21 сентября 2010 в 14:58
      использует Opera 10.62 на Windows XP
      Ну проверять имеются ли значения в нужных местах и если нет то скрывать столбцы или строки, у вас формулировка задачи слегка размытая, вы хотите скрывать не заполненные поля или формировать сам контент динамически?
      
      Ответить
      - Автор: Людмила, 21 сентября 2010 в 15:02
        использует Google Chrome 6.0.472.62 на Windows XP
        У меня имеется протокол проверки задач. Количество задач варьируется и количество этапов варьируется от предмета к предмету. Необходимо динамически формировать файл протокола, причем по заданной форме, с ограничениями на ширину, высоту, шрифт и т.п.
Автор: shafl, 21 сентября 2010 в 15:11
использует Opera 10.62 на Windows XP
Как вариант свой шаблон для каждого предмета.

Ответить
- Автор: Людмила, 21 сентября 2010 в 15:13
  использует Google Chrome 6.0.472.62 на Windows XP
  Я бы с удовольствием воспользовалась шаблоном! Просто кол-во задач варьируется по неизвестной мне заранее (чтобы сделать эти шаблоны) траектории! Кто знает сколько они заходят включить задач…
  
  Ответить
  - Автор: shafl, 21 сентября 2010 в 15:14
    использует Opera 10.62 на Windows XP
    пример протокола можно посмотреть?
    
    Ответить
    - Автор: Людмила, 21 сентября 2010 в 15:16
      использует Google Chrome 6.0.472.62 на Windows XP
      Протокол проверки олимпиадных работ
      Английский язык. 10-11. Школьный этап. Терский район. МОУ СОШ №1 г. Терека
      № п/п Ф.И.О. Класс Район ОУ 1 тур Итого 2 тур Итого Сумма баллов
      1 2 3 1 2 3
      1 Иванов Иван Иванович 10 Терский район МОУ СОШ №1 г. Терека 5 4 10 19 2 1 8 11 30
      2 Петров Петр Петрович 10 Терский район МОУ СОШ №1 г. Терека 2 7 0 9 3 6 5 14 23
      3 Васечкин Василий Васильевич 11 Терский район МОУ СОШ №1 г. Терека 9 7 3 19 4 5 3 12 31
      Максимальный балл – 60
      
      Председатель жюри _______________
      
      Члены жюри _______________
      _______________
      _______________
      
      15.10.2010 16:30
      
      Ответить
      - Автор: Людмила, 21 сентября 2010 в 15:17
        использует Google Chrome 6.0.472.62 на Windows XP
        Ой, как-то не хорошо получилось! Хотите на почту кину?
Автор: shafl, 21 сентября 2010 в 15:21
использует Opera 10.62 на Windows XP
Как я понимаю, цифры после “г.Терека” вроде баллы за задания? Их количество заранее не известно? Это же в цикле легко делается.

Ответить
- Автор: Людмила, 21 сентября 2010 в 15:24
  использует Google Chrome 6.0.472.62 на Windows XP
  Да легко! Но там надо объединять ячейки и форматировать!
  
  Ответить
  - Автор: Людмила, 21 сентября 2010 в 15:28
    использует Google Chrome 6.0.472.62 на Windows XP
    Поэтому и спрашиваю можно ли объединять ячейки…
    
    Ответить
  - Автор: shafl, 21 сентября 2010 в 15:30
    использует Opera 10.62 на Windows XP
    например вот так задать:
    $aSheet->setCellValue(‘A’.$row_num, $a_num[$j]); так форматировать:
    $aSheet->getStyle(‘A’.$row_num.’:F’.$row_num)->applyFromArray($border_all_thin); так объединять: $pExcel->getActiveSheet()->mergeCells(‘A’.$row_num.’:F’.$row_num);
    
    Ответить
    - Автор: shafl, 21 сентября 2010 в 15:36
      использует Opera 10.62 на Windows XP
      Если я вас правильно понял
      
      Ответить
      - Автор: Людмила, 21 сентября 2010 в 15:47
        использует Google Chrome 6.0.472.62 на Windows XP
        Да, спасибо! это я уже делала! Просто самый сок в том что вместо A и F, например, в ‘A’.$row_num.’:F’.$row_num, может быть что угодно… как это предусмотреть???
Автор: shafl, 21 сентября 2010 в 16:19
использует Opera 10.62 на Windows XP
ну например массив с алфавитом и обращаться к элементам, т.е. вместо А $column[$i], а вместо F $column[$j]

Ответить
- Автор: Людмила, 21 сентября 2010 в 16:31
  использует Google Chrome 6.0.472.62 на Windows XP
  Спасибо!!! Я так и предполагала делать, просто еще не уложилось в голове, как именно, параметров очень много динамических…
  
  Ответить
Автор: Людмила, 29 сентября 2010 в 16:13
использует Google Chrome 6.0.472.63 на Windows XP
Доброго всем времени суток! Не подскажите такую штуку: КАК СДЕЛАТЬ ВЫРАВНИВАНИЕ ПО ЦЕНТРУ? Не просто по центру текст поставить, а например при объединении двух строк, чтобы текст в объединенной ячейке выводится по центру ячейки как по ширине, так и по высоте!

Заранее спасибо!!!

Ответить
- Автор: shafl, 29 сентября 2010 в 16:22
  использует Opera 10.62 на Windows XP
  Для диапазона тоже можно задавать стиль текста.
  
  Ответить
  - Автор: Людмила, 29 сентября 2010 в 16:27
    использует Google Chrome 6.0.472.63 на Windows XP
    Класс! Извините, сразу не догадалась!
    
    Ответить
Автор: Людмила, 29 сентября 2010 в 23:04
использует Google Chrome 6.0.472.63 на Windows XP
Еще один маленький вопросик, как поставить просто линию? Т.е. не всю рамку, а отдельные элементы, например, нижнюю.

Ответить
- Автор: shafl, 30 сентября 2010 в 06:01
  использует Opera 10.62 на Windows XP
  например нижнюю:
  $styleArray = array(
  ‘borders’ => array(
  ‘bottom’ => array(
  ‘style’ => PHPExcel_Style_Border::BORDER_THICK,),
  ),
  ),
  );
  и применить $styleArray к ячейке.
  
  Ответить

Автор: Smirn, 29 октября 2010 в 20:16

использует

Opera 10.63 на

Windows XP

Потребовалось создать 2-й лист в книге, а не выходит….
остается только ваш…

$pExcel = new PHPExcel();
$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();
$aSheet->setTitle('Первый лист');
 
//	Новый лист
$sheet = $pExcel->createSheet();
$pExcel->addSheet($sheet);
$pExcel->setActiveSheetIndex(1);
$bSheet = $pExcel->getActiveSheet();
$bSheet->setTitle('Active');
$bSheet->setCellValueByColumnAndRow(1, 2, 'Проверка');

Лист Active не появляется в книге…
что делать?

Ответить

Автор: web-junior, 29 октября 2010 в 21:06

использует

Firefox 3.6.10 на

Windows 7

Я могу только догадываться как реагирует MS Excel на вывод этого документа. Он не отображает лист Active или пытается его восстановить. После восстановления (в MS Excel 2007) лист может быть отображен.

Скорее всего проблема в этой строке

1	$pExcel->addSheet($sheet);

Дело в том, что метод createSheet не только создает новый лист, но и добавляет его в документ $pExcel. Поэтому добавлять в документ еще один такой же лист с помощью addSheet не нужно. Это, судя по всему, плохо сказывается на отображении листов.

Ответить

Автор: Smirn, 29 октября 2010 в 21:31

использует

Opera 10.63 на

Windows XP

Даже удалив эту строку, я не получаю 2-й лист в книге (Библиотека 1.7.4)

set_include_path(get_include_path() . PATH_SEPARATOR . '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);
 
//	Новый лист
$sheet = $pExcel->createSheet();
$pExcel->setActiveSheetIndex(1);
$bSheet = $pExcel->getActiveSheet();
$bSheet->setTitle('Active');
$bSheet->setCellValueByColumnAndRow(1, 2, 'Проверка');
 
//отдаем пользователю в браузер
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');

Ответить

Автор: Smirn, 29 октября 2010 в 23:01
использует Opera 10.63 на Windows XP
метод setOffice2003Compatibility для Writer2007 – не помогает, пробую скачать конвертер…

Ответить

Автор: Smirn, 29 октября 2010 в 21:39
использует Opera 10.63 на Windows XP
Да, у меня 2003 офис стоит… попробую в ОО открыть.

Ответить
- Автор: web-junior, 29 октября 2010 в 21:55
  использует Firefox 3.6.10 на Windows 7
  у меня Excel 2007 вполне корректно открывает.
  
  Ответить
  - Автор: Smirn, 29 октября 2010 в 21:58
    использует Opera 10.63 на Windows XP
    ОО 3,2,1 так же открывает без 2-го листа…
    вопрос, можно ли как-то сохранять для МО 2003?

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

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

Ваш отзыв

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

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

web-junior.net

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