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

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>

]]>