Обработка и оформление отчетов в Excel на PHP

  • Tutorial

Не редко при разработке некоего проекта, возникает необходимость в формировании отчетной статистики. Если проект разрабатывается на Delphi, C# или к примеру, на С++ и под Windows, то тут проблем нет. Всего лишь необходимо воспользоваться COM объектом. Но дела обстоят иначе, если необходимо сформировать отчет в формате excel на PHP. И чтобы это творение функционировало на UNIX-подобных системах. Но, к счастью, не так все плохо. И библиотек для этого хватает. Я свой выбор остановил на PHPExcel. Я уже пару лет работаю с этой библиотекой, и остаюсь доволен. Поскольку она является кроссплатформенной, то не возникает проблем с переносимостью.


PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.


Установка библиотеки

Для работы необходима версия PHP 5.2.0 или выше. А также необходимы следующие расширения: php_zip, php_xml и php_gd2. Скачать библиотеку можно отсюда.


С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:

  • Excel 2007;
  • Excel 97 и поздние версии;
  • PHPExcel Serialized Spreadshet;
  • HTML;
  • PDF;
  • CSV.

Импорт данных из PHP в Excel


Рассмотрим пример по формированию таблицы умножения.


// Подключаем класс для работы с excel
require_once('PHPExcel.php');
// Подключаем класс для вывода данных в формате excel
require_once('PHPExcel/Writer/Excel5.php');

// Создаем объект класса PHPExcel
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Таблица умножения');

// Вставляем текст в ячейку A1
$sheet->setCellValue("A1", 'Таблица умножения');
$sheet->getStyle('A1')->getFill()->setFillType(
    PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE');

// Объединяем ячейки
$sheet->mergeCells('A1:H1');

// Выравнивание текста
$sheet->getStyle('A1')->getAlignment()->setHorizontal(
    PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

for ($i = 2; $i < 10; $i++) {
	for ($j = 2; $j < 10; $j++) {
        // Выводим таблицу умножения
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                          $i . "x" .$j . "=" . ($i*$j));
	    // Применяем выравнивание
	    $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	}
}

Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу:


// Выводим HTTP-заголовки
 header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
 header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
 header ( "Cache-Control: no-cache, must-revalidate" );
 header ( "Pragma: no-cache" );
 header ( "Content-type: application/vnd.ms-excel" );
 header ( "Content-Disposition: attachment; filename=matrix.xls" );

// Выводим содержимое файла
 $objWriter = new PHPExcel_Writer_Excel5($xls);
 $objWriter->save('php://output');


Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.


Рассмотрим еще на примере три полезные инструкции:


  • $sheet->getColumnDimension('A')->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
  • $sheet->getColumnDimension('B')->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
  • $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.

Также обратите внимание на следующие необходимые для работы с отчетом методы:


  • Методы для вставки данных в ячейку:
    • setCellValue([$pCoordinate = 'A1' [, $pValue = null [, $returnCell = false]]]) принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
    • setCellValueByColumnAndRow([$pColumn = 0 [, $pRow = 1 [, $pValue = null [, $returnCell = false]]]]) принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
  • Методы для получения ячейки:
    • getCell([$pCoordinate = 'A1']) принимает в качестве параметра координату ячейки;
    • getCellByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) принимает два параметра в виде номеров столбца и строки ячейки.

Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.


Оформление отчета средствами PHP в Excel


Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.


Назначить стиль ячейке можно двумя способами:


  • Применить метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами:
    • fill — массив с параметрами заливки;
    • font — массив с параметрами шрифта;
    • borders — массив с параметрами рамки;
    • alignment — массив с параметрами выравнивания;
    • numberformat — массив с параметрами формата представления данных ячейки;
    • protection — массив с параметрами защиты ячейки.
  • Использовать метода класса PHPExcel_Style для каждого из стилей в отдельности. К примеру, назначить ячейке шрифт можно так: $sheet->getStyle('A1')->getFont()->setName('Arial') .

Заливка

Значением параметра fill является массив со следующими необязательными параметрами:

  • type — тип заливки;
  • rotation — угол градиента;
  • startcolor — значение в виде массива с параметром начального цвета в формате RGB;
  • endcolor — значение в виде массива с параметром конечного цвета в формате ARGB;
  • color — значение в виде массива с параметром начального цвета в формате RGB.

