php - 将值导出为 Excel 文件格式

Question

您好，我在将值从数组导出到 Excel 时遇到问题，例如我该怎么做导出以下内容:

echo "<table ' border='1' >"
    echo "<th>ComputerName</th>"));
    echo "<th>SerialNumber</th>";
    echo "<th>SystemModel</th>";
    echo "<th>DateTime</th>";
    echo "<th>Software</th>";
    echo "<th>Hardware</th>";
    echo "<th>Support</th>";
    echo "<th>Delete</th>";
    echo "</tr>";

$alt_color = 0;
while($row = mysql_fetch_array($result))

  {
    error_reporting (E_ALL ^ E_NOTICE);
    //foreach( $array_values as $value )
 $bgc = ( $alt_color % 2 ? 'Dark' : 'Light' );
     echo "<tr class=".$bgc.">";
    //echo "<td><a href=\"update.php?LSUserID=" .$row['LSUserID']."\">" .$row['LSUserID']."</a></td>";
    echo "<td><a href=\"update.php?LSUserID=" .$row['LSUserID']."\">" .$row['ComputerName']."</a></td>";
    echo "<td>" . $row['SerialNumber'] . "</td>";
    echo "<td>" . $row['SystemModel'] . "</td>";
    echo "<td>" . $row['DateTime'] . "</td>";   

Answer 1

您需要适当的 header (下面的 header 来自 PHP 文档中的 example)

$export = "my_name.xls";  

header('Pragma: public'); 
/////////////////////////////////////////////////////////////
// prevent caching....
/////////////////////////////////////////////////////////////
// Date in the past sets the value to already have been expired.
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");    
header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT'); 
header('Cache-Control: no-store, no-cache, must-revalidate');     // HTTP/1.1 
header('Cache-Control: pre-check=0, post-check=0, max-age=0');    // HTTP/1.1 
header ("Pragma: no-cache"); 
header("Expires: 0"); 
/////////////////////////////////////////////////////////////
// end of prevent caching....
/////////////////////////////////////////////////////////////
header('Content-Transfer-Encoding: none'); 
// This should work for IE & Opera
header('Content-Type: application/vnd.ms-excel;');  
 // This should work for the rest
header("Content-type: application/x-msexcel");     
header('Content-Disposition: attachment; filename="'.basename($export).'"'); 

之后，您的代码应该可以进行一些修改(Excel 可以读取 HTML 结构):

error_reporting (E_ALL ^ E_NOTICE); // you shouldn't have that in the loop.
                                    // you actually should put this first
echo "<table >"
    // the rest of your table opening code.

$alt_color = 0;
while($row = mysql_fetch_array($result))
{
     echo "<tr>";
     // the rest of your columns.
     echo "</tr>";
}
echo "</table>";

如果由于某种原因这不起作用，那么您可以创建一个 CSV，但您需要担心转义它:

// add all of the headers.
echo '"ComputerName","SerialNumber","SystemModel",'; //etc for all columns.

// row makes sure that there is only one set of values.
$row = mysql_fetch_row($result);
if( !$row ) die(); // no value found. exit.
echo getCSVRow( $row );
do // do...while lets us handle the \n more gracefully.
{
    echo "\n". getCSVRow( $row );
} while ($row = mysql_fetch_row($result));

function getCSVRow( array $row )
{
    $ret = "";
    foreach( $row as $val )
        $ret .= '"' . escapeCSV( $val ) . '",';
    return sustr( $ret, 0, strlen( $ret ) ); // clear the tailing comma
}

function escapeCSV( $str )
{
    return str_replace( '"', '\"', $str );
}