SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义,precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型,precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM information_schema.`TABLES` AS pretab RIGHT JOIN information_schema.`COLUMNS` AS precol ON precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA ="此处填写库名" GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME;

 import java.io.BufferedReader;

 import java.io.FileInputStream;

 import java.io.FileOutputStream;

 import java.io.InputStreamReader;

 import java.util.HashMap;

 import java.util.Map;

 import java.util.Set;

 import org.apache.commons.collections4.MapUtils;

 import org.apache.poi.hssf.usermodel.HSSFCell;

 import org.apache.poi.hssf.usermodel.HSSFRow;

 import org.apache.poi.hssf.usermodel.HSSFSheet;

 import org.apache.poi.hssf.usermodel.HSSFWorkbook;

 import org.apache.poi.ss.usermodel.CellStyle;

 import org.apache.poi.ss.usermodel.FillPatternType;

 import org.apache.poi.ss.usermodel.Font;

 import org.apache.poi.ss.usermodel.HorizontalAlignment;

 import org.apache.poi.ss.usermodel.IndexedColors;

 import org.apache.poi.ss.usermodel.VerticalAlignment;

 import org.apache.poi.ss.usermodel.Workbook;

 import org.apache.poi.ss.util.CellRangeAddress;

 import org.apache.poi.xssf.usermodel.XSSFCell;

 import org.apache.poi.xssf.usermodel.XSSFRow;

 import org.apache.poi.xssf.usermodel.XSSFSheet;

 import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 /**

  * 生成数据库数据结构速查文件（数据库字典）

  *

  * @author ruran

  * @since 2019年7月4日 下午3:25:13

  */

 public class ProduceGuideOfDatabase {

     /*

      * 数据来源

      *

      * SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义,

      * precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型,

      * precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM

      * information_schema.`TABLES` AS pretab RIGHT JOIN

      * information_schema.`COLUMNS` AS precol ON

      * precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA ="此处填写库名"

      * GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME;

      */

     public static void main(String[] args) {

         System.out.println("开始运行程序。。。");

         long preTime = System.currentTimeMillis();

         // navicat导出txt-程序整理生成字典文件（人工参与步骤多，繁琐，不智能）

          reArrangeFromSQLtxt();

         System.out.println("运行完成，耗时：" + (System.currentTimeMillis() - preTime) + "ms");

     }

     /**

      * 从TXT文件中重整成excel

      *

      * @author ruran

      * @since 2019年7月24日 下午4:40:10

      */

     private static void reArrangeFromSQLtxt() {

         String url = "F:\\2-ME\\中心+部门\\1-scrs学习整理区\\数据库字典整理\\";

         String[] fromFiles = "scrssit-scrssit2-scrssit3-scrssit4-scrssit5-scrssit6-scrssit7-scrssit8-scrssit9-scrssit10-scrssit11"

                 .split("-");

         String forFile = "系统数据库结构参考速查表-20190724.xlsx";

         Map<String, Map<String, TablePojo>> database_tables = reDataFromSQLtxt(url, fromFiles, "@");

         if (MapUtils.isNotEmpty(database_tables)) {

             if (forFile.contains(".xlsx")) {

                 arrangeToXLSX(database_tables, url, forFile);

             } else {

                 arrangeToXLS(database_tables, url, forFile);

             }

         }

     }

     /**

      * 整理数据库字典

      *

      * 可防止分表多次输出

      *

      * @author ruran

      * @since 2019年7月22日 下午2:06:54

      * @param url

      * @param fileName

      * @param splitStr

      */

     private static Map<String, Map<String, TablePojo>> reDataFromSQLtxt(String url, String[] fromFileNames,

             String splitStr) {

         Map<String, Map<String, TablePojo>> database_table = new HashMap<>();

         for (String fromFileName : fromFileNames) {

             try (FileInputStream fis = new FileInputStream(url + fromFileName + ".txt");

                     InputStreamReader isr = new InputStreamReader(fis);

                     BufferedReader br = new BufferedReader(isr);) {

                 String readLine = "";

                 String columnLines = "";

                 int countAll = 0;// 表总数

                 Map<String, TablePojo> tableNames = new HashMap<>();

                 String preTableName = "";

                 String preTableComment = "";

                 while (isNotBlank((readLine = br.readLine()))) {

                     String[] lineSplit = readLine.split(splitStr);

                     int lineSplitLenght = lineSplit.length;

                     String currentTableName = "";

                     if (lineSplitLenght > 0) {

                         currentTableName = lineSplit[0];

                     }

                     if (tableNames.containsKey(getRealTablename(currentTableName))) {

                         continue;

                     }

                     String currentTableComment = "";

                     String currentColumnName = "";

                     String currentColumnType = "";

                     String currentColumnDefault = "";

                     String currentColumnComment = "";

                     if (lineSplitLenght > 1) {

                         currentTableComment = lineSplit[1];

                     }

                     if (lineSplitLenght > 2) {

                         currentColumnName = lineSplit[2];

                     }

                     if (lineSplitLenght > 3) {

                         currentColumnType = lineSplit[3];

                     }

                     if (lineSplitLenght > 4) {

                         currentColumnDefault = lineSplit[4];

                     }

                     if (lineSplitLenght > 5) {

                         currentColumnComment = lineSplit[5];

                     }

                     if (currentTableName.equals(preTableName)) {

                         columnLines += currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"

                                 + currentColumnComment + "@";

                         continue;

                     }

                     if (countAll != 0 && !tableNames.containsKey(getRealTablename(preTableName))) {

                         TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,

                                 columnLines.length() - 1));

                         tableNames.put(getRealTablename(preTableName), tablePojo);

                     }

                     countAll++;

                     columnLines = currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"

                             + currentColumnComment + "@";

                     preTableName = currentTableName;

                     preTableComment = currentTableComment;

                 }

                 // 最后一组数据判断+保存

                 if (!tableNames.containsKey(getRealTablename(preTableName))) {

                     TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,

                             columnLines.length() - 1));

                     tableNames.put(getRealTablename(preTableName), tablePojo);

                 }

                 database_table.put(fromFileName, tableNames);

             } catch (Exception e) {

                 e.printStackTrace();

                 continue;

             }

         }

         return database_table;

     }

     /**

      * 取数据整合到excel-xls

      *

      * @author ruran

      * @since 2019年7月23日 下午5:32:50

      * @param tableNamesMap

      * @param fos

      */

     private static void arrangeToXLS(Map<String, Map<String, TablePojo>> database_tables, String url, String forFile) {

         try (FileOutputStream fos = new FileOutputStream(url + forFile);) {

             if (MapUtils.isNotEmpty(database_tables)) {

                 HSSFWorkbook currentWorkbook = new HSSFWorkbook();

                 // 获取所有样式

                 Map<String, CellStyle> cellStyles = getCellStyles(currentWorkbook);

                 Set<String> databaseNames = database_tables.keySet();

                 for (String databaseName : databaseNames) {

                     HSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);

                     HSSFRow currentRow = null;

                     HSSFCell currentCell = null;

                     int rowIndex = -1;

                     Map<String, TablePojo> tableNames = database_tables.get(databaseName);

                     for (TablePojo tablePojo : tableNames.values()) {

                         // 空行

                         currentSheet.createRow(++rowIndex);

                         // 表头

                         currentRow = currentSheet.createRow(++rowIndex);

                         currentRow.setHeightInPoints(18);

                         currentCell = currentRow.createCell(0);

                         currentCell.setCellStyle(cellStyles.get("bluesStyle"));

                         currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");

                         CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);

                         currentSheet.addMergedRegion(region);

                         // 表-标题栏

                         currentRow = currentSheet.createRow(++rowIndex);

                         currentRow.setHeightInPoints(18);

                         currentCell = currentRow.createCell(0);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("列名");

                         currentCell = currentRow.createCell(1);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("类型");

                         currentCell = currentRow.createCell(2);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("默认值");

                         currentCell = currentRow.createCell(3);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("释义");

                         // 表字段

                         String tableColumnsStr = tablePojo.getTableColumns();

                         for (String tableColumns : tableColumnsStr.split("@")) {

                             currentRow = currentSheet.createRow(++rowIndex);

                             currentRow.setHeightInPoints(18);

                             String[] tableColumnArr = tableColumns.split("#");

                             for (int i = 0; i < tableColumnArr.length; i++) {

                                 currentCell = currentRow.createCell(i);

                                 currentCell.setCellStyle(cellStyles.get("baseStyle"));

                                 currentCell.setCellValue(tableColumnArr[i]);

                             }

                         }

                     }

                 }

                 currentWorkbook.write(fos);

             }

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     /**

      * 取数据整合到excel-xlsx

      *

      * @author ruran

      * @since 2019年7月24日 上午11:51:56

      * @param tableNamesMap

      * @param fos

      */

     private static void arrangeToXLSX(Map<String, Map<String, TablePojo>> database_tables, String url, String forFile) {

         try (FileOutputStream fos = new FileOutputStream(url + forFile);) {

             if (MapUtils.isNotEmpty(database_tables)) {

                 XSSFWorkbook currentWorkbook = new XSSFWorkbook();

                 // 获取所有样式

                 Map<String, CellStyle> cellStyles = getCellStyles(currentWorkbook);

                 Set<String> databaseNames = database_tables.keySet();

                 for (String databaseName : databaseNames) {

                     XSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);

                     XSSFRow currentRow = null;

                     XSSFCell currentCell = null;

                     int rowIndex = -1;

                     Map<String, TablePojo> tableNames = database_tables.get(databaseName);

                     for (TablePojo tablePojo : tableNames.values()) {

                         // 空行

                         currentSheet.createRow(++rowIndex);

                         // 表头

                         currentRow = currentSheet.createRow(++rowIndex);

                         currentRow.setHeightInPoints(18);

                         currentCell = currentRow.createCell(0);

                         currentCell.setCellStyle(cellStyles.get("bluesStyle"));

                         currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");

                         CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);

                         currentSheet.addMergedRegion(region);

                         // 表-标题栏

                         currentRow = currentSheet.createRow(++rowIndex);

                         currentRow.setHeightInPoints(18);

                         currentCell = currentRow.createCell(0);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("列名");

                         currentCell = currentRow.createCell(1);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("类型");

                         currentCell = currentRow.createCell(2);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("默认值");

                         currentCell = currentRow.createCell(3);

                         currentCell.setCellStyle(cellStyles.get("blueStyle"));

                         currentCell.setCellValue("释义");

                         // 表字段

                         String tableColumnsStr = tablePojo.getTableColumns();

                         for (String tableColumns : tableColumnsStr.split("@")) {

                             currentRow = currentSheet.createRow(++rowIndex);

                             currentRow.setHeightInPoints(18);

                             String[] tableColumnArr = tableColumns.split("#");

                             for (int i = 0; i < tableColumnArr.length; i++) {

                                 currentCell = currentRow.createCell(i);

                                 currentCell.setCellStyle(cellStyles.get("baseStyle"));

                                 currentCell.setCellValue(tableColumnArr[i]);

                             }

                         }

                     }

                 }

                 currentWorkbook.write(fos);

             }

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     /**

      * 样式集锦

      *

      * @author ruran

      * @since 2019年7月24日 下午7:32:26

      * @param workbook

      * @return

      */

     private static Map<String, CellStyle> getCellStyles(Workbook workbook) {

         // 实线边框

         // style1.setBorderTop(BorderStyle.THIN);

         // style1.setBorderBottom(BorderStyle.THIN);

         // style1.setBorderLeft(BorderStyle.THIN);

         // style1.setBorderRight(BorderStyle.THIN);

         // 设置自动换行

         // baseStyle.setWrapText(true);

         Map<String, CellStyle> cellStylesMap = new HashMap<>();

         // baseStyle

         CellStyle baseStyle = workbook.createCellStyle();

         // 水平对齐方式

         baseStyle.setAlignment(HorizontalAlignment.LEFT);

         // 垂直对齐方式

         baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);

         // 宋体设置

         Font baseFont = workbook.createFont();

         baseFont.setFontName("宋体");

         baseStyle.setFont(baseFont);

         cellStylesMap.put("baseStyle", baseStyle);// 存放样式-baseStyle

         // 深蓝色底部、白色字体、加粗

         CellStyle bluesStyle = workbook.createCellStyle();

         bluesStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式

         // 背景色

         bluesStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());

         bluesStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果

         // 白色加粗字体

         Font bluesFont = workbook.createFont();

         bluesFont.setColor(IndexedColors.WHITE.getIndex());

         bluesFont.setBold(true);

         bluesFont.setFontName("宋体");

         bluesStyle.setFont(bluesFont);

         cellStylesMap.put("bluesStyle", bluesStyle);// 存放样式-bluesStyle

         // 浅蓝色底部

         CellStyle blueStyle = workbook.createCellStyle();

         blueStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式

         // 背景色

         blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

         blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果

         cellStylesMap.put("blueStyle", blueStyle);// 存放样式-blueStyle

         return cellStylesMap;

     }

     /**

      * 字符串判非空

      *

      * @author ruran

      * @since 2019年7月23日 下午2:29:38

      * @param str

      * @return

      */

     private static boolean isNotBlank(String str) {

         if (null == str) {

             return false;

         }

         if (str.trim().length() == 0) {

             return false;

         }

         return true;

     }

     /**

      * 字符串判非空

      *

      * @author ruran

      * @since 2019年7月23日 下午3:48:57

      * @param str

      * @return

      */

     private static boolean isBlank(String str) {

         if (null == str) {

             return true;

         }

         if (str.trim().length() == 0) {

             return true;

         }

         return false;

     }

     /**

      * 获取真实的表名 - 逻辑是去除末尾的数字

      *

      * @author ruran

      * @since 2019年7月23日 下午3:51:03

      * @param tableName

      * @return

      */

     private static String getRealTablename(String tableName) {

         if (isBlank(tableName)) {

             return null;

         }

         return tableName.replaceAll("\\d+$", "");     }

     /**

      * 表数据内部类

      *

      * @author ruran

      * @since 2019年7月23日 下午4:16:28

      */

     @SuppressWarnings("unused")

     private static class TablePojo {

         String tableName = "";

         String tableComment = "";

         String tableColumns = "";

         public TablePojo() {

         }

         public TablePojo(String tablename, String tablecomment, String tablecolumns) {

             tableName = tablename;

             tableComment = tablecomment;

             tableColumns = tablecolumns;

         }

         public String getTableName() {

             return tableName;

         }

         public void setTableName(String tableName) {

             this.tableName = tableName;

         }

         public String getTableComment() {

             return tableComment;

         }

         public void setTableComment(String tableComment) {

             this.tableComment = tableComment;

         }

         public String getTableColumns() {

             return tableColumns;

         }

         public void setTableColumns(String tableColumns) {

             this.tableColumns = tableColumns;

         }

     }

 }