Hi All,
Can anybody share PHP code to generate excel file by querying MySQL database.

Thanks in advance..

You could use PHPMyAdmin to save the output of your SQL queries directly as MS Excel files.

If you want to write a PHP script to do that, the simplest way is to make a '.csv' file. You want a tip on this, make a post

It's actually quite easy to save the output as Excel (or Word or ...) but first you have to produce a table on the screen in the format that you want to see in Excel. You can go to my site and download the Desktop Write utility that will save it for you (link below):

Download Desktop Write

you may try to use this code

<?php
{
require_once "prerequisite.php";
$sql2 = mysql_query("SELECT * FROM student_registration ORDER BY stu_ID");
$count   = mysql_num_fields($sql2);
$header = '';
$data = '';

for ($i = 0; $i < $count; $i++) {
  $header .= mysql_field_name($sql2, $i)."\t";
}

while($row = mysql_fetch_assoc($sql2)) {
  $line = '';
  foreach($row as $key => $value) {

    if ($key === 'date_of_reg') {
      $value  = '"' . date('d/m/Y', $value) . '"';
    } else {
      $value  = '"' . $value . '"';
    }

    if ($line === '') {
      $line  = $value;
    } else {
      $line .= "\t" . $value;
    }
  }

  $data .= $line . "\n";
}

if ($data == "") 
{
    $data = "There is no record inside the table.";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=student_excel.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 

}
?>

Edited 3 Years Ago by mike_2000_17: Fixed formatting

This article has been dead for over six months. Start a new discussion instead.