php导出excel xml word
转载请注明来源：https://www.cnblogs.com/hookjc/
Excel:
<?php
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
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=11.xls ");
header("Content-Transfer-Encoding: binary ");
$filename="<table>
<tr>
<th>left_1</th>
<td>right_1</td>
</tr>
<tr>
<th>left_2</th>
<td>right_2</td>
</tr>
</table>";
//$filename=iconv("utf-8", "gb2312", $filename);
echo $filename;
?>
<?php
class Db {
var $conn;
/***************************************************************************
* 连接数据库
* return：MySQL 连接标识,失败返回FALSE
**************************************************************************/
function Db($host="localhost",$user="root",$pass="123456",$db="juren_gaokao") {
if(!$this->conn=mysql_connect($host,$user,$pass))
die("can't connect to mysql sever");
mysql_select_db($db,$this->conn);
mysql_query("SET NAMES 'UTF-8'");
}
/***************************************************************************
* 执行SQL查询
* return:查询结构集 resource
**************************************************************************/
function execute($sql) {
return mysql_query($sql,$this->conn);
}
/***************************************************************************
* 返回结构集中行数
* return：number 数字
**************************************************************************/
function findCount($sql) {
$result=$this->execute($sql);
return mysql_num_rows($result);
}
/***************************************************************************
* 执行SQL查询
* return：array 数组
**************************************************************************/
function findBySql($sql) {
$array=array();
$result=mysql_query($sql);
$i=0;
while($row=mysql_fetch_assoc($result)) {
$array[$i]=$row;
$i++;
}
return $array;
}
/***************************************************************************
*$con的几种情况
*空：返回全部记录
*array：eg. array('id'=>'1') 返回id=1的记录
*string :eg. 'id=1' 返回id=1的记录
* return：json 格式数据
***************************************************************************/
function toExtJson($table,$start="0",$limit="10",$cons="") {
$sql=$this->generateSql($table,$cons);
$totalNum=$this->findCount($sql);
$result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
$resultNum = count($result);//当前结果数
$str="";
$str.= "{";
$str.= "'totalCount':'?$totalNum',";
$str.="'rows':";
$str.="[";
for($i=0;$i<$resultNum;$i++) {
$str.="{";
$count=count($result[$i]);
$j=1;
foreach($result[$i] as $key=>$val) {
if($j<$count) {
$str.="'".$key."':'".$val."',";
}
elseif($j==$count) {
$str.="'".$key."':'".$val."'";
}
$j++;
}
$str.="}";
if ($i != $resultNum-1) {
$str.= ", ";
}
}
$str.="]";
$str.="}";
return $str;
}
/***************************************************************************
* $table:表名
* $cons:sql条件
* return:SQL语句
**************************************************************************/
function generateSql($table,$cons) {
$sql="";//sql条件
$sql="select * from ".$table;
if($cons!="") {
if(is_array($cons)) {
$k=0;
foreach($cons as $key=>$val) {
if($k==0) {
$sql.="where '";
$sql.=$key;
$sql.="'='";
$sql.=$val."'";
}else {
$sql.="and '";
$sql.=$key;
$sql.="'='";
$sql.=$val."'";
}
$k++;
}
}else {
$sql.=" where ".$cons;
}
}
return $sql;
}
/***************************************************************************
* $table:表名
* $cons：条件
* return：XML格式文件
**************************************************************************/
function toExtXml($table,$start="0",$limit="10",$cons="") {
$sql=$this->generateSql($table,$cons);
$totalNum=$this->findCount($sql);
$result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
$resultNum = count($result);//当前结果数
header("Content-Type: text/xml");
$xml='<?xml version="1.0" encoding="utf-8" ?>';
$xml.="<xml>";
$xml.="<totalCount>".$totalNum."</totalCount>";
$xml.="<items>";
for($i=0;$i<$resultNum;$i++) {
$xml.="<item>";
foreach($result[$i] as $key=>$val)
$xml.="<".$key.">".$val."</".$key.">";
$xml.="</item>";
}
$xml.="</items>";
$xml.="</xml>";
return $xml;
}
/***************************************************************************
* $table:表名
* $mapping：数组格式头信息$map=array('No','Name','Email','Age');
* $fileName：WORD文件名称
* return：WORD格式文件
**************************************************************************/
function toWord($table,$mapping,$fileName) {
header('Content-type: application/doc');
header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>'.$fileName.'</title>
</head>
<body>';
echo'<table border=1><tr>';
if(is_array($mapping)) {
foreach($mapping as $key=>$val)
echo'<td>'.$val.'</td>';
}
echo'</tr>';
$results=$this->findBySql('select * from '.$table);
foreach($results as $result) {
echo'<tr>';
foreach($result as $key=>$val)
echo'<td>'.$val.'</td>';
echo'</tr>';
}
echo'</table>';
echo'</body>';
echo'</html>';
}
/***************************************************************************
* $table:表名
* $mapping：数组格式头信息$map=array('No','Name','Email','Age');
* $fileName：Excel文件名称
* return：Excel格式文件
**************************************************************************/
function toExcel($table,$mapping,$fileName) {
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=".$fileName.".xls");
echo'<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
<head>
<meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name></x:Name>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple leftmargin=0 topmargin=0>';
echo'<table width="100%" border="0" cellspacing="0" cellpadding="0">';
echo'<tr>';
if(is_array($mapping)) {
foreach($mapping as $key=>$val)
echo'<td>'.$val.'</td>';
}
echo'</tr>';
$results=$this->findBySql('select * from '.$table);
foreach($results as $result) {
echo'<tr>';
foreach($result as $key=>$val)
echo'<td>'.$val.'</td>';
echo'</tr>';
}
echo'</table>';
echo'</body>';
echo'</html>';
}
function Backup($table) {
if(is_array ($table)) {
$str="";
foreach($table as $tab)
$str.=$this->get_table_content($tab);
return $str;
}else {
return $this->get_table_content($table);
}
}
/***************************************************************************
* 备份数据库数据到文件
* $table：表名
* $file：文件名
**************************************************************************/
function Backuptofile($table,$file) {
header("Content-disposition: filename=?$file.sql");//所保存的文件名
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");
if(is_array ($table)) {
$str="";
foreach($table as $tab)
$str.=$this->get_table_content($tab);
echo $str;
}else {
echo $this->get_table_content($table);
}
}
function Restore($table,$file="",$content="") {
//排除file，content都为空或者都不为空的情况
if(($file==""&&$content=="")||($file!=""&&$content!=""))
echo"参数错误";
$this->truncate($table);
if($file!="") {
if($this->RestoreFromFile($file))
return true;
else
return false;
}
if($content!="") {
if($this->RestoreFromContent($content))
return true;
else
return false;
}
}
//清空表，以便恢复数据
function truncate($table) {
if(is_array ($table)) {
$str="";
foreach($table as $tab)
$this->execute("TRUNCATE TABLE ?$tab");
}else {
$this->execute("TRUNCATE TABLE ?$table");
}
}
function get_table_content($table) {
$results=$this->findBySql("select * from $table");
$temp = "";
$crlf="rn";
foreach($results as $result) {
/*(";
foreach(?$result as ?$key=>?$val)
{
$schema_insert .= " `".?$key."`,";
}
$schema_insert = ereg_replace(",?$", "", ?$schema_insert);
$schema_insert .= ")
*/
$schema_insert = "INSERT INTO ?$table VALUES (";
foreach($result as $key=>$val) {
if($val != "")
$schema_insert .= " '".addslashes($val)."',";
else
$schema_insert .= "NULL,";
}
$schema_insert = ereg_replace(",?$", "", $schema_insert);
$schema_insert .= ");?$crlf";
$temp = $temp.$schema_insert ;
}
return $temp;
}
function RestoreFromFile($file) {
if (false !== ($fp = fopen($file, 'r'))) {
$sql_queries = trim(fread($fp, filesize($file)));
$this->splitMySqlFile($pieces, $sql_queries);
foreach ($pieces as $query) {
if(!$this->execute(trim($query)))
return false;
}
return true;
}
return false;
}
function RestoreFromContent($content) {
$content = trim($content);
$this->splitMySqlFile($pieces, $content);
foreach ($pieces as $query) {
if(!$this->execute(trim($query)))
return false;
}
return true;
}
function splitMySqlFile(&$ret, $sql) {
$sql= trim($sql);
$sql=split('',$sql);
$arr=array();
foreach($sql as $sq) {
if($sq!="");
$arr[]=$sq;
}
$ret=$arr;
return true;
}
}
