php导出mysql数据库中为excel代码
SQL数据库代码如下:
- 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.phpfensi.com'),
- (6, '搜狗', 'www.sogou.com'),
- (7, '必应', 'http://www.phpfensi.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;
- }
-
-
-
-
-
- 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));
- }
-
-
- }
-
-
-
-
-
- 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>
生成excel文件内容:
id websitename websiteurl
1 百度 http://www.baidu.com
5 google http://www.google.com
4 400电话 http://www.phpfensi.com
6 搜狗 www.sogou.com
7 必应 http://www.phpfensi.com
