Генерація прайс-листа у форматі Excel за допомогою PHP. Phpexcel

Від автора: дуже часто при розробці веб-додатків постає завдання створити прайс-лист товарів або послуг якийсь кампанії у форматі таблиць Microsoft Excel. Але при цьому необхідно передбачити, щоб він вільно генерувався на основі даних, що зберігаються в базі даних.

У сьогоднішньому уроці ми з Вами вирішимо таку задачу. Тобто ми навчимося генерувати документ у форматі таблиць Excel за допомогою мови PHP і спеціальної бібліотеки PHPExcel. Це дуже потужна і дуже широко застосовувана бібліотека з величезним функціоналом. Звичайно, всі її можливості за один урок розглянути ми не зможемо, але дізнатися основи і навчитися з нею працювати — ми цілком встигнемо.

План уроку

1. Підготовка до уроку.

2. Установка бібліотеки PHPExcel.

3. Основні параметри аркуша книги Excel.

4. Наповнення документа даними.

5. Стилізація даних.

Деталі підручника

1. Підготовка до уроку.

Перше що нам потрібно для створення прайс-листа – це дані, які в ньому виводити. Для цього я підготував базу даних під назвою price), в якій міститься лише одна табличка – stuff. В табличці міститься кілька найменувань товару, для виведення в прайс-листі. Її дамп Ви зможете взяти з изходников до уроку.

Далі я підготував дуже простий скрипт, який витягає дані з бази даних, а конкретніше з таблички stuff, і формує з цих даних масив. З цим масивом ми з Вами і будемо працювати.

Скрипт включає в себе всього три файлу:

Важливе зауваження!

Якщо Ви хочете використовувати для виводу даних у форматі EXCEL російські символи, то обов’язково всі файли треба зберігати в кодуванні UTF8.

Далі давайте наведу код всіх трьох файлів. Файл config.php:

Як Ви бачите, тут все елементарно. Просто задаємо чотири константи для зберігання налаштувань підключення до бази даних. І власне, виконуємо підключення та вибір бази даних для роботи. Далі встановлюємо кодування запитів.

Наступний файл functions.php:

Тут також все дуже просто. Описана лише одна функція, яка виконує запит до бази даних і формує за результатами вибірки масив. Далі я наведу роздруківку цього масиву.

І останній файл index.php:

У цьому файлі ми далі будемо вести всі кодування. Це поки лише заготовка. Як Ви бачите, спочатку я підключаю файли config.php і functions.php і викликаю функцію get_price(), для отримання масиву товарів.

Давайте я наведу роздруківку цього масиву (за допомогою функції print_r()):

Тепер, думаю, Вам зрозуміло, що міститься в масиві $price_list. Тепер перейдемо до встановлення бібліотеки PHPExcel.

2. Установка бібліотеки PHPExcel.

Першим ділом бібліотеку необхідно завантажити. Для цього переходимо на офіційний сайт бібліотеки — http://phpexcel.codeplex.com/ :

І натискаємо кнопку download. В результаті скачування у нас вийде архів PHPExcel-1.7.7.zip. Давайте його распакуем. Як Ви бачите, після розпакування у нас вийшло кілька файлів і папок:

Файли — це різні описи за попередніми версіями, ліцензійна угода і дуже коротка інструкція з встановлення. Далі в папці Classes, міститься безпосередньо сама бібліотека PHPExcel – цю папку необхідно скопіювати в корінь нашого скрипта, що я і зробив.

У папці Documentation – міститься документація по бібліотеці, але правда вона на англійській мові. Але все одно я Вам рекомендую з нею ознайомитися. Так як бібліотека має величезний функціонал, і, звичайно, сьогодні ми весь його не розглянемо. А в документації Ви знайдете багато всього цікавого.

У папці Tests – містяться приклади використання бібліотеки.

Отже, якщо Ви скопіювали папку Classes в корінь скрипта, то ми можемо приступати до подальшої роботи.

