Phpexcel перенос по словам

Phpexcel перенос по словам

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

require_once ‘Classes/PHPExcel.php’;
$pExcel = new PHPExcel();

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();

С помощью метода setActiveSheetIndex(0) указываем индекс (номер) активного листа. Нумерация листов начинается с нуля. Далее с помощью метода getActiveSheet() получаем объект этого активного листа, то есть другими словами получаем доступ к нему для работы. И сохраняем этот объект в переменную $aSheet.

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

$pExcel->createSheet();

Затем, по аналогии, указываем индекс и получаем объект активного листа.

// Ориентация страницы и размер листа
$aSheet->getPageSetup()
->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$aSheet->getPageSetup()
->SetPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Поля документа
$aSheet->getPageMargins()->setTop(1);
$aSheet->getPageMargins()->setRight(0.75);
$aSheet->getPageMargins()->setLeft(0.75);
$aSheet->getPageMargins()->setBottom(1);
// Название листа
$aSheet->setTitle(‘Прайс-лист’);
// Шапка и футер (при печати)
$aSheet->getHeaderFooter()

->setOddHeader(‘&CТД ТИНКО: прайс-лист’);
$aSheet->getHeaderFooter()
->setOddFooter(‘&L&B’.$aSheet->getTitle().’&RСтраница &P из &N’);
// Настройки шрифта
$pExcel->getDefaultStyle()->getFont()->setName(‘Arial’);
$pExcel->getDefaultStyle()->getFont()->setSize(8);

Вначале задаем ориентацию листа при помощи метода setOrientation(), которому передаем константу класса PHPExcel_Worksheet_PageSetup:

  • ORIENTATION_PORTRAIT — книжная
  • ORIENTATION_LANDSCAPE — альбомная

Обратите внимание, что перед методом setOrientation() необходимо вызвать метод getPageSetup(), который предоставляет доступ к настройкам страницы.

Далее вызываем метод SetPaperSize(), который позволяет задать размер страницы для печати. Ему передаем параметром константу PAPERSIZE_A4 класса PHPExcel_Worksheet_PageSetup. Что означает, что размер листа страницы будет установлен А4.

Далее устанавливаем поля документа, то есть отступы от краев документа. Отступы задаются в специальных символьных единицах. Вначале, обратите внимание, вызываем у объекта $aSheet метод getPageMargins(), который вернет объект класса, отвечающего за настройки полей страницы. Затем вызываем методы setTop(), setRight(), setLeft() и setBottom().

Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

  • setOddHeader();
  • setOddFooter();

Обратите внимание на передаваемые параметры:

  • для шапки передаем строку ‘&CТД ТИНКО: прайс-лист’; метка &C означает, что текст нужно расположить по центру.
  • для подвала передаем строку ‘&L&B’.$aSheet->getTitle().’&RСтраница &P из &N’; это означает, что нужно вывести слева и жирным шрифтом (&L&B) название листа (метод $aSheet->getTitle()), затем справа (&R) вывести номер страницы (&P) из общего количества страниц (&N).

Затем указываем настройки шрифта по умолчанию:

  • setName(‘Arial’) — задаем имя шрифта;
  • setSize(8) — задаем размер шрифта.

Наполнение документа данными

Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:

$aSheet->getColumnDimension(‘A’)->setWidth(3);
$aSheet->getColumnDimension(‘B’)->setWidth(7);
$aSheet->getColumnDimension(‘C’)->setWidth(20);
$aSheet->getColumnDimension(‘D’)->setWidth(40);
$aSheet->getColumnDimension(‘E’)->setWidth(10);

Теперь заполним несколько ячеек текстом:

$aSheet->mergeCells(‘A1:E1’);
$aSheet->getRowDimension(‘1’)->setRowHeight(20);
$aSheet->setCellValue(‘A1′,’ТД ТИНКО’);
$aSheet->mergeCells(‘A2:E2’);
$aSheet->setCellValue(‘A2′,’Поставка технических средств безопасности’);
$aSheet->mergeCells(‘A4:C4’);
$aSheet->setCellValue(‘A4′,’Дата создания прайс-листа’);

