Generate an excel file from MYSQL with PHPExcel

Question

I am using PHPExcel to generate an excel file after the end user clicks a "generate excel file" button. This button should open up an excel file for the client, and display the data. So far I have this code, but currently it only opens a blank page with no excel file. can anyone tell me what I am doing wrong? I am guessing that I'm not connecting to the database correctly..

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel   file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
 or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
 //select database 
 $Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($sql,$Connect) 
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 

 error_reporting(E_ALL);

 require_once '../Classes/PHPExcel.php';
 $objPHPExcel = new PHPExcel();

 // Set the active Excel worksheet to sheet 0 

$objPHPExcel->setActiveSheetIndex(0);  

// Initialise the Excel row number 

$rowCount = 1;  

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');

exit;

*I am guessing that I'm not connecting to the database correctly*. Don't guess, debug. First remove those ugly `@` and move `error_reporting(E_ALL);` to the top of your script. Add a `ini_set('display_errors', 1);` and `exit;` before the first `header()`. The see if you have any error message. — Tchoupi, Oct 01 '12 at 20:56
Also... `$table` is undefined, so you're question is obviously failing. — Tchoupi, Oct 01 '12 at 20:56
The code you've shown will create an empty Excel workbook, because you're not writing anything to any of the worksheets — Mark Baker, Oct 01 '12 at 21:12
could you write or give me an example of how to do this with the way I am currently writing this code? — Bobby Ricky, Oct 02 '12 at 00:00
I provided basic code in answer to your earlier question: http://stackoverflow.com/questions/12611148/how-to-export-data-to-an-excel-file-using-phpexcel - if that's giving you a blank workbook as well, then you need to do some basic debugging starting with your SQL query and its results — Mark Baker, Oct 02 '12 at 06:26
Read that to learn how to see errors: [Nothing is seen. The page is empty and white.](http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php/12772851#12772851) — Jocelyn, Oct 08 '12 at 11:04

score -1 · Answer 1 · answered Feb 05 '17 at 10:15

I know this has been posted long ago..But it might be of help to someone.

You did not assign any values to the worksheet. So give the following code after the select query

while($row=mysqli_fetch_array($result))
              {
               $objWorksheet->fromArray($data, ' ', $ii);
                }

This is how the entire code will be:

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel   file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
 or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
 //select database 
 $Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 


 error_reporting(E_ALL);

 require_once '../Classes/PHPExcel.php';
 $objPHPExcel = new PHPExcel();



  // Set the active Excel worksheet to sheet 0 

    $objWorksheet =$objPHPExcel->getActiveSheet(); 
//title for worksheet 
$objWorksheet->setTitle('Data');
//header values
$objWorksheet->setCellValue('A1', 'Sl.');
$objWorksheet->setCellValue('B1', 'Username');
$i=2;


$result = @mysql_query($sql,$Connect);


    while($row=mysqli_fetch_array($result))
              {
                $data= array($row[0],$row[1],$row[2],$row[3]);
                $ii="A".$i;
//fill data
                $objWorksheet->fromArray($data, ' ', $ii);
                 $i++;


              }

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');

exit;
?>

Generate an excel file from MYSQL with PHPExcel

1 Answers1