php匯出任意mysql資料庫中的表去excel檔案

最後更新：2018-12-07 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

簡介：這是php匯出任意mysql資料庫中的表去excel檔案的詳細頁面，介紹了和php,有關的知識、技巧、經驗，和一些php源碼等。

class='pingjiaF' frameborder='0' src='http://biancheng.dnbcw.info/pingjia.php?id=337541' scrolling='no'>

測試建表指令碼

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>

運行畫面

產生excel檔案內容

id	websitename	websiteurl
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

愛J2EE關注Java邁克爾傑克遜視頻站JSON線上工具

http://biancheng.dnbcw.info/php/337541.html pageNo:9

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More