Using PHP to download a MySQL table as an Excel sheet

This is one of the arbit ones, really. This needed to be done for one of the closing modules of my summer project at Ericsson, and though I searched high and low on the Internet I couldn't arrive at any definite solution.

However this bit of code does work. So feel free to try it out and use it if you need it.

So what are we doing here? Quite simply, a MySQL table exists in your database and we need the user to be able to see it as an Excel spreadsheet. In fact, as you'll see in the code that you can download it in any format be it, csv, ods or even docx and txt. But yeah, spreadsheet formats look good for tables and hence the priority is on that.

Here's the whole code.

<?php 
mysql_connect("localhost", "YOUR_MYSQL_USERNAME", "YOUR_MY_SQL_PASSWORD") or die(mysql_error());
mysql_select_db("DATABASE_NAME") or die(mysql_error());
 
$count = 0;

$sqlquery = "select * from TABLE_NAME" ;
$result = mysql_query($sqlquery) or die(mysql_error());  
$count = mysql_num_fields($result);

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

while($row = mysql_fetch_row($result))  {
  $line = '';
  foreach($row as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
  $data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  $data = "\nno matching records found\n";
}

$count = mysql_num_fields($result);



# This line will stream the file to the user rather than spray it across the screen
 header("Content-type: application/octet-stream");
//header("Content-type: text/plain");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");

header("Pragma: no-cache");
header("Expires: 0");

//echo $header."\n".$data;
echo $header."\n".$data."\n";
?>

Download the code from my Google Drive here. Don't forget to change the database and table names, and your MySQL username and password.

Also, if you are on Linux, and are opening the file in Libre Office set the separator options to "Tab".