如何基于JXL的Excel动态导出过程?
最近在做一个J2EE项目,就用了下JXL,发现还挺好用。 于是乎封装了一个简单易用、通用、动态的从数据库导出到Excel的方法,可以动态的指定导出那些列,显示什么名字,按什么顺序显示;支持本地文件存储和JSP/Servlet文件下载。
下载地址:
1,http://jexcelapi.sourceforge.net/
2,http://andykhan.com/jexcelapi/index.html
本方法包括两个类,Column是辅助类,Excel是主类:
/** * 用于Excel导出的辅助类,映射数据结果集(ResultSet)内列名的元数据和Excel内的显示列名 * Date: 2007-1-11 * Author: nescafe */ public class Column { private int index; private String metaName; private String displayName; /** * 构造函数 * @param index 显示顺序,0 为显示的第一列 * @param meta 元列名,在ResultSet内的名字,必须大写 * @param display 显示列名,在Excel内的显示,可以是任何文字 */ public Column(int index, String meta, String display){ this.index = index; this.metaName = meta; this.displayName = display; } /** * 显示列名,在Excel内的显示,可以是任何文字 * @return */ public String getDisplayName() { return displayName; } /** * 显示顺序,0 为显示的第一列 * @return */ public int getIndex() { return index; } /** * 元列名,在ResultSet内的名字,必须大写 * @return */ public String getMetaName() { return metaName; } public void setDisplayName(String displayName) { this.displayName = displayName; } public void setIndex(int index) { this.index = index; } public void setMetaName(String metaName) { this.metaName = metaName; } } /** * 简单的Excel操作,完成与数据库的动态导出 * Date: 2007-1-11 * Author: nescafe */ public class Excel { /** * @param args */ public static void main(String[] args) { File f=new File("c://kk.xls"); try { f.createNewFile(); /* 此处初始化一个数据库连接池,pool Connection可以用其他方式取得,不一定用pool */ Connection conn = pool.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("Select t.*, t.rowid from mis2_personal_weekly_job_plan t Where Rownum Between 1 And 2"); HashMap map = new HashMap(); map.put("ID", new Column(0, "ID", "编号")); map.put("JOB_CONTENT", new Column(1, "JOB_CONTENT", "工作内容")); map.put("JOB_TARGET", new Column(2, "JOB_TARGET", "工作目标")); export(new FileOutputStream(f), null, rs, map); } catch (Exception e) { e.printStackTrace(); } } /** * 从数据库读数据,写入Excel * @param os 数据流,如果是写本地文件的话,可以是FileOutputStream; * 如果是写Web下载的话,可以是ServletOupputStream * @param title Excel工作簿的标题,如果不用的话,可以写null或者"" * @param rs 数据结果集 * @param map 数据结果集对应Excel表列名映射:key对应数据结果集的列名,必须是大写; * value,目前只能对应Column对象 * @throws Exception 方法内的父类异常有SQLException和IOException */ public static void export(OutputStream os, String title, ResultSet rs, Map map)throws Exception{ jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件 jxl.write.WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabel = null; // Excel表格的Cell // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0, title, wcfFC); wsheet.addCell(wlabel1); // 设置列名字体 // 如果有标题的话,要设置一下偏移 int offset = 2; if(title == null || title.trim().equals("")) offset = 0; else{ wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); } //根据原数据和map来创建Excel的列名 ResultSetMetaData rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for(int i = 1; i <= count; i++){ String name = rsmd.getColumnName(i).toUpperCase(); if(map.containsKey(name)){ Column col = (Column)map.get(name); wlabel = new jxl.write.Label(col.getIndex(), offset, col.getDisplayName()); wsheet.addCell(wlabel); } } // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); //往Excel输出数据 int rowIndex = 1 + offset; Collection array = map.values(); while(rs.next()){ Iterator it = array.iterator(); while(it.hasNext()){ Column col = (Column)it.next(); String value = rs.getString(col.getMetaName()); wlabel = new jxl.write.Label(col.getIndex(), rowIndex, value); wsheet.addCell(wlabel); } rowIndex++; } wbook.write(); // 写入文件 wbook.close(); os.flush(); os.close(); } }
//JSP页面的下载如下,同样的代码也可以改成Servlet的
<% ConnectionPool pool = ConnectionPool.getInstance(); Connection conn = null; ResultSet rs = null; Statement stmt = null; conn = pool.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("Select t.*, t.rowid from mis2_personal_weekly_job_plan t Where Rownum Between 1 And 2"); HashMap map = new HashMap(); map.put("ID", new Column(0, "ID", "编号")); map.put("JOB_CONTENT", new Column(1, "JOB_CONTENT", "工作内容")); map.put("JOB_TARGET", new Column(2, "JOB_TARGET", "工作目标")); String fileName = "周工作计划.xls"; response.reset(); response.setContentType("application/vnd.ms-excel"); //response.addHeader("Content-Disposition","attachment;filename=" + fileName); Excel.export(response.getOutputStream(), "", rs, map); %>
本文地址:http://www.45fan.com/a/question/68563.html