Отже, переходимо в файл index.php і починаємо працювати з ним. Тепер нам необхідно підключити головний файл бібліотеки PHPExcel.php (який знаходиться в папці Classes) до нашого скрипта і створити об’єкт класу PHPExcel:

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

Для тих хто не знає клас – це свого роду креслення або описовий елемент, який певної сутності. Тобто в нашому випадку (якщо сказати простими словами) клас PHPExcel.php описує всі методи (це звичайні функції, описані тільки у класі) і властивості (змінні класу) необхідні для виведення даних у форматі Excel.

Для того що б створити об’єкт класу необхідно вказати змінну ($objPHPExcel) потім поставити знак присвоєння, далі ключове слово new і вказати ім’я класу. Об’єкт – це такий тип змінної, який дозволяє нам працювати з методами і властивостями класу. Тобто якщо ми створили об’єкт, то ми отримали доступ до усього, що описано в класі.

На цьому установка бібліотеки завершена, можна приступати до роботи з нею.

3. Основні параметри аркуша книги Excel.

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

Отже, насамперед необхідно вибрати активний аркуш, на який ми будемо виводити дані і отримати об’єкт цього листа.

$objPHPExcel->setActiveSheetIndex(0);
$active_sheet = $objPHPExcel->getActiveSheet();

За допомогою методу setActiveSheetIndex(0) – вказуємо індекс (номер) активного листа. Нумерація аркушів починається з нуля. Далі за допомогою методу getActiveSheet() – отримуємо об’єкт цього активного листа, тобто іншими словами отримуємо доступ до нього для роботи. І зберігаємо цей об’єкт у змінну $active_sheet.

Якщо Ви захочете вказати активним якийсь інший лист, то спочатку його потрібно створити, за допомогою методу:

$objPHPExcel->createSheet();

Потім за аналогією, вказуємо індекс і отримуємо об’єкт активного листа.

Тепер давайте згенеруємо наш перший документ Excel (щоправда звичайно поки порожній), для цього в файл index.php в його кінець додамо наступний код (далі весь код, який я буду наводити, слід вставляти перед цим кодом – тобто код який йде нижче повинен бути в самому низу файлу – це вже завершення скрипта!!!):

