您现在的位置是:首页 > Mysql > 网站首页Mysql
简介
最近有需求要一个关于表的设计文档,于是借助各种mysql工具看能不能导出来execl文档 然而工具并不是万能,无论如何都导不出来想要的样子,于是就想到用代码来实现
老规矩废话不多说直接上代码
SQL语句
SELECT
COLUMN_NAME 字段名称,
COLUMN_TYPE 数据类型,
IF(IS_NULLABLE='NO','是','否') AS '必填',
COLUMN_COMMENT 注释
FROM
INFORMATION_SCHEMA.COLUMNS
where
-- Finance为数据库名称,到时候只需要修改成你要导出表结构的数据库即可
table_schema ='jiazhong'
and table_name = 'sp_address'
PHP代码
public function getexecltable(){
$database_list = Db::query("SHOW TABLE STATUS");
$database_list = array_map('array_change_key_case', $database_list);
$arr=[];
$first[]=[
'name'=>'字段名称',
'type'=>'数据类型',
'value'=>'是否必填',
'remark'=>'注释'
];
$tablename=config('database.database');
foreach ($database_list as $k=>$v){
$sql="SELECT
COLUMN_NAME name,
COLUMN_TYPE type,
IF(IS_NULLABLE='NO','是','否') AS value,
COLUMN_COMMENT remark
FROM
INFORMATION_SCHEMA.COLUMNS
where
table_schema ='".$tablename."'
and table_name = '".$v['name']."'";
$data=Db::query($sql);
$newdata= array_merge($first, $data);
$arr[]=[
'title'=>$v['name'],
'remark'=>$v['comment'],
'items'=>$newdata
];
}
ini_set ( 'max_execution_time', '0' );
import('excel.PHPExcel', EXTEND_PATH);
$row=1;
ini_set ( 'max_execution_time', '0' );
import('excel.PHPExcel', EXTEND_PATH);
//开始设置缓存文件
$cacheMethod = \PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '100MB' );
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//设置缓存结束
$mode = 'public/execl.xls';
$objReader = \PHPExcel_IOFactory::createReader('Excel5'); //还有一种"Excel2007"
$objPHPExcel = $objReader->load($mode);
$objvalue=$objPHPExcel->setActiveSheetIndex(0);//获取汇总表
foreach ($arr as $k=>$v){
$str=$v['title'].'('.$v['remark'].')';
$objvalue->mergeCells('A' . $row . ':D' . $row)->setCellValue('A' . $row, $str);//进行合并 最后两个
$row+=1;
foreach ($v['items'] as $k2=>$v2){
$objvalue->setCellValue('A' . $row, $v2['name']);
$objvalue->setCellValue('B' . $row, $v2['type']);
$objvalue->setCellValue('C' . $row, $v2['value']);
$objvalue->setCellValue('D' . $row, $v2['remark']);
$row+=1;
}
$row+=1;
}
//'name'=>'字段名称',
// 'type'=>'数据类型',
// 'value'=>'是否必填',
// 'remark'=>'注释'
$fileName = "数据表结构.xls";
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$fileName");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // 文件通过浏览器下载
}