Using PHP Generate Excel Report From Database

---

When developing a website that uses a database, quite often there would be a requirement to store dynamic data in a table, whether that be contact form entries, activity logs, or even lists of registered users.

While that data can be easily extracted directly from the database using database software, you may need to export that data using a web application. The following code is some useful PHP that can be used to take all data from a MySQL table and export them in a Excel format, that can be downloaded by the user as a Excel sheet.

Click here for Demo        Download Script

A basic example :

<?php
    $db_host="localhost";
    $db_username="username";
    $db_password="password";
    $db_name="database name";

 $conn = mysql_connect($db_host,$db_username,$db_password) or die("Could not connect to Server" .mysql_error());
 mysql_select_db($db_name) or die("Could not connect to Database" .mysql_error());
  
   //your request
        $all_members ="SELECT * FROM `users`";
        $all_memb = mysql_query($all_members);
        $members_info =mysql_fetch_array($all_memb);

     $val=$members_info['firstname'];  
     $val1=$members_info['lastname'];
     $val2=$members_info['age'];
     $val3=$members_info['contact'];

   //Include PHPExcel
     require 'Classes/PHPExcel.php';

   // Create a new PHPExcel object
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getActiveSheet()->setTitle('List of User Details');
     $objPHPExcel->getActiveSheet()->setCellValue('A1', 'First Name');
     $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Last Name');
     $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Age');
     $objPHPExcel->getActiveSheet()->setCellValue('D1', 'Contact');

  //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
     $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
     $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
     $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);

     $objPHPExcel->getActiveSheet()->setCellValue('A2',$val);
     $objPHPExcel->getActiveSheet()->setCellValue('B2',$val1);
     $objPHPExcel->getActiveSheet()->setCellValue('C2',$val2);
     $objPHPExcel->getActiveSheet()->setCellValue('D2',$val3);

   // Save as an Excel BIFF (xls) file
      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="myFile.xls"');
      header('Cache-Control: max-age=0');

      $objWriter->save('php://output');
    exit();
?>