Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Export MySQL to Excel (.xls) using 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";
}
?>
@sanjeevchoudhary1004

This comment has been minimized.

Copy link

@sanjeevchoudhary1004 sanjeevchoudhary1004 commented Jul 12, 2018

Sir....Please send code for mysqli .

@sarelpoy

This comment has been minimized.

Copy link

@sarelpoy sarelpoy commented Jul 18, 2018

@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();
?>
@EduVillas

This comment has been minimized.

Copy link

@EduVillas EduVillas commented Aug 6, 2018

does not work here.

@Knavaneeth

This comment has been minimized.

Copy link

@Knavaneeth Knavaneeth commented Oct 5, 2018

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

image

@igorfostjak

This comment has been minimized.

Copy link

@igorfostjak igorfostjak commented Oct 31, 2018

thx sir

@rzc96

This comment has been minimized.

Copy link

@rzc96 rzc96 commented Feb 20, 2019

Hi, I used your code for csv download but it shows all in black boxes, the values are there, just hidden.
screen shot 2019-02-20 at 12 53 03

@alvaroarta

This comment has been minimized.

Copy link

@alvaroarta alvaroarta commented Apr 10, 2019

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";
    }   
?>
@DeveloperRijan

This comment has been minimized.

Copy link

@DeveloperRijan DeveloperRijan commented Aug 1, 2019

nice

@manulpz

This comment has been minimized.

Copy link

@manulpz manulpz commented Mar 25, 2020

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

@dipakcg

This comment has been minimized.

Copy link
Owner Author

@dipakcg dipakcg commented Mar 25, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.