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

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

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

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`

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

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

$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');

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))
        {

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

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++;

$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);

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

// 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>";