Export php-mysql records into excel sheet

To export php-mysql records into excel sheet and download it follow these simple steps.
  1. Make database connection.
  2. Read the headers (fieldname of your table) and store in a variable.
  3. Read records one by one and store it in another variable.
  4. Set the file name to be downloaded.
  5. Set appropriate http header types to write data on excel sheet and download it.
The code is given here.

 <?php
 ob_start();
 mysql_connect('localhost','username','password');
 mysql_select_db('dbname');
 $sql = "SELECT * from tablename";
 $res = mysql_query($sql) or die();
 $count = mysql_num_fields($res);

 // fetch table header from database
 $header = '';
 for ($i = 0; $i < $count; $i++){
    $header .= mysql_field_name($res, $i)."\t";
    }

 // fetch data each row, store on tabular row data
 while($row = mysql_fetch_row($res)){
   $line = '';
   foreach($row as $value){
   if(!isset($value) || $value == ""){
     $value = "\t";
   }else{
     $value = str_replace('"', '""', $value);
     $value = '"' . $value . '"' . "\t";
   }
   $line .= $value;
   }
   $data .= trim($line)."\n";
   $data = str_replace("\r", "", $data);
  }
 
 $name=date('d-m-y').'-list.xls';
 header("Content-type:application/vnd.ms-excel;name='excel'");
 header("Content-Disposition: attachment; filename=$name");
 header("Pragma: no-cache");
 header("Expires: 0");

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

Export php-mysql records into excel sheet Export php-mysql records into excel sheet Reviewed by Altaf Hussain on 5:11 PM Rating: 5

15 comments:

  1. thank you for ur great code!!! it useful for me!!!
    Dhaval Rajani

    ReplyDelete
  2. Thank u so much Altaf Hussain ........

    ReplyDelete
  3. thankyou very much for your useful code

    ReplyDelete
  4. It work´s!!!! perfect, thank you for you easy and clear solution

    ReplyDelete
  5. but it in the same row and column how can export with on data store in on row or column

    ReplyDelete
  6. it is exporting only the last row of the table. Please correct the code and post the new one.

    ReplyDelete
  7. Hey thanks for the code. However I am getting this warning:

    "Cannot modify header information - headers already sent"

    for the following piece of code:

    header("Content-type:application/vnd.ms-excel;name='excel'");
    header("Content-Disposition: attachment; filename=$name");
    header("Pragma: no-cache");
    header("Expires: 0");

    ReplyDelete
  8. khossain89@yahoo.comJune 24, 2013 at 2:57 PM

    i prefer to do it from phpmyadmin. just export the table as csv format from phpmyadmin and open it from excel. simple!

    ReplyDelete
  9. Could you advice regading while($row = mysql_fetch_row($res)){
    Where $row value is set?

    ReplyDelete
  10. The first instance of $data is concatenated.

    $data .= trim($line)."\n";

    Shouldn't there be an instance of $data before this??
    When I try to run this I'm getting an error that $data is undefined on that line. The period means that it is concatenating to an early instance of that variable..... So where is the earlier instance?

    Seems like a line is missing

    ReplyDelete
  11. $result1=executeQuery("select s.stdname,s.emailid,IFNULL((select sum(q.marks) from studentquestion as sq,question as q where q.qnid=sq.qnid and sq.testid=".$_REQUEST['testid']." and sq.stdid=st.stdid and sq.stdanswer=q.correctanswer),0) as om from studenttest as st, student as s where s.stdid=st.stdid and st.testid=".$_REQUEST['testid'].";" );


    I would like to execute these rows .
    Please help me :(
    But it wont give me the data ;( PLEASE HELP ASAP PLS

    ReplyDelete
  12. How can i download two tables in same excel sheet?

    ReplyDelete

Altaf Web. Powered by Blogger.