php导担任意mysql数据库中的表去excel文件
日期：2014-05-16 浏览次数：20493 次
测试建表脚本
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- 数据库: `mysqlnew`
--
-- --------------------
--
-- 表的结构 `test`
--
CREATE TABLE IF NOT EXISTS `test` (
? `id` int(10) NOT NULL auto_increment,
? `websitename` varchar(200) character set utf8 NOT NULL,
? `websiteurl` varchar(200) character set utf8 NOT NULL,
? PRIMARY KEY? (`id`)
) ENGINE=MyISAM? DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- 导出表中的数据 `test`
--
INSERT INTO `test` (`id`, `websitename`, `websiteurl`) VALUES
(1, '百度
', 'http://www.baidu.com
'),
(5, 'google
', 'http://www.google.com
'),
(4, '400电话
', 'http://www.my400800.cn
'),
(6, '搜狗
', 'www.sogou.com
'),
(7, '必应
', 'http://www.bing.com
');
php 文件代码
<?php
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;
}
/**
?*添加头部信息
?* @param <type> $rsRec
?*/
function addheader($rsRec) {
??? $fieldLen = mysql_num_fields($rsRec);
??? xlsBOF();
??? $iCell = 0;
??? for($i=0;$i<$fieldLen;$i++) {
??????? $fieldname = mysql_field_name($rsRec, $i);
??????? xlsWriteLabel(0,$iCell++, iconv("utf-8", "gb2312", $fieldname));
??? }
}
/**
?*添加记录信息
?* @param <type> $rsRec
?*/
function addData($rsRec) {
??? $xlsRow=1;
??? $fieldLen = mysql_num_fields($rsRec);
??? while($rsone=mysql_fetch_object($rsRec)) {
??????? $iCell = 0;
??????? for($i=0;$i<$fieldLen;$i++) {
??????????? $fieldname = mysql_field_name($rsRec, $i);
??????????? $fieldnameValue = $rsone->$fieldname;
??????????? xlsWriteLabel($xlsRow,$iCell++,iconv("utf-8", "gb2312", $fieldnameValue));
??????? }
??????? $xlsRow++;
??? }
??? xlsEOF();
??? exit;
}
if(isset($_POST["tableName"])) {
??? $con = mysql_connect("localhost", "root", "vertrigo");
??? $result=mysql_db_query("mysqlnew","select * from ".$_POST["tableName"]);
// 文件头
??? 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=test.xls ");
??? header("Content-Transfer-Encoding: binary ");
// 向表中添加数据
??? addheader($result) ;
??? addData($result) ;
??? mysql_close($con);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
??? <head>
??????? <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
??????? <title>excel信息导出</title>
??? </head>
??? <body>
??????? <div>
??????????? <form name="form1" method="post" action="" onSubmit="">
??????????????? <input type="text" name="tableName" value="" />
??????????????? <br>
???????????????
??????????????? <input class="ccc" name="" type="submit" value="提交" />
??????????? </form>
??????? </div>
??? </body>
</html>
运行画面