Здесь мы сначала объеденяем ячейки с А1 до E1 при помощи метода mergeCells(), далее задаем высоту строки: вначале получаем доступ к строке 1 при помощи метода getRowDimension(‘1’), затем задаем высоту — setRowHeight(20). Далее при помощи метода setCellValue(‘A1′,’ТД ТИНКО’), устанавливаем значение ячейки А1.

Далее давайте в ячейку D4 запишем текущую дату:

// Записываем данные в ячейку

$date = date(‘d-m-Y’);
$aSheet->setCellValue(‘D4’,$date);
// Устанавливает формат данных в ячейке (дата вида дд-мм-гггг)
$aSheet->getStyle(‘D4’)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14); С помощью констант, определенных в классе PHPExcel_Style_NumberFormat, можно задать формат ячейки: FORMAT_GENERAL (общий), FORMAT_TEXT (текст), FORMAT_NUMBER (число), FORMAT_NUMBER_00 (число с дробной частью), FORMAT_PERCENTAGE (процент), FORMAT_PERCENTAGE_00 (процент с дробной частью) и т.п.

Теперь, используя метод setCellValue(), а также цикл while() наполним данными наш прайс-лист:

mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_query(‘SET NAMES utf8’);
mysql_select_db(DB_NAME);
// Создаем шапку таблички данных
$aSheet->setCellValue(‘A6′,’№’);
$aSheet->setCellValue(‘B6′,’Код’);
$aSheet->setCellValue(‘C6′,’Наименование’);
$aSheet->setCellValue(‘D6′,’Описание’);
$aSheet->setCellValue(‘E6′,’Цена’);
$query = «SELECT `code`, `name`, `description`, `price` FROM `products` WHERE 1 LIMIT 10»;
$res = mysql_query( $query );
$i = 1;
while( $prd = mysql_fetch_assoc($res) ) {
$aSheet->setCellValue(‘A’.($i+6), $i);
$aSheet->setCellValue(‘B’.($i+6), $prd);
$aSheet->setCellValue(‘C’.($i+6), $prd);
$aSheet->setCellValue(‘D’.($i+6), $prd);
$aSheet->setCellValue(‘E’.($i+6), $prd);
$i++;
}

Стилизация данных

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

// массив стилей
$style_wrap = array(
// рамки
‘borders’=>array(
// внешняя рамка
‘outline’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_THICK,
‘color’ => array(
‘rgb’=>’006464’
)
),
// внутренняя
‘allborders’=>array(
‘style’=>PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’=>’CCCCCC’
)
)
)
);
$aSheet->getStyle(‘A1:F’.($i+5))->applyFromArray($style_wrap);

Теперь, по аналогии, применим стили к остальным ячейкам:

// Стили для верхней надписи (первая строка)
$style_header = array(
// Шрифт
‘font’=>array(
‘bold’ => true,
‘name’ => ‘Times New Roman’,
‘size’ => 15,
‘color’=>array(
‘rgb’ => ‘006464’
)
),
// Выравнивание
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
),
// Заполнение цветом
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ’99CCCC’
)
),
‘borders’=>array(
‘bottom’=>array(
‘style’=>PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’=>’006464’
)
)
)
);
$aSheet->getStyle(‘A1:E1’)->applyFromArray($style_header);
// Стили для слогана компании (вторая строка)
$style_slogan = array(
// шрифт
‘font’=>array(
‘bold’ => true,
‘italic’ => true,
‘name’ => ‘Times New Roman’,
‘size’ => 12,
‘color’=>array(
‘rgb’ => ‘006464’
)
),
// выравнивание
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
),
// заполнение цветом
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ’99CCCC’
)
),
//рамки
‘borders’ => array(
‘bottom’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’=>’006464’
)
)
)
);
$aSheet->getStyle(‘A2:E2’)->applyFromArray($style_slogan);
// Стили для текта возле даты
$style_tdate = array(
// выравнивание
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
),
// заполнение цветом
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ‘EEEEEE’
)
),
// рамки
‘borders’ => array(
‘right’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_NONE
)
)
);
$aSheet->getStyle(‘A4:D4’)->applyFromArray($style_tdate);
// Стили для даты
$style_date = array(
// заполнение цветом
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ‘EEEEEE’
)
),
// рамки
‘borders’ => array(
‘left’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_NONE
)
),
);
$aSheet->getStyle(‘E4’)->applyFromArray($style_date);
// Стили для шапки таблицы (шестая строка)
$style_hprice = array(
// выравнивание
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
),
// заполнение цветом
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ‘CFCFCF’
)
),
// шрифт
‘font’=>array(
‘bold’ => true,
/* ‘italic’ => true, */
‘name’ => ‘Times New Roman’,
‘size’ => 10
),
);
$aSheet->getStyle(‘A6:E6’)->applyFromArray($style_hprice);
// Cтили для данных в таблице прайс-листа
$style_price = array(
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_LEFT,
)
);
$aSheet->getStyle(‘A7:E’.($i+5))->applyFromArray($style_price);

Сохранение документа

Осталось только сохранить наш документ:

/*
$objWriter = PHPExcel_IOFactory::createWriter($pExcel, ‘Excel5’);
$objWriter->save(‘simple.xls’);
*/
$objWriter = PHPExcel_IOFactory::createWriter($pExcel, ‘Excel2007’);
$objWriter->save(‘simple.xlsx’);

или так

/*
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
$objWriter->save(‘simple.xls’);
*/
$objWriter = new PHPExcel_Writer_Excel2007($pExcel);
$objWriter->save(‘simple.xlsx’);

Если нужно вывести документ в браузер

/*
header(‘Content-Type:application/vnd.ms-excel’);
header(‘Content-Disposition:attachment;filename=»simple.xls»‘);
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
*/
header(‘Content-Type:xlsx:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition:attachment;filename=»simple.xlsx»‘);
$objWriter = new PHPExcel_Writer_Excel2007($pExcel);
$objWriter->save(‘php://output’);

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.

Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.

Примеры формул Excel: =27+36, =А1+А2-АЗ, =SUM(А1:А5), =MAX(АЗ:А5), =(А1+А2)/АЗ.

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

// формула для вычисления суммы
$formula = ‘=SUM(D2:D4)’;
$aSheet->setCellValue(‘D5’, $formula);

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

// Подключаем библиотеку
require_once ‘Classes/PHPExcel.php’;
$pExcel = PHPExcel_IOFactory::load(‘simple.xlsx’);
// Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
// выгружаем данные из объекта в массив
$tables = $worksheet->toArray();
}

Теперь можно вывести массив:

// Цикл по листам Excel-файла
foreach( $tables as $table ) {
echo ‘<table border=»1″>’;
// Цикл по строкам
foreach($table as $row) {
echo ‘<tr>’;
// Цикл по колонкам
foreach( $row as $col ) {
echo ‘<td>’.$col.'</td>’;
}
echo ‘</tr>’;
}
echo ‘</table>’;
}

Для получения значения отдельной ячейки:

// выбираем лист, с которым будем работать
$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();
// получаем доступ к ячейке по номеру строки
// (нумерация с единицы) и столбца (нумерация с нуля)
$cell = $aSheet->getCellByColumnAndRow($col, $row);
// читаем значение ячейки
$value = $cell->getValue()

или так:

$value = $pExcel->getActiveSheet()->getCellValue(‘B2’)

Еще два примера:

