Php виведення даних з xls за датою. PHP – вивантаження даних в Excel із бази MySQL. Імпорт даних з PHP до Excel

Об'єктом. Але справи інакше, якщо необхідно сформувати звіт у форматі 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;

Імпорт даних з PHP до Excel

Розглянемо приклад формування таблиці множення.

Тут сформовані дані відразу "виплюнуться" у браузер. Однак, якщо вам потрібно зберегти файл, а не "викинути" його відразу, то не потрібно виводити HTTP-заголовкиі замість “php://output” слід вказати шлях до файлу. Пам'ятайте, що каталог, який передбачає створення файлу, повинен мати права на запис. Це стосується UNIX-подібних систем.

Розглянемо ще на прикладі три корисні інструкції:

• $sheet->getColumnDimension("A")->setWidth(40)- Встановлює стовпцю "A" ширину в 40 одиниць;
• $sheet->getColumnDimension("B")->setAutoSize(true)- тут у стовпця "B" буде встановлена ​​автоматична ширина;
• $sheet->getRowDimension(4)->setRowHeight(20)– встановлює четвертий рядок висоту рівну 20 одиницям.

Також зверніть увагу на такі необхідні для роботи зі звітом методи:

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

Як бачимо, вищенаведені методи є парними. Тому ми можемо працювати з осередками, використовуючи рядкове або числове подання координат. Що, звичайно ж, є додатковою перевагою в роботі.

Оформлення звіту засобами PHP в Excel

Дуже часто виникає потреба виділити у звіті деякі дані. Зробити виділення шрифту або застосувати рамку із заливкою фону для деяких осередків тощо. Що дозволяє сконцентруватися на найважливішій інформації (правда може і навпаки відволікти). Для цих цілей у бібліотеці PHPExcel є цілий набір стилів, які можна застосовувати до осередків Excel. Є звичайно у цій бібліотеці невеликий “мінус” – не можна застосувати стиль до кількох осередків одночасно, а лише до кожної індивідуально. Але це не створює дискомфорту під час розробки web-додатків.

Призначити стиль комірці можна двома способами:

• Застосувати метод applyFromArray, класу PHPExcel_Style. У метод applyFromArrayпередається масив з наступними параметрами:
  • fill- масив із параметрами заливки;
  • font- масив із параметрами шрифту;
  • borders- масив із параметрами рамки;
  • alignment- масив із параметрами вирівнювання;
  • numberformat- масив із параметрами формату представлення даних осередку;
  • protection- масив із параметрами захисту осередку.
• Використовувати метод класу PHPExcel_Styleдля кожного стилю окремо. Наприклад, призначити осередку шрифт можна так: $sheet->getStyle("A1")->getFont()->setName("Arial") .

Заливання

Значення параметра fillє масив з такими необов'язковими параметрами:

• type- Тип заливання;
• rotation- Кут градієнта;
• startcolor- Значення у вигляді масиву з параметром початкового кольору у форматі RGB;
• endcolor- Значення у вигляді масиву з параметром кінцевого кольору у форматі ARGB;
• color- значення масиву з параметром початкового кольору у форматі RGB.

Або можна використовувати такі методи:

$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, то якщо лист вже був призначений раніше - відбудеться його перезапис і відповідно зображення буде видалено.

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

Ось так виглядає звіт із вставленим зображенням:

Шрифт

fontвказується масив, який містить такі необов'язкові параметри:

• name- Ім'я шрифту;
• size- розмір шрифту;
• bold- Виділяти жирним;
• italic- Виділяти курсивом;
• underline- Стиль підкреслення;
• strike- перекреслити;
• superScript- надрядковий знак;
• subScript- Підрядковий знак;
• color

Або скористатися такими методами:

$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.

Також можна вдатися до використання наступних методів:

$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- Відступ від лівого краю.

$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- Формат даних осередку.

А можна і скористатися методом:

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

Захист осередків

Як значення параметра protectionвиступає масив, який містить два необов'язкові параметри:

• locked- захистити комірку;
• hidden- приховати формули.

Або використовувати такі методи:

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

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

Тепер ми знаємо, які є налаштування стилів і які параметри у кожного стилю. Зараз ми до осередків таблиці застосуємо стиль оформлення, але зробимо це двома методами. Перший метод полягає у створенні масиву налаштувань, який як параметр ми передамо в метод applyFromArray, класу PHPExcel_Style.

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

Зараз застосуємо той самий стиль, але використовуючи іншу методику.

Ось що в нас вийшло:

Якщо потрібно застосовувати стиль багаторазово, то краще підійде перший метод, в іншому випадку краще зупинитися на другому. Для отримання об'єкта (примірник класу PHPExcel_Style) комірки, що відповідає за стиль, необхідно використовувати один з наступних методів:

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

Додавання коментарів

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

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

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

Ось який коментар ми маємо отримати.

.
Спосіб, представлений у тій статті дійсно дуже простий, але не завжди може бути зручний.
Є безліч інших способів передати табличні дані з PHP до Excel, я опишу той, який видався мені найпростішим і функціональнішим. Потрібно особливо відзначити, що я не говорю про генерацію xls файлу, а лише пропоную користувачеві відкрити отримані дані за допомогою Excel так, що користувачі не досвідчені в програмуванні не помітять підробку.

Отже, перше що потрібно зробити - розмістити на нашій сторінці посилання на скрипт, що генерує такі заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv("UTF-8", "CP1251", $object->getFileName()) . "\";");
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . $object->getFileSize());

$object – сферичний об'єкт у вакуумі, який кожен читач реалізує так, як йому більше подобається. Призначення гетерів getFileName() та getFileSize() зрозуміло з назв. Тут варто виділити один неочевидний нюанс (дякую за те, що нагадав про це) - getFileName() звичайно може повертати будь-яке ім'я файлу, але якщо ви хочете що б браузер запропонував відкрити отриманий контент в Excel, то розширення файлу має бути xls.
Нічого нового я поки не розповів, все це придумано до мене, втім, як і те, що буде описано нижче.
Як правильно було зазначено у коментарях до нотатки , Excel набагато швидше працює з XML. Але найголовніша перевага, мабуть, все ж таки не в швидкості, а в набагато ширших можливостях. Заглиблюватися в нетрі я не стану, а лише наведу простий приклад і посилання на докладний опис усіх тегів.

Отже, після того, як заголовки згенеровані, нам потрібно віддати користувачеві власне дані. Я зазвичай загортаю генерацію таблиці в окремий метод:
echo $object->getContent();

А таблицю генерую за допомогою Smarty:

Як приклад я навів найпростішу таблицю, за бажання можна маніпулювати набагато більшим числом атрибутів. Це особливо приємно, якщо врахувати, що для реалізації не потрібні сторонні бібліотеки.
Описаний метод кілька років працює на одному проекті і жоден користувач досі не запідозрив, що дані, що їм відкриваються, не є документом MS Office.

Докладніше про структуру XML, що використовується в MS Excel, можна почитати в

У багатьох при роботі з PHP у зв'язці з MySQLвиникає така потреба, як експорт даних з бази у формат xls, щоб люди, яким потрібні ці дані, обробляли в Excel або просто користувачам було зручно дивитися ці дані. Нещодавно у мене виникла така потреба, і сьогодні я розповім, як цю справу можна реалізувати.

Відразу скажу, що цей спосіб є досить простим, але дані вивантажуються нормально.

Для початку наведу приклад кінцевого xls файлу, у програмі Excel вивантаження виглядатиме приблизно так:

Інакше кажучи, жодних картинок, стилів вивантажено нічого очікувати, лише заголовки стовпців і самі дані.

До того як я прийшов саме до такого варіанту вивантаження, я пробував вивантажувати у формат csv, але виходило трохи коряво, потім пробував малювати таблицю і зберігати її з розширенням xls, також виходило якесь нісенітниця, спосіб, який я зараз опишу, мене повністю влаштував. і зараз з Вами я їм поділюся.

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

Експорт даних з MySQL в Excel на PHP

А ще щоб Ви розуміли, які дані я вивантажую, наведу приклад простої таблиці у БД ( у мене її назва test):

Тестова таблиця:

Вся ідея тут полягає у функції pack()яка упаковує дані в бінарний рядок, а ми в свою чергу послідовно заповнюємо цей рядок даними, які ми вивантажили з бази даних MySql за допомогою звичайної функції mysql_query().

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

http://ваш_сайт/назва _файлу. php?id=2

І у Вас має вивантажитись два рядки з id рівним 2.

І тепер кожен, якому Ви дозволите вивантажувати дані, може легко експортувати їх на свій локальний комп'ютер через web інтерфейс. Цей спосіб зручний як для корпоративних користувачів, якщо Ви розробляєте програму для своєї організації, так і для користувачів Вашого веб-сайту в Інтернеті. Сподіваюся, цей спосіб Вам допоміг. Успіхів!