Создаем отчеты в Excel на PHP
Не редко при разработке некоего проекта, возникает необходимость в формировании отчетной статистики. Если проект разрабатывается на 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 — массив с параметрами защиты ячейки.
- Применить метод duplicateStyle, класса PHPExcel_Style. Этот метод может оказаться весьма полезным, если предстоит работа с заранее загруженным файлом (шаблоном), где удобнее будет продублировать стиль некой ячейки, чем самостоятельно его определять. Данный метод принимает два параметра:
- pCellStyle – данный параметр является экземпляром класса PHPExcel_Style;
- pRange – диапазон ячеек.
- Использовать методы класса 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’]) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.
А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):
$sheet->duplicateStyle($sheet->getStyle('B2'), 'F2:F10');
Добавление комментариев
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
...
// Стили шрифтов
$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() – возвращает количество занятых строк в активном листе.
Другие полезные методы
Возможностей по работе с отчетами формата excel с использованием PHP как мы видим, достаточно много. Но мы рассмотрим еще несколько полезных методов, которые могут оказаться весьма полезны в работе:
- getMergeCells() – с помощью данного метода принадлежащего классу PHPExcel_Worksheet можно получить информацию обо всех объединенных ячейках в листе;
- setPreCalculateFormulas([$pCellStyle = true]) – данный метод необходимо использовать если требуется произвести расчет формул в листе (он имеется у двух классов: PHPExcel_Writer_Excel5 и PHPExcel_Writer_Excel2007). В рассматриваемый метод передается параметр типа boolean: true или false (если передать значение true, то расчет формул произойдет перед сохранением файла автоматически, иначе расчета формул не последует). Использование данного метода может оказаться полезным если созданный файл потребуется загрузить, к примеру на Google Drive. Ведь в таком случае расчет формул не будет произведен автоматически указанным сервисом и здесь вся ответственность ложиться на нас;
- stringFromColumnIndex([$pColumnIndex = 0]) – данный метод позволяет определить по номеру столбца его символьное представление, для этого в качестве параметра необходимо передать его номер;
- columnIndexFromString([$pString = ‘A’]) – с помощью данного метода можно определить номер столбца по его символьному представлению, для этого в качестве единственного параметра необходимо передать его обозначение.
Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:
PHPExcel_Cell::stringFromColumnIndex(15);
PHPExcel_Cell::columnIndexFromString('A1');
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
24.10.2014 @ 11:10 пп
Спасибо автору, вы очень помогли с своим постом! Но всё же возник вопрос с оформлением: мне нужно в одном ячейке некоторых особых слов покрасит цветом. Я понял как окрасит весь текст в ячейке а вот часть текста в ячейке никак не могу понят, весь гугл прорыл, покрасить весь текст в ячейке есть а часть текста нет( Автор, будьте добры, подскажите что нибудь! За ранее спасибо!
25.10.2014 @ 10:19 дп
Приветствую!
В PHPExcel я такого не встречал. Много чего не документировано (по крайней мере я не видел документации). О некоторых функция я узнал из исходников.
31.10.2014 @ 8:08 пп
Респект автору. Грамотно подал.
01.11.2014 @ 2:35 дп
Спасибо!
05.11.2014 @ 4:28 пп
Спасибо большое! Отличная статья!
06.11.2014 @ 4:06 дп
Спасибо! Рад что в помощь. )
27.11.2014 @ 2:30 пп
Автору спасибо за статью. Возник вопрос. При формировании отчета необходимо заменить нулевые значения на «-«. Подскажите, как это можно реализовать? Заранее спасибо!
27.11.2014 @ 3:51 пп
Добрый день!
Ну, при формировании отчета используйте str_replace.
05.12.2014 @ 3:34 пп
Не получается объединить ячейки способом :
$sheet->mergeCells(‘A1:H1’). Подскажите, пожалуйста, может быть, синтаксис немного другим должен быть
05.12.2014 @ 4:44 пп
Добрый день!
Вы все делаете верно.
Отправьте мне на email (указан в контактах) ваш код я посмотрю.
06.12.2014 @ 11:02 пп
Здравствуйте! у меня вопрос следующий:
Когда мы обходим в цикле таблицу мы можем отделить первую строку как заголовки? то есть из нее создать отдельный массив со значениями, а из строк другой массив? как это делается?
Заранее благодарю
07.12.2014 @ 2:10 дп
Приветствую!
Вот как то так:
$table = array(array('Mazda', 'Audi', 'Ford'),
array(1992, 1994, 1995),
array(2001, 2004, 2007),
array(1981, 1982, 1980)
);
$keys = array ();
$values = array ();
for ($i = 0; $i < count($table); $i++) { for ($j = 0; $j < count($table[0]); $j++) { if ($i == 0) { $keys[] = $table[$i][$j]; } else { $values[$j][] = $table[$i][$j]; } } } print_r(array_combine($keys, $values));
Вам останется только чуть изменить.
07.12.2014 @ 2:34 дп
Вопрос был в другом — применительно к phpexcel)) но ладно — на него я ответ нашел. Помоги пожалуйста. Нашел вот эту статью: http://habrahabr.ru/post/136540/ . В ней чувак написал функцию для того чтобы получать отображаемое значение ячеек. Куда нужно ее вставить в phpexcele для дальнейшего использования? Заранее благодарю
07.12.2014 @ 4:26 дп
1. Это был пример, который вы можете легко изменить под PHPExcel. Ведь как я понял, вам нужен был принцип алгоритма.
2. Я так понял, что он намекает на то, что он создал свой класс-наследник от PHPExcel. То же самое и вы делайте.
07.12.2014 @ 12:34 пп
Я извиняюсь. Я плохо разбираюсь в классах. Вы не поможете?
require_once(‘что подсоединять???’);
Class Myclass extended (какой класс??) {
public function getCellValue($cellOrCol, $row = null, $format = ‘d.m.Y’)
…
Так? или что то нужно добавить?
07.12.2014 @ 1:17 пп
Вот так:
require_once(‘PHPExcel.php’);
Class Myclass extended PHPExcel
07.12.2014 @ 5:50 пп
Нет. выдает ошибку… Parse error: syntax error, unexpected T_STRING, expecting ‘{‘ in Z:\home\localhost\www\dokky\Classes\myClass.php on line 3…
07.12.2014 @ 9:47 пп
Вам сообщают, что ожидается открывающая фигурная скобка ‘{‘. Где то вы забыли ее указать.
08.12.2014 @ 12:39 дп
Уже нашел) исправил)
Но теперь блин обратиться не получается к этой функции(
18.12.2014 @ 9:33 дп
Добрый день!
Использую такой код для перевода EXCEL в HTML
require_once(ROOT.’basis/package/PHPExcel/PHPExcel.php’);
$xls = PHPExcel_IOFactory::load($folder.$filename);
$objWriter = PHPExcel_IOFactory::createWriter($xls, ‘HTML’);
$objWriter->setSheetIndex(0);
$data = «».$objWriter->generateStyles(false).»».$objWriter->generateSheetData();
Далее $data записыватся в файл html и при необходимости инклудится на страницу.
К сожалению Ексель конвертируется в html без сохранения изображений находящихся в нем.
Существует ли какие то решения как в html перенести и изображения?
Спасибо
18.12.2014 @ 10:47 дп
Добрый день!
Сам я не сталкивался с такой задачей.
Можно попробовать сделать экспорт методом прохода ячеек и с указанием путей к изображениям.
18.12.2014 @ 1:52 пп
Мне необходимо перенести ексель документ в html страницу.
Без потери визуальной составляющей (граница ячеек, цвет, ширина и высота и тд) Зачастую в этих документах встречаются изображения их соответственно тоже необходимо вставить в html/
Вопрос как это лучше реализовать или может быть есть готовые решения.
Спасибо
18.12.2014 @ 4:49 пп
Я бы попробовал как то так: поискать есть ли возможность сохранения изображения из excel в некий каталог, а затем вставить его (зная его расположение и имя) в html.
31.12.2014 @ 11:37 дп
Добрый день, уважаемый Администратор.
Спасибо за статью. Только проверить работоспособность скрипта у меня не получается. Хотел проверить чтение данных из Excel через веб-форму. Я ранее использовал класс PHPExcelReader. Но данная реализация не может считывать данные из Excel 2007. И иногда наблюдаются проблемы и при считывании файлов Excel 2003.
Объявляю переменные:
$excel_name = $_FILES[‘excel_name’][‘name’];
$tmp_path = $_FILES[‘excel_name’][‘tmp_name’];
$uploadfile = $tmp_path.basename($excel_name);
Далее указываю путь к файлу $xls = PHPExcel_IOFactory::load($uploadfile);
В итоге выходит ошибка при отправке данных по кнопке submit: Uncaught exception ‘PHPExcel_Reader_Exception’ with message ‘Could not open C:\Program Files\php5\tmp\php73.tmptest.xlsx for reading! File does not exist.’ (потом много ещё ругани на файлы Excel2007.php, IOFactory.php и т.д.)
В этой связи у меня вопрос, скажите, пожалуйста, как правильно реализовать загрузку файлов через веб-форму для считывания таблиц с помощью класса PHPExcel? Видимо, всё-таки, я как-то некорректно пытаюсь «подсунуть» свой файл для считывания. Хотя PHPExcelReader таким способом файлы считывал.
Заренее благодарю за ответ.
04.01.2015 @ 2:01 дп
Приветствую!
Да все как обычно. Роли не играет через веб или нет.
$Path = $_FILES[‘file’][‘tmp_name’];
if (@file_exists($Path)) {
…
$xls = PHPExcel_IOFactory::load($Path);
…
}
10.01.2015 @ 2:42 дп
День добрый. Благодарю, ваш вариант указания пути к файлу сработал. Файл считывается и выводится, но только если в ячейках содержатся простые значения (например, 11 22 33). Как только серьёзный текст, сразу очередная ругань в духе «Allowed memory size of 134217728 bytes exhausted».
Уж я не знаю, на что грешить. Ну какие ещё разрешённые объёмы памяти могут быть исчерпаны, если я пытаюсь считать жалкие 400Кб ?! Неужели Денвер накладывает какие-то ограничения? А ведь я всего-то навсего ваш пример отсюда скопировал. Ничего же своего ещё не успел добавить. Снова в тупике каком-то.
12.01.2015 @ 10:11 пп
Добрый вечер!
Если объем данных большой и стандартными средствами прочесть файл не удается. Используйте: Microsoft Excel Spreadsheets. Напиши ваш email, могу сбросить пример.
13.01.2015 @ 9:14 пп
Здравствуйте.
>Если объем данных большой
Маленький объём. Три строки и 10 колонок. В общем я разобрался. В файле CachedObjectStorageFactory.php есть массив с параметрами, чтобы настроить под себя работу скрипта. В нём по умолчанию задан 1Мб на кэширование. Я увеличил объём до 1Гб. Чтоб уж наверняка. ‘memoryCacheSize’ => ‘1024MB’ И сразу всё заработало. И даже с бОльшими по объёму файлами.
Кстати, в цикле for ($i = 0; $i getHighestRow(); $i++) добавьте равно $i getHighestRow() Иначе последняя строка в таблице не считается.
>Напишите ваш email, могу сбросить пример.
А разве мой адрес вам не виден, как администратору? Ведь при добавлении комментария это поле обязательное. Зачем тогда оно?
13.01.2015 @ 9:59 пп
Да нет, все в цикле верно:
for ($i = 0; $i < $sheet->getHighestRow(); $i++)
Цикл начинается с нуля, поэтому и оператор «<», а не «<=». Специально только что проверил.
20.01.2015 @ 12:05 пп
Спасибо огромное. Еще б комментарии нормальные прикрутил
http://habrahabr.ru/post/148203/
———————————————————————————
Несколько вопросов и пожеланий.
1. Для нубов можно было бы и сказать что запускаем свой файл из папки с PHPExcel.php
2. Для нубов можно было напомнить что кодировка UTF-8 (без BOM) в npp а то иначе кирилица не читается
3. Что это за странная ошибка?
http://i.imgur.com/V5nePeP.png
Думал от того что заголовок теряет он пытается восстановить, но и после смены кодировки таже проблема.
—————
Но это так придирки — самый нужный вопрос в следующем — как таблицу HTML преобразовать к виду Excel ?
Так вот чтобы не париться и самому все не парсить и не записывать а чтобы phpexcel сам посмотрел что за таблица и по образу и подобию создал
если найдете ответ напишите плиз des1roer@gmail.com
20.01.2015 @ 2:52 пп
1. Но вот вы сами и помогли: ответили на свои же вопросы.
2. Что там у вас за ошибка – не ясно. Первый раз такую встречаю.
3. Что касается по поводу экспорта таблицы в HTML, то я знаю, что такая возможность имеется в самом PHPExcel, но мне пока не приходилось работать с такими «вещами».
31.01.2015 @ 10:08 пп
Не подскажите как сохранить файл в pdf?
02.02.2015 @ 1:33 пп
Добрый день!
Вот можете здесь почитать.