Стили заливки
FILL_NONE none
FILL_SOLID solid
FILL_GRADIENT_LINEAR linear
FILL_GRADIENT_PATH path
FILL_PATTERN_DARKDOWN darkDown
FILL_PATTERN_DARKGRAY darkGray
FILL_PATTERN_DARKGRID darkGrid
FILL_PATTERN_DARKHORIZONTAL darkHorizontal
FILL_PATTERN_DARKTRELLIS darkTrellis
FILL_PATTERN_DARKUP darkUp
FILL_PATTERN_DARKVERTICAL darkVertical
FILL_PATTERN_GRAY0625 gray0625
FILL_PATTERN_GRAY125 gray125
FILL_PATTERN_LIGHTDOWN lightDown
FILL_PATTERN_LIGHTGRAY lightGray
FILL_PATTERN_LIGHTGRID lightGrid
FILL_PATTERN_LIGHTHORIZONTAL lightHorizontal
FILL_PATTERN_LIGHTTRELLIS lightTrellis
FILL_PATTERN_LIGHTUP lightUp
FILL_PATTERN_LIGHTVERTICAL lightVertical
FILL_PATTERN_MEDIUMGRAY mediumGray
Пример указания настроек для заливки:
array(
	'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
	'rotation'   => 0,
	'startcolor' => array(
		'rgb' => '000000'
	),
	'endcolor'   => array(
		'argb' => 'FFFFFFFF'
	),
	'color'   => array(
		'rgb' => '000000'
	)
);

Или можно использовать следующие методы:

$PHPExcel_Style->getFill()->setFillType(PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR);

$PHPExcel_Style->getFill()->setRotation(0);

$PHPExcel_Style->getFill()->getStartColor()->applyFromArray(array('rgb' => 'C2FABD'));

$PHPExcel_Style->getFill()->getEndColor()->applyFromArray(array('argb' => 'FFFFFFFF')).


Вставка изображений

Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:

  • setPath([$pValue = '', [$pVerifyFile = true]]) данный метод принимает два параметра. В качестве первого параметра указывается путь к файлу с изображением. А второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false).
  • setCoordinates([$pValue = 'A1'])) принимает на вход один параметр в виде строки с координатой ячейки.
  • setOffsetX([$pValue = 0]) принимает один параметр со значением смещения по X от левого края ячейки.
  • setOffsetY([$pValue = 0]) принимает один параметр со значением смещения по Y от верхнего края ячейки.
  • setWorksheet([$pValue = null, [$pOverrideOld = false]]) этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр объекта активного листа. Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее – произойдет его перезапись и соответственно изображение удалится.

Код демонстрирующий алгоритм вставки изображения приведен ниже:


...
	
$sheet->getColumnDimension('B')->setWidth(40);

$imagePath = dirname ( __FILE__ ) . '/excel.png';
		
		
if (file_exists($imagePath)) {
	$logo = new PHPExcel_Worksheet_Drawing();
	$logo->setPath($imagePath);
	$logo->setCoordinates("B2");				
	$logo->setOffsetX(0);
	$logo->setOffsetY(0);	
	$sheet->getRowDimension(2)->setRowHeight(190);
	$logo->setWorksheet($sheet);
} 

...

Вот так выглядит отчет со вставленным изображением:




Шрифт

В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:

  • name — имя шрифта;
  • size — размер шрифта;
  • bold — выделять жирным;
  • italic — выделять курсивом;
  • underline — стиль подчеркивания;
  • strike — перечеркнуть;
  • superScript — надстрочный знак;
  • subScript — подстрочный знак;
  • color — значение в виде массива с параметром цвета в формате RGB.

Стили подчеркивания
UNDERLINE_NONE нет
UNDERLINE_DOUBLE двойное подчеркивание
UNDERLINE_SINGLE одиночное подчеркивание
Пример указания параметров настроек для шрифта:
array(
	'name'      	=> 'Arial',
	'size'     	=> 12,
	'bold'      	=> true,
	'italic'    	=> false,
	'underline' 	=> PHPExcel_Style_Font::UNDERLINE_DOUBLE,
	'strike'    	=> false,
	'superScript' 	=> false,
	'subScript' 	=> false,
	'color'     	=> array(
		'rgb' => '808080'
	)
);

Или воспользоваться следующими методами:

$PHPExcel_Style->getFont()->setName(‘Arial’);

$PHPExcel_Style->getFont()->setBold(true);

$PHPExcel_Style->getFont()->setItalic(false);

$PHPExcel_Style->getFont()->setSuperScript(false);

$PHPExcel_Style->getFont()->setSubScript(false);

$PHPExcel_Style->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);

