poi生成Word数据库设计详细说明书

package com.web.action;
 
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTJc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblGrid;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblGridCol;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STJc;

import com.alibaba.fastjson.JSONObject;
 
 
/**
 * @Description: 生成数据库设计详细说明书   
 * @Title:  WordExportTable.java    
 * @author: huafu.su     
 * @date: 2019年2月21日 下午5:18:02
 */
public class WordExportTable {
 
    public static void main(String[] args)throws Exception {
        //Blank Document
        XWPFDocument document= new XWPFDocument();
        //添加标题
        XWPFParagraph titleParagraph = document.createParagraph();
        //设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.CENTER);
        XWPFRun titleParagraphRun = titleParagraph.createRun();
        titleParagraphRun.setText("数据库设计详细说明书");
        titleParagraphRun.setColor("000000");
        titleParagraphRun.setFontSize(20);
        WordExportTable we = new WordExportTable();
        List<JSONObject> list= we.getTables("test");
        for (JSONObject json : list) {
        	List<String[]> columns = we.getTablesDetail("test", json.getString("name"));
            addTable(document, json.getString("name"), json.getString("remark"), columns);
		}
        //Write the Document in file system
        FileOutputStream out = new FileOutputStream(new File("d:\\数据库设计详细说明书V1.0.0.docx"));
        document.write(out);
        out.close();
        System.out.println("create_table document written success.");
    }
    
    String driverUrl = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false";
    String username = "root";
    String password = "123456";
    
    private  List<String[]> getTablesDetail(String schema, String tableName){
    	List<String[]> list = new ArrayList<>();
    	Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection(driverUrl, username, password);
            //定义sql语句 ?表示占位符
            String sql = "SELECT COLUMN_NAME  , COLUMN_TYPE  ,  COLUMN_DEFAULT  , IS_NULLABLE  ,  COLUMN_COMMENT "  
            		+" FROM INFORMATION_SCHEMA.COLUMNS    WHERE  table_schema = ? and table_name = ?  ";
            //获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, schema);
            preparedStatement.setString(2, tableName);
            //向数据库发出sql执行查询，查询出结果集
            resultSet =  preparedStatement.executeQuery();
            int i = 1;
            //遍历查询结果集
            while(resultSet.next()){
            	String[] str = new String[7];
            	str[0] = i+"";
            	str[1] = resultSet.getString("COLUMN_NAME");
            	str[2] = resultSet.getString("COLUMN_TYPE");
            	str[3] = resultSet.getString("COLUMN_DEFAULT");
            	str[4] = resultSet.getString("IS_NULLABLE");
            	str[5] = "";
            	str[6] = resultSet.getString("COLUMN_COMMENT");
            	list.add(str);
            	i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return list;
    }
    
    private  List<JSONObject> getTables(String schema){
    	List<JSONObject> list = new ArrayList<>();
    	Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection(driverUrl, username, password);
            //定义sql语句 ?表示占位符
            String sql = "select TABLE_NAME,TABLE_COMMENT from information_schema.tables where table_schema= ? ORDER BY TABLE_NAME";
            //获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, schema);
            //向数据库发出sql执行查询，查询出结果集
            resultSet =  preparedStatement.executeQuery();
            //遍历查询结果集
            while(resultSet.next()){
            	JSONObject j = new JSONObject();
            	j.put("name", resultSet.getString("TABLE_NAME"));
            	j.put("remark", resultSet.getString("TABLE_COMMENT"));
            	list.add(j);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return list;
    }
    
    private static void addTable(XWPFDocument document,String tableName,String remark, List<String[]> columns){
    	
    	//两个表格之间加个换行
        document.createParagraph().createRun().setText("\r");
    	 // 标题1，1级大纲
        document.createParagraph().createRun().setText(remark+" "+tableName);
        //工作经历表格
        XWPFTable ComTable = document.createTable();
//        //列宽自动分割
//        CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
//        comTableWidth.setType(STTblWidth.DXA);
//        comTableWidth.setW(BigInteger.valueOf(9072));
 
        CTTbl ttbl = ComTable.getCTTbl();
        int[] COLUMN_WIDTHS = new int[] {572,2072,1372,872,672,672,2572};
        CTTblGrid tblGrid = ttbl.getTblGrid() != null ? ttbl.getTblGrid()
                : ttbl.addNewTblGrid();
        for (int j = 0, len = COLUMN_WIDTHS.length; j < len; j++) {
            CTTblGridCol gridCol = tblGrid.addNewGridCol();
            gridCol.setW(new BigInteger(String.valueOf(COLUMN_WIDTHS[j])));
        }
        //表格第一行
        XWPFTableRow comTableRowOne = ComTable.getRow(0);
        setCellvalue(comTableRowOne.getCell(0), "序号");
        setCellvalue(comTableRowOne.addNewTableCell(),"字段名");
        setCellvalue(comTableRowOne.addNewTableCell(),"类型");
        setCellvalue(comTableRowOne.addNewTableCell(),"默认值");
        setCellvalue(comTableRowOne.addNewTableCell(),"是否可为空");
        setCellvalue(comTableRowOne.addNewTableCell(),"是否主键");
        setCellvalue(comTableRowOne.addNewTableCell(),"注释"); 
        for (String[] str : columns) { 
        	//表格第二行
            XWPFTableRow comTableRowTwo = ComTable.createRow();
			for (int j = 0; j < str.length; j++) {
				if(j==0 || j==3 || j==4 || j==5){
					setCellvalue(comTableRowTwo.getCell(j),str[j]); 
				}else{
					comTableRowTwo.getCell(j).setText(str[j]);
				}
			}
		}
 
    }
    
   private static void setCellvalue(XWPFTableCell cell, String text){
	   cell.setText(text);
	   cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
	   CTTc cttc = cell.getCTTc();
	   CTP ctp = cttc.getPList().get(0);
	   CTPPr ctppr = ctp.getPPr();
	   if (ctppr == null) {
	       ctppr = ctp.addNewPPr();
	   }
	   CTJc ctjc = ctppr.getJc();
	   if (ctjc == null) {
	       ctjc = ctppr.addNewJc();
	   }
	   ctjc.setVal(STJc.CENTER); //水平居中 
   }
 
}