—»     —»   Создаем полноценные таблицы в Excel из базы MySQL
  Раздел: Базы данных, Руководства   Комментариев: 7  

Создаем полноценные таблицы в Excel из базы MySQL



Детали руководства

* Язык разработки: PHP 5.3+
* Охваченные темы: MySQL, PHP, PHPExcel, PDO
* Уровень сложности: Средний
* Время на завершение: 1 час
* Прикрепленные файлы

Файла формата CSV (Comma Separated Value или «Значения, разделенные запятыми») обычно достаточно для экспорта данных MySQL в таблицу Excel. Тем не менее, в фалах CSV присутствуют только данные. В реальной таблице Excel имеется форматирование, формулы и, порой, даже графические элементы. Т.е. здесь разница равна различиям между обычной передачей данных и профессиональным отчетом.

Данное руководство расскажет вам о том, как воспользоваться PHP-компонентами (которые распространяются с открытым исходным кодом) для создания настоящих таблиц Excel из указателей MySQL SELECT. Интересно? Тогда давайте приступим!

Обзор

PHPExcel – это набор классов PHP, которые позволяют нам считывать и записывать различные форматы файлов таблиц, а также управлять ими. Перед тем как начать, вам понадобится дистрибутив PHPExcel. Последнюю версию компонента PHPExcel можно скачать здесь http://www.phpexcel.net.

Основное взаимодействие между таблицами данных и электронными таблицами

Значение SQL SELECT возвращает нам набор результатов, а точнее, подборку колонок с именами, в ячейках которых содержатся данные. В PHP/MySQL каждая ячейка результата может быть (и зачастую так и бывает) представлена в виде ассоциативного массива, в котором ключ к массиву – это название колонки.

Электронная таблица Excel – это некоторое число страниц, в которых можно видеть пронумерованные строки и упорядоченные по алфавиту колонки.

Метод заключается в том, чтобы одно значение SQL было задано одной рабочей странице, чего можно добиться за счет совпадения имени колонки в ассоциативном массиве, который был возвращен командой извлечения PDO, и буквы колонки в электронной таблице.

Этап 1: учимся правильно выставлять наименования колонок по алфавиту

Ключевой момент данной техники заключается в алгоритме называния колонок, который позволяет вам правильно называть колонки в алфавитном порядке. Как было указано ранее, в электронных таблицах Excel колонки идентифицируются буквами, а строчки определяются числами. Если вас запрос содержит менее 26 колонок, вы можете просто остановиться на букве Z. Тем не менее, многие запросы содержат гораздо больше колонок.

Схема наименования колонок в Excel

Когда названия колонок доходят до буквы Z, к имени начинает добавляться еще одна буква, т.е.: A… Z,AA, AB … AZ. После AZ пойдет BA (как можно видеть ниже).

Создаем полноценные таблицы в Excel из базы MySQL

Создаем полноценные таблицы в Excel из базы MySQL

Алгоритм наименования колонок

Следующий код PHP показывает нам, как правильно называть колонки от А до ZZ.

$keys = array_keys($row); // Get the Column Names
$min = ord("A"); // ord returns the ASCII value of the first character of string.
$max = $min + count($keys);
$firstChar = ""; // Initialize the First Character
$abc = $min; // Initialize our alphabetical counter
for($j = $min; $j <= $max; ++$j)
{
$col = $firstChar.chr($abc); // This is the Column Label.
$last_char = substr($col, -1);
if ($last_char> "Z") // At the end of the alphabet. Time to Increment the first column letter.
{
$abc = $min; // Start Over
if ($firstChar == "") // Deal with the first time.
$firstChar = "A";
else
{
$fchrOrd = ord($firstChar);// Get the value of the first character
$fchrOrd++; // Move to the next one.
$firstChar = chr($fchrOrd); // Reset the first character.
}
$col = $firstChar.chr($abc); // This is the column identifier
}
/*
Use the $col here.
*/

$abc++; // Move on to the next letter
}

Данный алгоритм не пойдет далее ZZ. Алгоритм приводится в исполнение (немного в другом виде) с помощью дополнительного файла в пользовательской функции MySqlExcelBuilder::mapColumns.

Этап 2: Форматирование и тестирование SQL-значений

Простая схема

Представленная ниже схема отображает нам упрощенное взаимодействие клиент/заказ. Значения SQL CREATE для нижеприведенной схемы включены в заархивированный в zip-формат файл xls_sample.sql, который приложен к руководству.

Создаем полноценные таблицы в Excel из базы MySQL

«быстрое и грязное» значение SELECT

Обычно здесь можно просто быстро извлечь данные из значения SQL в таблицах:

SELECT * FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
AND item_id = `order_item`.`id`

Главное преимущество подобного метода заключается в экономии времени. Хотя результаты редко выглядят приемлемо.

PHPMyAdmin – это инструмент для администрирования баз данных MySQL, который зачастую предоставляется вместе с планом хостинга. Для структурирования и тестирования ваших страниц с электронными таблицами, вы можете использовать различные SQL-инструменты, которые идут в комплекте с PHPMyAdmin.

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

Создаем полноценные таблицы в Excel из базы MySQL

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

Создаем полноценные таблицы в Excel из базы MySQL

Имена колонок написаны с маленькой буквы, и вместо пробелов мы видим нижнее подчеркивание.

Получаем более привлекательный результат из значения SELECT

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

SELECT `name` AS `Customer Name`,
`email_address` AS `Email Address`,
CONCAT( right(`phone_number`,3) , '-' , mid(`phone_number`,4,3) , '-', right(`phone_number`,4)) AS `Phone Number`,
`item_sku` AS `Part Number`,
`item_name` AS `Item Name`,
`price` AS `Price`,
`order_date` as `Order Date`
FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
AND item_id = `order_item`.`id`

Что в результате дает:

Создаем полноценные таблицы в Excel из базы MySQL

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

Этап 3: Отображение результатов MySQL на странице электронной таблицы

Класс MySqlExcelBuilder включает в себя функционал, требующийся для того, чтобы добавить значения SQL в страницу электронной таблицы Excel при помощи PDO и PHPExcel. Полный класс находится в сопутствующем zip-файле.

Класс MySqlExcelBuilder

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