$PHPExcel_Style->getFont()->setStrikethrough(false);

$PHPExcel_Style->getFont()->getColor()->applyFromArray(array('rgb' => '808080'));

$PHPExcel_Style->getFont()->setSize(12).


Рамка

В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:

  • тип рамки — (top|bootom|left|right|diagonal|diagonaldirection);
  • style — стиль рамки;
  • color — значение в виде массива с параметром цвета в формате RGB.

Стили линий
BORDER_NONE нет
BORDER_DASHDOT пунктирная с точкой
BORDER_DASHDOTDOT пунктирная с двумя точками
BORDER_DASHED пунктирная
BORDER_DOTTED точечная
BORDER_DOUBLE двойная
BORDER_HAIR волосная линия
BORDER_MEDIUM средняя
BORDER_MEDIUMDASHDOT пунктирная с точкой
BORDER_MEDIUMDASHDOTDOT утолщенная пунктирная линия с двумя точками
BORDER_MEDIUMDASHED утолщенная пунктирная
BORDER_SLANTDASHDOT наклонная пунктирная с точкой
BORDER_THICK утолщенная
BORDER_THIN тонкая
Пример указания параметров настроек для рамки:
array(
	'bottom'     => array(
		'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
		'color' => array(
		'	rgb' => '808080'
		)
	),
	'top'     => array(
		'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
		'color' => array(
			'rgb' => '808080'
		)
	)
);

Так же можно прибегнуть к использованию следующих методов:

$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));

$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));

$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));

$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));

$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));

$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080'))).


Выравнивание

Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:

  • horizontal — константа горизонтального выравнивания;
  • vertical — константа вертикального выравнивания;
  • rotation — угол поворота текста;
  • wrap — разрешить перенос текста;
  • shrinkToFit — изменять ли размер шрифта при выходе текста за область ячейки;
  • indent — отступ от левого края.

Выравнивание по горизонтали
HORIZONTAL_GENERAL основное
HORIZONTAL_LEFT по левому краю
HORIZONTAL_RIGHT по правому краю
HORIZONTAL_CENTER по центру
HORIZONTAL_CENTER_CONTINUOUS по центру выделения
HORIZONTAL_JUSTIFY по ширине
Выравнивание по вертикали
VERTICAL_BOTTOM по нижнему краю
VERTICAL_TOP по верхнему краю
VERTICAL_CENTER по центру
VERTICAL_JUSTIFY по высоте
Пример параметров настройки стилей выравнивания:
array(
	'horizontal' 	=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
	'vertical'   	=> PHPExcel_Style_Alignment::VERTICAL_CENTER,
	'rotation'   	=> 0,
	'wrap'       	=> true,
	'shrinkToFit'	=> false,
	'indent'	=> 5
)

Или использовать следующие методы:

$PHPExcel_Style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$PHPExcel_Style->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);

$PHPExcel_Style->getAlignment()->setTextRotation(10);

$PHPExcel_Style->getAlignment()->setWrapText(true);

$PHPExcel_Style->getAlignment()->setShrinkToFit(false);

$PHPExcel_Style->getAlignment()->setIndent(5).


Формат представления данных

Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.


Список возможных форматов
FORMAT_GENERAL General
FORMAT_TEXT @
FORMAT_NUMBER 0
FORMAT_NUMBER_00 0.00
FORMAT_NUMBER_COMMA_SEPARATED1 #,##0.00
FORMAT_NUMBER_COMMA_SEPARATED2 #,##0.00_-
FORMAT_PERCENTAGE 0%
FORMAT_PERCENTAGE_00 0.00%
FORMAT_DATE_YYYYMMDD2 yyyy-mm-dd
FORMAT_DATE_YYYYMMDD yy-mm-dd
FORMAT_DATE_DDMMYYYY dd/mm/yy
FORMAT_DATE_DMYSLASH d/m/y
FORMAT_DATE_DMYMINUS d-m-y
FORMAT_DATE_DMMINUS d-m
FORMAT_DATE_MYMINUS m-y
FORMAT_DATE_XLSX14 mm-dd-yy
FORMAT_DATE_XLSX15 d-mmm-yy
FORMAT_DATE_XLSX16 d-mmm
FORMAT_DATE_XLSX17 mmm-yy
FORMAT_DATE_XLSX22 m/d/yy h:mm
FORMAT_DATE_DATETIME d/m/y h:mm
FORMAT_DATE_TIME1 h:mm AM/PM
FORMAT_DATE_TIME2 h:mm:ss AM/PM
FORMAT_DATE_TIME3 h:mm
FORMAT_DATE_TIME4 h:mm:ss
FORMAT_DATE_TIME5 mm:ss
FORMAT_DATE_TIME6 h:mm:ss
FORMAT_DATE_TIME7 i:s.S
FORMAT_DATE_TIME8 h:mm:ss
FORMAT_DATE_YYYYMMDDSLASH yy/mm/dd; @
FORMAT_CURRENCY_USD_SIMPLE "$"#,##0.00_-;@
FORMAT_CURRENCY_USD $#,##0_-
FORMAT_CURRENCY_EUR_SIMPLE [$EUR ]#,##0.00_-
Пример настройки для формата данных ячейки:
array(
	'code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE
);

