thinkphp3.2结合PHPExcel导出数据库里所有表的结构
- WBOY原創
- 2016-06-07 11:40:441162瀏覽
thinkphp3.2结合PHPExcel导出数据库里所有表的结构,含注释
先看图
一、控制器代码如下： public function out(){<br>
$sql="show tables";//读取库里所有的表<br>
<br>
$result=M()->query($sql);<br>
<br>
foreach ($result as $k => $v) {<br>
$k++;<br>
$_sql="SHOW FULL COLUMNS FROM ".$v['Tables_in_'.C('DB_NAME')];<br>
$data[][0]=array("表 {$k}.{$v['Tables_in_test']} 表",'','','','','','');<br>
$data[][1]=array("字段","类型","校对","NULL","键","默认","额外","权限","注释");<br>
$data[]=M()->query($_sql);<br>
$data[][]=array();<br>
<br>
}<br>
<br>
//导入PHPExcel类库<br>
import("Org.Util.PHPExcel");<br>
import("Org.Util.PHPExcel.Writer.Excel5");<br>
import("Org.Util.PHPExcel.IOFactory.php");<br>
<br>
$filename="test_excel";<br>
$headArr=array("ID","字段","类型","NULL","键","默认","额外","注释");<br>
$this->getExcel($filename,$headArr,$data);<br>
}<br>
<br>
<br>
private function getExcel($fileName,$headArr,$data){<br>
//对数据进行检验<br>
if(empty($data) || !is_array($data)){<br>
die("data must be a array");<br>
}<br>
$date = date("Y_m_d",time());<br>
$fileName .= "_{$date}.xls";<br>
<br>
//创建PHPExcel对象，注意，不能少了\<br>
$objPHPExcel = new \PHPExcel();<br>
$objProps = $objPHPExcel->getProperties();<br>
<br>
//设置表头<br>
$key = ord("A");<br>
$column = 2;<br>
$objActSheet = $objPHPExcel->getActiveSheet();<br>
<br>
foreach ($data as $ke => $row) { <br>
foreach($row as $key => $rows){ //行写入<br>
$span = ord("B");<br>
foreach($rows as $keyName=>$value){// 列写入<br>
$j = chr($span);<br>
$objActSheet->setCellValue($j.$column, $value);<br>
$span++;<br>
}<br>
$column++;<br>
}<br>
}<br>
<br>
$fileName = iconv("utf-8", "gb2312", $fileName);<br>
//设置活动单指数到第一个表,所以Excel打开这是第一个表<br>
$objPHPExcel->setActiveSheetIndex(0);<br>
header('Content-Type: application/vnd.ms-excel');<br>
header("Content-Disposition: attachment;filename=\"$fileName\"");<br>
header('Cache-Control: max-age=0');<br>
<br>
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');<br>
$objWriter->save('php://output'); //文件通过浏览器下载<br>
exit;<br>
}二、下载附件,把里面的文件及文件夹放到tp的核心目录 ThinkPHP\Library\Org\Util 下
三、数据库的配置项自行配置
四、访问out方法,就可以导出excel表了
五、想要更多的功能自行修改. OVER
AD：真正免费，域名+虚机+企业邮箱=0元