Export MySQL table to Excel file using php script
You might want to export your data to view in excel so is’t more read able rather than reading the result in a mysql console’s. here’s how.
First you need to change the configuration to work well.
<?php
/**
* @author sapta
* @copyright 2009
*/
$cdate = date("Y-m-d"); // get current date
// configuration
$mysql_host = 'localhost';
$mysql_user = 'root';
$mysql_pass = '';
$mysql_db = 'jasindo';
$query_string = "select * from applicant";
$export_filename = "exported_table_on_$cdate.xls";
// connect to mysql server
$mysql_link = mysql_connect($mysql_host,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db,$mysql_link);
// query from table
$result = mysql_query($query_string);
$count = mysql_num_fields($result);
// fetch table header
$header = '';
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}
// fetch data each row, store on tabular row data
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";
}
// create table header showing to download a xls (excel) file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$export_filename");
header("Cache-Control: public");
header("Content-length: ".strlen($data)); // tells file size
header("Pragma: no-cache");
header("Expires: 0");
// output data
echo $header."\n".$data;
?>
Related posts:
- How to: Export MySQL data table to excel / csv format with SQL query
- Handling file upload using PHP Script
- Creating user online program with php
- PHP script for reading RSS
i found that the script ignores the last record fetched from the datatable (for example: from 100 records, only 99 are transferred to excel). if in line 71 $header.”\n” is removed, all the records show up in excel.
to make things easier, i’ve simply replaced all occurences of $header with $data, so the header and the record data is filled into a single variable ($data). this works just fine.
[Reply]
It doesn`t works !!!!! doesn`t appear dialog box… asking for downloading file.
nevertheless Thanks.
[Reply]
It really works, thanks a lot!
[Reply]
How to save it automatically to server? not to download it?
[Reply]
atpaz Reply:
September 19th, 2009 at 09:40
@John,
Hi, it’s simple you can edit the code at header response (line 62 – 67) with the code to write $data to file. Here is example :
[php]
$filename = ‘export_result.csv’;
$somecontent = $data;
// Let’s make sure the file exists and is writable first.
if (is_writable($filename)) {
// In our example we’re opening $filename in append mode.
// The file pointer is at the bottom of the file hence
// that’s where $somecontent will go when we fwrite() it.
if (!$handle = fopen($filename, ‘a’)) {
echo “Cannot open file ($filename)”;
exit;
}
// Write $somecontent to our opened file.
if (fwrite($handle, $somecontent) === FALSE) {
echo “Cannot write to file ($filename)”;
exit;
}
echo “Success, wrote ($somecontent) to file ($filename)”;
fclose($handle);
} else {
echo “The file $filename is not writable”;
}
[/php]
[Reply]
Thank you man….
[Reply]