EasyPOi循环导出Oracle数据库字典
模板表格
结果表格
依赖
cn.afterturn
easypoi-spring-boot-starter
4.0.0
org.apache.shiro
shiro-core
org.apache.poi
poi
4.0.0
org.apache.poi
poi-ooxml
4.0.0
代码
在这里插入代码片
public class DataDistExport {
public void dataDistExport(String url,String user,String pass){
//1.配置连接信息
Connection conn = getConnection(url, user, pass);
ResultSet fieldSet = null;
ResultSet tableSet = null;
try {
//2.获取所有表名
String tableSql = "select ut.TABLE_NAME,utc.COMMENTS AS TABLE_NAME_CH from user_tables ut LEFT JOIN user_tab_comments utc ON ut.TABLE_NAME = utc.TABLE_NAME ORDER BY ut.TABLE_NAME";
tableSet = getTable(conn,tableSql);
List> convertList = convertList(tableSet);
//3.基础模板位置
String templatePath = "C:\Users\admin\Desktop\DatabaseDistTemplate.docx";
String temDir = "C:\Users\admin\Desktop";
String fileName = "数据字典.docx";
exportWord(templatePath,temDir,fileName,convertList,fieldSet,conn);
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn,null,tableSet);
close(conn,null,fieldSet);
}
}
public static List> convertList(ResultSet rs) {
List> list = new ArrayList>();
try {
ResultSetmetaData md = rs.getmetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public static Map convertMap(ResultSet rs) {
Map map = new TreeMap();
try {
ResultSetmetaData md = rs.getmetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
map.put(md.getColumnName(i), rs.getObject(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
return map;
}
}
private ResultSet getTable(Connection conn,String sql) throws SQLException {
PreparedStatement pstmt = conn.prepareStatement(sql);
return pstmt.executeQuery();
}
public static Connection getConnection(String url,String user,String pass){
Connection conn = null;
try {
//加载数据驱动,连接数据库
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, pass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("加载数据库驱动失败");
}catch(Exception e){
e.printStackTrace();
System.out.println("连接数据库失败");
}
return conn;
}
public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void exportWord(String templatePath, String temDir, String fileName, List> convertList, ResultSet fieldSet, Connection conn) {
Assert.notNull(templatePath,"模板路径不能为空");
Assert.notNull(temDir,"临时文件路径不能为空");
Assert.notNull(fileName,"导出文件名不能为空");
Assert.isTrue(fileName.endsWith(".docx"),"word导出请使用docx格式");
if (!temDir.endsWith("/")){
temDir = temDir + File.separator;
}
File dir = new File(temDir);
if (!dir.exists()) {
dir.mkdirs();
}
try {
List wordList = new ArrayList<>();
for (Map objectMap : convertList) {
if(objectMap.get("TABLE_NAME") == null){continue;};
objectMap.putIfAbsent("TABLE_NAME_CH", " ");
//遍历所有表，获取每一个表的字段信息 中文表名 英文表名
String tableName = (String) objectMap.get("TABLE_NAME");
String fieldSql = "SELECt utc.COMMENTS AS TABLE_NAME_CH ,C.* FROM (SELECt A.TABLE_NAME,A.COLUMN_NAME,DECODE(A.CHAR_LENGTH,0,DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'),A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')') as "FIELD_TYPE",A.DATA_DEFAULT,A.NULLABLE,B.comments FROM sys.user_tab_columns A, sys.user_col_comments B WHERe A.table_name = B.table_name AND A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME = '"+tableName+"' ORDER BY A.TABLE_NAME) C LEFT JOIN user_tab_comments utc ON C.TABLE_NAME = utc.TABLE_NAME";
fieldSet = getTable(conn,fieldSql);
List> fieldList = convertList(fieldSet);
XWPFdocument metadocument = WordExportUtil.exportWord07(templatePath, objectMap);
for (Map fieldMap : fieldList) {
fieldMap.forEach((k,v) -> {
fieldMap.putIfAbsent(k," ");
});
fieldMap.put("TABLE_NAME_CH",objectMap.get("TABLE_NAME_CH"));
}
Map map = new HashMap<>();
map.put("maplist", fieldList);
WordExportUtil.exportWord07(metadocument, map);
//导入
wordList.add(metadocument);
}
// 2.把集合里面的word文档全部合并在一个文档
XWPFdocument word = mergeWord(wordList);
// 3.将合并后的word文档输出到文件
String tmpPath = temDir + fileName;
FileOutputStream fos = new FileOutputStream(tmpPath);
word.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
//delAllFile(temDir);//这一步看具体需求，要不要删
}
}
private static XWPFdocument mergeWord(List wordList) throws Exception{
if (CollectionUtils.isEmpty(wordList)) {
throw new RuntimeException("待合并的word文档list为空");
}
XWPFdocument doc = wordList.get(0);
int size = wordList.size();
if (size > 1) {
doc.createParagraph().setPageBreak(true);
for (int i = 1; i < size; i++) {
// 从第二个word开始合并
XWPFdocument nextPageDoc = wordList.get(i);
// 最后一页不需要设置分页符
if (i != (size-1)) {
nextPageDoc.createParagraph().setPageBreak(true);
}
appendBody(doc, nextPageDoc);
}
}
return doc;
}
private static void appendBody(XWPFdocument src, XWPFdocument append) throws Exception {
CTBody src1Body = src.getdocument().getBody();
CTBody src2Body = append.getdocument().getBody();
List allPictures = append.getAllPictures();
// 记录图片合并前及合并后的ID
Map map = new HashMap<>();
for (XWPFPictureData picture : allPictures) {
String before = append.getRelationId(picture);
//将原文档中的图片加入到目标文档中
String after = src.addPictureData(picture.getData(), document.PICTURE_TYPE_PNG);
map.put(before, after);
}
appendBody(src1Body, src2Body,map);
}
private static void appendBody(CTBody src, CTBody append,Map map) throws Exception {
XmlOptions optionsOuter = new XmlOptions();
optionsOuter.setSaveOuter();
String appendString = append.xmlText(optionsOuter);
String srcString = src.xmlText();
String prefix = srcString.substring(0,srcString.indexOf(">")+1);
String mainPart = srcString.substring(srcString.indexOf(">")+1,srcString.lastIndexOf("<"));
String sufix = srcString.substring( srcString.lastIndexOf("<") );
String addPart = appendString.substring(appendString.indexOf(">") + 1, appendString.lastIndexOf("<"));
if (map != null && !map.isEmpty()) {
//对xml字符串中图片ID进行替换
for (Map.Entry set : map.entrySet()) {
addPart = addPart.replace(set.getKey(), set.getValue());
}
}
//将两个文档的xml内容进行拼接
CTBody makeBody = CTBody.Factory.parse(prefix+mainPart+addPart+sufix);
src.set(makeBody);
}
}
需要配置的位置
//3.基础模板位置
String templatePath = "C:\Users\admin\Desktop\DatabaseDistTemplate.docx";
String temDir = "C:\Users\admin\Desktop";
String fileName = "数据字典.docx";
