利用POI将数据库表结构信息导入excel模板中

周末闲来无事，想工作中经常要导出数据库的表信息到excel作为文档使用，琢磨一下，用poi来根据固定的模板导出一个excel数据表信息，这个类非原创，但是有所加工改进，写给大家分享。所需的poi核心jar包为poi-3.0.1-FINAL-20070705.jar。有点老旧，能用就行，下面贴上代码，有注释：

import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.lang.reflect.InvocationTargetException; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.commons.beanutils.BeanUtils; 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.HSSFFooter; import org.apache.poi.hssf.usermodel.HSSFPrintSetup; 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.Region; /* * author shenruipeng * date 2014/5/10 * 把数据库的表结构导出指定格式的excel中 */ public class DbToExecl { private static String model_path = "d:/test.xls"; // 模版位置 private static String distinct_path = "d:/nn";// 存放目标位置 private static HSSFWorkbook model_wb = null; // 模版对象 private static String coversheet = "0";// 模板所在的sheet private static int cellcounts = 15;// 列数 private static Map fontMap = new HashMap();// 字体 private static Map styleMap = new HashMap();// excel样式 private static String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; private static String db_userid = "hzzk"; private static String db_password = "hzzk"; private static String remarksReporting = "true"; private static Connection conn = null; // 连接 private static String db_driver = "oracle.jdbc.driver.OracleDriver"; // 头部操作循环下标 private static int head_start = 0; private static int head_end = 4; //表名注释名循环下标 private static int tablename_start =5; private static int tablecomment_end = 6; //表名信息循环下标 private static int tableinfo_start =7; private static int tableinfo_end = 8; /** * 创建数据库连接 * * @return */ public static Connection getConn() { try { if (conn == null || conn.isClosed()) { Class.forName(db_driver); Properties props = new Properties(); props.put("user", db_userid); props.put("password", db_password); props.put("remarksReporting", remarksReporting); conn = DriverManager.getConnection(db_url, props); } return conn; } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 获取模版 * * @return */ public static HSSFWorkbook getModelWorkbook() { ; try { if (model_wb == null) { File model_file = new File(model_path); System.out.println(model_path + "----------------------"); model_wb = new HSSFWorkbook(new FileInputStream(model_file)); } return model_wb; } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 获取模版中的行 * * @param sheetid * @param start * @param end * @return */ public static List<hssfrow> getModelRows(int sheetid, int start, int end) { HSSFWorkbook wb = getModelWorkbook(); HSSFSheet sheet = wb.getSheetAt(sheetid); List<hssfrow> list = new ArrayList<hssfrow>(); for (int i = start; i <= end; i++) { HSSFRow r = sheet.getRow(i); System.out.println(r.getRowNum() + "++++++++++++++++"); list.add(r); } return list; } /** * 保存 * * @param wb * @param name */ public static void saveWookbook(HSSFWorkbook wb, String name) { FileOutputStream fileOut = null; long time = System.currentTimeMillis(); try { File fileName = new File(distinct_path + "/" + name + time + ".xls"); fileOut = new FileOutputStream(fileName); wb.write(fileOut); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (fileOut != null) { try { fileOut.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } /** * 拷贝列的宽度 * * @param modelsheet * @param mysheet */ public static void copyColumnWidth(HSSFSheet modelsheet, HSSFSheet mysheet) { mysheet.setDefaultColumnWidth(modelsheet.getDefaultColumnWidth()); for (int i = 0; i < 100; i++) { short w = modelsheet.getColumnWidth((short) i); mysheet.setColumnWidth((short) i, (short) (w + 1)); } // 页边距 mysheet.setMargin(HSSFSheet.BottomMargin, modelsheet .getMargin(HSSFSheet.BottomMargin)); mysheet.setMargin(HSSFSheet.LeftMargin, modelsheet .getMargin(HSSFSheet.LeftMargin)); mysheet.setMargin(HSSFSheet.RightMargin, modelsheet .getMargin(HSSFSheet.RightMargin)); mysheet.setMargin(HSSFSheet.TopMargin, modelsheet .getMargin(HSSFSheet.TopMargin)); // 页眉页脚 HSSFFooter footer = modelsheet.getFooter(); HSSFFooter myfooter = mysheet.getFooter(); myfooter.setCenter(footer.getCenter()); myfooter.setLeft(footer.getLeft()); myfooter.setRight(footer.getRight()); // 打印设置 HSSFPrintSetup ps = modelsheet.getPrintSetup(); HSSFPrintSetup myps = mysheet.getPrintSetup(); myps.setHeaderMargin(ps.getHeaderMargin()); // 页边距 myps.setFooterMargin(ps.getFooterMargin()); myps.setLandscape(ps.getLandscape()); // 打印方向 myps.setPaperSize(ps.getPaperSize()); myps.setFitWidth(ps.getFitWidth()); // 缩放 myps.setFitHeight(ps.getFitHeight()); myps.setHResolution(ps.getHResolution()); myps.setVResolution(ps.getVResolution()); mysheet.setHorizontallyCenter(modelsheet.getHorizontallyCenter()); // 水平居中 } /** * 拷贝行 * * @param model_start * @param model_end * @param rows * @param wb * @param sheet * @param newrow * @param params */ public void copyRows(int model_start, int model_end, List<hssfrow> rows, HSSFWorkbook wb, HSSFSheet sheet, int newrow, Map params) { if (rows == null) return; int i = model_start; for (HSSFRow r : rows) { copyRow(i, r, wb, sheet, newrow++, params); i++; } } /** * 拷贝行 * * @param model_row_id * @param model_row * @param my_wb * @param my_sheet * @param my_row_id * @param params */ public void copyRow(int model_row_id, HSSFRow model_row, HSSFWorkbook my_wb, HSSFSheet my_sheet, int my_row_id, Map params) { if (model_row == null) return; // 行 HSSFRow myrow = my_sheet.createRow(my_row_id); String sheettitle = my_sheet.toString(); myrow.setHeight(model_row.getHeight()); // 列 for (int i = 0; i < cellcounts; i++) { HSSFCell model_cell = model_row.getCell((short) i); if (model_cell == null) continue; HSSFCellStyle style = model_cell.getCellStyle(); String key = model_cell.getStringCellValue(); HSSFCell mycell = myrow.createCell((short) i); // mycell.setEncoding(model_cell.getEncoding()); // 类型 mycell.setCellType(model_cell.getCellType()); // 样式 Object obj = styleMap .get(model_row_id + "_" + sheettitle + "_" + i); System.out.println(model_row_id + "_" + sheettitle + "_" + i); HSSFCellStyle mystyle = null; if (obj == null) { mystyle = my_wb.createCellStyle(); styleMap .put(model_row_id + "_" + sheettitle + "_" + i, mystyle); } else { mystyle = (HSSFCellStyle) obj; } if (style != null) { try { BeanUtils.copyProperties(mystyle, style); } catch (IllegalAccessException e1) { } catch (InvocationTargetException e1) { } mystyle.setBorderBottom(style.getBorderBottom()); mystyle.setBorderLeft(style.getBorderLeft()); mystyle.setBorderTop(style.getBorderTop()); mystyle.setBorderRight(style.getBorderRight()); mystyle.setDataFormat(style.getDataFormat()); mystyle.setAlignment(style.getAlignment()); mystyle.setFillBackgroundColor(style.getFillBackgroundColor()); mystyle.setFillForegroundColor(style.getFillForegroundColor()); mystyle.setVerticalAlignment(style.getVerticalAlignment()); } // 字体 HSSFFont cellFont = getModelWorkbook().getFontAt( model_cell.getCellStyle().getFontIndex());// 得到单元格的字 Object font_obj = fontMap.get(model_row_id + "_" + i); HSSFFont myfont = null; if (obj == null) { myfont = my_wb.createFont(); fontMap.put(model_row_id + "_" + i, myfont); } else { myfont = (HSSFFont) font_obj; } if (cellFont != null) { try { myfont.setBoldweight(cellFont.getBoldweight()); myfont.setColor(cellFont.getColor()); myfont.setFontName(cellFont.getFontName()); myfont.setFontHeight(cellFont.getFontHeight()); myfont.setFontHeightInPoints(cellFont .getFontHeightInPoints()); myfont.setItalic(cellFont.getItalic()); myfont.setStrikeout(cellFont.getStrikeout()); myfont.setTypeOffset(cellFont.getTypeOffset()); } catch (Exception e) { } } mystyle.setFont(myfont); mycell.setCellStyle(mystyle); if (key != null) { Object val = null; val = params.get(key.trim());// 值 System.out.println(key.trim() + "==========="); if (val == null) val = key; if (mycell.getCellType() == HSSFCell.CELL_TYPE_STRING) {// 文本 try { mycell.setCellValue(val.toString()); System.out .println(val.toString() + "================="); } catch (Exception e) { e.printStackTrace(); } } else if (mycell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { try { mycell.setCellValue(Double.parseDouble(val.toString())); } catch (Exception e) { } } else { try { mycell.setCellValue(val.toString()); } catch (Exception e) { } } } } } /** * 拷贝合并 * * @param modelsheet * @param mysheet * @param model_rs * @param model_re * @param myrow */ public static void copyMegetdRegion(HSSFSheet modelsheet, HSSFSheet mysheet, int model_rs, int model_re, int myrow) { for (int i = 0; i < 120; i++) { try { Region reg = modelsheet.getMergedRegionAt(i); System.out.println(reg.getRowFrom() + ";;;;;;;"); System.out.println(reg.getRowTo() + ";;;;;;+++;"); if (reg == null) continue; if (reg.getRowFrom() >= model_rs && reg.getRowTo() <= model_re) { int h = reg.getRowFrom() - model_rs; int h2 = reg.getRowTo() - model_rs; System.out.println(myrow + h + "--------++++-----"); System.out.println(myrow + h2 + "--------+++----------"); reg.setRowFrom(myrow + h); reg.setRowTo(myrow + h2); mysheet.addMergedRegion(reg); } } catch (Exception e) { } } } public static HSSFWorkbook createWorkbook() { File f = new File(model_path); HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(new FileInputStream(f)); for (int i = 0; i < 10; i++) { try { wb.removeSheetAt(0); } catch (Exception e) { } } } catch (FileNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return wb; } public static void copyRowbreak(HSSFSheet modelsheet, HSSFSheet mysheet, int model_rs, int model_re, int myrow) { int[] rbs = modelsheet.getRowBreaks(); if (rbs == null) return; for (int i = 0; i < rbs.length; i++) { if (rbs[i] >= model_rs && rbs[i] <= model_re) { mysheet.setRowBreak(rbs[i] - model_rs + myrow); } } } public void writeData() { Connection connection = getConn(); PreparedStatement statement = null; ResultSet resultSet = null; ResultSet resultSet2 = null; List<hssfrow> modelRows_sign_tab=null; int rowid = 0; try { DatabaseMetaData databaseMetaData = connection.getMetaData(); String commentSql = " SELECT a.TABLE_NAME,a.COMMENTS FROM user_tab_comments a order by TABLE_NAME"; statement = connection.prepareStatement(commentSql); resultSet2 = statement.executeQuery(); Map params = new HashMap();//数据项 Map params_tab = new HashMap();//表名,注释 HSSFWorkbook wb = createWorkbook();// new HSSFWorkbook(); // 清空样式 styleMap.clear(); fontMap.clear(); // HSSFWorkbook wb = createWorkbook(); // 封面sheet HSSFSheet sheet_p = wb.createSheet("数据字典"); if (wb != null && sheet_p != null) { /* 表格头部*/ copyColumnWidth(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p); List<hssfrow> modelRows_sign = getModelRows(Integer .parseInt(coversheet), head_start, head_end); copyRows(head_start, head_end, modelRows_sign, wb, sheet_p, 0, params); copyMegetdRegion(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, head_start, head_end, 0); copyRowbreak(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, head_start, head_end, 0); rowid += modelRows_sign.size(); } while (resultSet2.next()) { resultSet = databaseMetaData.getColumns(null, null, resultSet2.getString("TABLE_NAME").toUpperCase(), "%"); StringBuffer result = new StringBuffer(); String tabledesc = null; String tablename = resultSet2.getString("TABLE_NAME") == null ? "" : resultSet2.getString("TABLE_NAME"); String tablecomments = resultSet2.getString("COMMENTS") == null ? "" : resultSet2.getString("COMMENTS"); params_tab.put("TABLE_NAME", tablename == null ? "" : tablename); params_tab.put("COMMENTS", tablecomments == null ? "" : tablecomments); /*表名，表注释部分*/ if (wb != null && sheet_p != null) { copyColumnWidth(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p); List<hssfrow> modelRows_sign = getModelRows(Integer .parseInt(coversheet), tablename_start, tablecomment_end); copyRows(tablename_start, tablecomment_end, modelRows_sign, wb, sheet_p, rowid, params_tab); copyMegetdRegion(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, tablename_start, tablecomment_end, rowid); copyRowbreak(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, tablename_start, tablecomment_end, rowid); while (resultSet.next()) { String COLUMN_NAME = resultSet.getString("COLUMN_NAME"); String TYPE_NAME = resultSet.getString("TYPE_NAME"); String COLUMN_SIZE = resultSet.getString("COLUMN_SIZE"); String DECIMAL_DIGITS = resultSet .getString("DECIMAL_DIGITS"); String IS_NULLABLE = resultSet.getString("IS_NULLABLE"); String REMARKS = resultSet.getString("REMARKS"); params.put("COLUMN_NAME", COLUMN_NAME == null ? "" : COLUMN_NAME); params.put("TYPE_NAME", TYPE_NAME == null ? "" : TYPE_NAME); params.put("COLUMN_SIZE", COLUMN_SIZE == null ? "" : COLUMN_SIZE); params.put("DECIMAL_DIGITS", DECIMAL_DIGITS == null ? "" : DECIMAL_DIGITS); params.put("IS_NULLABLE", IS_NULLABLE == null ? "" : IS_NULLABLE); params.put("REMARKS", REMARKS == null ? "" : REMARKS); /*表信息字段释部分*/ if (wb != null && sheet_p != null) { rowid += modelRows_sign.size(); copyColumnWidth(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p); modelRows_sign_tab = getModelRows(Integer .parseInt(coversheet), tableinfo_start, tableinfo_end); copyRows(tableinfo_start, tableinfo_end, modelRows_sign_tab, wb, sheet_p, rowid, params); copyMegetdRegion(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, tableinfo_start, tableinfo_end, rowid); copyRowbreak(getModelWorkbook().getSheetAt( Integer.parseInt(coversheet)), sheet_p, tableinfo_start, tableinfo_end, rowid); rowid += modelRows_sign_tab.size()-3; } } } } if (wb != null) { saveWookbook(wb, "sheet"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void main(String args[]) { DbToExecl dt = new DbToExecl(); dt.writeData(); } } </hssfrow></hssfrow></hssfrow></hssfrow></hssfrow></hssfrow></hssfrow>

注：代码269行BeanUtils.copyProperties(mystyle, style);在poi 3.9以上不适用，需要进行如下修改：

mycell.setCellStyle(style);
       //BeanUtils.copyProperties(mystyle, style);