export_xls.php

<?php
  require('PHPExcel.php');
  
  class export_xls 
  {    
      private $currentDB = null;
      private $currentTable = null;
      private $conn = null;
      
      public function __construct($dbName, $tableName = null)
      {
          if (DatabaseExists($dbName))
          {
             $this->currentDB = $dbName; 
             if (!is_null($tableName) && (TableExists($tableName, $dbName)))
             {
                 $this->currentTable = $tableName;
             }
          }
          $this->conn = Database::getConnection();
          if (!$this->conn)
          {
              die("Ocorreu um erro ao ligar à BD.");
          }
          else
          {
              $this->conn->select_db($this->currentDB);
              $this->xlsExport();  
          }
      } //__construct
      
      function xlsExport()
      {
          if (is_null($this->currentTable)) //base de dados
          {       
              $tableList = tablesFromDB($this->currentDB);
              //Instanciar um objecto da classe PHPExcel
              $objPHPExcel = new PHPExcel();
              $objPHPExcel->getProperties()->setCreator("The Sequel");
              $objPHPExcel->getProperties()->setTitle("Backup da BD '$this->currentDB'");
              $objPHPExcel->getProperties()->setSubject("Backup da BD '$this->currentDB'");
              //Contador de tabelas
              $i = 0;                                     
              foreach ($tableList as $table)
              {
                  $query = "SELECT * FROM `$table`";
                  $result = $this->conn->query($query);
                  if (($result) && ($result->num_rows > 0)) 
                  {
                      $colNames = $result->fetch_fields();                
                      $objWorkSheet = $objPHPExcel->createSheet($i);
                      $objWorkSheet->setTitle($table);                
                      $rowNumber = 1;
                      $col = 'A';
                      foreach ($colNames as $colName) 
                      {
                         $objWorkSheet->setCellValue($col.$rowNumber, $colName->name);
                         $col++;
                      }
                      //Output dos dados
                      $rowNumber = 2;
                      while ($row = $result->fetch_row()) 
                      {
                         $col = 'A';
                         foreach ($row as $cell) 
                         {                      
                            $objWorkSheet->setCellValue($col.$rowNumber, $cell);
                            $col++;
                         }
                         $rowNumber++;
                      }
                      //Fixar a linha do cabeçalho para impedir o scroll                
                      $objWorkSheet->freezePane('A2');
                      //Guardar o ficheiro em formato Excel BIFF (xls)
                      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                 } //endif;    
                 $i++;           
              } //endforeach;
              //Eliminar folha criada por defeito na instanciação do objecto
              $sheetIndex = $objPHPExcel->getIndex($objPHPExcel->getSheetByName('Worksheet')); 
              $objPHPExcel->removeSheetByIndex($sheetIndex); 
              //Informação de header e download do ficheiro XLS
              header('Content-Type: application/vnd.ms-excel');
              header('Content-Disposition: attachment;filename="' . $this->currentDB . '.xls"');
              header('Cache-Control: max-age=0');
              $objWriter->save('php://output');
              //Elimina as folhas adicionadas ao objecto e remove-o
              $objPHPExcel->disconnectWorksheets();
              unset($objPHPExcel);
              exit();
          } //endif;   
          else //tabela
          {
              $query = "SELECT * FROM " . $this->currentTable;
              $result = $this->conn->query($query);
              if ($result) 
              {
                  $colNames = $result->fetch_fields();
                  //Instanciar um objecto da classe PHPExcel
                  $objPHPExcel = new PHPExcel();
                  $objPHPExcel->getProperties()->setCreator("The Sequel");
                  $objPHPExcel->getProperties()->setTitle("Backup da BD '$this->currentDB'");
                  $objPHPExcel->getProperties()->setSubject("Backup da BD '$this->currentDB'");
                  $objPHPExcel->getActiveSheet()->setTitle($this->currentTable);
  
                  $rowNumber = 1;
                  $col = 'A';
                  foreach ($colNames as $colName) 
                  {
                     $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber, $colName->name);
                     $col++;
                  }
                  //Output dos dados
                  $rowNumber = 2;
                  while ($row = $result->fetch_row()) 
                  {
                     $col = 'A';
                     foreach ($row as $cell) 
                     {
                        $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber, $cell);
                        $col++;
                     }
                     $rowNumber++;
                  }
                  //Fixar a linha do cabeçalho para impedir o scroll                
                  $objPHPExcel->getActiveSheet()->freezePane('A2');
                  //Guardar o ficheiro em formato Excel BIFF (xls)
                  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                  //Informação de header e download do ficheiro XLS
                  header('Content-Type: application/vnd.ms-excel');
                  header('Content-Disposition: attachment;filename="' . $this->currentTable . '.xls"');
                  header('Cache-Control: max-age=0');
                  $objWriter->save('php://output');
                  //Elimina as folhas adicionadas ao objecto e remove-o
                  $objPHPExcel->disconnectWorksheets();
                  unset($objPHPExcel);
                  exit();           
              }
          } //endif;  
      } //xlsExport    
  } //classe;