How to include field names with mysql data

Topics: Developer Forum, User Forum
Nov 8, 2012 at 12:01 AM

I would like to include the mySQL field names as line 1 in the generated xlsx. I have tried some things but have not had any luck. If anyone is willing to offer some advise on the subject I would be very appreciative. Here is my code:

 

$query = "SELECT * FROM evolve2012 WHERE cast(Date as DATE) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND Blah Blah Blah";

if ($result = mysql_query($query) or die(mysql_error())) {
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Data');   
}
$rowNumber = 1;
while ($row = mysql_fetch_assoc($result)) {       
$col = 'A'; // start at column A       
foreach($row as $cell) {         
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);         
$col++;       
}       
$rowNumber++;

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="evolve2012-last7days.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

Nov 8, 2012 at 10:22 AM
Edited Nov 8, 2012 at 10:25 AM

how about the following:

$query = "SELECT * FROM evolve2012 WHERE cast(Date as DATE) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND Blah Blah Blah";

if ($result = mysql_query($query) or die(mysql_error())) {
  $objPHPExcel = new PHPExcel();
  $objPHPExcel->getActiveSheet()->setTitle('Data');   
}
$rowNumber = 1;
while ($row = mysql_fetch_assoc($result)) {       
  $col = 'A'; // start at column A       

  if ( $rowNumber == 1 ){
    $headers = array_keys($row);
    foreach ( $headers as $header ({
      $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber, $header);
      $rowNumber++;
      $col++;
    }
    $col = 'A';
  }

  foreach($row as $cell) {          
    $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);          
    $col++;       
  }       
  $rowNumber++;
} 

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="evolve2012-last7days.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

Nov 9, 2012 at 8:58 PM
Edited Nov 9, 2012 at 9:19 PM

Thanks Borft,

I modified your code a little and got it to work. The way you had it setup returns the title row as it should but it also moves down a row so the spreadsheet had the header row in a slant like so.

1
...... 2
............ 3
...................4  etc.

 

Also it wasn't returning any other data along with the title row. But I figured it out, thanks again for the help. This is what worked:

 

if ($result = mysql_query($query) or die(mysql_error())) {
/** Create a new PHPExcel object 1.0 */
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()->setTitle('Data');
   }  

}
$rowNumber = 1; //start in cell 1	
while ($row = mysql_fetch_assoc($result)) {
	$col = 'A'; // start at column A
	// returns title row
	   if ( $rowNumber == 1 ){
       	$headers = array_keys($row);
       		foreach($headers as $header) {
          	$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$header);
          	$col++;
       		}
	   	$rowNumber++;
	  }else{ //returns content rows
	   $col = 'A';
	 	foreach($row as $cell) {          
		$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);          
		$col++;       
		} 
	  $rowNumber++;       
	  }
      
}
Nov 11, 2012 at 10:31 PM

glad it helped! :) I must admit I hadn't tested the code I posted in any way :D