<?
class MySqlExcelBuilder
{
protected $pdo; // PHP Data Object
public $phpExcel; // PHP Excel
protected $sql_pages = array(); //Sheet Name, Sql Statement, Options

* $pdo – это объект данных в PHP, использующийся для создания запроса к базе данных.
* $phpExcel – это объект PHPExcel, использующийся для создания и управления электронной таблицей.
* $sql_pages - удерживает SQL-значение и информацию об имени и форматировании страницы.
* конструктор (не отображен) инициализирует данные из PDO и PHPExcel.

Подготовка каждой страницы

Нижеприведенное изображение электронной таблицы – это прототип, созданный в Excel для того, чтобы показать нам, как должна выглядеть электронная таблица.

Создаем полноценные таблицы в Excel из базы MySQL

Функция add_page используется для добавления значения SQL в страницы с именем:

public function add_page($wsName,$sql,$total_colums=null,$start_col="A",$start_row="1")
{
// $wsName, is the Work Sheet Name that will be shown on the tab at the bottom of the spreadhseet
$this-&gt;sql_pages[$wsName]['Sql'] = $sql; // This is the statement to be executed
$this-&gt;sql_pages[$wsName]['Col'] = $start_col; // This is the column to start putting data into.
// Note that it must be between "A" and "Z", staring in Column "AA" and after is not supported.
$this-&gt;sql_pages[$wsName]['Row'] = $start_row; // This the row number to start putting data into
$this-&gt;sql_pages[$wsName]['Totals'] = $total_colums; // This is a comma delimted list of Column Names (NOT Column Labels) that will be totaled.
//If null it will be ignored.

}

sql_pages удерживает информацию, которую мы хотим использовать для занесения sql на страницы.

Пример использования пользовательской функции

Данный код является примером того, как следует использовать пользовательскую функцию add_page:

$xls_sql = new MySqlExcelBuilder('database','username','password');
$sql_statement = &lt;&lt;&lt;END_OF_SQL

SELECT `name` AS `Customer Name`,
`email_address` AS `Email Address`,
CONCAT( right(`phone_number`,3) , '-' , mid(`phone_number`,4,3) , '-', right(`phone_number`,4)) AS `Phone Number`,
`item_sku` AS `Part Number`,
`item_name` AS `Item Name`,
`price` AS `Price`,
`order_date` as `Order Date`
FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
AND item_id = `order_item`.`id`
AND `item_sku` = 'GMG1'

END_OF_SQL;

$xls_sql->add_page('Gold Mugs',$sql_statement,'Price');

Нижеприведенное изображение отображает нам, как пользовательская функция add_page должна быть определена на электронной таблице.

Создаем полноценные таблицы в Excel из базы MySQL

Этап 4: Разработка электронной таблицы

Понимание PHPExcel

Если вы понимаете, как управлять электронной таблицей Excel посредством мыши и клавиатуры, то вы наверняка быстро освоите PHPExcel. PHPExcel основан на принципе управления моделью электронной таблицы при использовании команд, схожих с командами, которые вы присваиваете в Excel. В документации для разработчиков по PHPExcel все расписано более подробно.

Пользовательская функция getExcel()

Пользовательская функция getExcel() использует PHPExcel для создания отдельных рабочих таблиц из SQL-значений, которые вы определяли в 3 этапе. После того, как будут созданы рабочие таблицы, объект PHPExcel возвращается к запрашиваемому. Ниже можно видеть описание 4 основных отделов пользовательской функции getExcel.

А. Повторение на страницах

Основной цикл данной пользовательской функции повторяется на страницах, которые ранее были добавлены посредством add_page. В каждом повторении, в объекте phpExcel создается последующая страница, а затем добавляются данные. Пользовательская функция createSheet в PHPExcel используется для создания новых рабочих таблиц для каждой ранее добавленной страницы.

public function getExcel()
{
$i = 0;
foreach($this->sql_pages as $wsName=>$page)
{
$start_of_page = true;
$sql = $page['Sql'];
$start_col = $page['Col'];
$start_row = $page['Row'];
$this->phpExcel->createSheet();
$sheet = $this->phpExcel->setActiveSheetIndex($i);

if ($sh = $this->pdo->query($sql))
{

Изображение, приведенное ниже, отображает нам процесс взаимодействия кода с таблицей. column_map обсуждается далее в руководстве.

Создаем полноценные таблицы в Excel из базы MySQL

Б. Логика «Начало страницы»

В начале каждой страницы есть специальное форматирование. Сначала для каждой конкретной страницы из базы данных извлекается строка, которая выполняет задачи, нужные для произведения форматирования в начале страницы. Она вовлекает пользовательскую функцию mapColumns, которую мы обсуждали в этапе №1. PHPExcel, как и Excel, использует пару БукваЧисло для определения конкретной ячейки. В MySqlExcelBuilder это рассматривается в виде ключа ячейки (cellKey). Ключ ячейки создается за счет соединения заголовка столбика с числом строчки.

$rowNum = $start_row;
while($row = $sh->fetch(PDO::FETCH_ASSOC))
{
$keys = array_keys($row); // Get the Column Names
if ($start_of_page) // Initialize the Page
{
$this->mapColumns($wsName,$keys,$start_col);
foreach($keys as $key)
{
$col = $this->column_map[$wsName]['xls'][$key];
$cellKey = $col.$rowNum;
$sheet->setCellValue($cellKey,$key);
// The next two lines are for formatting your header
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);
$sheet->getColumnDimension($col)->setAutoSize(true);
}
$rowNum++; // The next row is for data
$start_of_page = false; // Done with Intialization
}

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

* setCellValue – вносит актуальное значение в поле. Учтите, что ячейка является частью рабочей таблицы. Определенная ячейка определяется переменной cellKey.
* getStyle – данный параметр возвращает данные для атрибута стиля конкретной ячейки, поэтому данным значением можно управлять.
* getColumnDimension – это метод объекта рабочей таблицы. Ширина колонки привязана к переменной col.

В. Заполнение данными

Благодаря подготовительным работам и указанию колонок процесс, в действительности, теперь будет заполнять соответствующую ячейку соответствующими данными. Мы рассматриваем рабочую таблицу на наличие определенной колонки данных, создаем ключ ячейки, а затем вносим значение в ячейку.

foreach($keys as $key) // Put the value of the data into each cell
{
$col = $this->column_map[$wsName]['xls'][$key]; // Get the appropriate column
$cellKey = $col.$rowNum; // Build the column key
$val = $row[$key]; // Get the data value
$sheet->setCellValue($cellKey,$val); // Put it in the cell.
}
$rowNum++;

Г. Добавляем формулы

Последняя часть getExcel() показывает нам, как в рабочую таблицу PHPExcel добвалять формулы. В данном случае, здесь все относится к колонке. PHPExcel вносит формулы в ячейки точно также, как вы бы вносили их в рабочую таблицу в Excel. Значение ячеек начинается со знака равенства (=), а затем следует формула. В данном случае, у нас идет сумма нескольких ячеек. Смотрим дальше:

Создаем полноценные таблицы в Excel из базы MySQL

А вот так выглядит код:

$col = $this->column_map[$wsName]['xls'][$key];
// Add the Total Label
$cellLabelKey = $col.$rowNum;
$total_label = "Total $key";
$sheet->setCellValue($cellLabelKey,$total_label);
$style = $sheet->getStyle($cellLabelKey);
$style->getFont()->setBold(true);

// Add the actual totals
$total_row = $rowNum+1;
$cellKey = $col.$total_row;
$startTotal = $col.$start_row;
$endTotal = $col.$this->sql_pages[$wsName]['lastDataRow'];
$total_forumla = "=SUM($startTotal:$endTotal)";
$sheet->setCellValue($cellKey,$total_forumla);
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);

Этап 5: Вносим последние штрихи

После того как вы уже получили рабочую таблицу Excel, заполненную посредством базы данных MySQL и getExcel, пришло время внести финальные штрихи. В нашем примере мы задали заголовок каждой рабочей таблице.

// Get the spreadsheet after the SQL statements are built...
$phpExcel = $mysql_xls->getExcel(); // This needs to come after all the pages have been added.

$phpExcel->setActiveSheetIndex(0); // Set the sheet to the first page.
// Do some addtional formatting using PHPExcel
$sheet = $phpExcel->getActiveSheet();
$date = date('Y-m-d');
$cellKey = "A1";
$sheet->setCellValue($cellKey,"Gold Mugs Sold as Of $date");
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);

$phpExcel->setActiveSheetIndex(1); // Set the sheet to the second page.
$sheet = $phpExcel->getActiveSheet();
$sheet->setCellValue($cellKey,"Tea Sold as Of $date");
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);

$phpExcel->setActiveSheetIndex(0); // Set the sheet back to the first page, so the first page is what the user sees.

Этап 6: Сохранение файла

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

// Write the spreadsheet file...
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5'); // 'Excel5' is the oldest format and can be read by old programs.
$fname = "TestFile.xls";
$objWriter->save($fname);

// Make it available for download.
echo "&lt;a href="$fname"&gt;Download $fname&lt;/a&gt;";

Итог

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

Создаем полноценные таблицы в Excel из базы MySQL

Вывод

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

Класс MySqlExcelBuilder, например, может быть расширен для использования функций PHPExcel, чтобы заполнить существующий шаблон электронной таблицы данными из базы MySQL. Так как PDO является интерфейсом базы данных, DSN в конструкторе MySqlExcelBuilder может быть без проблем изменен для работы с другими базами данных.

Спасибо за то, что были с нами!
Внимание! У вас нет прав для просмотра скрытого текста.
Обнаружили ошибку или мёртвую ссылку?
Выделите проблемный фрагмент мышкой и нажмите CTRL+ENTER.
В появившемся окне опишите проблему и отправьте уведомление Администрации ресурса.
Нужна органическая вечная ссылка из данной статьи? Постовой?
Подробности здесь
Вам понравился материал? Поблагодарить легко!
Будем весьма признательны, если поделитесь этой статьей в социальных сетях:

Ключевые тэги: MySQL, Excel, PHP
Опубликовал Design FactoRy   Прочитано (раз): 23220   |   Оставлено комментариев: 7
Источник материала / оригинал статьи   Распечатать
Другие статьи и новости по теме:
Комментарий #1: 6 января 2012 @ 15:11
Написал: Vitalij — группа: Гости  
На сайте с: --   |   Публикаций: 0   |   Комментариев: 0
ICQ: --- не указано ---
Хорошая, практичная статейка :)
Комментарий #2: 6 января 2012 @ 16:22
Написал: Design FactoRy — группа: Администраторы  
На сайте с: 21.07.2009   |   Публикаций: 3300   |   Комментариев: 500
ICQ: --- не указано ---
Vitalij, стараемся быть практичными :)
Комментарий #3: 6 января 2012 @ 19:57
Написал: Влад — группа: Гости  
На сайте с: --   |   Публикаций: 0   |   Комментариев: 0
ICQ: --- не указано ---
Отличный материал продолжайте в томже духе
Комментарий #4: 6 января 2012 @ 21:56
Написал: Design FactoRy — группа: Администраторы  
На сайте с: 21.07.2009   |   Публикаций: 3300   |   Комментариев: 500
ICQ: --- не указано ---
Влад, слушаюсь и повинуюсь :)
Комментарий #5: 21 января 2012 @ 22:41
Написал: mrBoombastixx — группа: Гости  
На сайте с: --   |   Публикаций: 0   |   Комментариев: 0
ICQ: --- не указано ---
Спасибо!
Комментарий #6: 12 февраля 2012 @ 20:07
Написал: RisiDEN — группа: Гости  
На сайте с: --   |   Публикаций: 0   |   Комментариев: 0
ICQ: --- не указано ---
Спасибо за хороший урок по выгрузке в excel данных.
У меня вопрос:
На английском всё выгружается замечательно, но вот русскую кодировку он не воспринимает (не cp1251, не utf8)...выводит в виде восклицательных знаков.

