oracle数据库表,字段信息导出为excel

package com.excel.util; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; public class Test { private static final String SQL1 = "SELECT TABLE_NAME, TABLE_TYPE, COMMENTS FROM USER_TAB_COMMENTS"; private static final String SQL2 = "select distinct a.column_name as COLUMN_NAME, a.data_type as dataType, a.DATA_LENGTH as dataLength, b.comments as COMMENTS from User_Tab_Cols a, USER_COL_COMMENTS b where b.column_name = a.column_name and b.table_name = a.table_name and a.table_name ="; public static void main(String[] args) { System.out.println("程序开始"); HSSFWorkbook wb = new HSSFWorkbook(); ResultSet rs = null; try { Statement st = JdbcUtil.getConn().createStatement(); rs = st.executeQuery(SQL1); HSSFSheet sheet = wb.createSheet("数据表"); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 3500); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); cell.setCellValue("表名"); cell1.setCellValue("注释"); HSSFCellStyle linkStyle = wb.createCellStyle(); HSSFFont cellFont= wb.createFont(); cellFont.setUnderline((byte) 1); cellFont.setColor(HSSFColor.BLUE.index); linkStyle.setFont(cellFont); int i = 1; while(rs.next()){ String tableName = rs.getString("TABLE_NAME"); String comments = rs.getString("COMMENTS"); HSSFRow r = sheet.createRow(i); HSSFCell c = r.createCell(0); HSSFCell c1 = r.createCell(1); c1.setCellValue(comments); c.setCellType(HSSFCell.CELL_TYPE_FORMULA); c.setCellFormula("HYPERLINK(\"[suyuan.xls]'"+tableName+"'!A1\",\""+tableName+"\")"); c.setCellStyle(linkStyle); getList(tableName, wb); i++; } try { FileOutputStream os = new FileOutputStream("e:\\suyuan.xls"); wb.write(os); os.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("执行结束"); JdbcUtil.closeConn(); } catch (SQLException e) { e.printStackTrace(); } } private static void getList(String tableName, HSSFWorkbook wb) { ResultSet rs = null; HSSFSheet sheet = wb.createSheet(tableName); HSSFCellStyle linkStyle = wb.createCellStyle(); HSSFFont cellFont= wb.createFont(); cellFont.setUnderline((byte) 1); cellFont.setColor(HSSFColor.BLUE.index); linkStyle.setFont(cellFont); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 3500); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); cell.setCellValue("字段"); cell1.setCellValue("注释"); cell2.setCellValue("类型"); cell3.setCellValue("长度"); try { Statement st = JdbcUtil.getConn().createStatement(); rs = st.executeQuery(SQL2 + "'" + tableName + "'"); int i = 1; while(rs.next()){ String dataType = rs.getString("dataType"); String dataLength = rs.getString("dataLength"); HSSFRow r = sheet.createRow(i); HSSFCell c = r.createCell(0); HSSFCell c1 = r.createCell(1); HSSFCell c2 = r.createCell(2); HSSFCell c3 = r.createCell(3); String columName = rs.getString("COLUMN_NAME"); String comments = rs.getString("COMMENTS"); c.setCellValue(columName); c1.setCellValue(comments); c2.setCellValue(dataType); c3.setCellValue(dataLength); i++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }