* Язык разработки: 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 (как можно видеть ниже).
Алгоритм наименования колонок
Следующий код 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, который приложен к руководству.
«быстрое и грязное» значение SELECT
Обычно здесь можно просто быстро извлечь данные из значения SQL в таблицах:
SELECT * FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
AND item_id = `order_item`.`id`Главное преимущество подобного метода заключается в экономии времени. Хотя результаты редко выглядят приемлемо.
PHPMyAdmin – это инструмент для администрирования баз данных MySQL, который зачастую предоставляется вместе с планом хостинга. Для структурирования и тестирования ваших страниц с электронными таблицами, вы можете использовать различные SQL-инструменты, которые идут в комплекте с PHPMyAdmin.
Следующий скриншот отображает нам результат вышеприведенного запроса:
Если рассмотреть колонки поближе, то видно, что их имена вполне значимы и удобны для программистов, но вряд ли это будет привлекательным для деловых пользователей.
Имена колонок написаны с маленькой буквы, и вместо пробелов мы видим нижнее подчеркивание.
Получаем более привлекательный результат из значения 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`Что в результате дает:
Вышеприведенный прототип демонстрирует нам то, как может выглядеть электронная таблица.
Этап 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 для того, чтобы показать нам, как должна выглядеть электронная таблица.
Функция 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->sql_pages[$wsName]['Sql'] = $sql; // This is the statement to be executed
$this->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->sql_pages[$wsName]['Row'] = $start_row; // This the row number to start putting data into
$this->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 = <<<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 должна быть определена на электронной таблице.
Этап 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 обсуждается далее в руководстве.
Б. Логика «Начало страницы»
В начале каждой страницы есть специальное форматирование. Сначала для каждой конкретной страницы из базы данных извлекается строка, которая выполняет задачи, нужные для произведения форматирования в начале страницы. Она вовлекает пользовательскую функцию 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. Значение ячеек начинается со знака равенства (=), а затем следует формула. В данном случае, у нас идет сумма нескольких ячеек. Смотрим дальше:
А вот так выглядит код:
$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 "<a href="$fname">Download $fname</a>";Итог
Нижеприведенное изображение отображает нам итоговый скриншот с нашим проектом. Двухстраничная таблица, наполненная данными из баз данных и оформленная пользовательскими заголовками:
Вывод
Как только данные из базы будут в объекте PHPExcel, вы можете использовать другие функции класса PHPExcel для воспроизведения дополнительного форматирования, добавления большего числа формул, сохранения их в файлы разных форматов и многое другое.
Класс MySqlExcelBuilder, например, может быть расширен для использования функций PHPExcel, чтобы заполнить существующий шаблон электронной таблицы данными из базы MySQL. Так как PDO является интерфейсом базы данных, DSN в конструкторе MySqlExcelBuilder может быть без проблем изменен для работы с другими базами данных.
Спасибо за то, что были с нами!
Внимание! У вас нет прав для просмотра скрытого текста.