// Цикл по листам Excel-файла
foreach( $pExcel->getWorksheetIterator() as $worksheet ) {
echo ‘<h2>Лист «‘.$worksheet->getTitle().'»</h2>’;
echo ‘<table border=»1″>’;
// Цикл по строкам
foreach( $worksheet->getRowIterator() as $row ) {
echo ‘<tr>’;
// Цикл по колонкам
foreach( $row->getCellIterator() as $cell ) {
$value = $cell->getValue();
// $calcValue = $cell->getCalculatedValue()
// $dataType = PHPExcel_Cell_DataType::dataTypeForValue($value);
echo ‘<td>’.$value.'</td>’;
}
echo ‘</tr>’;
}
echo ‘</table>’;
} // Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // например, 10
$highestColumn = $worksheet->getHighestColumn(); // например, ‘E’
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) — 64;
echo ‘<h2>Лист «‘.$worksheetTitle.'» ‘;
echo $nrColumns . ‘ колонок (A-‘ . $highestColumn . ‘) ‘;

Привет! Однажды у меня возникала задача по генерации отчетов в Excel на backend’е, которую я решил и теперь могу поделиться одним из способов создания документов Excel и добавлением в них данных средствами PHP.

Существует несколько библиотек для работы с xls файлами на PHP, но мы рассмотрим PHPExcel, попробуем создать файл xls, наполнить его данными и немного оформить стиль, то есть разберемся с базовым функционалом, которого будет достаточно для выполнения многих задач связанных с генерацией документов Excel на PHP. Однако, библиотека позволяет делать многое, в том числе работать с формулами, применять различные стили оформления, работать с листами.

Код скрипта, который вы увидите по ходу прочтения статьи, доступен на github.

Библиотека PHPExcel доступна в Composer как PHPOffice/PHPExcel. Я инициализирую в пустом каталоге composer и добавлю в зависимости PHPOffice/PHPExcel.

1 2 $ composer init $ composer require PHPOffice/PHPExcel

После создам файл generator.php и в нем подключу автозагрузку библиотек Composer’а.

PHP

1 2 3 <?php require ‘./vendor/autoload.php’;

Теперь можно приступить к работе, используя библиотеку PHPExcel.

Давайте создадим пустой документ со списком котов «CatList».

1 2 3 4 $document = new \PHPExcel(); $objWriter = \PHPExcel_IOFactory::createWriter($document, ‘Excel5’); $objWriter->save(«CatList.xls»);

Мы создали экземпляр класса PHPExcell и создали объект, используя фабрику, который занимается сохранением документов. Кстати, вторым аргументом метода createWriter можно передавать значение «PDF» и таким образом генерировать PDF файлы средствами PHP.

После запуска скрипта в директории появился файл CatList.xls, его можно открыть в Excel и убедиться, что он пустой и программа его открывает без ошибок.

Заполнение Excel документа данными

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

PHP

1 2 3 4 5 $catList = , , , ];

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

PHP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 $document = new \PHPExcel(); $sheet = $document->setActiveSheetIndex(0); // Выбираем первый лист в документе $columnPosition = 0; // Начальная координата x $startLine = 2; // Начальная координата y // Вставляем заголовок в «A2» $sheet->setCellValueByColumnAndRow($columnPosition, $startLine, ‘Our cats’); // Выравниваем по центру $sheet->getStyleByColumnAndRow($columnPosition, $startLine)->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Объединяем ячейки «A2:C2» $document->getActiveSheet()->mergeCellsByColumnAndRow($columnPosition, $startLine, $columnPosition+2, $startLine); // Перекидываем указатель на следующую строку $startLine++;

На выходе получаем документ с заголовком, вставленным в объединенную ячейку по центру. Перейдем к отрисовке шапки.

PHP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 // Массив с названиями столбцов $columns = ; // Указатель на первый столбец $currentColumn = $columnPosition; // Формируем шапку foreach ($columns as $column) { // Красим ячейку $sheet->getStyleByColumnAndRow($currentColumn, $startLine) ->getFill() ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor() ->setRGB(‘4dbf62’); $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $column); // Смещаемся вправо $currentColumn++; }

