參考:?POI生成Word多級標題格式_poi設置word標題-CSDN博客
1.概述
? ? ? ? 使用jdbc查詢數據庫把表信息導出為word文檔, 導出為word時需要下載word模板文件。
已實現數據庫:?
- KingbaseES, 實現代碼: 點擊跳轉
2.效果圖
2.1.生成word內容
所有數據庫合并
數據庫不合并
2.2.生成文件列表
3.代碼文件結構
4.代碼實現
4.1.dto
4.1.1.DbInfoDTO.java
package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@AllArgsConstructor
@NoArgsConstructor
public class DbInfoDTO {private String dbName;private String dbDescribe;private List<TableInfo> tableInfos;@Data@AllArgsConstructor@NoArgsConstructorpublic static class TableInfo {private String tableName;private String tableDescribe;private List<TableField> tableFields;private List<List<String>> tableContents;public TableInfo(String tableName, String tableDescribe, List<TableField> tableFields) {this.tableName = tableName;this.tableDescribe = tableDescribe;this.tableFields = tableFields;}}@Data@AllArgsConstructor@NoArgsConstructorpublic static class TableField {private String name;private String type;private Integer length;private String allowEmpty;private String isPrimaryKey;private String defaultValue;private String describe;}}
4.1.2.DbConnParamDTO.java
package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@AllArgsConstructor
@NoArgsConstructor
public class DbConnParamDTO {private String host;private Integer port;private String username;private String password;// 隨機填一個數據庫即可private String randomDatabase;// 數據庫前綴過濾private String dbPrefixFilter;// 等于true是只會導出第一張表private Boolean testRun;
}
4.1.3.ExportWordParamDTO
package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExportWordParamDTO {// 數據信息private List<DbInfoDTO> ds;// 導出word模板文件private String wordTemplateFile;// 導出目錄private String exportFolder;// 所有數據庫是否合并到一個文檔, 默認每個數據庫一個文檔private Boolean merge = Boolean.FALSE;
}
4.2.util
4.2.1.CommonUtil.java
package cn.nordrassil.db2doc.util;import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;public class CommonUtil {private static DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");private static DateTimeFormatter dateFormatter2 = DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss");public static String readByLineRs(String path) {StringBuffer sb = new StringBuffer();File file = new File(path);BufferedReader reader = null;String temp = null;try {reader = new BufferedReader(new FileReader(file));while ((temp = reader.readLine()) != null) {sb.append(temp).append("\n");}} catch (Exception e) {e.printStackTrace();} finally {if (reader != null) {try {reader.close();} catch (Exception e) {e.printStackTrace();}}}return sb.toString();}/*** 依次替換{}* 例 : join("a={},b={}",1,2) => a=1,b=2** @param str* @param param* @return*/public static String join(String str, Object... param) {try {for (Object p : param) {String processedParam = p.toString().replace("\\", "\\\\");str = str.replaceFirst("\\{\\}", processedParam);}} catch (Exception e) {for (Object p : param) {str = str + p;}}return str;}public static void writeString(String content, String path) {try {File file = new File(path);if (!file.exists()) {file.createNewFile();}FileWriter fw = new FileWriter(file.getAbsoluteFile());BufferedWriter bw = new BufferedWriter(fw);bw.write(content);bw.close();} catch (IOException e) {e.printStackTrace();}}public static void info(String str, Object... param) {System.out.println(LocalDateTime.now().format(dateFormatter) + "|" + join(str, param));}public static String getDayTime() {return LocalDateTime.now().format(dateFormatter2);}public static void createDirectoryIfNotExists(String absolutePath) {File file = new File(absolutePath);// 獲取文件所在的目錄File parentDir = file.getParentFile();if (parentDir != null && !parentDir.exists()) {// 創建目錄parentDir.mkdirs();}}}
4.3.service
4.3.1.DbInfoExportWordService.java
package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbInfoDTO;
import cn.nordrassil.db2doc.dto.ExportWordParamDTO;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTStyles;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;import static cn.nordrassil.db2doc.util.CommonUtil.*;// 參考: https://blog.csdn.net/qq_37945565/article/details/121518330public class DbInfoExportWordService {public static List<String> tableTitles = new ArrayList<String>() {{add("序號");add("字段名");add("字段類型");add("長度");add("非空");add("主鍵");add("默認值");add("注釋");}};public static List<String> fields = new ArrayList<String>() {{add("name");add("type");add("length");add("allowEmpty");add("isPrimaryKey");add("defaultValue");add("describe");}};public static void generateWord(ExportWordParamDTO paramDTO) throws Exception {// 獲得模板文檔的整體樣式XWPFDocument template = new XWPFDocument(new FileInputStream(paramDTO.getWordTemplateFile()));CTStyles wordStyles = template.getStyle();int totalTableCount = 0;int index = 1;List<DbInfoDTO> ds = paramDTO.getDs();XWPFDocument document = null;boolean merge = paramDTO.getMerge() != null && paramDTO.getMerge();if (merge) {document = new XWPFDocument();}for (DbInfoDTO d : ds) {info("執行第[{}/{}]生成word文檔...表數量:{}", index, ds.size(), d.getTableInfos().size());if (!merge) {document = new XWPFDocument();}// 獲取新建文檔對象的樣式XWPFStyles newStyles = document.createStyles();// 關鍵行// 修改設置文檔樣式為靜態塊中讀取到的樣式newStyles.setStyles(wordStyles);if (merge) {XWPFParagraph title1 = document.createParagraph();title1.setStyle("2");XWPFRun titleRun1 = title1.createRun();String dbText = index + "." + d.getDbName();if (d.getDbDescribe() != null) {dbText += "(" + d.getDbDescribe().replaceAll("\r\n", "").replaceAll("\r", "").replaceAll("\n", "") + ")";}titleRun1.setText(dbText);}List<DbInfoDTO.TableInfo> tableInfos = d.getTableInfos();totalTableCount += tableInfos.size();for (int k = 0; k < tableInfos.size(); k++) {DbInfoDTO.TableInfo tableInfo = tableInfos.get(k);XWPFParagraph title2 = document.createParagraph();XWPFRun titleRun2 = title2.createRun();String tableText;if (merge) {title2.setStyle("3");tableText = index + "." + (k + 1) + "." + tableInfo.getTableName();} else {title2.setStyle("2");tableText = (k + 1) + "." + tableInfo.getTableName();}if (tableInfo.getTableDescribe() != null) {tableText += "(" + tableInfo.getTableDescribe().replaceAll("\r\n", "").replaceAll("\r", "").replaceAll("\n", "") + ")";}titleRun2.setText(tableText);// 創建表格(第一列為序號)List<DbInfoDTO.TableField> tableFields = tableInfo.getTableFields();XWPFTable table = document.createTable(tableFields.size(), 8);int titleFontSize = 10;int textFontSize = 8;// 設置表格標題樣式for (int i = 0; i < table.getRows().get(0).getTableCells().size(); i++) {XWPFTableCell cell = table.getRows().get(0).getCell(i);XWPFParagraph cellParagraph = cell.getParagraphs().get(0);cellParagraph.setIndentationLeft(0); // 設置左側縮進為0cellParagraph.setIndentationRight(0); // 設置右側縮進為0cellParagraph.setIndentationFirstLine(0); // 設置首行縮進為0cellParagraph.setIndentationLeft(0);// 設置單元格背景色為灰色CTTcPr cellPr = cell.getCTTc().addNewTcPr();CTShd shd = cellPr.addNewShd();shd.setFill("D3D3D3");cellParagraph.setAlignment(ParagraphAlignment.CENTER);XWPFRun cellRun = cellParagraph.createRun();cellRun.setBold(true);cellRun.setText(tableTitles.get(i));cellRun.setFontSize(titleFontSize);}// 寫入表格內容for (int row = 1; row < table.getNumberOfRows(); row++) {for (int col = 0; col < table.getRow(row).getTableCells().size(); col++) {XWPFTableCell cell = table.getRow(row).getCell(col);XWPFParagraph cellParagraph = cell.getParagraphs().get(0);XWPFRun cellRun = cellParagraph.createRun();if (col == 0) {cellRun.setText(row + "");} else {DbInfoDTO.TableField tableField = tableFields.get(row - 1);JSONObject jsonObject = JSONObject.parseObject(JSONObject.toJSONString(tableField));cellRun.setText(jsonObject.getString(fields.get(col - 1)));}CTTcPr tcPr = cell.getCTTc().getTcPr();if (tcPr == null) {tcPr = cell.getCTTc().addNewTcPr();}if (!tcPr.isSetTcMar()) {tcPr.addNewTcMar();}tcPr.getTcMar().addNewLeft().setW(0);tcPr.getTcMar().addNewRight().setW(0);tcPr.getTcMar().addNewTop().setW(0);tcPr.getTcMar().addNewBottom().setW(0);cellRun.setFontSize(textFontSize);cellParagraph.setIndentationLeft(0);// 設置段落左對齊cellParagraph.setAlignment(ParagraphAlignment.LEFT);// 設置左縮進為 0cellParagraph.setIndentationLeft(0);}}}if (!merge) {// 保存文檔String outputFile = paramDTO.getExportFolder() + File.separator + d.getDbName() + ".docx";createDirectoryIfNotExists(outputFile);try (FileOutputStream out = new FileOutputStream(outputFile)) {document.write(out);info("第[{}/{}]數據庫信息生成word文檔完成, 文檔路徑:{}", index, ds.size(), outputFile);} catch (IOException e) {e.printStackTrace();}}index++;}if (merge) {// 保存文檔String outputFile = paramDTO.getExportFolder() + File.separator + getDayTime() + "_all_db.docx";createDirectoryIfNotExists(outputFile);try (FileOutputStream out = new FileOutputStream(outputFile)) {document.write(out);info("所有數據庫信息生成word文檔完成, 文檔路徑:{}", outputFile);} catch (IOException e) {e.printStackTrace();}}info("數據庫已導出為word文檔完成, 數據庫數量:{}, 所有表總數量:{}", ds.size(), totalTableCount);}
}
4.3.2.GetDbInfoService.java
package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbConnParamDTO;
import cn.nordrassil.db2doc.dto.DbInfoDTO;import java.util.List;public interface GetDbInfoService {List<DbInfoDTO> get(DbConnParamDTO dto);
}
5.依賴?
5.1.mave依賴
<dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.83</version><scope>compile</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.28</version><scope>provided</scope></dependency><!-- Apache Commons Compress 用于處理多種歸檔文件格式 --><dependency><groupId>org.apache.commons</groupId><artifactId>commons-compress</artifactId><version>1.24.0</version></dependency><!-- Apache Commons IO 提供了 IO 操作的工具類 --><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.13.0</version></dependency><dependency><groupId>com.github.junrar</groupId><artifactId>junrar</artifactId><version>7.5.5</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency>5.2.word文檔模板
word/word模板.docx · 楽焫旒璡/public-share - 碼云 - 開源中國
6.測試
6.1.MyTest.java
package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbConnParamDTO;
import cn.nordrassil.db2doc.dto.DbInfoDTO;
import cn.nordrassil.db2doc.dto.ExportWordParamDTO;
import cn.nordrassil.db2doc.impl.GetDbInfoKingBaseEsService;
import cn.nordrassil.db2doc.util.CommonUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;import java.io.File;
import java.util.ArrayList;
import java.util.List;public class MyTest {public static void main(String[] args) throws Exception {String exportFolder = System.getProperty("user.dir") + File.separator + "files";String templateFile = "F:\\文檔\\開發\\word模板.docx";String resultFile = exportFolder + File.separator + "result.json";//getDbInfo(resultFile);List<DbInfoDTO> ds = readData(resultFile);ExportWordParamDTO exportWordParamDTO = new ExportWordParamDTO(ds, templateFile, exportFolder, true);//DbInfoExportWordService.generateWord(exportWordParamDTO);}public static void getDbInfo(String resultFile) throws Exception {GetDbInfoService getDbInfoService = new GetDbInfoKingBaseEsService();List<DbInfoDTO> dbInfoDTOS = getDbInfoService.get(new DbConnParamDTO("192.168.1.1",5432,"root","123456","test","test",true));CommonUtil.writeString(JSONObject.toJSONString(dbInfoDTOS), resultFile);}public static List<DbInfoDTO> readData(String resultFile) {JSONArray jas = JSONObject.parseArray(CommonUtil.readByLineRs(resultFile));List<DbInfoDTO> ds = new ArrayList<>();for (int i = 0; i < jas.size(); i++) {JSONObject ja = jas.getJSONObject(i);DbInfoDTO dbInfo = new DbInfoDTO();dbInfo.setDbName(ja.getString("dbName"));dbInfo.setDbDescribe(ja.getString("dbDescribe"));List<DbInfoDTO.TableInfo> tableInfos = new ArrayList<>();dbInfo.setTableInfos(tableInfos);JSONArray tis = ja.getJSONArray("tableInfos");for (int k = 0; k < tis.size(); k++) {JSONObject ti = tis.getJSONObject(k);DbInfoDTO.TableInfo tableInfo = new DbInfoDTO.TableInfo();tableInfos.add(tableInfo);tableInfo.setTableName(ti.getString("tableName"));tableInfo.setTableDescribe(ti.getString("tableDescribe"));JSONArray tfs = ti.getJSONArray("tableFields");List<DbInfoDTO.TableField> tableFields = new ArrayList<>();tableInfo.setTableFields(tableFields);for (int j = 0; j < tfs.size(); j++) {JSONObject tf = tfs.getJSONObject(j);tableFields.add(new DbInfoDTO.TableField(tf.getString("name"),tf.getString("type"),tf.getInteger("length"),tf.getString("allowEmpty"),tf.getString("isPrimaryKey"),tf.getString("defaultValue"),tf.getString("describe")));}}ds.add(dbInfo);}return ds;}
}
)
(C/C++))
