php导出mysql数据库中为excel代码

时间：2015-01-23 来源：西部数据作者：西部数据

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;

}

/**

*添加头部信息

* @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>