А можно и воспользоваться методом:

$PHPExcel_Style->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);


Защита ячеек

В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:

  • locked — защитить ячейку;
  • hidden — скрыть формулы.

Пример настройки параметров для защиты ячейки:
array(
	'locked' => true,
	'hidden' => false
);

Или использовать следующие методы:


$PHPExcel_Style->getProtection()->setLocked(true);

$PHPExcel_Style->getProtection()->setHidden(false);

Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это двумя методами. Первый метод заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.


$style = array(
	'font' => array(
		'name' => 'Arial',
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'color' => array (
				'rgb' => 'C2FABD'
		)
	),
	'alignment' => array (
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
	)
);

Далее мы применим созданный нами стиль к ячейкам excel.

$sheet->getStyleByColumnAndRow($i - 2, $j)->applyFromArray($style);

Сейчас применим тот же стиль, но используя другую методику.


//Устанавливаем выравнивание
$sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->setHorizontal(
    PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Устанавливаем шрифт
$sheet->getStyleByColumnAndRow($i - 2, $j)->getFont()->setName('Arial');
// Применяем заливку
$sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()->
    setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()->
    getStartColor()->applyFromArray(array('rgb' => 'C2FABD'));

Вот что у нас получилось:



Если требуется применять стиль многократно, то лучше подойдет первый метод, в другом же случае, лучше остановиться на втором. Для получения объекта (экземпляр класса PHPExcel_Style) ячейки отвечающего за стиль, необходимо использовать один из следующих методов:


  • getStyleByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) – применяется если требуется обратиться к ячейке по числовым координатам. Методу необходимо передать два параметра в виде номеров столбца и строки ячейки;
  • getStyle([pCellCoordinate = 'A1']) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.

Добавление комментариев

Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:


...
	
// Стили шрифтов
$fBold = array('name' => 'Tahoma',  'size' => 10, 'bold' => true);
$fNormal = array('name' => 'Tahoma',  'size' => 10);

$richText = $sheet->getComment('B2')->getText();
 
$richText->createTextRun("Lorem ipsum ")->getFont()->
   applyFromArray($fNormal);
$richText->createTextRun("dolor sit")->getFont()->
   applyFromArray($fBold);
$richText->createTextRun(" amet consectetuer")->getFont()->
   applyFromArray($fNormal);
// Ширина поля комментария
$sheet->getComment('B2')->setWidth('250');
// Высота поля комментария
$sheet->getComment('B2')->setHeight('25');

...

Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:

  • getFont() – возвращает объект класса для работы со шрифтами PHPExcel_Style_Font.
  • setFont([$pFont = null]))]) – данному методу требуется передать в качестве параметра объект класса PHPExcel_Style_Font.

Вот какой комментарий мы должны получить:




Вставка ссылки

Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:


...
	
// Ссылка на веб-ресурс
$sheet->getCell('A2')->getHyperlink()->setUrl('http://www.phpexcel.net');
// Ссылка на ячейку листа с названием Sheet2
$sheet->getCell('A2')->getHyperlink()->setUrl("sheet://'Sheet2'!D5");

...

Так же в виде ссылки может быть использован, к примеру, email адрес: mailto:example@mail.com.



Чтение данных из Excel

Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.

С помощью библиотеки PHPExcel можно читать следующие форматы:

  • Excel 2007;
  • Excel 5.0/Excel 95;
  • Excel 97 и поздние версии;
  • PHPExcel Serialized Spreadshet;
  • Symbolic Link;
  • CSV.

Для работы нам понадобятся объекты двух классов:

  • PHPExcel_Worksheet_RowIterator – используется для перебора строк;
  • PHPExcel_Worksheet_CellIterator – используется для перебора ячеек.

Для демонстрации выведем данные из таблицы с информацией об автомобилях.



