Results 1 to 2 of 2

Thread: Download MySQL Data To Excel

  1. #1
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Download MySQL Data To Excel

    I have no issue in getting my data to download into MySQL, but can you

    A) Style the info? So like make a certain font and size?

    B) It isn't displaying my table column names as the heading any clues?

    This is what I have

    PHP Code:
    <?php
    include 'library/config.php';
    include 
    'library/opendb.php';

    $query  "SELECT * FROM tbl WHERE `Member`='A'";
    $result mysql_query($query) or die('Error, query failed');

    $tsv  = array();
    $html = array();
    while(
    $row mysql_fetch_array($resultMYSQL_NUM))
    {
       
    $tsv[]  = implode("\t"$row);
       
    $html[] = "<tr><td>" .implode("</td><td>"$row) .              "</td></tr>";
    }

    $tsv implode("\r\n"$tsv);
    $html "<table>" implode("\r\n"$html) . "</table>";

    $fileName 'mysql-to-excel.xls';
    header("Content-type: application/vnd.ms-excel"); 
    header("Content-Disposition: attachment; filename=$fileName");

    echo 
    $tsv;
    //echo $html;

    include 'library/closedb.php';
    ?>

  2. #2
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    A long time ago I made as a small project a script that exports MySQL Data and outputs to an excel files and I use it now in all my PHP based programs for exporting to Excel.

    Heres my code for everyone to use:

    PHP Code:
    <?PHP

    include('include/config.php');
    include(
    'include/opendb.php');

    $result mysql_query('SELECT * FROM tbl WHERE `Member`=\'A\'');
    $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";
    //}

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

    # replace $dbname.xls with whatever you want the filename to default to
    # Default $dbname uses the Database name you are exporting from
    header("Content-Disposition: attachment; filename=$dbname.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo 
    $header."\n".$data

    ?>
    Ryan
    Sevierville, TN

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •