Export MySQL to Excel (.xls) using PHP

export.php

<?php
  /***** EDIT BELOW LINES *****/
  $DB_Server = "localhost"; // MySQL Server
  $DB_Username = "username"; // MySQL Username
  $DB_Password = "password"; // MySQL Password
  $DB_DBName = "databasename"; // MySQL Database Name
  $DB_TBLName = "tablename"; // MySQL Table Name
  $xls_filename = 'export_'.date('Y-m-d').'.xls'; // Define Excel (.xls) file name
   
  /***** DO NOT EDIT BELOW LINES *****/
  // Create MySQL connection
  $sql = "Select * from $DB_TBLName";
  $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Failed to connect to MySQL:<br />" . mysql_error() . "<br />" . mysql_errno());
  // Select database
  $Db = @mysql_select_db($DB_DBName, $Connect) or die("Failed to select database:<br />" . mysql_error(). "<br />" . mysql_errno());
  // Execute query
  $result = @mysql_query($sql,$Connect) or die("Failed to execute query:<br />" . mysql_error(). "<br />" . mysql_errno());
   
  // Header info settings
  header("Content-Type: application/xls");
  header("Content-Disposition: attachment; filename=$xls_filename");
  header("Pragma: no-cache");
  header("Expires: 0");
   
  /***** Start of Formatting for Excel *****/
  // Define separator (defines columns in excel &amp; tabs in word)
  $sep = "\t"; // tabbed character
   
  // Start of printing column names as names of MySQL fields
  for ($i = 0; $i<mysql_num_fields($result); $i++) {
    echo mysql_field_name($result, $i) . "\t";
  }
  print("\n");
  // End of printing column names
   
  // Start while loop to get data
  while($row = mysql_fetch_row($result))
  {
    $schema_insert = "";
    for($j=0; $j<mysql_num_fields($result); $j++)
    {
      if(!isset($row[$j])) {
        $schema_insert .= "NULL".$sep;
      }
      elseif ($row[$j] != "") {
        $schema_insert .= "$row[$j]".$sep;
      }
      else {
        $schema_insert .= "".$sep;
      }
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
  }
?>

Sir....Please send code for mysqli .

@sanjeevchoudhary1004
I used .csv For some reason xls dont work for me

<?php
  /***** EDIT BELOW LINES *****/
  $DB_TBLName = "operation"; // MySQL Table Name
  $xls_filename = 'export_'.date('Y-m-d').'.csv'; // Define Excel (.xls) file name
   // Create connection
  $conn = new mysqli($servername.':'.$port, $username, $password,$db);
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  /***** DO NOT EDIT BELOW LINES *****/
  // Create MySQL connection
  $sql = "Select * from $DB_TBLName";
  $conn->query("SET NAMES 'utf8'");
  $data = array();
  $result = $conn->query($sql);
  $fields_Name = [];
  if ($result) {
    $finfo = $result->fetch_fields();
    foreach ($finfo as $val) {
        //printf("Name:      %s\n",   $val->name);
        array_push($fields_Name,$val->name);
    }
    //$result->free();
  }
   
  // Header info settings
  header("Content-Type: application/xls");
  header("Content-Disposition: attachment; filename=$xls_filename");
  header("Pragma: no-cache");
  header("Expires: 0");
 //for Hebrew letters
  echo chr(0xEF).chr(0xBB).chr(0xBF);
  /***** Start of Formatting for Excel *****/
  // Define separator (defines columns in excel &amp; tabs in word)
  $sep = ","; // tabbed character
   
  // Start of printing column names as names of MySQL fields
  foreach ($fields_Name as $value) {
    echo $value .  $sep;
  }
  print("\n");

  // End of printing column names
  
  // Start while loop to get data
  while($row = $result->fetch_assoc())
  {
    $schema_insert = "";
    foreach ($fields_Name as $value) 
    {
      if(!isset($row[$value])) {
        $schema_insert .= "NULL".$sep;
      }
      elseif ($row[$value] != "") {
        $field_value = $row[$value];
        $field_value = str_replace($sep , "",$field_value );
        $schema_insert .= $field_value.$sep;
      }
      else {
        $schema_insert .= "".$sep;
      }
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= $sep;
    print(trim($schema_insert));
    print "\n";
  }
  $conn->close();
?>

does not work here.

While I try to open the excel file it shows " the file format and extension of don't match " .

thx sir

Hi, I used your code for csv download but it shows all in black boxes, the values are there, just hidden.

Hi there, looking for mysqli version ? @sanjeevchoudhary1004:

<?php


/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "yourpassword";             //MySQL Password     
$DB_DBName = "dbname";         //MySQL Database Name  
$DB_TBLName = "tblname"; //MySQL Table Name   
$filename = "filename";         //File Name

/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/ 

$con = mysqli_connect($DbServer, $DB_Username, $DB_Password, $DB_DBName); 
    mysqli_select_db ($con, $DB_TBLName); 
    // Change character set to utf8
    mysqli_set_charset($con,"utf8");

$sql = "Select * from $DB_TBLName";

   
//execute query 
$result = @mysqli_query($con, $sql) or die("Couldn't execute query:<br>" . mysqli_error($con));    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysqli_num_fields($result); $i++) {
echo mysqli_fetch_field($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysqli_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysqli_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

nice

I have a problem, my language is Spanish and when I open the xls I get for example like this "MartÃn RodrÃguez" the correct is "Martín Rodríguez" .

some way for me to interpret it well?
thanks

Hi @manulpz,

Due to heavy workload, I can't look into this and amend the code for you but you can update header info settings (lines 19-23) to fix it.

// Header info settings
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$xls_filename");
header("Pragma: no-cache");
header("Expires: 0");

I hope this helps.

I am getting this:

after klicking yes my table looks like that:

The Code around line 37:

/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysqli_num_fields($result); $i++) {
echo (mysqli_fetch_field($result, $i) . "\t");
}
print("\n");  

@Pankraz01 Could you double-check you've copied the exact code I've provided above? - https://gist.github.com/dipakcg/4684a67337eff947e90ebdd069d59d73#file-export-php?