На выходе получаем

Шапка документа

Перейдем к заполнению данными из списка наших котов.

PHP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 // Формируем список foreach ($catList as $key=>$catItem) { // Перекидываем указатель на следующую строку $startLine++; // Указатель на первый столбец $currentColumn = $columnPosition; // Вставляем порядковый номер $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $key+1); // Ставляем информацию об имени и цвете foreach ($catItem as $value) { $currentColumn++; $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $value); } }

Теперь, открыв отчёт, можно убедиться, что он сформирован как нужно.

Список котов

Весь код скрипта

PHP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 <?php require ‘./vendor/autoload.php’; $catList = , , , ]; $document = new \PHPExcel(); $sheet = $document->setActiveSheetIndex(0); // Выбираем первый лист в документе $columnPosition = 0; // Начальная координата x $startLine = 2; // Начальная координата y // Вставляем заголовок в «A2» $sheet->setCellValueByColumnAndRow($columnPosition, $startLine, ‘Our cats’); // Выравниваем по центру $sheet->getStyleByColumnAndRow($columnPosition, $startLine)->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Объединяем ячейки «A2:C2» $document->getActiveSheet()->mergeCellsByColumnAndRow($columnPosition, $startLine, $columnPosition+2, $startLine); // Перекидываем указатель на следующую строку $startLine++; // Массив с названиями столбцов $columns = ; // Указатель на первый столбец $currentColumn = $columnPosition; // Формируем шапку foreach ($columns as $column) { // Красим ячейку $sheet->getStyleByColumnAndRow($currentColumn, $startLine) ->getFill() ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor() ->setRGB(‘4dbf62’); $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $column); // Смещаемся вправо $currentColumn++; } // Формируем список foreach ($catList as $key=>$catItem) { // Перекидываем указатель на следующую строку $startLine++; // Указатель на первый столбец $currentColumn = $columnPosition; // Вставляем порядковый номер $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $key+1); // Ставляем информацию об имени и цвете foreach ($catItem as $value) { $currentColumn++; $sheet->setCellValueByColumnAndRow($currentColumn, $startLine, $value); } } $objWriter = \PHPExcel_IOFactory::createWriter($document, ‘Excel5’); $objWriter->save(«CatList.xls»);

Для работы с Excel использовал библиотеку PHPExcel. Установка простейшая – кладем папку Classes в нужную папку на сервере, указываем корректные пути в include/require.

Примеры кода по чтению/генерации файлов Excel можно посмотреть на github странице библиотеки.

Красивости

и этим не ограничивается функционал, это лишь то, что использовал:

  • mergeCells(“cell_range”) – Объединение указанных ячеек в одну. Данные должны лежать в первой ячейке, иначе они теряются.

$doc->getActiveSheet()->mergeCells(«B3:C3»);

  • setSize(16) – Делаем размер шрифта 16 для указанных ячеек.

$doc->getActiveSheet()->getStyle(‘B2:G2’)->getFont()->setSize(16);

  • setBold(true) – Делаем текст “жирным”

$doc->getActiveSheet()->getStyle(‘A1:O1’)->getFont()->setBold(true);

  • setWrapText(true) – Делаем перенос слов по умолчанию для всех ячеек

$doc->getDefaultStyle()->getAlignment()->setWrapText(true); $doc->getDefaultStyle()->getAlignment()->setWrapText(false); #disable

  • setAutoFilter – Включить фильтр по умолчанию

$doc->getActiveSheet()->setAutoFilter(‘A1:K1’);

  • freezePane – Закрепить какие либо строки, например первую

$doc->getActiveSheet()->freezePane(‘A2’);

  • borders – делается через создание стиля, а потом его применение на указанный диапазон ячеек

