php下 导出数据库的内容到execl中

1. 导出数据库的内容到xls或xils格式的文件中
这个需要一个类的支持,我们命名为phpexecl.class.php,创建这个文件，里面的大致内容如下：

class Excel_XML
{
private $header = "\n";
private $footer = "";
private $lines = array();
private $sEncoding;
private $bConvertTypes;
private $sWorksheetTitle;
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
{
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}
public function setEncoding($sEncoding)
{
$this->sEncoding = $sEncoding;
}
public function setWorksheetTitle ($title)
{
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
$title = substr ($title, 0, 31);
$this->sWorksheetTitle = $title;
}
private function addRow ($array)
{
$cells = "";
foreach ($array as $k => $v):
$type = 'String';
if ($this->bConvertTypes === true && is_numeric($v)):
$type = 'Number';
endif;
$v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
$cells .= "" . $v . "\n";
endforeach;
$this->lines[] = "\n" . $cells . "\n";
}
public function addArray ($array)
{
foreach ($array as $k => $v)
$this->addRow ($v);
}
public function generateXML ($filename = 'excel-export')
{
$filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);

header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

echo stripslashes (sprintf($this->header, $this->sEncoding));
echo "\nsWorksheetTitle . "\">\n\n";
foreach ($this->lines as $line)
echo $line;

echo "\n\n";
echo $this->footer;
}

}
?>
然后创建文件引用类文件实现数据导入到execl的文件中，命名为name.php(名字可随意)，内容大致如下：

$conn=mysql_connect("hostname","username","password");
mysql_select_db("db_name",$conn);
mysql_query("set names gb2312");
$result=mysql_query("select a.book_id as book_id,book_title,start_chapter_id,syn_flg,del_flg,end_status,qqbook_id,update_status,chapter_num,upload_count from table_a as a left join table_b as b on a.book_id=b.book_id order by update_time desc");
$nums=mysql_num_rows($result);//连接数据库，并调用内容。
require_once './phpexecl.class.php';
 $data = array(
1 => array ('序号','书号','书名','起始章节序号',"是否同步","同步状态","作品状态","是否完本","总章节数","每天同步章节数","  腾讯书号  "),//标题使用中文则到表格里面值为空，待解决
);
while($row=mysql_fetch_array($result))
{
if($row['syn_flg'] ==1){
$sys_flg="是";
}else{
$sys_flg="否";
}
if($row['syn_flg'] ==1 && $row['del_flg'] ==0){
if($row['end_status'] ==1){
$status="上传完毕";
}else{
$status="上传中";
}
}else{
$status="--";
}
if($row['del_flg'] ==1){
$del_flg="无数据";
}else{
$del_flg="正常";
}
if($row['update_status'] ==1){
$is_over="连载";
}else if($row['update_status'] ==2){
$is_over="完本";
}else{
$is_over="暂更";
}
array_push($data,array($nums,$row['book_id'], $row['book_title'], $row['start_chapter_id'],$sys_flg,$status,$del_flg,$is_over,$row['chapter_num'],$row['upload_count'], $row['qqbook_id']));
$nums--;
}
$name="同步列表uplist_".date("Ymd",time());
// generate file (constructor parameters are optional)
$xls = new Excel_XML('gb2312', false, 'My Test Sheet');
$xls->addArray($data);
$xls->generateXML($name);

//echo $data;

?>

2.导出到csv格式的execl文件中实例，可以根据实际情况修改：

创建文件test.php,内容为：

/**
 *PHP导出mysql数据库数据为csv文件
 *从http://isblog.blog.163.com/blog/static/7241675200910229615381/导出为xsl文件修改
 */

$DB_Server = "host_name";
$DB_Username = "username";
$DB_Password = "password";
$DB_DBName = "dbname";
$DB_TBLName = "TABLE_name";
$sql = "Select * from ".$DB_TBLName;
$Connect = mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
mysql_query("set names 'gb2312'");
$savename = date("Ymd")."_dateinfo";
$file_type = "vnd.ms-excel";
$file_ending = "csv";
set_header($savename,$file_type,$file_ending);

$now_date = date("Y-m-j H:i:s");


$ALT_Db = mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
$result = mysql_query($sql,$Connect) or die(mysql_error());
$sep = ",";
$header = '';
   for ($i = 0; $i < mysql_num_fields($result); $i++) {
    $header .= mysql_field_name($result,$i) . $sep;
}
echo ("$header \n");
$i = 0;
while($row = mysql_fetch_array($result)) {
    $schema_insert = "";
    //for($j=1; $j
        if(!isset($row[$j])){
            $schema_insert .= "NULL".$sep;
        }elseif ($row[$j] != ""){
            $schema_insert .= "$row[month],$row[book_name],$row[worth],";
        }else{
            $schema_insert .= "".$sep;
        }
    //}
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    echo trim($schema_insert) ;
    echo "\n";
    $i++;
}

return true;

/**
 * 聲明頭部信息
 * @param string $savename
 * @param string $file_type
 * @param string $file_ending
*/
function set_header($savename,$file_type,$file_ending){
    header("Content-Type: application/$file_type");
    header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: no-cache");
    header("Expires: 0");
    header("Pragma:public");
}
?>