FL.ru – фриланс сайт удаленной работы №1. Поиск удаленной работы, фрилансеры.

Формирование полноценной Эксель-таблицы средствами РНР

Категории: Вебмастеру

Большинство РНР-разработчиков вспоминают о формате CSV, когда речь заходит об экспорте данных в Эксель. Как правило, почти всех это устраивает, так как приоритетная задача – выдача данных, а этого формата для хранения данных хватает.

Однако предположим, что нам нужно не просто вывалить информацию в табличном виде, а сформировать полноценный экселевский отчет, с форматированием, объединением ячеек и прочими «вкусностями» на основе обычного запроса к БД MySQL «SELECT».

Как быть? Стоит применить такое чудное приспособление, как PHPExcel (официальный сайт — http://www.phpexcel.net).

Для начала определимся с алгоритмом. Результатом выполнения запроса MySQL «SELECT» является ассоциативный массив, ключ к которому – название колонки, а данные в нем – результаты, отобранные запросом. Собственно, это и есть структура, которую нам нужно «запихнуть» в Эксель.

Таблица Excel представляет собой набор страниц. Каждая из страниц содержит в себе нумерованные строки и обозначенные буквами колонки. Соответственно, для упрощения экспорта данных нужно написать код, который бы привел в соответствие наименование колонок в ассоциативном массиве и колонки в Экселе.

Потому первая задача, которую нам нужно решить – это алгоритм буквенного именования колонок, аналогичного экселю, то есть A – Z, AA – ZZ. Следующий несложный код устраивает нумерацию.

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

Следующая задача, которая нам предстоит – это формирование результатов запроса SQL. Самый простой способ получить данные из базы данных – это запрос на выборку, выполняемый следующим образом:

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


Однако при этом заголовки колонок будут представлять собой названия полей из баз данных. Мы, как программисты, понимаем, что названия полей в БД понятны нам, но покажутся «загадочными рунами» простому пользователю. Потому необходимо выводить данные в более «человечном виде». Для этого можно использовать широкий инструментарий оператора SELECT. К примеру, форматированный вариант предыдущего запроса будет выглядеть так:

1
2
3
4
5
6
7
8
9
10
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`


Теперь переходим к, собственно, построению экселевской таблицы. Для этого применим класс MySqlExcelBuilder. В нем используется инструментарий PDO и PHPExcel. Данный класс имеет следующие важные элементы:

$pdo – объект, отправляющий запросы.

$phpExcel – это объект класса PHPExcel, который и будет формировать нашу таблицу

$sql_pages – массив результатов запроса, а также параметров для каждого листа нашей эксель-книги

А далее все довольно просто. Фактически, класс PHPExcel дает нам возможность при помощи команд РНР делать действия, аналогичные тем, которые пользователь совершает в Экселе. Имеется в виду создание страниц, форматирование, добавление формул и т.п.

Далее приведет пример, как нужно использовать функцию add_page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$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');

При помощи функции getExcel() мы будем формировать отдельные таблицы на основе результатов запросов. Рассмотрим алгоритм данной функции.

В основе алгоритма – цикл, который за каждый проход обрабатывает страницы, добавленные с помощью функции add_page ранее. При этом цикл добавляет новые эксель-страницы через phpExcel и заполняет их данными. Следующий отрывок кода и рисунок демонстрируют, как происходит заполнение данными таблицы.

1
2
3
4
5
6
7
8
9
10
11
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);


В первой ячейке каждой страницы присутствует специальной форматирование. Данный эффект достигается с помощью такого алгоритма:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$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
}

Нужно помнить, что класс PHPExcel применяет аналогичную самому Экселю шифровку ячеек, каждая ячейка определяется как БукваЧисло. Потому в алгоритмах фигурирует так называемый «ключ ячейки» (cellKey), который представляет собой сочетание заголовка столбца с номером строки.

Заполнение данными осуществляется путем цикличной обработки массива. Берем данные, формируем соответствующий ключ ячейки, пишем данные.

1
2
3
4
5
6
7
8
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++;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$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);




Вообще, нужно учитывать, что инструментарий PHPExcel необычайно широк. Можно форматировать ячейки, объединять их, изменять шрифты… В этом примере всё было сделано довольно просто и «по-спартански», но реальные возможности намного шире.

Всё, наша таблица готова. Но ее еще нужно вывести и выдать пользователю. Для этого делаем последние штрихи, устанавливаем заголовки и подготавливаем ее к выдаче.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 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.

С помощью инструментария PHPExcel экспортируем файл в формате Excel5. В принципе, формат можно задать какой угодно.

1
2
3
4
5
6
// 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>";


Вот и всё. Таким образом, данный несложный алгоритм позволяет генерировать полноценную форматированную эксель-таблицу на основе запроса к базе данных «на лету».




Подпишитесь на рассылку блога
max1net.com:
Уже подписались человек
Укажите свой e-mail