$styleBorder = array( ‘borders’ => array( ‘allborders’ => array( ‘style’ => PHPExcel_Style_Border::BORDER_THIN ) ) ); $doc->getActiveSheet()->getStyle(‘B2:G2’)->applyFromArray($styleBorder);

  • color – Аналогично с помощью стилей меняем цвет шрифта (Font)

$styleFontColor = array( ‘font’ => array( ‘color’ => array(‘rgb’ => ‘FF0000’), )); $doc->getActiveSheet()->getStyle(‘D10’)->applyFromArray($styleFontColor);

  • setARGB – Изменить цвет ячейки, например
    • всей первой строки
    • конкретной ячейки (делал так цвет был переменным и задавался на основе данных – формировался разноцветный показательный Excel)
    • диапазона ячеек по диагонали

$doc->getActiveSheet()->getStyle(‘A1:E1’)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(‘FFE8E5E5’); $doc->getActiveSheet()->getStyle(«A$val:$last_column$val»)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(‘FFE8E5E5’); $doc->getActiveSheet()->getStyle(‘I1:Z1000’)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(‘FFE8E5E5’);

ЧТЕНИЕ

Код для чтения (два столбца):

<!doctype> <html> <head> </head> <body> <?php require_once «Classes/PHPExcel.php»; $tmpfname = «test.xlsx»; $excelReader = PHPExcel_IOFactory::createReaderForFile($tmpfname); $excelObj = $excelReader->load($tmpfname); $worksheet = $excelObj->getSheet(0); $lastRow = $worksheet->getHighestRow(); echo «<table>»; for ($row = 1; $row <= $lastRow; $row++) { echo «<tr><td>»; echo $worksheet->getCell(‘A’.$row)->getValue(); echo «</td><td>»; echo $worksheet->getCell(‘B’.$row)->getValue(); echo «</td><tr>»; } echo «</table>»; ?> </body> </html>

Редактирование

Открываем файл test.xlsx, на его основе создаем новый new.xlsx с измененными парой ячеек.

<?php error_reporting(E_ALL); require_once(‘Classes/PHPExcel.php’); $excel2 = PHPExcel_IOFactory::createReader(‘Excel2007’); $excel2 = $excel2->load(‘test.xlsx’); $excel2->setActiveSheetIndex(0); $excel2->getActiveSheet()->setCellValue(‘J103’, ‘LOLOLOLOL’) ->setCellValue(‘O103’, ‘LOLOLOLOL’) ->setCellValue(‘Q103’, ‘LOLOLOLOL’); $objWriter = PHPExcel_IOFactory::createWriter($excel2, ‘Excel2007’); $objWriter->save(‘new.xlsx’); ?>

ГЕНЕРАЦИЯ

Пример генерации на основе результата MySQL (не тестил, использовал универсальную функцию ниже).

Если нужно протестить базовую работу генерации на основе двумерного массива

<!doctype> <html> <head> </head> <body> <?php require_once(‘Classes/PHPExcel.php’); $dataArray = array( array( ‘str1raw1’, ‘str1raw2’, ‘str1raw3’, ‘str1raw4’, ), array( ‘str2raw1’, ‘str2raw2’, ‘str2raw3’, ‘str2raw4’, ) ); // FILENAME $filename = «result.xlsx»; // create php excel object $doc = new PHPExcel(); // set active sheet $doc->setActiveSheetIndex(0); // read data to active sheet $doc->getActiveSheet()->fromArray($dataArray); //save our workbook as this file name //mime type //OLD EXCEL header(‘Content-Type: application/vnd.ms-excel’); //NEW EXCEL header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’); //tell browser what’s the file name header(‘Content-Disposition: attachment;filename=»‘ . $filename . ‘»‘); header(‘Cache-Control: max-age=0’); //no cache // clean data ob_end_clean(); //OLD EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, ‘Excel5’); //NEW EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, ‘Excel2007’); //force user to download the Excel file without writing it to server’s HD $objWriter->save(‘php://output’); exit; ?> </body> </html>

Пример генерации xls из двумерного массива с настройками

  • имени (определяется на основе значения в переменной $_POST),
  • ширины столбца (на основе $_POST),
  • bold первой строки (setBold),
  • переноса слов (setWrapText).

<!doctype> <html> <head> </head> <body> <?php require_once(‘Classes/PHPExcel.php’); //$dataArray = unserialize($_POST); $dataArray = json_decode($_POST); //print_r($_POST);//print out the whole post // FILENAME if (isset($_POST)) $filename = $_POST; //OLD EXCEL $filename = «result.xls»; //NEW EXCEL //$filename = «result.xlsx»; // create php excel object $doc = new PHPExcel(); // set active sheet $doc->setActiveSheetIndex(0); // read data to active sheet $doc->getActiveSheet()->fromArray($dataArray); // set bold for first string $doc->getActiveSheet()->getStyle(‘A1:H1’)->getFont()->setBold(true); // set wrap to all columns $doc->getDefaultStyle()->getAlignment()->setWrapText(true); // set column width based on POST if (isset($_POST)) $excelSettings = $_POST; if ($excelSettings == ‘default’) { $doc->getActiveSheet()->getColumnDimension(‘A’)->setWidth(17); $doc->getActiveSheet()->getColumnDimension(‘B’)->setWidth(40); $doc->getActiveSheet()->getColumnDimension(‘C’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘D’)->setWidth(40); $doc->getActiveSheet()->getColumnDimension(‘E’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘F’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘G’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘H’)->setWidth(15); } if ($excelSettings == ‘<vashnaya_tablica_1>’) { $doc->getActiveSheet()->getColumnDimension(‘A’)->setWidth(17); $doc->getActiveSheet()->getColumnDimension(‘B’)->setWidth(30); $doc->getActiveSheet()->getColumnDimension(‘C’)->setWidth(40); $doc->getActiveSheet()->getColumnDimension(‘D’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘E’)->setWidth(15); $doc->getActiveSheet()->getColumnDimension(‘F’)->setWidth(80); } if ($excelSettings == ‘<vashnaya_tablica_2>’) { $doc->getActiveSheet()->getColumnDimension(‘A’)->setWidth(20); $doc->getActiveSheet()->getColumnDimension(‘C’)->setWidth(40); } //save our workbook as this file name //mime type //OLD EXCEL header(‘Content-Type: application/vnd.ms-excel’); //NEW EXCEL header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’); //tell browser what’s the file name header(‘Content-Disposition: attachment;filename=»‘ . $filename . ‘»‘); header(‘Cache-Control: max-age=0’); //no cache // clean data ob_end_clean(); //OLD EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, ‘Excel5’); //NEW EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, ‘Excel2007’); //force user to download the Excel file without writing it to server’s HD $objWriter->save(‘php://output’); exit; ?> </body> </html>

Пример вызова и код по генерации кнопки, добавлению к названию файла даты/времени (формат 20170123_003800_Название.xlsx) и переходу на страницу генерации xls.

function create_xls($dataArray,$name,$excelSettings = «default») { //$serialized = htmlspecialchars(serialize($dataArray)); $serialized = htmlspecialchars(json_encode($dataArray)); $date = date(«Ymd»); $time = date(«Gis»); $filename = «$date-$time-$name.xlsx»; echo «<form action=create_xls.php method=\»post\»>»; echo «<input type=submit name=s_button value=\»Выгрузить\» id=\»submit\»>»; echo «<input type=\»hidden\» name=\»ArrayData\» value=\»$serialized\»/>»; echo «<input type=\»hidden\» name=\»filename\» value=\»$filename\»/>»; echo «<input type=\»hidden\» name=\»excelSettings\» value=\»$excelSettings\»/>»; echo «</form>»; }

Вызов функции (про функцию iconv_for_xls ниже):

$array_for_xls = array(); array_push($array_for_xls,array(iconv_for_xls(«Тест»),iconv_for_xls(«Тест2″))); create_xls($array_for_xls,»Название файла»,»Тип шаблона настроек для excel, например vashnaya_tablica_1″);

Особенности

Мусор

Нужно очень внимательно смотреть, чтобы php не генерировал никакого вывода, в противном случае возможны разные ошибки и искажения файла.

Например, возможны кракозябры при генерации файла больше 20-40 строк, если оставить включенным debug типа print_r($_POST); (почему не воспроизводиться при генерации мелких файлов – это вопрос :)).

