读库操作
/**
-
@Author zw
-
@DATE 2019/8/7 17:24
-
@VERSION 1.0.0
**/
@Service
public class SaveTemplateServiceImpl {@Autowired
private WordExport wordExport;private String file = “config/jdbc.json”;
public Map<String,DataSource> dataSourceMap = new HashMap<String,DataSource>();
public List tableEntities = new ArrayList();
@Data
public class TableInfo{
private String tablename;
private String remark;
private String dataSourceId;
}@Data
public class TableEntity{
private String column_name = “”;
private String data_type = “”;
private String data_length = “”;
private String nullAble = “true”;
private String comments = “”;
}public void init(){
try {
String databasesString = IOUtils.toString(new FileInputStream(file), “UTF-8”);
JSONObject jsonObject = JSONObject.fromObject(databasesString);
JSONArray jsonArray = jsonObject.getJSONArray(“datasources”);
for (int i = 0;i<jsonArray.size();i++){
JSONObject jsObject = jsonArray.getJSONObject(i);
Map.Entry entry = getDataSourceEntry(jsObject);
dataSourceMap.put(String.valueOf(entry.getKey()),(DataSource) entry.getValue());
}JSONArray tableArray = jsonObject.getJSONArray("tables"); for (int i = 0;i<tableArray.size();i++){ JSONObject object = tableArray.getJSONObject(i); String tableName = object.getString("tablename").toUpperCase(); String remark = object.getString("remark"); String datasourceId = object.getString("datasource_id"); TableInfo tableInfo = new TableInfo(); tableInfo.setTablename(tableName); tableInfo.setDataSourceId(datasourceId); tableInfo.setRemark(remark); tableEntities.add(tableInfo); } } catch (Exception e) { e.printStackTrace(); }
}
private Map.Entry getDataSourceEntry(JSONObject jsObject) {
String key = jsObject.getString(“key”);
String url = jsObject.getString(“url”);
String driverclass = jsObject.getString(“driverclass”);
String username = jsObject.getString(“username”);
String password = jsObject.getString(“password”);DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(url); dataSource.setDriverClassName(driverclass); dataSource.setUsername(username); dataSource.setPassword(password); DefaultMapEntry entry = new DefaultMapEntry(key,dataSource); return entry;
}
public void importData(){
init();
Map<TableInfo,List> map = new HashMap<>();
for (TableInfo tableInfo:tableEntities){
List list = getTableEntity(tableInfo);
System.out.println(JSONArray.fromObject(list).toString() + “---------” + JSONArray.fromObject(tableEntities).toString());
map.put(tableInfo,list);
}
//导出word文档 调用工具类
wordExport.export(map);
}private List getTableEntity(TableInfo tableInfo) {
String tableName = tableInfo.getTablename();
DataSource dataSource = dataSourceMap.get(tableInfo.dataSourceId);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
StringBuffer sqlformat = new StringBuffer();
sqlformat.append(“SELECT t1.Column_Name ,”).append(" t1.DATA_TYPE ,t1.DATA_LENGTH ,t1.NullAble ,t2.Comments “);
sqlformat.append(” FROM cols t1");
sqlformat.append(" LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name");
sqlformat.append(" LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name");
sqlformat.append(" LEFT JOIN user_objects t4 ON t1.table_name = t4.OBJECT_NAME");
sqlformat.append(" WHERE");
sqlformat.append(" NOT EXISTS (SELECT t4.Object_Name FROM User_objects t4 WHERE t4.Object_Type = ‘TABLE’ AND t4.Temporary = ‘Y’ AND t4.Object_Name = t1.Table_Name ) “);
sqlformat.append(” and t1.Table_Name = ‘%s’");
sqlformat.append(" ORDER BY t1.Table_Name, t1.Column_ID");
String sql = String.format(sqlformat.toString(),tableName);
List tableEntities = new ArrayList();
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
for (Map<String, Object> map:list){
TableEntity tableEntity = new TableEntity();
String columnName = String.valueOf(map.get(“column_name”)).toUpperCase();
String dataType = String.valueOf(map.get(“DATA_TYPE”));
String length = String.valueOf(map.get(“DATA_length”));
String isNull= String.valueOf(map.get(“NULLABLE”));
String comments= String.valueOf(map.get(“Comments”));
tableEntity.setColumn_name(columnName);
tableEntity.setData_type(dataType);
tableEntity.setData_length(length);
tableEntity.setNullAble(isNull);
tableEntity.setComments(comments);
tableEntities.add(tableEntity);
}
return tableEntities;}
}
导出操作
package com.djhu.imp.utils;
import com.djhu.imp.service.SaveTemplateServiceImpl;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy;
import org.apache.poi.xwpf.usermodel.;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
/**
-
Created by zhouhs on 2017/1/9.
*/
@Service
public class WordExport {public void export(Map<SaveTemplateServiceImpl.TableInfo,List<SaveTemplateServiceImpl.TableEntity>> map) {
try {
//Blank Document
XWPFDocument document= new XWPFDocument();//Write the Document in file system FileOutputStream out = new FileOutputStream(new File("create_table.docx")); //添加标题 XWPFParagraph titleParagraph = document.createParagraph(); //设置段落居中 titleParagraph.setAlignment(ParagraphAlignment.CENTER); /* XWPFRun titleParagraphRun = titleParagraph.createRun(); titleParagraphRun.setText("Java PoI"); titleParagraphRun.setColor("000000"); titleParagraphRun.setFontSize(20);*/ this.settingParagraphs(document,map); CTSectPr sectPr = document.getDocument().getBody().addNewSectPr(); XWPFHeaderFooterPolicy policy = new XWPFHeaderFooterPolicy(document, sectPr); //添加页眉 CTP ctpHeader = CTP.Factory.newInstance(); CTR ctrHeader = ctpHeader.addNewR(); CTText ctHeader = ctrHeader.addNewT(); String headerText = "Java POI create MS word file."; ctHeader.setStringValue(headerText); XWPFParagraph headerParagraph = new XWPFParagraph(ctpHeader, document); //设置为右对齐 headerParagraph.setAlignment(ParagraphAlignment.RIGHT); XWPFParagraph[] parsHeader = new XWPFParagraph[1]; parsHeader[0] = headerParagraph; policy.createHeader(XWPFHeaderFooterPolicy.DEFAULT, parsHeader); //添加页脚 CTP ctpFooter = CTP.Factory.newInstance(); CTR ctrFooter = ctpFooter.addNewR(); CTText ctFooter = ctrFooter.addNewT(); String footerText = "http://blog.csdn.net/zhouseawater"; ctFooter.setStringValue(footerText); XWPFParagraph footerParagraph = new XWPFParagraph(ctpFooter, document); headerParagraph.setAlignment(ParagraphAlignment.CENTER); XWPFParagraph[] parsFooter = new XWPFParagraph[1]; parsFooter[0] = footerParagraph; policy.createFooter(XWPFHeaderFooterPolicy.DEFAULT, parsFooter); document.write(out); out.close(); System.out.println("create_table document written success."); } catch (IOException e) { e.printStackTrace(); }
}
//设置段落信息
public void settingParagraphs( XWPFDocument document,Map<SaveTemplateServiceImpl.TableInfo,List<SaveTemplateServiceImpl.TableEntity>> map){
for (SaveTemplateServiceImpl.TableInfo table:map.keySet()) {
//段落
XWPFParagraph firstParagraph = document.createParagraph();
XWPFRun run = firstParagraph.createRun();
run.setText(table.getRemark()+"("+table.getTablename()+")");
//设置字体颜色
run.setColor(“696969”);
run.setFontSize(16);
run.setBold(true); //加粗//设置段落背景颜色 CTShd cTShd = run.getCTR().addNewRPr().addNewShd(); cTShd.setVal(STShd.CLEAR);
// cTShd.setFill(“D3D3D3”);
//表格
XWPFTable ComTable = document.createTable();
//列宽自动分割
CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
comTableWidth.setType(STTblWidth.DXA);
comTableWidth.setW(BigInteger.valueOf(9072));
//表格第一行
XWPFTableRow comTableRowOne = ComTable.getRow(0);
comTableRowOne.getCell(0).setText("列名");
comTableRowOne.addNewTableCell().setText("类型");
comTableRowOne.addNewTableCell().setText("是否空");
comTableRowOne.addNewTableCell().setText("注释");
comTableRowOne.getCell(0).setColor("D3D3D3");
comTableRowOne.getCell(1).setColor("D3D3D3");
comTableRowOne.getCell(2).setColor("D3D3D3");
comTableRowOne.getCell(3).setColor("D3D3D3");
List<SaveTemplateServiceImpl.TableEntity> list = map.get(table);
for(int i=0;i<list.size();i++){
XWPFTableRow comTableRowTwo = ComTable.createRow();
comTableRowTwo.getCell(0).setText(list.get(i).getColumn_name());
comTableRowTwo.getCell(1).setText(list.get(i).getData_type()+"("+list.get(i).getData_length()+")");
comTableRowTwo.getCell(2).setText(list.get(i).getNullAble());
comTableRowTwo.getCell(3).setText(list.get(i).getComments());
}
//两个表格之间加个换行
XWPFParagraph paragraph = document.createParagraph();
XWPFRun paragraphRun = paragraph.createRun();
paragraphRun.setText("\r");
}
}
}
代码地址:https://download.csdn.net/download/qq_33337186/11504954