php mysql数据导出到excel文件

前端之家收集整理的这篇文章主要介绍了php mysql数据导出到excel文件前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
感兴趣的小伙伴,下面一起跟随编程之家 jb51.cc的小编来看看吧。
经测试代码如下:

<?PHP
/**
 * MysqL数据导出到excel文件
 *
 * @param 
 * @arrange (512.笔记) jb51.cc
 **/
$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);
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,$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,$v );
$col++;
}
// reset col and goto next row
$col = 0;
$row++;
}

xlSEOF();
exit();


/*** 来自:编程之家 jb51.cc(jb51.cc) ***/ 
?>
原文链接:https://www.f2er.com/php/528802.html

猜你在找的PHP相关文章