Аналогично, будет выдавать ошибку:

«Не удаётся открыть файл «название файла. xlsx», так как формат или расширение этого файла являются недопустимыми.» «Excel cannot open the file «____» because the file format or file extension not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file».

  1. Если в конце кода php нет “exit;”
  2. Если перед генерацией файла нет очистки буфера вывода через “ob_end_clean();”
  3. Если используется генерация xlsx (Excel2007), но не установлены xml и xmlwriter модули PHP. Так же может выдаваться ошибка “Fatal error: Class ‘XMLWriter’ not found in /<path>/XMLWriter.php on line 37” т.к. библиотека PHPExcel использует наследование класса от XMLWriter (“class PHPExcel_Shared_XMLWriter extends XMLWriter”), т.е. требует установленного модуля xmlwriter. Для “нормальных” дистрибутивов это делается простым sudo apt-get/yum install php-xmlwriter (или php5-xml) и перезагрузкой apache, для gentoo это делается через пересборку всего php с новым модулем.

# php -m | grep xml libxml xml А должно быть: # php -m | grep xml libxml xml xmlreader xmlwriter

Отправка array на другую страницу

Чтобы функция по генерации xlsx была универсальной, сделал отдельную страницу по генерации, но на эту страницу потребовалось передать двумерный массив. В интернете гуляет два варианта решения: сохранить массив в сессии/куках, передать его через json (лучше) или serialize (хуже).

Через сессии все передавалось, только значение почему то не апдейтилось корректно. Сходу не разобрался в причинах, использовал сначала serialize (полный пример см. в function create_xls), но потом мигрировал на json из-за периодических проблем со спец. символами serialize.

SESSION session_start(); $_SESSION = $dataArray; SERIALIZE //$serialized = htmlspecialchars(serialize($dataArray)); $serialized = htmlspecialchars(json_encode($dataArray)); echo «<form action=create_xls.php method=\»post\»>»; echo «<input type=submit name=s_button value=\»Выгрузить\» id=\»submit\»>»; echo «<input type=\»hidden\» name=\»ArrayData\» value=\»$serialized\»/>»; echo «</form>»;

Кодировка

Если на сайте кодировка cp-1251, то при генерации обязательно нужно использовать iconv в utf-8. В противном случае вместо русских символов в ячейке будет бред (например, “ИСТИНА”).

Обработка и оформление отчетов в 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 и setCellValueByColumnAndRow.

  • setCellValue(pCoordinate, pValue, returnCell = false) принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр необязателен (если присвоить ему значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
  • setCellValueByColumnAndRow(pColumn, pRow, pValue = null, returnCell = false) принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue.

То есть можно обращаться к ячейкам двумя разными способами. Что является очень удобным.

Оформление отчета средствами 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.

Стили заливки
Пример указания настроек для заливки:
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() принимает один параметр со значением смещения по Y от верхнего края ячейки.
  • setWorksheet(PHPExcel_Worksheet $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 — формат данных ячейки.
Список возможных форматов
Пример настройки для формата данных ячейки:
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, pRow) – для обращения к ячейке по индексу;
  • getStyle(pCellCoordinate) – для обращения по координате ячейки.

Чтение данных из 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. А также были продемонстрированы почти все возможные методы для работы со стилями.


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

Ваш e-mail не будет опубликован. Обязательные поля помечены *