这篇文章主要为大家详细介绍了php mysql数据导出到excel文件,具有一定的参考价值,可以用来参考一下。
感兴趣的小伙伴,下面一起跟随512笔记的小编罗X来看看吧。
经测试代码如下:
/**
* mysql数据导出到excel文件
*
* @param
* @arrange (512.笔记)
**/
$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();
/*** 来自:512笔记() ***/
?>
注:关于php mysql数据导出到excel文件的内容就先介绍到这里,更多相关文章的可以留意512笔记的其他信息。
关键词:excel
您可能感兴趣的文章
如果觉得《php mysql 导出到excel php mysql数据导出到excel文件》对你有帮助,请点赞、收藏,并留下你的观点哦!