Пример чтения файла представлен ниже:


require_once ('PHPExcel/IOFactory.php');

// Открываем файл
$xls = PHPExcel_IOFactory::load('xls.xls');
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();

Первый вариант

...

echo "<table>";

// Получили строки и обойдем их в цикле
$rowIterator = $sheet->getRowIterator();
foreach ($rowIterator as $row) {
	// Получили ячейки текущей строки и обойдем их в цикле
	$cellIterator = $row->getCellIterator();

	echo "<tr>";
		
	foreach ($cellIterator as $cell) {
		echo "<td>" . $cell->getCalculatedValue() . "</td>";
	}
	
	echo "</tr>";
}
echo "</table>";

Второй вариант

...

echo "<table>";

for ($i = 1; $i <= $sheet->getHighestRow(); $i++) {  
    echo "<tr>";
	
	$nColumn = PHPExcel_Cell::columnIndexFromString(
		$sheet->getHighestColumn());
	
	for ($j = 0; $j < $nColumn; $j++) {
		$value = $sheet->getCellByColumnAndRow($j, $i)->getValue();
		echo "<td>$value</td>";
	}
     
    echo "</tr>";
}
echo "</table>";


В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:

  • getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
  • getHighestRow() – возвращает количество занятых строк в активном листе.

А также нам не обойтись без помощи метода columnIndexFromString, который входит в состав класса PHPExcel_Cell. Данный метод позволяет определить индекс столбца по его символьному представлению.

