Как получить цельный лист Excel из MySQL

Данный материал показывает как использовать открытые PHP-компоненты для создания «настоящих» Excel листов из MySQL.

Обзор.

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

 

Фундаментальное родство между таблицами данных и листами.

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

Экселевский лист — это череда страниц (называемых рабочими листами, или Worksheets) с пронумерованными рядами и колонками с буквенным обозначением.

Основной метод работы тут — это связать SQL-оператор с одним рабочим листом, привязывая названия колонок ассоциативного массива, возвращаемого на комманду PDO fetch, к буквенным обозначением колонок листа Excel.

Шаг 1. Понять как правильно обозвать буквенные колонки.

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

Схема обозначения столбцов в Excel.

Когда метки столбцов доходят до Z, добавляется вторая буква: «A...

Z, AA,AB … AZ». Затем следует «BA» как показано ниже.

Алгоритм обозначения стобцов.

Следующая вставка из PHP показывает как именовать заголовки столбцов от «A» до «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-оператор.

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

Приведённая схема показывает упрощённую связь customer/order (заказчик/заказ).

Простой и быстрый оператор SELECT.

Обычно выборка данных осуществялется следующим способом:

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

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

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

Ниже приведены результаты показанного запроса:

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

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

Получаем красивый результат от оператора SELECT.

Мы хотим получить ответ от оператора SELECT в виде красивого бизнес-отчёта. Итак, используя PHPMyAdmin инструмент, отредактируем оператор так, чтобы названия столбцов были нормальными словами и отображались только нужные столбцы. Переделанный 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 оператора на рабочий лист используя 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 = << 
  
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. Создаём полный лист (Spreadsheet).

Понимание PHPExcel

Если вы понимаете как управлять листом Excel с помощью мышки и клавиатуры, ддля вас не составит труда поладить с PHPExcel. PHPExcel построен на управлении модели листа по схожим принципам, что и управление листами в Excel. Подробности можно посмотреть в PHPExcel Developer Documentation.

Функция get_excel ()

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

А. Постраничная итерация.

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

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. Как и Excel, PHPExcel использует пару «число-буквы», чтобы определить конкретную ячейку. В MySqlExcelBuilder это называется cellKey. Этот 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 – вставляет действительное значение в поле
getStyle – возвращает ссылку на аттрибуты запращиваемой ячейки
getColumnDimension – ширина столбца присваивается переменной col

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

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

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 fir

Шаг 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 "Download $fname";

Конечный продукт

На рисунке приведён вид части готового файла, полученного с помощью изложенного здесь кода. Это проект, состоящих из двух рабочих листов, заполненных данными из пробной базы данных и отформатированной по названиям для кажого листа.

Заключение

Как только данные из базы данных оказываются в объекте PHPExcel, вы можете использовать другие инструменты PHPExcel для дальнейшего форматирования, добавления формул, сохранения в различных форматах и для других задач.
Класс MySqlExcelBuilder , например, может быть использован для занесения данных в уже существующий шаблон листа. PDO может быть использован для того, чтобы прописать альтернативный DSN (Data Source Name) в конструкторе MySqlExcelBuilder.