PHP特區 : [分享]如何使用php將mysql的查詢結果導出為excel

<?php
// define database parameter
$dbHost = "192.168.OOO.XXX";  //資料庫IP
$dbUsername = "OOXX";         //資料庫連線帳號
$dbPassword = "OOXXOOXXOOXX"; //資料庫連線密碼
$dbDbName = "OOXX";           //資料庫名稱
$dbTablename = "OOOXXX";      //資料表名稱

// connection to the database
$Connect = mssql_connect($dbHost, $dbUsername, $dbPassword)
    or die("Couldn't connect to SQL Server on $dbHost");

// select a database to work with
$selected = mssql_select_db($dbDbName, $Connect)
    or die("Couldn't open database $dbDbName");
?>

<?php
// set header infomation
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=$dbTablename.$file_ending");
header("Content-Transfer-Encoding: binary ");
header("Pragma: no-cache");
header("Expires: 0");
?>

<?php
// export data to excel
$sql = 'SELECT * FROM ' . $dbTablename . ' WHERE (type IS NOT NULL) ORDER BY sn ;';
//$ALT_Db = @mysql_select_db($dbDbName, $Connect) or die("Couldn't select database");
//$result = @mysql_query($sql,$Connect) or die(mysql_error());
$result = @mssql_query($sql,$Connect) or die(mysql_error());
?>

<?php
$sep = "\t";
$title2Write = array('產品編號' , '產品序號' , '產品類別' , '產品名稱' , '產品單位' , '產品單價' , '最低訂量' );
for ( $i = 0 ; $i < 7 ; $i++ ) {
	echo iconv( "UTF-8" , "big5" , $title2Write[$i] ) . "\t";
}
print("\n");
?>

<?php
$i = 0;
while($row = mssql_fetch_row($result)) {
    $schema_insert = "";
    for( $j = 0 ; $j < mssql_num_fields($result) ; $j++ ) {
        if(!isset($row[$j]))
            $schema_insert .= "NULL".$sep;
        elseif ($row[$j] != "")
            $schema_insert .= "$row[$j]".$sep;
        else
            $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
    $i++;
}?>

<script language="javascript">
	window.opener=null;
	window.open("","_self");
	window.close();
</script>