最近在工作中需要整理一份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的程序