Alexhoward Posted May 22, 2008 Share Posted May 22, 2008 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 Quote Link to comment Share on other sites More sharing options...
litebearer Posted May 22, 2008 Share Posted May 22, 2008 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'); ?> Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted May 22, 2008 Author Share Posted May 22, 2008 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? Quote Link to comment Share on other sites More sharing options...
freakus_maximus Posted May 22, 2008 Share Posted May 22, 2008 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 ); ?> Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted May 23, 2008 Author Share Posted May 23, 2008 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 Quote Link to comment Share on other sites More sharing options...
Alexhoward Posted May 23, 2008 Author Share Posted May 23, 2008 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!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.