php mysql数据导出到excel文件

这篇文章主要为大家详细介绍了php mysql数据导出到excel文件，具有一定的参考价值，可以用来参考一下。

<?php
/**
 * mysql数据导出到excel文件
 *
 * @param 
 * @arrange （www.idcnote.com）
 **/
$dbhost = "127.0.0.1:3306";
$dbuser = "XXXXX";
$dbpass = "XXXXX";
$dbname = "XXXXX";
$dbtable = "XXXXX";

// END CHANGING STUFF


//我们要做的第一件事就是写一些函数来写出来
//和excel文件。 这些函数做了一些十六进制写作，老实说我得到了
//他们来自其他地方，但是嘿它有效，所以我不打算质疑它
//只是重用


// This one makes the beginning of the xls file
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}

// This one makes the end of the xls file
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}

// this will write text in the cell you specify
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}

// make the connection an DB query
$dbc = mysql_connect( $dbhost , $dbuser , $dbpass ) or die( mysql_error() );
mysql_select_db( $dbname );
$q = "SELECT * FROM ".$dbtable."";
$qr = mysql_query( $q ) or die( mysql_error() );


// Ok now we are going to send some headers so that this
// thing that we are going make comes out of browser
// as an xls file.
//
header("Pragma: public");
header("Expires: 0");
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/download");

//this line is important its makes the file name
header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");

header("Content-Transfer-Encoding: binary ");

// start the file
xlsBOF();

// these will be used for keeping things in order.
$col = 0;
$row = 0;

// This tells us that we are on the first row
$first = true;

while( $qrow = mysql_fetch_assoc( $qr ) )
{
// Ok we are on the first row
// lets make some headers of sorts
if( $first )
{
foreach( $qrow as $k => $v )
{
// take the key and make label
// make it uppper case and replace _ with ' '
xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
$col++;
}

// prepare for the first real data row
$col = 0;
$row++;
$first = false;
}

// go through the data
foreach( $qrow as $k => $v )
{
// write it out
xlsWriteLabel( $row, $col, $v );
$col++;
}
// reset col and goto next row
$col = 0;
$row++;
}

xlsEOF();
exit();


/*** 来自：php教程（www.idcnote.com） ***/ 
?>

注：关于php mysql数据导出到excel文件的内容就先介绍到这里，更多相关文章的可以留意