使用poi将sql脚本转化为word文档

最近在工作中需要整理一份sql的word文档，格式如下：

sql脚本如下:

*==============================================================*/
/* Table: REC_VIDEO_OPERATE_LOG                                 */
/*==============================================================*/
create table REC_VIDEO_OPERATE_LOG 
(
   RVO_LOG_ID           INTEGER              not null,
   RECORD_INFO_ID       INTEGER,
   USER_ID              INTEGER,
   LOG_TYPE             INTEGER,
   CREATE_DATE          TIMESTAMP,
   constraint PK_REC_VIDEO_OPERATE_LOG primary key (RVO_LOG_ID)
);

/*==============================================================*/
/* Table: BANK_REPORT_MENU                                      */
/*==============================================================*/
create table BANK_REPORT_MENU 
(
   REPORT_ID            INTEGER              not null,
   REPORT_NAME          VARCHAR2(300),
   REPORT_URL           VARCHAR2(3000),
   constraint PK_BANK_REPORT_MENU primary key (REPORT_ID)
);

comment on table BANK_REPORT_MENU is
'银行自已积分报表';

/*==============================================================*/
/* Table: CD_CITY                                               */
/*==============================================================*/
create table CD_CITY 
(
   CODE                 VARCHAR2(100)        not null,
   FULL_NAME            VARCHAR2(100),
   SHORT_NAME           VARCHAR2(100),
   PY                   VARCHAR2(100),
   PROV_CODE            VARCHAR2(100),
   CREATE_DATE          TIMESTAMP,
   UPDATE_DATE          TIMESTAMP,
   CREATE_USER          INTEGER,
   UPDATE_USER          INTEGER,
   constraint PK_CD_CITY primary key (CODE)
);

comment on table CD_CITY is
'市';

/*==============================================================*/
/* Table: CD_CLIENT_TYPE                                        */
/*==============================================================*/
create table CD_CLIENT_TYPE 
(
   CLIENT_TYPE_ID       INTEGER              not null,
   CLIENT_TYPE_NAME     VARCHAR2(60),
   SORTNO               INTEGER,
   CREATE_DATE          TIMESTAMP,
   UPDATE_DATE          TIMESTAMP,
   UPDATE_USER          INTEGER,
   CREATE_USER          INTEGER,
   constraint PK_CD_CLIENT_TYPE primary key (CLIENT_TYPE_ID)
);

comment on table CD_CLIENT_TYPE is
'终端类型';

package com.banger.Export.excel;


import org.apache.poi.xwpf.usermodel.*;
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 java.io.*;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/7/5.
 */
public class exportSqlExcel {

    public static void main(String[] args) {
        try {
            createDoc("e:\\1.docx","e:\\1.sql");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getTableName(String sql) {
        String result = null;
        if (sql.contains("(")) {
            result = sql.substring(5, sql.indexOf("(")).trim();
            if (sql.contains("comment on table")) {
                String s = sql.split(";")[1];
                result += "(" + s.substring(s.indexOf("'") + 1, s.lastIndexOf("'")) + ")";
            }
        }
        return result;
    }

    private static void insertCells(XWPFDocument doc, String sql) {

        //获得field属性的sql
        String substring = sql.substring(sql.indexOf("(") + 1, sql.lastIndexOf(")"));
        String[] split = substring.split(",\n");
        //创建表格
        XWPFTable table = doc.createTable(split.length, 5);
        //设置表格宽度
        setW(table, 5);
        //第一行数据插入
        insertHeader(table);
        //获得备注的map
        Map<String, String> map = getCommentInfo(sql);
        //表字段数据插入
        for (int i = 0; i < split.length - 1; i++) {
            int index = 0;
            String[] split1 = split[i].split(" ");
            List<XWPFTableCell> tableCells = table.getRow(i + 1).getTableCells();
            //是否为null
            if (split[i].contains("not null")) {
                tableCells.get(3).setText("是");
            } else {
                tableCells.get(3).setText("否");
            }
            //遍历获得字段信息
            for (int j = 0; j < split1.length; j++) {
                if (split1[j].trim().length() > 0) {
                    if (index == 0) {
                        //设置属性名
                        String name = split1[j].trim();
                        tableCells.get(0).setText(name);
                        //备注
                        String remark = map.get(name) != null ? map.get(name) : "";
                        tableCells.get(4).setText(remark);
                        index = 1;
                    } else if (index == 1) {
                        //类型
                        String type = split1[j].trim();
                        //长度
                        String length = "";
                        if (type.contains("(")) {
                            length = type.substring(type.indexOf("(") + 1, type.indexOf(")"));
                            type = type.substring(0, type.indexOf("("));
                        }
                        tableCells.get(1).setText(type);
                        tableCells.get(2).setText(length);
                        break;
                    }
                }
            }
        }
    }

    private static Map<String, String> getCommentInfo(String sql) {
        Map<String, String> map = new HashMap<String, String>();
        String[] split = sql.split(";");
        for (int i = 1; i < split.length; i++) {
            String row = split[i];
            if (row.contains(" column ")) {
                map.put(row.substring(row.indexOf(".") + 1, row.indexOf(" is")), row.substring(row.indexOf("'") + 1, row.lastIndexOf("'")));
            }
        }
        return map;
    }

    private static void insertHeader(XWPFTable table) {
        List<XWPFTableCell> tableCells = table.getRow(0).getTableCells();
        tableCells.get(0).setText("字段名称");
        tableCells.get(1).setText("类型");
        tableCells.get(2).setText("长度");
        tableCells.get(3).setText("是否必填");
        tableCells.get(4).setText("备注");
    }

    private static void createDoc(String docDir,String sqlDir) throws IOException {
        //创建新的word
        XWPFDocument doc = new XWPFDocument();
        //从1.sql中拿
        String sql = getSql(sqlDir);
        //截成表
        String[] creates = sql.split("create ");
        for (int i = 0; i < creates.length; i++) {
            if (creates[i].contains("table")) {
                insertItem(doc, creates[i]);
            }
        }

        try {
            //导出到本地,E:\demo.docx
            FileOutputStream out = new FileOutputStream(docDir);
            try {
                doc.write(out);
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static String getSql(String dir) throws IOException {
        File file = new File(dir);
        String result = "";
        InputStreamReader isr = new InputStreamReader(new FileInputStream(file), "UTF8");
        BufferedReader br = new BufferedReader(isr);//构造一个BufferedReader类来读取文件
        String s = null;
        while ((s = br.readLine()) != null) {//使用readLine方法，一次读一行
            result = result + "\n" + s;
        }
        br.close();
        System.out.println(result);
        return new String(result);
    }

    private static void insertItem(XWPFDocument doc, String sql) {
        //创建段落
        XWPFParagraph p1 = doc.createParagraph();
        //写入段落内容
        XWPFRun run = p1.createRun();
        run.setText(getTableName(sql));
        //插入字段
        insertCells(doc, sql);
    }


    private static void setW(XWPFTable table, int colNum) {
        CTTbl ttbl = table.getCTTbl();
        CTTblGrid tblGrid = ttbl.getTblGrid() != null ? ttbl.getTblGrid()
                : ttbl.addNewTblGrid();
        for (int i = 0; i < colNum; i++) {
            CTTblGridCol gridCol = tblGrid.addNewGridCol();
            gridCol.setW(new BigInteger("" + 1700));
        }
        table.setCellMargins(20, 20, 20, 20);
    }

}

用poi写了一份根据sql自动导出word的程序