header(“Content-Type:application/vnd.ms-excel”);
header(“Content-Disposition:attachment;filename=’simple.xls'”);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
$objWriter->save(‘php://output’);
exit();

На початку, зверніть увагу, відправляємо заголовки.

Перший – вказуємо браузеру тип відкритого контенту – це документ формату Excel. І другий – вказуємо браузеру, що документ необхідно віддати користувачеві на скачування і тут же вказуємо ім’я файлу, що завантажується (‘simple.xls’ ).

Далі документ необхідно зберегти. Спочатку потрібно вказати, в якій версії ми зберігаємо його. Для цього в класу PHPExcel_IOFactory викликаємо метод createWriter() і передаємо йому об’єкт $objPHPExcel і вказуємо версію Excel5. При цьому буде створений об’єкт класу PHPExcel_Writer_Excel5 і у нього викликаємо метод save(). Цим методом передаємо параметром рядок ‘php://output’, це означає, що файл не збережено, а буде відданий браузеру, а браузер в свою чергу, віддасть його нам на скачування.

Тепер трохи про версіях. Крім версії Excel5, доступна також версія Excel2007, але так як у мене встановлений MS Office 2003, версія Excel2007 у мене просто не відкриється. Тому я створюю документ версії Excel5.

Тепер давайте перейдемо в браузер і подивимося, що у нас вийшло:

Як Ви бачите, ми з Вами отримали порожній документ у форматі Excel, з одним активним листом.

Тепер давайте пропишемо інші налаштування аркуша (зверніть увагу, що всі методи ми викликаємо у об’єкта $active_sheet – об’єкта активного аркуша документа і весь код вставляємо перед заголовками – функція header):

//Орієнтація сторінки і розмір аркуша
$active_sheet->getPageSetup()
->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$active_sheet->getPageSetup()
->SetPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//Поля документа
$active_sheet->getPageMargins()->setTop(1);
$active_sheet->getPageMargins()->setRight(0.75);
$active_sheet->getPageMargins()->setLeft(0.75);
$active_sheet->getPageMargins()->setBottom(1);
//Назва аркуша
$active_sheet->setTitle(“Прайс-лист”);
//Шапа і футер
$active_sheet->getHeaderFooter()->setOddHeader(“&СШапка нашого прайс-листа”);
$active_sheet->getHeaderFooter()->setOddFooter(‘&L&B’.$active_sheet->getTitle().’&RСтраница &P &N’);
//Налаштування шрифту
$objPHPExcel->getDefaultStyle()->getFont()->setName(‘Arial’);
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);

Отже, спочатку запитуємо орієнтацію аркуша, за допомогою методу setOrientation(), якому передаємо константу, ORIENTATION_PORTRAIT, класу PHPExcel_Worksheet_PageSetup. Ця константа — означає, що орієнтація сторінки буде портретом.

Якщо Ви хочете розташувати аркуш у вигляді альбомного аркуша, потрібно методу setOrientation(), передати константу Worksheet_PageSetup::ORIENTATION_LANDSCAPE.

Зверніть увагу, що перед методом setOrientation() необхідно викликати метод getPageSetup() – який надасть нам доступ до налаштувань сторінки (поверне об’єкт класу, який відповідає за налаштування сторінки).

Далі викликаємо метод SetPaperSize(), який дозволяє задати розмір сторінки для друку. Йому передаємо параметром константу PAPERSIZE_A4, класу — PHPExcel_Worksheet_PageSetup. Що означає, що розмір листа сторінки буде встановлено А4.

Це можна перевірити, якщо відкрити документ у форматі Excel і перейти в меню Файл, потім на пункт параметри сторінки.

Далі встановлюємо поля документа, тобто відступи від країв документа. Відступи мають спеціальних символьних одиницях. Спочатку, зверніть увагу, викликаємо у об’єкта $active_sheet, метод getPageMargins() (він поверне об’єкт класу, який відповідає за налаштування полів сторінки). Потім викликаємо метод setTop(1), який визначає верхній відступ сторінки.

Далі за аналогією методи setRight(0.75), setLeft(0.75), setBottom(1) – задають відповідно правий, лівий і нижній відступи документа.

Далі давайте за допомогою методу setTitle(«Прайс-лист») – задамо назва нашого листа.

Потім при друку (якщо комусь це потрібно), можна виводити шапку і нижній футер листа. Таку можливість можна задати за допомогою методів setOddHeader(“&СШапка нашого прайс-листа”) і setOddFooter(‘&L&B’.$active_sheet->getTitle().’&RСтраница &P &N’), які задають відповідно — шапку і футер документа.

Зверніть увагу на передані параметри:

Для шапки – передаємо рядок — &C Шапка нашого прайс-листа (мітка &C – означає, що текст потрібно розташувати по центру).

Для футера – передаємо рядок — &L&B’.$active_sheet->getTitle().’&RСтраница &P &N’, це означає, що потрібно вивести зліва і жирним шрифтом (&L&B’), назва аркуша (метод $active_sheet->getTitle()), потім праворуч (&R) вивести номер сторінки (Сторінка &P) із загальної кількості сторінок (&N).

Повний список міток, можна подивитися в документації. Я цей список Вам не наводжу, так як він дуже великий, та й багато хто з них Вам не знадобляться.

Потім вказуємо параметри шрифту за замовчуванням. Це можна зробити за допомогою послідовного виклику методів:

Тепер якщо зберегти зміни і згенерувати наш документ, то можна побачити що у нас змінилося назва аркуша документа на Прайс-лист, і якщо відкрити попередній перегляд сторінки, то можна побачити як виводиться шапка і футер документа.

4. Наповнення даними документа

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

$active_sheet->getColumnDimension(‘A’)->setWidth(7);
$active_sheet->getColumnDimension(‘B’)->setWidth(80);
$active_sheet->getColumnDimension(‘C’)->setWidth(10);
$active_sheet->getColumnDimension(‘D’)->setWidth(10);

Як ви бачите спочатку звертаємося до нашого об’єкту активного аркуша, далі викликаємо метод getColumnDimension(‘A’) – тобто отримуємо доступ ширини стовпця А (або будь-якого іншого стовпця, якою ми передамо методом параметром), потім задаємо ширину, за допомогою методу setWidth(ширина стовпця) і передаємо йому ширину в символьних одиницях.

Для інших стовпців – все аналогічно.

Давайте подивимося, що вийшло:

Як Ви бачите, розміри стовпців змінилися. Тепер давайте заповнимо кілька осередків текстом:

$active_sheet->mergeCells (“A1:D1′);
$active_sheet->getRowDimension(‘1’)->setRowHeight(40);
$active_sheet->setCellValue(‘A1′,’Техно світ’);
$active_sheet->mergeCells(‘A2:D2’);
$active_sheet->setCellValue(‘A2′,’Компьютеы та комплектуючі на будь-який смак і колір’);
$active_sheet->mergeCells(‘A4:C4’);
$active_sheet->setCellValue(‘A4′,’Дата створення прайс-листа’);

На початку давайте об’єднаємо клітинки А1 до D1, за допомогою методу mergeCells(діапазон комірок) – що дозволяє об’єднувати комірки.

Далі задаємо висоту цих об’єднаних клітинок (рядок 1), за допомогою методів getRowDimension(‘1’)->setRowHeight(40) – дивіться, спочатку отримуємо доступ до рядку 1 (getRowDimension(‘1’), а потім задаємо висоту (setRowHeight(висота рядка)).
І потім за допомогою методу setCellValue(‘A1′,’Техно світ’), встановлюємо значення комірки А1. Цей метод дозволяє записати дані в будь-яку клітинку. Координати комірки передаємо першим параметром, а значення – другим.

Такі осередки заповнюємо за аналогією. Давайте подивимося, що у нас вийшло:

Далі давайте в клітинку D4 запишемо поточну дату:

//Записуємо дані в клітинку
$date = date(‘d-my’);
$active_sheet->setCellValue(‘D4’,$date);
//Встановлює формат даних у клітинці – дата
$active_sheet->getStyle(‘D4’)
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);

Для цього запишемо поточну дату в клітинку D4, а потім встановимо формат даних комірки – дата. Для цього необхідно послідовно викликати методи об’єкту $active_sheet:

getStyle(‘D4’)->getNumberFormat()->
setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);

