PHP 未以正确的格式导出 MySQL 数据库

Question

所以我在将数据从 MySQL 导出到 Excel 时遇到问题

    $output = '';  
 if(isset($_POST["export_excel"]))  
 {  
      $sql = "SELECT * FROM Logs ORDER BY item";  
      $result = mysqli_query($connect, $sql);  
      if(mysqli_num_rows($result) > 0)  
      {  
           $output .= '  
                <table class="table bordered="1">  
                     <tr>  
                          <th>Sort</th>  
                          <th>Unit Size</th>  
                          <th>Quantity</th>  
                          <th>Price per Unit</th>  
                          <th>Time</th>  
                     </tr>  
           ';  
           while($row = mysqli_fetch_array($result))  
           {  
                $output .= '  
                     <tr>  
                          <td>'.$row["Sort"].'</td>  
                          <td>'.$row["Unit Size"].'</td>  
                          <td>'.$row["Quantity"].'</td>  
                          <td>'.$row["Price per Unit"].'</td>  
                          <td>'.$row["Time"].'</td>  
                     </tr>  
                ';  
           }  
           $output .= '</table>';  
           header("Content-Type: application/xls");   
           header("Content-Disposition: attachment; filename=download.xls");  
           echo $output;  
      }  
 } 

因此，当我点击 index.php 页面上的导出按钮时，它会以以下格式输出数据:Google Sheets Link to the excel file

<table class="table bordered="1">   
<tr>    
<th>Sort</th>   
<th>Unit Size</th>  
<th>Quantity</th>   
<th>Price per Unit</th> 
<th>Time</th>   
</tr>   

<tr>    
<td></td>   
<td>45</td> 
<td>0</td>  
<td>0</td>  
<td>2016-08-11 16:53:12</td>    
</tr>   

<tr>    
<td></td>   
<td>6</td>  
<td>0</td>  
<td>0</td>  
<td>2016-08-11 16:53:12</td>    
</tr>   
</table>    

所以它输出了正确的数据，但只是格式被关闭，这是它在index.php页面上的样子: What the excel file should look like如果有人能告诉我我做错了什么那就太好了!预先感谢您!

Answer 1

也许输出为 csv 有点作弊，而不是正确的 xls，但它或多或少应该可以工作。

<?php

    if( isset( $_POST["export_excel"] ) ) {

        ob_clean();

        $sql = 'select * from logs order by item';
        $result = mysqli_query( $connect, $sql );  
        if( mysqli_num_rows( $result ) > 0){


            $delimiter=',';
            $enclosure='"';

            /* rather than create an actual file, use an output buffer and write to that */
            $output=fopen('php://output','w+');

            /* add column headers */
            $headers=array( 'Unit Size', 'Quantity', 'Price_per_Unit', 'Time' );

            /* write the headers to the output stream */
            fputcsv( $output,$headers, $delimiter, $enclosure );

            /* loop through recordset and add that to the stream */
            while( $row = mysqli_fetch_array( $result ) ) {
                fputcsv( $output, $row, $delimiter, $enclosure );
            }

            fclose( $output );

            /* set the headers accordingly */
            header('Content-Type: application/csv');
            header('Content-Disposition: attachment; filename=download.csv');

            /* send the new content */
            ob_flush();

        }
    }

?>