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.

What's $rownumber after the loop exits? Any data getting out of the query at all? — Marc B, Jan 20 '11 at 14:10
I dont' see where you are getting your column from. `$col = '';` — Andrew Coder, Oct 19 '15 at 17:11

Mark Baker · Accepted Answer · 2011-01-21T14:02:39.513

12

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

edited Jan 21 '11 at 14:02

answered Jan 20 '11 at 14:12

Mark Baker

209,507
32
346
385

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` – Cheerio Jan 20 '11 at 21:55
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. – Mark Baker Jan 20 '11 at 22:10
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 Jan 20 '11 at 22:22
Im sorry for this terrible message – Cheerio Jan 20 '11 at 22:22
My bad: $row should have been $rowNumber in the line $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); – Mark Baker Jan 20 '11 at 22:43
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` – Cheerio Jan 21 '11 at 08:54
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 Jan 21 '11 at 08:58
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. – Mark Baker Jan 21 '11 at 08:59
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 Jan 21 '11 at 13:56
Another Wow. Thanks `$rowNumber++` should be `$rowNumber++;` LOve PHPExcel – Cheerio Jan 21 '11 at 17:06

Problem with PHPExcel

1 Answers1

Linked