С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.

Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 28

    0
    Неплохая статья. Но вот про форматы, которые библиотека может читать/записывать, вы похоже перепутали местами.
    Т.е. это запись:
    С помощью библиотеки PHPExcel можно читать следующие форматы:

    • Excel 2007;
    • Excel 97 и поздние версии;
    • PHPExcel Serialized Spreadshet;
    • HTML;
    • PDF;
    • CSV.

    А это чтение:
    С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:

    • Excel 2007;
    • Excel 5.0/Excel 95;
    • Excel 97 и поздние версии;
    • PHPExcel Serialized Spreadshet;
    • Symbolic Link;
    • CSV.
      0
      Доброй ночи!
      Большое спасибо за указание на неточность предоставленных данных. Исправлено.
      +2
      Насколько я понимаю phpexcel.codeplex.com и github.com/PHPOffice/PHPExcel одна и та же библиотека, только по 2 ссылке более свежая информация.
        0
        Да, все верно.
        –7
        *.fods + Libre Office. Чистый XML. Хватит кормить Microsoft.
          +3
          Вы пробовали генерировать XLSX файлы с количеством строк более 5000?

          У нас была задача сделать экспорт в Excel списка товаров. Там было в районе 15000 строк. При генерации файла с использованием данной библиотеки, php на пике потреблял ~1024Mb, что не могло радовать.

          Пришлось изворачиваться и генерировать набор файлов, где первый — сроки с 1 по 5000, второй — с 5001 по 10000 и т.д. Файлы генерировались в отдельном процессе php, запускаемом примерно так:

          <?php
          ...
          
          for($cycleIndex = 0; $cycleIndex < $cycleCount; $cycleIndex++)
          {
          	$executableTemplate = ':phpExec minion export --exportClass=:minionTask';
          	$executableTemplate .= ' --skip=:skip --take=:take --fileName=:fileName --userId=:userId --extra=:extra';
          	$executable = strtr($executableTemplate, array(
          		':phpExec' => Kohana::$config->load('core.phpExec'),
          		':minionTask' => $minionTask,
          		':fileName' => $fileName,
          		':userId' => $userId,
          		':take' => $maxTake,
          		':skip' => $cycleIndex * $maxTake,
          		':extra' => base64_encode(json_encode($this->_extraStateOptions)),
          	));
          
          	$output = array();
          	exec($executable, $output);
          	...
          	//Проверки статусов и сбор файлов для последующей упаковки
          }
          ...
          
          


          Только так вышло экспортировать нормально все это добро, но ощущение костылей преследует до сих пор :)
            0
            Очень прожорливая эта штука. Мы в свое время вообще от неё отказались в пользу простенькой библиотеки, заточенной под наши нужды.
            А пхпэксель оставили для генерации «красивых отчетов», одно- двух- страничных.
              +2
              обошел это очень просто в своё время: генерировал обычный html table, приделывал расширения xlsx, MSO кушал и не плевался(кроме сообщения о том, что «что-то с форматом не том, открывается как есть»).
              Это тоже костыль, но довольно рабочий. Альтернатива — CSV; работает всё-таки неплохо(кроме небольшой пляски с кодировками, но там всё совсем просто).
              и как уже написали ниже, из табличек/CSV можно гонять консольным LO/OO куда угодно
                0
                Хотел написать про html и приделывание расширения, но вы меня опередили :)
                  0
                  Сделал в одной из своих программ выгрузку в формате XML Spreadsheet (одиночный xml-файл). Правда, картинки туда вроде не впихнёшь. Но размеры и стили ячеек можно задавать спокойно и делать вполне прилично выглядящие документы на много листов.
                  0
                  Да, PHPExcel прожорливенькая на RAM. Отчеты более 5000 строк за одну выгрузку не генерировал (уже точно не помню). При больших объемах тоже приходится извращаться. В большинстве случаев (если не требуется украшательств) спасает импорт в CSV. Или же выбор лимита.
                    0
                    Соглашусь. В нашем случае как раз украшательства и были нужны, т.е. форматирование, ячейки заголовки синие и жирным шрифтом, все красиво разлиновано и прочий офисный ужас.

                    Сейчас сижу и думаю, надо было проверить то, что рекомендовал хабраюзер la0 — решение на просторах коллективного разума предложено было не раз и не два, но мы его благополучно проигнорировали, погнавшись за фичами :)
                  0
                  Все хорошо пока вы пишете / читаете небольшой файл.
                  Как только к-во строк переваливает за тысячи, работать с этой библиотекой невозможно. Съедается не только память, может просто уйти в себя.
                  Так же есть проблемы с кодировкой Excel 95 который генерит 1С 7, хотя это проблема на другой стороне, пишут что формат реализован не до конца.

                  Проблема с лидирующими 0'ми в формате CSV.

                  Спасает конвертация консолью Libre/Open Office в CSV и обработка нативными средствами.
                    0
                    Здесь рекомендации по чтению файлов больших объемов. Сам пока не пробовал.
                      –1
                      Даже если вот эта статья Вашего авторства — копипаста на хабре вроде как недопустима.
                        0
                        Да это моя статья.
                        Я перед публикацией смотрел некоторые статьи с хабры специально (есть ли где нибудь их еще публикации). Находил 1-3 копии. Самая первая моя статья размещена на хабре уже года. Предупреждений не было. Ну если вынесут предупреждение – придется удалить.
                          0
                          копипаста это по определению не авторский текст.
                          0
                          Хм, обычно (если xms для внутреннего пользования) я сохранял в обычный file.html обычную таблицу, но назвыал его file.xls. Там можно и цвета менять, и ширину столбцов, и формулы работают вроде :)
                            0
                            Спасибо за статью, очень порадовали примеры со стилями. Не хватает только инструкции по merged cells.
                              0
                              Рад что в помощь.
                              Вот:
                              // Получаем массив объединенных ячеек
                              print_r($sheet->getMergeCells());

                              // объединение
                              $sheet ->mergeCells(«A1:G1»);
                                0
                                prishelec, это-то как раз понятно. Я имел в виду какой-нибудь сложный пример с чтением\записью смерженных ячеек. Ну да ладно :)
                                  0
                                  Вы имеете ввиду, как прочесть множество объединенных ячеек как одну?
                                    0
                                    В том числе. А еще можно было бы привести пример переопределения итератора для строк/столбцов.
                                      0
                                      Извиняюсь за долгий ответ. Не понял, что вы имеете ввиду про итераторы.
                                        0
                                        phpExcel дает возможность получить итераторы сторк и столбцов. Обернув их в IteratorIterator или FilterIterator можно получить достаточно крутые возможности, а именно:

                                        • Читать строки «пачками», например по 6 штук сразу. Этот пример актуален если нужно читать какую-то свзанную информацию из таблицы, в моем случае это были данные клиентов компании.
                                        • Читать только необходимые строки. Например только те, у которых в указанной ячейке нужное значение.


                                        И прочее-прочее. Еще очень удобно такие штуки дебажить с помощью LimitIterator. Ну это так все, из собственного опыта.
                                          0
                                          Что-что, но пока не приходилось читать связанные данные. Может у вас есть ссылки на примеры?
                                            0
                                            Имеется в виду логические связанные. Так удобнее обычно для дальнейше обработки.
                              0
                              Тот случай, когда гуглил, как запилить очередную фичу и нашел ровно то, что нужно и даже чуть больше!
                              Автору огромное спасибо

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое