Problem with PHPExcel

Question

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.

Solution

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++;
}

Comments

[Cheerio]

Thank you. Very strange I get an error: Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\app\Classes\PHPExcel\Worksheet.php on line 2103

[Mark Baker]

OK! Now we're getting somewhere. The Invalid argument is a genuine error in the fromArray() method. As an alternative to using fromArray(), use the re-edited version of the while loop in my example above.

[Cheerio]

OMG ` Fatal error: Uncaught exception 'Exception' with message 'Invalid cell coordinate AARRAY' in C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Cell.php:‌​501 Stack trace: #0 C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Worksheet‌​.php(959): PHPExcel_Cell::coordinateFromString('AARRAY') #1 C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Worksheet‌​.php(860): PHPExcel_Worksheet->getCell('AArray') #2 C:\xampp\htdocs\2011\register\web\xls.php(31): PHPExcel_Worksheet->setCellValue('AArray', '115') #3 {main} thrown in C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Cell.php on line 501`

[Cheerio]

Im sorry for this terrible message

[Mark Baker]

My bad: $row should have been $rowNumber in the line $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber‌​,$cell);

[Cheerio]

Another error :( Fatal error: Uncaught exception 'Exception' with message 'Invalid cell coordinate 1' in C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Cell.php:‌​501 Stack trace: #0 C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Worksheet‌​.php(959): PHPExcel_Cell::coordinateFromString('1') #1 C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Worksheet‌​.php(860): PHPExcel_Worksheet->getCell('1') #2 C:\xampp\htdocs\2011\register\web\xls.php(31): PHPExcel_Worksheet->setCellValue('1', '1') #3 {main} thrown in C:\xampp\htdocs\2011\register\web\Classes\PHPExcel\Cell.php on line 501

[Mark Baker]

Can you please take these specific questions to the PHPExcel forums... it's a lot easier for me to answer there rather than constantly eithe rediting my response here, or writing comments.

[Mark Baker]

The answer to the problem is that you're initialising $col to '', not to 'A'... Excel column lettering begins with 'A' for the first column; same as the first row in an Excel worksheet is row 1.

[Cheerio]

Wow It works like magic :) Can I ask for the last thing please? I want to add a name for the first cols: (Name - Email - Phone). I'm in love with PHPExcel :)

[Cheerio]

Another Wow. Thanks $rowNumber++ should be $rowNumber++; LOve PHPExcel