Тобто отримуємо доступ до стилів комірки D4 ( метод getStyle(‘D4’) поверне об’єкт класу відповідає за стилі комірки), потім отримуємо формат даних комірки і встановлюємо новий формат даних (setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14)).

У константі FORMAT_DATE_XLSX14 зберігається рядок з форматом дати (виду дд-мм-рррр). Всі константи, що відносяться до форматів дати можна подивитися в документації по бібліотеці.

Тепер, використовуючи метод setCellValue(), а також цикл foreach() давайте наповнимо даними наш прайс-лист:

//Створюємо шапку таблички даних
$active_sheet->setCellValue(‘A6′,’№п. п’);
$active_sheet->setCellValue(‘B6′,’Назва’);
$active_sheet->setCellValue(‘C6′,’Ціна’);
$active_sheet->setCellValue(‘D6′,’кількість’);
//У циклі проходимся по елементам масиву і виводимо все у відповідні комірки
$row_start = 7;
$i = 0;
foreach($price_list as $item) {
$row_next = $row_start + $i;
$active_sheet->setCellValue(‘A’.$row_next,$item[‘id’]);
$active_sheet->setCellValue(‘B’.$row_next,$item[‘name’]);
$active_sheet->setCellValue(‘C’.$row_next,$item[‘price’]);
$active_sheet->setCellValue(‘D’.$row_next,$item[‘quantity’]);
$i++;
}

Тепер давайте збережемо зміни і подивимося, що вийшло:

Як Ви бачите, дані успішно вивелися.

