根据数据库表结构生成Excel表设计
在系统运维的过程中,很多时候大家都不喜欢改文档,修改了数据库字段后很少同步再更新表设计文档的,这就导致系统运行一段时间后,系统表设计与实际生产跑业务的设计出入很大,这个时候一个个去核对费时费力。所以就根据对应表结构直接生成相应的设计文档,省时省力。
设计思路
查询相应数据库视图,设计Excel版式,使用jxl来读取写入数据库查询出来的数据。
分为三部分进行生成:
汇总信息,查询用户表结构和注释
索引信息,查询用户表索引及约束
明细信息,查询用户表及用户列明细
最后查询数据分别写入Excel。
汇总信息查询
String[] colstitle = {"表名","类型","用途"};
try {
ExcelUtil.createExcel(fileName, colstitle, "汇总");
} catch (Exception e) {
e.printStackTrace();
throw new Exception("创建EXCEL失败",e);
}
//获取首页数据
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
conn = JdbcUtil.getConnection();
String sql = "select t.table_name 表名, t.TABLE_TYPE 类型, t.comments 用途" +
" from user_tab_comments t" +
" where t.TABLE_NAME in (select table_name from user_tables)" +
" order by t.table_name";
List<String> tableNames = new ArrayList<String>();
List<String[]> dataList = new ArrayList<String[]>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
String[] data = {rs.getString("表名"),rs.getString("类型"),rs.getString("用途")};
tableNames.add(rs.getString("表名"));
dataList.add(data);
// System.out.println("表名:" + rs.getString("表名") +
// "\t类型:" + rs.getString("类型") +
// "\t用途:" + rs.getString("用途")
// );
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new Exception("查询表信息失败",e);
} finally {
JdbcUtil.close(conn, ps, rs);
}
索引信息查询
//新增索引表内容
String sql1 = "SELECT t.TABLE_NAME," +
" t.INDEX_NAME," +
" LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY t.TABLE_NAME) INDEX_COLS" +
" from user_ind_columns t, user_indexes i,user_tables ut" +
" where t.index_name = i.index_name" +
" and t.table_name = i.table_name" +
" and ut.table_name = i.table_name" +
" GROUP BY t.TABLE_NAME, t.INDEX_NAME";
// 创建excel 表数据项
String[] colstitle1 = { "表名", "索引名", "索引字段" };
try {
conn = JdbcUtil.getConnection();
ExcelUtil.createSheet2(fileName, colstitle1, "表索引", 1);
ps = conn.prepareStatement(sql1);
rs = ps.executeQuery();
while (rs.next()) {
int rowIndex = ExcelUtil.getSheetLastLine(fileName, 1);
String[] data = { rs.getString("TABLE_NAME"), rs.getString("INDEX_NAME"), rs.getString("INDEX_COLS") };
System.out.println(rs.getString("TABLE_NAME") + "\t" + rs.getString("INDEX_NAME") + "\t"
+ rs.getString("INDEX_COLS"));
ExcelUtil.insertData(fileName, data, 1, rowIndex);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new Exception("查询索引表字段信息失败", e);
} finally {
JdbcUtil.close(conn, ps, rs);
}
明细信息查询
//新增表内容
String sql2 = "select t1.COLUMN_NAME 字段名," +
" t2.DATA_TYPE 类型," +
" t2.DATA_LENGTH 长度," +
" t2.NULLABLE 能否为空," +
" t1.COMMENTS 说明" +
" from user_col_comments t1, cols t2" +
" where t1.COLUMN_NAME = t2.COLUMN_NAME" +
" and t1.TABLE_NAME = t2.TABLE_NAME" +
" and t1.TABLE_NAME = ? " +
" order by t1.COLUMN_NAME ";
//创建excel 表数据项
String[] colstitle2 = {"字段名","类型","长度","能否为空","说明"};
try {
conn = JdbcUtil.getConnection();
int i=0;
for (i=0;i<tableNames.size();i++) {
ExcelUtil.createSheet(fileName, colstitle2, tableNames.get(i), i+3);
ps = conn.prepareStatement(sql2);
ps.setString(1, tableNames.get(i));
rs = ps.executeQuery();
System.out.println("表名:\t"+tableNames.get(i));
while (rs.next()){
int rowIndex = ExcelUtil.getSheetLastLine(fileName, i+1);
String[] data = {rs.getString("字段名"),rs.getString("类型"),rs.getString("长度"),rs.getString("能否为空"),rs.getString("说明")};
System.out.println(rs.getString("字段名")+"\t"+rs.getString("类型")+"\t"+rs.getString("长度")+"\t"+rs.getString("能否为空")+"\t"+rs.getString("说明"));
ExcelUtil.insertData(fileName, data, i+1, rowIndex);
}
// break;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new Exception("查询表字段信息失败",e);
} finally {
JdbcUtil.close(conn, ps, rs);
}
Excel操作
创建
public static String createSheet(String fileName, String[] colstitle, String sheetName, int sheetIndex)
throws IOException, JXLException {
File file = new File(dir + fileName + ".xls");
Workbook workbook = Workbook.getWorkbook(file);
WritableWorkbook workbook1 = Workbook.createWorkbook(file, workbook);
WritableSheet sheet = workbook1.createSheet(sheetName, sheetIndex);
//设置列宽
sheet.setColumnView(0, 30);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 80);
// 单元格样式
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
wcf.setBackground(jxl.format.Colour.GREY_25_PERCENT); // 设置单元格的背景颜色
wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
// 5:单元格
Label label = null;
// 6:给第一行设置列名
for (int i = 0; i < colstitle.length; i++) {
// x,y,第一行的列名
label = new Label(i, 0, colstitle[i], wcf);
// 7:添加单元格
sheet.addCell(label);
}
// 写入数据,一定记得写入数据,不然你都开始怀疑世界了,excel里面啥都没有
workbook1.write();
// 最后一步,关闭工作簿
workbook1.close();
return null;
}
写入
public static String insertData(String fileName, String[] data,int sheetIndex, int rowIndex) throws Exception {
File file = new File(dir + fileName + ".xls");
Workbook workbook = Workbook.getWorkbook(file);
WritableWorkbook workbook1 = Workbook.createWorkbook(file, workbook);
WritableSheet sheet = workbook1.getSheet(sheetIndex);
int i = rowIndex;
Label label = null;
for (i = 0; i < data.length; i++) {
label = new Label(i, rowIndex, data[i]);
sheet.addCell(label);
}
label = new Label(5, 0, "返回");
sheet.addCell(label);
WritableSheet sheet1 = workbook1.getSheet("汇总");
WritableHyperlink link = new WritableHyperlink(5,0,"返回",sheet1,5,0);
sheet.addHyperlink(link);
// 写入数据,一定记得写入数据,不然你都开始怀疑世界了,excel里面啥都没有
workbook1.write();
// 最后一步,关闭工作簿
workbook1.close();
return null;
}