转自：http://www.phpchina.com/bbs/thread-59181-1-1.html
本类实现：
数据库信息导出：word，excel，json，xml，sql数据库恢复：从sql，从文件
具体用法:
首先新建测试用数据库mytest，然后在里面建张表
1 --
2 -- 表的结构 `test`
3 --
4 CREATE TABLE `test` (
5 `id` int(11) NOT NULL auto_increment,
6 `name` varchar(100) NOT NULL,
7 `email` varchar(200) NOT NULL,
8 `age` int(3) NOT NULL,
9 PRIMARY KEY (`id`)
10 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
11 --
12 -- 导出表中的数据 `test`
13 --
14 INSERT INTO `test` (`id`, `name`, `email`, `age`) VALUES
15 (1, 'pjq518', [email=]'pjq518@126.com'[/email], 22),
16 (2, 'xiaoyu', [email=]'xiaoyu@126.com'[/email], 21);
17
2 -- 表的结构 `test`
3 --
4 CREATE TABLE `test` (
5 `id` int(11) NOT NULL auto_increment,
6 `name` varchar(100) NOT NULL,
7 `email` varchar(200) NOT NULL,
8 `age` int(3) NOT NULL,
9 PRIMARY KEY (`id`)
10 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
11 --
12 -- 导出表中的数据 `test`
13 --
14 INSERT INTO `test` (`id`, `name`, `email`, `age`) VALUES
15 (1, 'pjq518', [email=]'pjq518@126.com'[/email], 22),
16 (2, 'xiaoyu', [email=]'xiaoyu@126.com'[/email], 21);
17
1.导出ext能方便调用的json
1 $db=new db();
2 echo $db->toExtJson('test');
3 //输出结果为
4 //{'totalCount':'2','rows':[{'id':'1','name':'pjq518','email':'pjq518@126.com','age':'22'},{'id':'2','name':'xiaoyu','email':'xiaoyu@126.com','age':'21'}]}
2 echo $db->toExtJson('test');
3 //输出结果为
4 //{'totalCount':'2','rows':[{'id':'1','name':'pjq518','email':'pjq518@126.com','age':'22'},{'id':'2','name':'xiaoyu','email':'xiaoyu@126.com','age':'21'}]}
toExtJson($table,$start="0",$limit="10",$cons="")有4个参数，$table为表名，$cons为条件，可以为string或array
2导出xml
1 $db=new db();
2 echo $db->toExtXml('test');
3
2 echo $db->toExtXml('test');
3
//输出结果
3导出excel和word
1 $db=new db();
2 //toExcel
3 $map=array('No','Name','Email','Age');//表头
4 $db->toExcel('test',$map,'档案');
5 //导出word表格
6 //$db->toWord('test',$map,'档案');
2 //toExcel
3 $map=array('No','Name','Email','Age');//表头
4 $db->toExcel('test',$map,'档案');
5 //导出word表格
6 //$db->toWord('test',$map,'档案');
1 <?php
2 class Db
3 {
4 var $conn;
5 function Db($host="localhost",$user="root",$pass="",$db="mytest")
6 {
7 if(!$this->conn=mysql_connect($host,$user,$pass))
8 die("can't connect to mysql sever");
9 mysql_select_db($db,$this->conn);
10 mysql_query("SET NAMES 'UTF-8'");
11 }
12 function execute($sql)
13 {
14 return mysql_query($sql,$this->conn);
15 }
16 function findCount($sql)
17 {
18 $result=$this->execute($sql);
19 return mysql_num_rows($result);
20 }
21 function findBySql($sql)
22 {
23 $array=array();
24 $result=mysql_query($sql);
25 $i=0;
26 while($row=mysql_fetch_assoc($result))
27 {
28 $array[$i]=$row;
29 $i++;
30 }
31 return $array;
32 }
33 //$con的几种情况
34 //空：返回全部记录
35 //array：eg. array('id'=>'1') 返回id=1的记录
36 //string :eg. 'id=1' 返回id=1的记录
37 function toExtJson($table,$start="0",$limit="10",$cons="")
38 {
39 $sql=$this->generateSql($table,$cons);
40 $totalNum=$this->findCount($sql);
41 $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
42 $resultNum = count($result);//当前结果数
43 $str="";
44 $str.= "{";
45 $str.= "'totalCount':'$totalNum',";
46 $str.="'rows':";
47 $str.="[";
48 for($i=0;$i<$resultNum;$i++){
49 $str.="{";
50 $count=count($result[$i]);
51 $j=1;
52 foreach($result[$i] as $key=>$val)
53 {
54 if($j<$count)
55 {
56 $str.="'".$key."':'".$val."',";
57 }
58 elseif($j==$count)
59 {
60 $str.="'".$key."':'".$val."'";
61 }
62 $j++;
63 }
64
65 $str.="}";
66 if ($i != $resultNum-1) {
67 $str.= ",";
68 }
69 }
70 $str.="]";
71 $str.="}";
72 return $str;
73 }
74 function generateSql($table,$cons)
75 {
76 $sql="";//sql条件
77 $sql="select * from ".$table;
78 if($cons!="")
79 {
80 if(is_array($cons))
81 {
82 $k=0;
83 foreach($cons as $key=>$val)
84 {
85 if($k==0)
86 {
87 $sql.="where '";
88 $sql.=$key;
89 $sql.="'='";
90 $sql.=$val."'";
91 }else
92 {
93 $sql.="and '";
94 $sql.=$key;
95 $sql.="'='";
96 $sql.=$val."'";
97 }
98 $k++;
99 }
100 }else
101 {
102 $sql.=" where ".$cons;
103 }
104 }
105 return $sql;
106 }
107 function toExtXml($table,$start="0",$limit="10",$cons="")
108 {
109 $sql=$this->generateSql($table,$cons);
110 $totalNum=$this->findCount($sql);
111 $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
112 $resultNum = count($result);//当前结果数
113 header("Content-Type: text/xml");
114 $xml="<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n";
115 $xml.="<xml>\n";
116 $xml.="\t<totalCount>".$totalNum."</totalCount>\n";
117 $xml.="\t<items>\n";
118 for($i=0;$i<$resultNum;$i++){
119 $xml.="\t\t<item>\n";
120 foreach($result[$i] as $key=>$val)
121 $xml.="\t\t\t<".$key.">".$val."</".$key.">\n";
122 $xml.="\t\t</item>\n";
123 }
124 $xml.="\t</items>\n";
125 $xml.="</xml>\n";
126 return $xml;
127 }
128 //输出word表格
129 function toWord($table,$mapping,$fileName)
130 {
131 header('Content-type: application/doc');
132 header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
133 echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
134 xmlns:w="urn:schemas-microsoft-com:office:word"
135 xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
136 <head>
137 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
138 <title>'.$fileName.'</title>
139 </head>
140 <body>';
141 echo'<table border=1><tr>';
142 if(is_array($mapping))
143 {
144 foreach($mapping as $key=>$val)
145 echo'<td>'.$val.'</td>';
146 }
147 echo'</tr>';
148 $results=$this->findBySql('select * from '.$table);
149 foreach($results as $result)
150 {
151 echo'<tr>';
152 foreach($result as $key=>$val)
153 echo'<td>'.$val.'</td>';
154 echo'</tr>';
155 }
156 echo'</table>';
157 echo'</body>';
158 echo'</html>';
159 }
160 function toExcel($table,$mapping,$fileName)
161 {
162 header("Content-type:application/vnd.ms-excel");
163 header("Content-Disposition:filename=".$fileName.".xls");
164 echo'<html xmlns:o="urn:schemas-microsoft-com:office:office"
165 xmlns:x="urn:schemas-microsoft-com:office:excel"
166 xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
167 <head>
168 <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
169 <meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
170 <!--[if gte mso 9]><xml>
171 <x:ExcelWorkbook>
172 <x:ExcelWorksheets>
173 <x:ExcelWorksheet>
174 <x:Name></x:Name>
175 <x:WorksheetOptions>
176 <x:DisplayGridlines/>
177 </x:WorksheetOptions>
178 </x:ExcelWorksheet>
179 </x:ExcelWorksheets>
180 </x:ExcelWorkbook>
181 </xml><![endif]-->
182 </head>
183 <body link=blue vlink=purple leftmargin=0 topmargin=0>';
184 echo'<table width="100%" border="0" cellspacing="0" cellpadding="0">';
185 echo'<tr>';
186 if(is_array($mapping))
187 {
188 foreach($mapping as $key=>$val)
189 echo'<td>'.$val.'</td>';
190 }
191 echo'</tr>';
192 $results=$this->findBySql('select * from '.$table);
193 foreach($results as $result)
194 {
195 echo'<tr>';
196 foreach($result as $key=>$val)
197 echo'<td>'.$val.'</td>';
198 echo'</tr>';
199 }
200 echo'</table>';
201 echo'</body>';
202 echo'</html>';
203 }
204 function Backup($table)
205 {
206 if(is_array ($table))
207 {
208 $str="";
209 foreach($table as $tab)
210 $str.=$this->get_table_content($tab);
211 return $str;
212 }else{
213 return $this->get_table_content($table);
214 }
215 }
216 function Backuptofile($table,$file)
217 {
218 header("Content-disposition: filename=$file.sql");//所保存的文件名
219 header("Content-type: application/octetstream");
220 header("Pragma: no-cache");
221 header("Expires: 0");
222 if(is_array ($table))
223 {
224 $str="";
225 foreach($table as $tab)
226 $str.=$this->get_table_content($tab);
227 echo $str;
228 }else{
229 echo $this->get_table_content($table);
230 }
231 }
232 function Restore($table,$file="",$content="")
233 {
234 //排除file，content都为空或者都不为空的情况
235 if(($file==""&&$content=="")||($file!=""&&$content!=""))
236 echo"参数错误";
237 $this->truncate($table);
238 if($file!="")
239 {
240 if($this->RestoreFromFile($file))
241 return true;
242 else
243 return false;
244 }
245 if($content!="")
246 {
247 if($this->RestoreFromContent($content))
248 return true;
249 else
250 return false;
251 }
252 }
253 //清空表，以便恢复数据
254 function truncate($table)
255 {
256 if(is_array ($table))
257 {
258 $str="";
259 foreach($table as $tab)
260 $this->execute("TRUNCATE TABLE $tab");
261 }else{
262 $this->execute("TRUNCATE TABLE $table");
263 }
264 }
265 function get_table_content($table)
266 {
267 $results=$this->findBySql("select * from $table");
268 $temp = "";
269 $crlf="\r\n";
270 foreach($results as $result)
271 {
272
273 /*(";
274 foreach($result as $key=>$val)
275 {
276 $schema_insert .= " `".$key."`,";
277 }
278 $schema_insert = ereg_replace(",$", "", $schema_insert);
279 $schema_insert .= ")
280 */
281 $schema_insert = "INSERT INTO $table VALUES (";
282 foreach($result as $key=>$val)
283 {
284 if($val != "")
285 $schema_insert .= " '".addslashes($val)."',";
286 else
287 $schema_insert .= "NULL,";
288 }
289 $schema_insert = ereg_replace(",$", "", $schema_insert);
290 $schema_insert .= ");$crlf";
291 $temp = $temp.$schema_insert ;
292 }
293 return $temp;
294 }
295 function RestoreFromFile($file){
296 if (false !== ($fp = fopen($file, 'r'))) {
297 $sql_queries = trim(fread($fp, filesize($file)));
298 $this->splitMySqlFile($pieces, $sql_queries);
299 foreach ($pieces as $query) {
300 if(!$this->execute(trim($query)))
301 return false;
302 }
303 return true;
304 }
305 return false;
306 }
307 function RestoreFromContent($content)
308 {
309 $content = trim($content);
310 $this->splitMySqlFile($pieces, $content);
311 foreach ($pieces as $query) {
312 if(!$this->execute(trim($query)))
313 return false;
314 }
315 return true;
316 }
317 function splitMySqlFile(&$ret, $sql)
318 {
319 $sql= trim($sql);
320 $sql=split(';',$sql);
321 $arr=array();
322 foreach($sql as $sq)
323 {
324 if($sq!="");
325 $arr[]=$sq;
326 }
327 $ret=$arr;
328 return true;
329 }
330 }
331 $db=new db();
332 $map=array('No','Name','Email','Age');
333 //$db->toExcel('test',$map,'档案');
334 echo $db->toExtXml('test');
335 ?>
2 class Db
3 {
4 var $conn;
5 function Db($host="localhost",$user="root",$pass="",$db="mytest")
6 {
7 if(!$this->conn=mysql_connect($host,$user,$pass))
8 die("can't connect to mysql sever");
9 mysql_select_db($db,$this->conn);
10 mysql_query("SET NAMES 'UTF-8'");
11 }
12 function execute($sql)
13 {
14 return mysql_query($sql,$this->conn);
15 }
16 function findCount($sql)
17 {
18 $result=$this->execute($sql);
19 return mysql_num_rows($result);
20 }
21 function findBySql($sql)
22 {
23 $array=array();
24 $result=mysql_query($sql);
25 $i=0;
26 while($row=mysql_fetch_assoc($result))
27 {
28 $array[$i]=$row;
29 $i++;
30 }
31 return $array;
32 }
33 //$con的几种情况
34 //空：返回全部记录
35 //array：eg. array('id'=>'1') 返回id=1的记录
36 //string :eg. 'id=1' 返回id=1的记录
37 function toExtJson($table,$start="0",$limit="10",$cons="")
38 {
39 $sql=$this->generateSql($table,$cons);
40 $totalNum=$this->findCount($sql);
41 $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
42 $resultNum = count($result);//当前结果数
43 $str="";
44 $str.= "{";
45 $str.= "'totalCount':'$totalNum',";
46 $str.="'rows':";
47 $str.="[";
48 for($i=0;$i<$resultNum;$i++){
49 $str.="{";
50 $count=count($result[$i]);
51 $j=1;
52 foreach($result[$i] as $key=>$val)
53 {
54 if($j<$count)
55 {
56 $str.="'".$key."':'".$val."',";
57 }
58 elseif($j==$count)
59 {
60 $str.="'".$key."':'".$val."'";
61 }
62 $j++;
63 }
64
65 $str.="}";
66 if ($i != $resultNum-1) {
67 $str.= ",";
68 }
69 }
70 $str.="]";
71 $str.="}";
72 return $str;
73 }
74 function generateSql($table,$cons)
75 {
76 $sql="";//sql条件
77 $sql="select * from ".$table;
78 if($cons!="")
79 {
80 if(is_array($cons))
81 {
82 $k=0;
83 foreach($cons as $key=>$val)
84 {
85 if($k==0)
86 {
87 $sql.="where '";
88 $sql.=$key;
89 $sql.="'='";
90 $sql.=$val."'";
91 }else
92 {
93 $sql.="and '";
94 $sql.=$key;
95 $sql.="'='";
96 $sql.=$val."'";
97 }
98 $k++;
99 }
100 }else
101 {
102 $sql.=" where ".$cons;
103 }
104 }
105 return $sql;
106 }
107 function toExtXml($table,$start="0",$limit="10",$cons="")
108 {
109 $sql=$this->generateSql($table,$cons);
110 $totalNum=$this->findCount($sql);
111 $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
112 $resultNum = count($result);//当前结果数
113 header("Content-Type: text/xml");
114 $xml="<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n";
115 $xml.="<xml>\n";
116 $xml.="\t<totalCount>".$totalNum."</totalCount>\n";
117 $xml.="\t<items>\n";
118 for($i=0;$i<$resultNum;$i++){
119 $xml.="\t\t<item>\n";
120 foreach($result[$i] as $key=>$val)
121 $xml.="\t\t\t<".$key.">".$val."</".$key.">\n";
122 $xml.="\t\t</item>\n";
123 }
124 $xml.="\t</items>\n";
125 $xml.="</xml>\n";
126 return $xml;
127 }
128 //输出word表格
129 function toWord($table,$mapping,$fileName)
130 {
131 header('Content-type: application/doc');
132 header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
133 echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
134 xmlns:w="urn:schemas-microsoft-com:office:word"
135 xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
136 <head>
137 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
138 <title>'.$fileName.'</title>
139 </head>
140 <body>';
141 echo'<table border=1><tr>';
142 if(is_array($mapping))
143 {
144 foreach($mapping as $key=>$val)
145 echo'<td>'.$val.'</td>';
146 }
147 echo'</tr>';
148 $results=$this->findBySql('select * from '.$table);
149 foreach($results as $result)
150 {
151 echo'<tr>';
152 foreach($result as $key=>$val)
153 echo'<td>'.$val.'</td>';
154 echo'</tr>';
155 }
156 echo'</table>';
157 echo'</body>';
158 echo'</html>';
159 }
160 function toExcel($table,$mapping,$fileName)
161 {
162 header("Content-type:application/vnd.ms-excel");
163 header("Content-Disposition:filename=".$fileName.".xls");
164 echo'<html xmlns:o="urn:schemas-microsoft-com:office:office"
165 xmlns:x="urn:schemas-microsoft-com:office:excel"
166 xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
167 <head>
168 <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
169 <meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
170 <!--[if gte mso 9]><xml>
171 <x:ExcelWorkbook>
172 <x:ExcelWorksheets>
173 <x:ExcelWorksheet>
174 <x:Name></x:Name>
175 <x:WorksheetOptions>
176 <x:DisplayGridlines/>
177 </x:WorksheetOptions>
178 </x:ExcelWorksheet>
179 </x:ExcelWorksheets>
180 </x:ExcelWorkbook>
181 </xml><![endif]-->
182 </head>
183 <body link=blue vlink=purple leftmargin=0 topmargin=0>';
184 echo'<table width="100%" border="0" cellspacing="0" cellpadding="0">';
185 echo'<tr>';
186 if(is_array($mapping))
187 {
188 foreach($mapping as $key=>$val)
189 echo'<td>'.$val.'</td>';
190 }
191 echo'</tr>';
192 $results=$this->findBySql('select * from '.$table);
193 foreach($results as $result)
194 {
195 echo'<tr>';
196 foreach($result as $key=>$val)
197 echo'<td>'.$val.'</td>';
198 echo'</tr>';
199 }
200 echo'</table>';
201 echo'</body>';
202 echo'</html>';
203 }
204 function Backup($table)
205 {
206 if(is_array ($table))
207 {
208 $str="";
209 foreach($table as $tab)
210 $str.=$this->get_table_content($tab);
211 return $str;
212 }else{
213 return $this->get_table_content($table);
214 }
215 }
216 function Backuptofile($table,$file)
217 {
218 header("Content-disposition: filename=$file.sql");//所保存的文件名
219 header("Content-type: application/octetstream");
220 header("Pragma: no-cache");
221 header("Expires: 0");
222 if(is_array ($table))
223 {
224 $str="";
225 foreach($table as $tab)
226 $str.=$this->get_table_content($tab);
227 echo $str;
228 }else{
229 echo $this->get_table_content($table);
230 }
231 }
232 function Restore($table,$file="",$content="")
233 {
234 //排除file，content都为空或者都不为空的情况
235 if(($file==""&&$content=="")||($file!=""&&$content!=""))
236 echo"参数错误";
237 $this->truncate($table);
238 if($file!="")
239 {
240 if($this->RestoreFromFile($file))
241 return true;
242 else
243 return false;
244 }
245 if($content!="")
246 {
247 if($this->RestoreFromContent($content))
248 return true;
249 else
250 return false;
251 }
252 }
253 //清空表，以便恢复数据
254 function truncate($table)
255 {
256 if(is_array ($table))
257 {
258 $str="";
259 foreach($table as $tab)
260 $this->execute("TRUNCATE TABLE $tab");
261 }else{
262 $this->execute("TRUNCATE TABLE $table");
263 }
264 }
265 function get_table_content($table)
266 {
267 $results=$this->findBySql("select * from $table");
268 $temp = "";
269 $crlf="\r\n";
270 foreach($results as $result)
271 {
272
273 /*(";
274 foreach($result as $key=>$val)
275 {
276 $schema_insert .= " `".$key."`,";
277 }
278 $schema_insert = ereg_replace(",$", "", $schema_insert);
279 $schema_insert .= ")
280 */
281 $schema_insert = "INSERT INTO $table VALUES (";
282 foreach($result as $key=>$val)
283 {
284 if($val != "")
285 $schema_insert .= " '".addslashes($val)."',";
286 else
287 $schema_insert .= "NULL,";
288 }
289 $schema_insert = ereg_replace(",$", "", $schema_insert);
290 $schema_insert .= ");$crlf";
291 $temp = $temp.$schema_insert ;
292 }
293 return $temp;
294 }
295 function RestoreFromFile($file){
296 if (false !== ($fp = fopen($file, 'r'))) {
297 $sql_queries = trim(fread($fp, filesize($file)));
298 $this->splitMySqlFile($pieces, $sql_queries);
299 foreach ($pieces as $query) {
300 if(!$this->execute(trim($query)))
301 return false;
302 }
303 return true;
304 }
305 return false;
306 }
307 function RestoreFromContent($content)
308 {
309 $content = trim($content);
310 $this->splitMySqlFile($pieces, $content);
311 foreach ($pieces as $query) {
312 if(!$this->execute(trim($query)))
313 return false;
314 }
315 return true;
316 }
317 function splitMySqlFile(&$ret, $sql)
318 {
319 $sql= trim($sql);
320 $sql=split(';',$sql);
321 $arr=array();
322 foreach($sql as $sq)
323 {
324 if($sq!="");
325 $arr[]=$sq;
326 }
327 $ret=$arr;
328 return true;
329 }
330 }
331 $db=new db();
332 $map=array('No','Name','Email','Age');
333 //$db->toExcel('test',$map,'档案');
334 echo $db->toExtXml('test');
335 ?>
