【MySQL数据表结构导出到excel】

<?php
 require_once './vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

 $con = mysqli_connect("localhost","root","123456");
 mysqli_select_db($con,"information_schema");
 $sql = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, (CASE WHEN is_nullable = 'NO' THEN 'NO'ELSE 'YES'END ) as is_null, COLUMN_DEFAULT,COLUMN_COMMENT FROM INFORMATION_SCHEMA. COLUMNS WHERE table_schema = 'test_schema'"; 
 $result = mysqli_query($con,$sql);
 $table_result = mysqli_query($con,"SELECT
   TABLE_NAME,
    TABLE_COMMENT
    
FROM
    INFORMATION_SCHEMA. TABLES
WHERE
    table_schema = 'test_table'");
 $table_list = [];
 while($row = $table_result->fetch_array(MYSQLI_ASSOC)){
	if(!in_array($row['TABLE_NAME'],$table_list)){
		$table_list[$row['TABLE_NAME']] = $row['TABLE_COMMENT'];
	}
}
$table_arr = [];
$objPHPExcel = new PHPExcel(); 
$objPHPExcel->setActiveSheetIndex(0);//公式
//得到当前活动的表,注意下文教程中会经常用到$objActSheet
$objActSheet = $objPHPExcel->getActiveSheet(); 
$styleThinBlackBorderOutline = array(
       'borders' => array (
             'outline' => array (
                   'style' => PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                   'color' => array ('argb' => 'FF000000'),          //设置border颜色
            ),
      ),
);
$i = 1;
$column_arr = ['A','B','C','D','E'];
while($row = $result->fetch_array(MYSQLI_ASSOC)){
	if(!in_array($row['TABLE_NAME'],$table_arr)){
		$now = $i+2;
		$next = $i+4;
		$objActSheet->mergeCells("A{$now}:E{$next}");      // A28:B28合并 
		$objActSheet->getDefaultStyle("A{$now}:E{$next}")->getFont()->setName( '宋体');
		$objActSheet->getDefaultStyle("A{$now}:E{$next}")->getFont()->setSize(16); 
		$objActSheet->getStyle()->getFont()->setBold(true); 
		$objActSheet->getStyle( "A{$now}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    //水平方向上对齐
		$objActSheet->getStyle( "A{$now}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);       //垂直方向上中间居中
		$objActSheet->setCellValue( "A{$now}", $row['TABLE_NAME'].'（'.$table_list[$row['TABLE_NAME']].'）' );
		$objActSheet->getStyle( "A{$now}:E{$next}")->applyFromArray($styleThinBlackBorderOutline); 
		$title_key = $i+5;
		$objActSheet->getDefaultStyle("A{$title_key}:E{$title_key}")->getFont()->setName( '宋体');
		$objActSheet->getDefaultStyle("A{$title_key}:E{$title_key}")->getFont()->setSize(14); 
		$objActSheet->getStyle()->getFont()->setBold(true); 
		foreach($column_arr as $column){
			$objActSheet->getStyle( "{$column}{$title_key}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    //水平方向上对齐
			$objActSheet->getStyle( "{$column}{$title_key}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);       //垂直方向上中间居中
			switch($column){
				case 'A':
					$title = '字段';
					break;
				case 'B':
					$title = '类型';
					break;
				case 'C':
					$title = '空';
					break;
				case 'D':
					$title = '默认';
					break;
				case 'E':
					$title = '注释';
					break;
			}
			$objActSheet->setCellValue( "{$column}{$title_key}", $title);
			$objActSheet->getStyle( "{$column}{$title_key}")->applyFromArray($styleThinBlackBorderOutline); 
		}
		$table_arr[] = $row['TABLE_NAME'];
		$i=$i+6;
	} else {
		// var_dump($row);exit;
		foreach($column_arr as $column){
			$objActSheet->getDefaultStyle("{$column}{$i}")->getFont()->setName( '宋体');
			$objActSheet->getDefaultStyle("{$column}{$i}")->getFont()->setSize(12);
			$value = '';
			switch($column){
				case 'A':
					$value = $row['COLUMN_NAME'];
					break;
				case 'B':
					$value = $row['DATA_TYPE'];
					break;
				case 'C':
					$value = $row['is_null'];
					break;
				case 'D':
					$value = $row['COLUMN_DEFAULT'];
					break;
				case 'E':
					$value = $row['COLUMN_COMMENT'];
					break;
			}
			$objActSheet->getStyle( "{$column}{$i}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);    //水平方向上对齐
			$objActSheet->getStyle( "{$column}{$i}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);       //垂直方向上中间居中
			$objActSheet->getStyle( "{$column}{$i}")->applyFromArray($styleThinBlackBorderOutline); 
			$objActSheet->setCellValue( "{$column}{$i}", $value);
		}
		
		$i++;
	}
}

// $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// $objWriter->save('CRM DATABASE.xlsx');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="crm_database.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;