php 将mysql数据导出成表格形式

来源：互联网发布：2017网络流行语有哪些编辑：程序博客网时间：2024/04/30 16:16

以下为两种方式：

<?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();