5. Стилізація даних.

Тепер давайте красиво прикрасимо наш прайс-лист, тобто кожній клітинці додамо стилів. Для цього необхідно створити масив зі стилями (стилі дуже схожі на правила CSS), і за допомогою методу applyFromArray($масив стилів), застосувати цей масив до осередку:

//масив стилів
$style_wrap = array(
//рамки
‘borders’=>array(
//зовнішня рамка
‘outline’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_THICK
),
//внутрішня
‘allborders’=>array(
‘style’=>PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’=>’696969′
)
)
)
);
//застосовуємо масив стилів до клітинок
$active_sheet->getStyle (“A1:D’.($i+6))->applyFromArray($style_wrap);

Зверніть увагу, як необхідно оформляти масив стилів. Спочатку йде головний масив з ключем – головне правило (наприклад borders – рамки), а в ньому ще масиви з правилами. Стилі для кожного правила (для кожного виду рамки) – це константи. Приміром, BORDER_THICK – це жирна рамка, BORDER_THIN – це тоненька рамка.

Після створення масиву викликаємо методи getStyle() – для отримання об’єкту класу, що відповідає за стилі вибраної комірки, а потім метод applyFromArray($style_wrap), який застосовує масив стилів до осередку.

Зверніть увагу, як я вказав діапазон клітинок в методі getStyle (“A1:D’.($i+6)). Змінна $i – це число ітерацій циклу, який виводить дані з масиву. А число 6 – це кількість рядків до висновку даних з циклу. Давайте перейдемо в браузер і подивимося, що вийшло:

Як Ви бачите, стилі успішно застосувались. Тепер давайте за аналогією, можна застосувати стилі до решти клітинок:

//Стилі для верхньої написи рядок 1
$style_header = array(
//Шрифт
‘font’=>array(
‘bold’ => true,
‘name’ => ‘Times New Roman’,
‘size’ => 20
),
//Вирівнювання
‘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’ => ‘CFCFCF’
)
)
);
$active_sheet->getStyle (“A1:D1′)->applyFromArray($style_header);
//Стилі для слогана компанії – друга рядок
$style_slogan = array(
//шрифт
‘font’=>array(
‘bold’ => true,
‘italic’ => true,
‘name’ => ‘Times New Roman’,
‘size’ => 13,
‘color’=>array(
‘rgb’ => ‘8B8989’
)
),
//вирівнювання
‘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’ => ‘CFCFCF’
)
),
//рамки
‘borders’ => array(
‘bottom’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_THICK
)
)
);
$active_sheet->getStyle(‘A2:D2’)->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’ => ‘CFCFCF’
)
),
//рамки
‘borders’ => array(
‘right’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_NONE
)
)
);
$active_sheet->getStyle(‘A4:C4’)->applyFromArray($style_tdate);
//Стилі дати
$style_date = array(
//заповнення кольором
‘fill’ => array(
‘type’ => PHPExcel_STYLE_FILL::FILL_SOLID,
‘color’=>array(
‘rgb’ => ‘CFCFCF’
)
),
//рамки
‘borders’ => array(
‘left’ => array(
‘style’=>PHPExcel_Style_Border::BORDER_NONE
)
),
);
$active_sheet->getStyle(‘D4’)->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
),
);
$active_sheet->getStyle(‘A6:D6’)->applyFromArray($style_hprice);
//стилі даних у таблиці прайс-листа
$style_price = array(
‘alignment’ => array(
‘horizontal’ => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_LEFT,
)
);
$active_sheet->getStyle(‘A7:D’.($i+6))->applyFromArray($style_price);

Тепер давайте подивимося на остаточний результат нашої роботи:

Ось так виглядає остаточний варіант нашого прайс-листа. Погодьтеся дуже гідний вигляд. Притому, що ми не витратили на його створення дуже багато часу. Ще раз Вам рекомендую переглянути документацію по бібліотеці PHPExcel, там міститься дуже багато всього цікавого.

На цьому у мене все. Вдалого Вам кодування і до нових зустрічей!