根据数据库表结构生成Excel表设计——源码设计说明

根据数据库表结构生成Excel表设计

在系统运维的过程中，很多时候大家都不喜欢改文档，修改了数据库字段后很少同步再更新表设计文档的，这就导致系统运行一段时间后，系统表设计与实际生产跑业务的设计出入很大，这个时候一个个去核对费时费力。所以就根据对应表结构直接生成相应的设计文档，省时省力。

序号	内容	连接地址
1	工具使用说明	https://blog.csdn.net/qq_21271511/article/details/124940471
2	工具下载地址	https://download.csdn.net/download/qq_21271511/85448701
3	源码设计说明	https://blog.csdn.net/qq_21271511/article/details/125188813
4	源码下载地址	https://download.csdn.net/download/qq_21271511/62264071

设计思路

查询相应数据库视图，设计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;
	}

相关sql语句

https://blog.csdn.net/qq_21271511/article/details/125196712