由于毕业设计要从Mysql中导出许多表，如果一张一张重新编写会比较复杂，因此在借鉴大佬代码的基础上，完善了以下功能：
1、更换字体和字体大小BaseFont bfComic0 = BaseFont.createFont("C:\Windows\Fonts\simsunb.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
Font font = new Font(bfComic0, 10.5f);
Cell cell=new Cell(new Paragraph(content,font));
2、实现table表格中三线格的设计
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包具有一定的理解，具体可参考工具网站：lowagie包
最终效果如图所示：
另外由于字段名、数据类型，长度，缺省值，备注要求使用Times_newRoman字体，所以分列实现了英文字体和宋体的分别显示
备注：本文默认使用10.5f------五号字体
新建maven项目
之后在pom.xml中加入如下代码
com.lowagie itext 2.1.7 com.itextpdf itext-asian 5.2.0 com.lowagie itext-rtf 2.1.7 mysql mysql-connector-java 5.1.47 com.alibaba druid 1.1.10 org.projectlombok lombok true 1.18.0 org.apache.commons commons-lang3 3.9
新建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 = "你的数据库名称";
private static String userName = "你的数据库账号";
private static String password = "你的数据库密码";
public static void main(String[] args) throws SQLException {
DataSource ds = getDataSource();
table2Word(ds, dbName, dbName + ".doc");
}
public static void table2Word(DataSource ds, String databaseName, String fileName) throws SQLException {
List 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个】已经处理完成", tables.size());
}
private static void gebTableInfoDesc(Document document, List 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("产生表清单开始");
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("产生表清单结束");
}
private static void genTableStructDesc(Document document, List tables, DataSource ds) throws DocumentException, SQLException, IOException {
Paragraph p = new Paragraph("表结构描述", 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;
//循环处理每一张表
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 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, "数据类型",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表结束", 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);
}
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();
}
}
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 getTableInfos(DataSource ds, String databaseName) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List 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 getTableFields(DataSource ds, String tblName) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List list = Lists.newArrayList();
try {
conn = ds.getConnection();
//返回的列顺序是: Field,Type,Collation,Null,Key,Default,Extra,Privileges,Comment
String 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
代码仍然不够精简，希望和小伙伴们多多交流~~~
