由于畢業(yè)設(shè)計要從Mysql中導出許多表，如果一張一張重新編寫會比較復雜，因此在借鑒大佬代碼的基礎(chǔ)上，完善了以下功能：

1、更換字體和字體大小

BaseFont bfComic0 = BaseFont.createFont("C:\\Windows\\Fonts\\simsunb.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED); Font font = new Font(bfComic0, 10.5f); /**其中content為寫入table.cell單元中的內(nèi)容 **/ Cell cell=new Cell(new Paragraph(content,font));

2、實現(xiàn)table表格中三線格的設(shè)計

3 //隱藏上邊框 4 // cell.disableBorderSide(1); 5 //隱藏下邊框 6 // cell.disableBorderSide(2); 7 //隱藏上、下邊框 8 //cell.disableBorderSide(3); 9 //隱藏左邊框 10 //cell.disableBorderSide(4); 11 //隱藏左、上邊框 12 //cell.disableBorderSide(5); 13 //隱藏左、下邊框 14 //cell.disableBorderSide(6); 15 //隱藏左、上、下邊框 16 //cell.disableBorderSide(7); 17 //隱藏右邊框 18 //cell.disableBorderSide(8); 19 //隱藏右、上邊框 20 // cell.disableBorderSide(9); 21 //隱藏右、下邊框 22 //cell.disableBorderSide(10); 23 //隱藏右、上、下邊框 24 //cell.disableBorderSide(11); 25 //隱藏左、右邊框 26 //cell.disableBorderSide(12);//左右沒了 27 //隱藏上、左、右邊框 28 //cell.disableBorderSide(13);//只剩下 29 //隱藏下、左、右邊框 30 //cell.disableBorderSide(14);//只剩上 31 //隱藏全部 32 //cell.disableBorderSide(15);//全沒了

閱讀指南

本文需要對Java com.lowagie.text包具有一定的理解，具體可參考工具網(wǎng)站：lowagie包

最終效果如圖所示：

另外由于字段名、數(shù)據(jù)類型，長度，缺省值，備注要求使用Times_newRoman字體，所以分列實現(xiàn)了英文字體和宋體的分別顯示
備注：本文默認使用10.5f------五號字體

全部代碼如下：

新建maven項目
之后在pom.xml中加入如下代碼

<dependencies><dependency><groupId>com.lowagie</groupId><artifactId>itext</artifactId><version>2.1.7</version></dependency><dependency><groupId>com.itextpdf</groupId><artifactId>itext-asian</artifactId><version>5.2.0</version></dependency><dependency><groupId>com.lowagie</groupId><artifactId>itext-rtf</artifactId><version>2.1.7</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.10</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional><version>1.18.0</version></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.9</version></dependency></dependencies>

新建POITableToWordUtil.java文件，寫入如下代碼：

import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.util.JdbcUtils; //import com.lowagie.text.Font; import com.lowagie.text.*; import com.lowagie.text.Font; import com.lowagie.text.pdf.BaseFont; import com.lowagie.text.rtf.RtfWriter2; import lombok.Data; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.util.StringUtil; import org.assertj.core.util.Lists; import sun.font.FontFamily;import javax.sql.DataSource; import java.awt.*; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.sql.*; import java.util.Arrays; import java.util.List;@Data class TableInfo {private String tblName;private String tblType;private String tblComment; }@Data class TableFiled {private String field;private String type;private String length;private boolean isNull;private String key;private String defaultVal;private String extra;private String comment; }public class POITableToWordUtil {public static String GEN_FILE_PATH = "生成doc文件存儲的目錄";private static String dbHost = "127.0.0.1";private static int dbPort = 3306;private static String dbName = "你的數(shù)據(jù)庫名稱";private static String userName = "你的數(shù)據(jù)庫賬號";private static String password = "你的數(shù)據(jù)庫密碼";public static void main(String[] args) throws SQLException {DataSource ds = getDataSource();table2Word(ds, dbName, dbName + ".doc");}/*** 生成word文檔** @param ds：數(shù)據(jù)源* @param fileName：生成文件地址* @return: void*/public static void table2Word(DataSource ds, String databaseName, String fileName) throws SQLException {List<TableInfo> tables = getTableInfos(ds, databaseName);Document document = new Document(PageSize.A4);try {File dir = new File(GEN_FILE_PATH);if (!dir.exists()) {dir.mkdirs();}fileName = GEN_FILE_PATH + File.separator + fileName;File file = new File(fileName);if (file.exists() && file.isFile()) {file.delete();}file.createNewFile();// 寫入文件信息RtfWriter2.getInstance(document, new FileOutputStream(fileName));document.open();// gebTableInfoDesc(document, tables);genTableStructDesc(document, tables, ds);document.close();} catch (Exception e) {e.printStackTrace();}printMsg("所有表【共%d個】已經(jīng)處理完成", tables.size());}private static void gebTableInfoDesc(Document document, List<TableInfo> tables) throws DocumentException {Paragraph ph = new Paragraph();Paragraph p = new Paragraph("表清單描述", new Font(Font.TIMES_ROMAN, 24, Font.NORMAL, new Color(0, 0, 0)));p.setAlignment(Element.ALIGN_LEFT);document.add(p);printMsg("產(chǎn)生表清單開始");Table table = new Table(2);int[] widths = new int[]{500, 900};table.setWidths(widths);table.setBorderWidth(1);table.setPadding(0);table.setSpacing(0);//添加表頭行Cell headerCell = new Cell("表名");headerCell.setHorizontalAlignment(Element.ALIGN_LEFT);headerCell.setBackgroundColor(new Color(192, 192, 192));table.addCell(headerCell);headerCell = new Cell("表描述");headerCell.setHorizontalAlignment(Element.ALIGN_LEFT);headerCell.setBackgroundColor(new Color(192, 192, 192));table.addCell(headerCell);table.endHeaders();for (TableInfo tableInfo : tables) {addCell(table, tableInfo.getTblName());addCell(table, tableInfo.getTblComment());}document.add(table);printMsg("產(chǎn)生表清單結(jié)束");}private static void genTableStructDesc(Document document, List<TableInfo> tables, DataSource ds) throws DocumentException, SQLException, IOException {Paragraph p = new Paragraph("表結(jié)構(gòu)描述", new Font(Font.TIMES_ROMAN, 24, Font.NORMAL, new Color(0, 0, 0)));p.setAlignment(Element.ALIGN_CENTER);document.add(p);printMsg("共需要處理%d個表", tables.size());int colNum = 9;//循環(huán)處理每一張表for (int i = 0; i < tables.size(); i++) {TableInfo tableInfo = tables.get(i);String tblName = tableInfo.getTblName();String tblComment = tableInfo.getTblComment();printMsg("處理%s表開始", tableInfo);//寫入表說明 // String tblTile = "" + (i + 1) + " 表名稱:" + tblName + "（" + tblComment + "）"; // Paragraph paragraph = new Paragraph(tblTile); // document.add(paragraph);List<TableFiled> fileds = getTableFields(ds, tables.get(i).getTblName());Table table = new Table(colNum);int[] widths = new int[]{160, 250, 350, 160, 80, 80, 160, 80, 80};table.setWidths(widths); // table.setBorderWidth(1);table.setPadding(0);table.setSpacing(0);// 添加表名行String tblInfo = StringUtils.isBlank(tblComment) ? tblName : String.format("%s(%s)", tblName, tblComment); // Cell headerCell = new Cell(tblInfo); // // headerCell.disableBorderSide(15); // // // headerCell.setColspan(colNum); // headerCell.setHorizontalAlignment(Element.ALIGN_CENTER); // table.addCell(headerCell);Paragraph ph = new Paragraph(tblInfo, new Font(Font.TIMES_ROMAN, 24, Font.NORMAL, new Color(0, 0, 0)));document.add(ph);BaseFont bfComic0 = BaseFont.createFont("C:\\Windows\\Fonts\\simsunb.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);Font font = new Font(bfComic0, 10.5f);//添加表頭行addCell(table, "字段名",0,font);addCell(table, "字段描述",0,font);addCell(table, "數(shù)據(jù)類型",0,font);addCell(table, "長度",0,font);addCell(table, "可空",0,font);addCell(table, "是否主鍵",0,font);addCell(table, "約束",0,font);addCell(table, "缺省值",0,font);addCell(table, "備注",0,font);table.endHeaders();int k;// 表格的主體for (k = 0; k < fileds.size()-1; k++) {TableFiled field = fileds.get(k);addCell(table, field.getField());addCell(table, field.getComment(),font);addCell(table, field.getType());addCell(table, field.getLength());addCell(table, field.isNull() ? "是" : "否",font);addCell(table, field.getKey().equals("PRI") ? "是" : "否",font);addCell(table, "",font);addCell(table, field.getDefaultVal());addCell(table, field.getExtra());}//生成表格/*** 最后一行*/if(k==fileds.size()-1){TableFiled field = fileds.get(k);addCell(table, field.getField(),1);addCell(table, field.getComment(),1,font);addCell(table, field.getType(),1);addCell(table, field.getLength(),1);addCell(table, field.isNull() ? "是" : "否",1,font);addCell(table, field.getKey().equals("PRI") ? "是" : "否",1,font);addCell(table, "",1,font);addCell(table, field.getDefaultVal(),1);addCell(table, field.getExtra(),1);}// table.setBorder(2); // table.setBorderWidth(15f);document.add(table);printMsg("處理%s表結(jié)束", tableInfo);}}// private static void addCell(Table table, String content, int width) { // addCell(table, content, width, Element.ALIGN_CENTER); // } private static void addCell(Table table, String content, int flag) {addCell(table, content, -1, Element.ALIGN_CENTER,flag); }private static void addCell(Table table, String content, int flag,Font font) {addCell(table, content, -1, Element.ALIGN_CENTER,flag,font);}private static void addCell(Table table, String content,Font font){addCell(table, content, -1, Element.ALIGN_CENTER,font);}private static void addCell(Table table, String content){addCell(table, content, -1, Element.ALIGN_CENTER);}/*** 添加表頭到表格** @param table* @param content* @param width* @param align*/private static void addCell(Table table, String content, int width, int align,Font font) { // Font font = new Font(Font.TIMES_ROMAN, 5, Font.BOLD); // Cell cell = new Cell(content); // if (width > 0) // cell.setWidth(width); // cell.setHorizontalAlignment(align); // cell.disableBorderSide(15); // table.addCell(cell);try{Cell cell=new Cell(new Paragraph(content,font));if (width > 0)cell.setWidth(width);cell.setHorizontalAlignment(align);cell.disableBorderSide(15);table.addCell(cell);}catch (Exception e){e.printStackTrace();}}private static void addCell(Table table, String content, int width, int align) {Font font = new Font(Font.TIMES_ROMAN, 10.5f, Font.NORMAL);try{Cell cell=new Cell(new Paragraph(content,font));if (width > 0)cell.setWidth(width);cell.setHorizontalAlignment(align);cell.disableBorderSide(15);table.addCell(cell);}catch (Exception e){e.printStackTrace();}}/**** @param table* @param content* @param width* @param align*/private static void addCell(Table table, String content, int width, int align,int flag) {try{Font font = new Font(Font.TIMES_ROMAN, 10.5f, Font.NORMAL);Cell cell = new Cell(new Paragraph(content,font));if (width > 0)cell.setWidth(width);cell.setHorizontalAlignment(align);//0---header,有上下邊界,1----有下邊界if(flag==0){cell.disableBorderSide(12);cell.setBorderColorTop(new Color(0, 0, 0));cell.setBorderWidthTop(3f);cell.setBorderColorBottom(new Color(0, 0, 0));cell.setBorderWidthBottom(3f); // cell.Border = Rectangle.RIGHT_BORDER | Rectangle.TOP_BORDER | Rectangle.BOTTOM_BORDER; // cell.setBorderWidth(3f); // cell.setBackgroundColor(new Color(192, 192, 192));}else{cell.disableBorderSide(13);cell.setBorderColorBottom(new Color(0, 0, 0));cell.setBorderWidthBottom(3f);}table.addCell(cell);}catch (Exception e){e.printStackTrace();}}private static void addCell(Table table, String content, int width, int align,int flag,Font font) {try{Cell cell = new Cell(new Paragraph(content,font));if (width > 0)cell.setWidth(width);cell.setHorizontalAlignment(align);//0---header,有上下邊界,1----有下邊界if(flag==0){cell.disableBorderSide(12);cell.setBorderColorTop(new Color(0, 0, 0));cell.setBorderWidthTop(3f);cell.setBorderColorBottom(new Color(0, 0, 0));cell.setBorderWidthBottom(3f); // cell.Border = Rectangle.RIGHT_BORDER | Rectangle.TOP_BORDER | Rectangle.BOTTOM_BORDER; // cell.setBorderWidth(3f); // cell.setBackgroundColor(new Color(192, 192, 192));}else{cell.disableBorderSide(13);cell.setBorderColorBottom(new Color(0, 0, 0));cell.setBorderWidthBottom(3f);}table.addCell(cell);}catch (Exception e){e.printStackTrace();}}private static void printMsg(String format, Object... args) {System.out.println(String.format(format, args));}private static List<TableInfo> getTableInfos(DataSource ds, String databaseName) throws SQLException {Connection conn = null;PreparedStatement stmt = null;ResultSet rs = null;List<TableInfo> list = Lists.newArrayList();try {conn = ds.getConnection();String sql = "select TABLE_NAME,TABLE_TYPE,TABLE_COMMENT from information_schema.tables where table_schema =? order by table_name";stmt = conn.prepareStatement(sql);setParameters(stmt, Arrays.asList(databaseName));rs = stmt.executeQuery();ResultSetMetaData rsMeta = rs.getMetaData();while (rs.next()) {TableInfo row = new TableInfo();row.setTblName(rs.getString(1));row.setTblType(rs.getString(2));row.setTblComment(rs.getString(3));list.add(row);}} finally {JdbcUtils.close(rs);JdbcUtils.close(stmt);JdbcUtils.close(conn);}return list;}private static List<TableFiled> getTableFields(DataSource ds, String tblName) throws SQLException {Connection conn = null;PreparedStatement stmt = null;ResultSet rs = null;List<TableFiled> list = Lists.newArrayList();try {conn = ds.getConnection();//返回的列順序是: Field,Type,Collation,Null,Key,Default,Extra,Privileges,CommentString sql = "SHOW FULL FIELDS FROM " + tblName;//返回的列順序是: Field,Type,Null,Key,Default,Extra // sql = "show columns FROM " + tblName;stmt = conn.prepareStatement(sql);rs = stmt.executeQuery();ResultSetMetaData rsMeta = rs.getMetaData();while (rs.next()) {TableFiled field = new TableFiled();field.setField(rs.getString(1));String type = rs.getString(2);String length = "";if (type.contains("(")) {int idx = type.indexOf("(");length = type.substring(idx + 1, type.length() - 1);type = type.substring(0, idx);}field.setType(type);field.setLength(length);field.setNull(rs.getString(4).equalsIgnoreCase("YES") ? true : false);field.setKey(rs.getString(5));field.setDefaultVal(rs.getString(6));field.setExtra(rs.getString(7));field.setComment(rs.getString(9));list.add(field);}} finally {JdbcUtils.close(rs);JdbcUtils.close(stmt);JdbcUtils.close(conn);}return list;}private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {for (int i = 0, size = parameters.size(); i < size; ++i) {Object param = parameters.get(i);stmt.setObject(i + 1, param);}}private static DataSource getDataSource() {DruidDataSource datasource = new DruidDataSource();datasource.setUrl(String.format("jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=false", dbHost, dbPort, dbName));datasource.setUsername(userName);datasource.setPassword(password);datasource.setDriverClassName("com.mysql.jdbc.Driver");datasource.setInitialSize(1);datasource.setMinIdle(1);datasource.setMaxActive(3);datasource.setMaxWait(60000);return datasource;} }

代碼仍然不夠精簡，希望和小伙伴們多多交流~~~

總結(jié)

以上是生活随笔為你收集整理的将mysql数据库批量导出为word三线表格形式的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。