以下为两种方式:
<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=
user.xls");
header("Pragma: no-cache");
header("Expires: 0");
$title = "数据库名:
user, 数据表: wyx_
user, 备份日期:" . date("Y-m-d H:i:s");
$conn=mysql_connect("localhost","root","123") or die("不能连接数据库");
mysql_select_db("wyx");
mysql_query("set names gbk");
echo '<table border="1" cellspacing="2" cellpadding="2" width="50%" align="center">';
// 输出标题
echo '<tr bgcolor="#cccccc"><td colspan="3" align="center">' . $title . '</td></tr>';
$query = "select * from wyx_user";
$result = mysql_query($query) or die(mysql_error());
$fields = mysql_num_fields($result);
// 输出字段名
echo '<tr bgcolor="blue">';
for($i = 0; $i < $fields; $i++) {
echo '<td>' . mysql_field_name($result, $i) . '</td>';
}
echo '</tr>';
// 输出内容
while($row = mysql_fetch_row($result)) {
echo '<tr>';
for($i = 0; $i<$fields; $i++) {
echo '<td>' . $row[$i] . '</td>';
}
echo '</tr>';
}
echo '</table>';
?>
这一种方式是将表中的数据以字段形式在excel中显示
另外一种方式是到处部分字段,显示名称也自定义:
<?php
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");;
header("Content-Disposition: attachment;filename=user.xls ");
header("Content-Transfer-Encoding: binary ");
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
function aa($a){
switch($a){
case 0:
return "有意向";
break;
case 1:
return "初级";
break;
case 2:
return "中级";
break;
case 3:
return "高级";
break;
case 4:
return "vip";
break;
default:
return "啊唷,出错了";
break;
}
}
function ab($b){
if($b==0){
return "未跟踪";
}
else {
return $b."号客服";
}
}
$conn=mysql_connect("localhost","root","123");
mysql_select_db("wyx");
mysql_query("set names gbk");
$sql = "select wyx_user_id,wyx_user_name,qq,email,mobile,sex,birthday,id_card,address,pay,wyx_user_type,wyx_user_flag,wyx_user_follow from wyx_user";
$query = mysql_query($sql);
// 向表中添加数据
xlsBOF();
xlsWriteLabel(0,0,"id");
xlsWriteLabel(0,1,"姓名");
xlsWriteLabel(0,2,"qq");
xlsWriteLabel(0,3,"邮箱");
xlsWriteLabel(0,4,"手机号");
xlsWriteLabel(0,5,"性别");
xlsWriteLabel(0,6,"生日");
xlsWriteLabel(0,7,"id_card");
xlsWriteLabel(0,8,"住址");
xlsWriteLabel(0,9,"帐号");
xlsWriteLabel(0,10,"类型");
xlsWriteLabel(0,11,"备注");
xlsWriteLabel(0,12,"跟踪客服");
$xlsRow = 1;
while($array = mysql_fetch_array($query)) {
++$i;
xlsWriteNumber($xlsRow,0,"$i");
xlsWriteNumber($xlsRow,0,"$array[0]");
xlsWriteLabel($xlsRow,1,"$array[1]");
xlsWriteLabel($xlsRow,2,"$array[2]");
xlsWriteLabel($xlsRow,3,"$array[3]");
xlsWriteLabel($xlsRow,4,"$array[4]");
xlsWriteLabel($xlsRow,5,"$array[5]");
xlsWriteLabel($xlsRow,6,"$array[6]");
xlsWriteLabel($xlsRow,7,"$array[7]");
xlsWriteLabel($xlsRow,8,"$array[8]");
xlsWriteLabel($xlsRow,9,"$array[9]");
xlsWriteLabel($xlsRow,10,aa("$array[10]"));
xlsWriteLabel($xlsRow,11,"$array[11]");
xlsWriteLabel($xlsRow,12,ab($array[12]));
$xlsRow++;
}
xlsEOF();
exit();
?>