dougudu3564
2015-06-18 03:49
浏览 46
已采纳

如何使用PHP将MySQL表导出到Excel?

I am currently developing an App for my school to record Class Cleanliness Results for each class, so I need to convert the results collated in MySQL table into Microsoft Excel using PHP, preferably also able to be opened by a Android OS Phone.

I used the following PHP code:

<?PHP

$mysqli_user = "(user)";
$mysqli_password = "(password)";
$mysqli_host = "(host)";
$mysqli_database = "(database)";


  $filename = "grading_results_" . time() . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

$link = mysqli_connect($mysqli_host,$mysqli_user,$mysqli_password,$mysqli_database);
$query = 'SELECT * FROM (table_name)';

$result = mysqli_query($link, $query);

while ($row = mysqli_fetch_row($result)){
 print implode("\t", $row) . "
";
}
mysqli_close($link);
?>

This is how my table looks like in phpMyAdmin:

https://www.dropbox.com/s/7pr3gh06zta5d8u/Snip20150618_2.png?dl=0

This is how the Excel file looks like after I used this code to convert.

https://www.dropbox.com/s/571m9lfj64tklpc/Snip20150618_3.png?dl=0

Why is there no columns and rows? I need the Excel file to be exactly the same formatting and style as the table in phpMyAdmin. Can anyone help edit my code instead of providing me a brand new code?

Thanks in advance for your answers!

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douyan2470 2015-06-18 03:54
    已采纳

    Manual

    1. Run tour localhost and log in to phpMyAdmin
    2. Click on your database then click on the table which you want to get Excel.
    3. image

    then

    1. enter image description here
    2. then press GO button

    With Code

    define ("DB_HOST", "localhost");
    define ("DB_USER", "root");
    define ("DB_PASS","");
    define ("DB_NAME","DATABASE_NAME");
    
    $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
    $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
    

    then

    $setCounter = 0;
    
    $setExcelName = "download_excal_file";
    
    $setSql = "YOUR SQL QUERY GOES HERE";
    
    $setRec = mysql_query($setSql);
    
    $setCounter = mysql_num_fields($setRec);
    
    for ($i = 0; $i < $setCounter; $i++) {
        $setMainHeader .= mysql_field_name($setRec, $i)."\t";
    }
    
    while($rec = mysql_fetch_row($setRec))  {
      $rowLine = '';
      foreach($rec as $value)       {
        if(!isset($value) || $value == "")  {
          $value = "\t";
        }   else  {
    //It escape all the special charactor, quotes from the data.
          $value = strip_tags(str_replace('"', '""', $value));
          $value = '"' . $value . '"' . "\t";
        }
        $rowLine .= $value;
      }
      $setData .= trim($rowLine)."
    ";
    }
      $setData = str_replace("", "", $setData);
    
    if ($setData == "") {
      $setData = "no matching records found";
    }
    
    $setCounter = mysql_num_fields($setRec);
    
    
    
    //This Header is used to make data download instead of display the data
     header("Content-type: application/octet-stream");
    
    header("Content-Disposition: attachment; filename=".$setExcelName."_Report.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    
    //It will print all the Table row as Excel file row with selected column name as header.
    echo ucwords($setMainHeader)."
    ".$setData."
    ";
    

    More About Code

    已采纳该答案
    打赏 评论
  • dongyin8991 2015-06-18 05:57
    SELECT id, name, email INTO OUTFILE '/tmp/ram.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY ‘\\’
    LINES TERMINATED BY '
    '
    FROM users WHERE id=1
    

    Here /tmp/ is address where u want to store the csv

    打赏 评论
  • doudang9147 2018-02-23 13:27
      <?php
        header( "Content-Type: application/vnd.ms-excel" );
        header( "Content-disposition: attachment; filename=spreadsheet.xls" );
    
        // print your data here. note the following:
        // - cells/columns are separated by tabs ("\t")
        // - rows are separated by newlines ("
    ")
    
        // for example:
        echo 'First Name' . "\t" . 'Last Name' . "\t" . 'Phone' . "
    ";
        echo 'John' . "\t" . 'Doe' . "\t" . '555-5555' . "
    ";
    ?>
    

    // You can fetch the data from the database and display in the table. Then put the table in echo to get Excel file.

    打赏 评论

相关推荐 更多相似问题