В чём может быть подвох в этой ситуации.
(В index вставлял "set name", но думаю копать надо глубже)
Комментарий #7: 1 января 2017 @ 17:31
Написал: сергей — группа: Гости  
На сайте с: --   |   Публикаций: 0   |   Комментариев: 0
ICQ: --- не указано ---
а может ли кто за вознаграждение для чайника вывести из базы данных несколько колонок в ексел или даже в CSV файлик.
Мне надо из базы данных фотогалереи gallery2 вывести названия и описания фоток в XML таблицу . пишите кто сделает a-aniskin@mail.ru
Добавление комментария
Уважаемые пользователи!
При добавлении комментариев на сайт Вам следует учитывать следующее - все комментарии проверяются Администрацией на предмет отсутствия спама. При обнаружении признаков спама, в оставленном Вами комментарии, сам комментарий будет незамедлительно удалён, а Ваш IP-адрес будет забанен без предупреждения! Учётные записи пользователей, рассылающих спам, блокируются/удаляются без права последующего восстановления.

С уважением, Администрация сайта.
* = поля обязательны к заполнению
Полужирный Наклонный текст Подчеркнутый текст Зачеркнутый текст | Выравнивание по левому краю По центру Выравнивание по правому краю | Вставка смайликов Выбор цвета | Скрытый текст Вставка цитаты Преобразовать выбранный текст из транслитерации в кириллицу Вставка спойлера
Вопрос : Национальная денежная единица России
Подтверждение кода безопасности :

Включите эту картинку для отображения кода безопасности
обновить, если не виден код


Популярные публикации


















Свежие шаблоны сайтов каждый день
С миру по нитке
«    Август 2017    »
ПнВтСрЧтПтСбВс
 123456
78910111213
14151617181920
21222324252627
28293031