Php – Problem with PHPExcel

PHPphpexcelxls

I have a simple table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `email` varchar(225) NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL,
  `status` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

I'm using PHPExcel to export tha table in XLS format.
I wrote a simple PHP lines:


$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "svn_register";

mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

// require the PHPExcel file
require 'Classes/PHPExcel.php';

// simple query

$query = "SELECT * FROM users ORDER by id DESC";

if ($result = mysql_query($query) or die(mysql_error())) {
    // Create a new PHPExcel object
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()->setTitle('List of Cities');

   // Loop through the result set
    $rowNumber = 1;
    while ($row = mysql_fetch_row($result)) {
       $col = '';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
          $col++;
       }
       $rowNumber++;
   }
   // 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();
}
echo 'a problem has occurred... no data retrieved from the database';

I get an empty page.

Best Answer

I stated this several time when you originally raised thsi question... that script will generate a blank page.

$objWriter->save('CityList.xls');

writes the Excel workbook to a file called CityList.xls on the server's filesystem. It does NOT display anything to the screen... therefore the screen will be blank.

Look on the server. Find the file called CityList.xls. Open that file in MS Excel.

EDIT

Alternatively, set the appropriate headers, and save to php://output

// connection with the database
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "database";

mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

// require the PHPExcel file
require 'Classes/PHPExcel.php';

// simple query

$query = "SELECT id FROM users ORDER by id DESC";

if ($result = mysql_query($query) or die(mysql_error())) {
    // Create a new PHPExcel object
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()->setTitle('List of Cities');

   // Loop through the result set
    $rowNumber = 1;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
          $col++;
       }
       $rowNumber++;
   }
   // 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();
}
echo 'a problem has occurred... no data retrieved from the database';

EDIT 2

Alternative:

// Loop through the result set
$rowNumber = 1;
while ($row = mysql_fetch_row($result)) {
    $objPHPExcel->getActiveSheet()->fromArray(array($row),NULL,'A'.$rowNumber++);
}

should also fix this error

EDIT #3

To add a heading row.

$rowNumber = 1;
$headings = array('Name','EMail','Phone');
$objPHPExcel->getActiveSheet()->fromArray(array($headings),NULL,'A'.$rowNumber);

$rowNumber++

// Loop through the result set
while ($row = mysql_fetch_row($result)) {
   $col = 'A';
   foreach($row as $cell) {
      $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
      $col++;
   }
   $rowNumber++;
}
Related Topic