[SOLVED] Simple way to Export MYSQL table to excel...

[Alexhoward]

Hi Guys,

 

This site has been a massive help so far,

 

Thanks very much 

 

I was wondering if any one knew a simple way to export a MYSQL table to an excel file using a PHP script?

 

Thanks in advance

[litebearer]

Might try this

 

<?
// Connect database
$database="tutorial";
$table="name_list";
mysql_connect("localhost","","");
mysql_select_db("tutorial");

$result=mysql_query("select * from $table");

$out = ''; 

// Get all fields names in table "name_list" in database "tutorial".
$fields = mysql_list_fields(tutorial,$table);

// Count the table fields and put the value into $columns. 
$columns = mysql_num_fields($fields);


// Put the name of all fields to $out. 
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="n";

// Add all values in the table to $out. 
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="n";
}

// Open file export.csv.
$f = fopen ('export.csv','w');

// Put all values from $out to export.csv. 
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>

[Alexhoward]

Cheers Litebearer,

 

That pulls out all the data into csv, and displays it in html.

 

What i'm really after is to download it into an excel file?

 

is this possible?

[freakus_maximus]

This will do the trick. I didnt write it, but it will do what you want. It takes the table and opens it directly into Excel.

 

<?

function xls_format_row( $field ) {
$line = '';
foreach($field as $value){
if(!isset($value) || $value == "") $value = "\t";
else{
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
return trim($line)."\n";
}


function xls_send( $headers, $rows ) {
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=excelfile.xls");
echo $headers."\n".$rows;
}


\\INCLUDE YOUR DB CONNECTION HERE

\\This will allow you to give proper names to your columns in Excel, typically you do not want the table column names.
\\Just add to it for all your columns you are exporting
$headers = ("column_A_name"."\t"."column_B_name"."\t"."column_C_name"."\t"); 

$result = mysql_query('SELECT * FROM yourtable');
$count = mysql_num_fields($result);

$data = array();
while($row = mysql_fetch_row($result)) {
array_push($data, $row);
}

$xls_rows = '';
foreach( $data as $row )
$xls_rows .= xls_format_row( $row );

$xls_rows = str_replace(array("</p><p>", "</li><li>"), ", ", $xls_rows);
$xls_rows = str_replace(" ", "", $xls_rows);

$xls_rows = strip_tags($xls_rows);

xls_send( $headers, $xls_rows );

?>

[Alexhoward]

Excellent,

 

Thanks!

 

This seems to do the job on recent browsers, but old ones are stuggling, or not exporting at all.

 

Also, excel is getting the error

 

"Unknown format - SYLK file"

 

On recent excels you just press ok and it works anyway, but on older ones it won't.

 

Do you know what's causing this?

 

Thanks again

[Alexhoward]

Alright!

 

I've sorted it!

 

basically you can't have "ID" in cell 'A1'

 

Thanks for all your help with this everyone!

 

And, thanks for the code freakus_maximus!

 

Nice one!!