采用jxl实现数据库结果集导出到excel文件

http://www.blogjava.net/youlq/archive/2005/11/06/18459.html

代码：

servlet：

import  jxl.WorkbookSettings; import  jxl.Workbook; import  jxl.write.WritableWorkbook; import  jxl.write.WritableSheet; import  jxl.write.Label; import  jxl.write.WriteException; import  org.springframework.web.context.WebApplicationContext; import  org.springframework.web.context.support.WebApplicationContextUtils; import  org.springframework.jdbc.core.JdbcTemplate; import  org.springframework.jdbc.core.ResultSetExtractor; import  org.springframework.jdbc.support.JdbcUtils; import  org.springframework.dao.DataAccessException; import  org.apache.commons.logging.Log; import  org.apache.commons.logging.LogFactory; import  org.apache.commons.lang.StringUtils; import  org.apache.commons.lang.ArrayUtils; import  javax.servlet.http.HttpServlet; import  javax.servlet.http.HttpServletRequest; import  javax.servlet.http.HttpServletResponse; import  javax.servlet.ServletException; import  javax.servlet.ServletConfig; import  java.util.Locale; import  java.util.HashMap; import  java.util.Map; import  java.io.IOException; import  java.sql.ResultSet; import  java.sql.SQLException; import  java.sql.ResultSetMetaData; /** */ /**  *  Title:ExcelGenerator servlet  *  Description: 采用jxl实现数据库结果集导出到excel文件。  *  Copyright: Copyright.com (c) 2003  *  Company:  * History:  * create  *  * @author youlq  * @version 1.0  */ public   class  ExcelGenerator  extends  HttpServlet {   //设定每个Sheet的行数   private int pagesize=5000;   private WorkbookSettings workbookSettings=new WorkbookSettings();   //springframework 的 WebApplicationContext   public static WebApplicationContext wac=null;   //springframework 的 jdbc 操作模版类   public static JdbcTemplate jdbcTemplate=null;   protected final Log logger=LogFactory.getLog(getClass());   /** *//**    * 初始化    *    * @param config    * @throws ServletException    */   public void init(ServletConfig config) throws ServletException{     super.init(config);     try{       if(null!=getInitParameter("pagesize")){         pagesize=Integer.parseInt(getInitParameter("pagesize"));       }       workbookSettings.setLocale(Locale.getDefault());       wac=WebApplicationContextUtils.getRequiredWebApplicationContext(getServletContext());       jdbcTemplate=(JdbcTemplate)wac.getBean("jdbcTemplate");     } catch(Exception e){       logger.error("ExcelGenerator init() error !"+e, e.getCause());       e.printStackTrace();     }   }   public String getServletInfo(){     return "Servlet used to generate excel output";   }   public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{     generateExcel(request, response);   }   public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{     generateExcel(request, response);   }   /** *//**    * in:    * field1#Title&field2#Title&field3#Title    * out:    * {    * field1:Title,    * field2:Title    * field3:Title    * }    *    * @param columnTitle    */   public static HashMap generateColumnTitleMap(String columnTitle){     HashMap map=new HashMap();     String[] level1=StringUtils.split(columnTitle, "&");     if(ArrayUtils.isEmpty(level1)) return null;     for(int i=0;i<level1.length;i++){       String[] level2=StringUtils.split(level1[i], "#");       if(ArrayUtils.isEmpty(level2)||level2.length!=2) return null;       map.put(level2[0].toLowerCase(), level2[1]);     }     return map;   }   public void generateExcel(HttpServletRequest request, HttpServletResponse response)     throws ServletException, IOException{     //todo 只允许本机调用。     request.getRemoteHost();     request.getServerName();     response.setHeader("Content-Disposition", "attachment;");     response.setContentType("application/x-msdownload");     String sql=(String)request.getSession().getAttribute("ExcelGenerator_sql");     String columnTitle=(String)request.getSession().getAttribute("ExcelGenerator_columntitle");     Map columnTitleMap=null;     if(StringUtils.isBlank(sql)) throw new ServletException("sql 字符串为空！");     if(!StringUtils.isBlank(columnTitle)){       columnTitleMap=generateColumnTitleMap(columnTitle);       if(null==columnTitleMap){         logger.error("generateColumnTitleMap error !columnTitle="+columnTitle);       }     }     final WritableWorkbook writableWorkbook=Workbook.createWorkbook(response.getOutputStream(), workbookSettings);     if(jdbcTemplate==null) throw new ServletException("ExcelGenerator 没有初始化成功！jdbcTemplate==null。");     final Map columnTitleMap1=columnTitleMap;     jdbcTemplate.query(sql, new ResultSetExtractor(){       public Object extractData(ResultSet rs) throws SQLException, DataAccessException{         try{           int counter=0;           int page=1;           WritableSheet writableSheet=writableWorkbook.createSheet("第"+page+"页", 0);           ResultSetMetaData rsmd=rs.getMetaData();           int columnCount=rsmd.getColumnCount();           String[] columnNames=new String[columnCount];           for(int i=1;i<=columnCount;i++){             columnNames[i-1]=rsmd.getColumnName(i).toLowerCase();             if(columnTitleMap1==null){               writableSheet.addCell(new Label(i-1, counter, columnNames[i-1]));             } else{               writableSheet.addCell(new Label(i-1, counter, (String)columnTitleMap1.get(columnNames[i-1])));             }           }           counter=1;           Object oValue=null;           String value=null;           while(rs.next()){             //row             for(int i=1;i<=columnCount;i++){               oValue=JdbcUtils.getResultSetValue(rs, i);               if(oValue==null){                 value="";               } else{                 value=oValue.toString();               }               writableSheet.addCell(new Label(i-1, counter, value));             }             if(counter++>pagesize){               counter=0;               writableSheet=writableWorkbook.createSheet("第"+(++page)+"页", 0);             }           }         } catch(WriteException e){           e.printStackTrace();         }         return null;       }     }     );     writableWorkbook.write();     try{       writableWorkbook.close();     } catch(WriteException e){       logger.error("writableWorkbook.close() error !"+e, e.getCause());       e.printStackTrace();     }   } }

web.xml

   < servlet >      < servlet-name > ExcelGenerator SPAN style="COLOR: #800000">servlet-name >      < servlet-class > com.fsti.xmnms.web.servlet.ExcelGenerator SPAN style="COLOR: #800000">servlet-class >      < init-param >       < param-name > pagesize SPAN style="COLOR: #800000">param-name >       < param-value > 5000 SPAN style="COLOR: #800000">param-value >      SPAN style="COLOR: #800000">init-param >      < load-on-startup > 3 SPAN style="COLOR: #800000">load-on-startup >    SPAN style="COLOR: #800000">servlet >    < servlet-mapping >      < servlet-name > ExcelGenerator SPAN style="COLOR: #800000">servlet-name >      < url-pattern > *.xls SPAN style="COLOR: #800000">url-pattern >    SPAN style="COLOR: #800000">servlet-mapping >

测试页面：

@ page contentType="text/html;charset=GB2312" language="java" %>]]> <html> <head>   <title>ExcelGenerator testSPAN style="COLOR: #000000">title> SPAN style="COLOR: #000000">head> <body>   String sql="select id,source_id,user_label from alarm_state";   String columntitle="id#ID&source_id#源设备&user_label#用户标签";   session.setAttribute("ExcelGenerator_sql",sql);   session.setAttribute("ExcelGenerator_columntitle",columntitle);   response.sendRedirect("asd.xls"); %>]]> SPAN style="COLOR: #000000">body> SPAN style="COLOR: #000000">html>

]]>