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） ***/ 
?>

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!