Using PHP Excel to Generate Excel Report from SQL Database

Topics: Developer Forum
Jun 7, 2013 at 9:38 AM
I'm new with php. Can you explain how to use PHP Excel to generate excel report from SQL database. Basically it should work like this:
  • Read the data from database
  • Transfer all data in database to excel - report.xsl
  • Download report.xsl file..
Please help me with this.. TQ..
Jun 7, 2013 at 1:33 PM
Edited Jun 10, 2013 at 3:55 PM
A basic example :
<?php
$host='localhost'; $user='me'; $pass='mypassword'; $DataBase='mydatabase';//define the correct values
// open the connexion to the databases server
$Link=@mysqli_connect($host,$user,$pass,$DataBase) or die('Can\'t connect !');
mysqli_set_charset($Link, 'utf8');//if not by default
//your request
$SQL='SELECT Field1, Field2 FROM MyTable';
$rs=mysqli_query($Link, $SQL);//get the result (ressource)
/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';//change if necessary

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$F=$objPHPExcel->getActiveSheet();
$Line=1;
while($Trs=mysqli_fetch_assoc($rs)){//extract each record
    $F->setCellValue('A'.$Line, $Trs['Field1'])
        ->setCellValue('B'.$Line, $Trs['Field2']);//write in the sheet
    ++$Line;
}
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="report.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;