poi方式将数据导出到execl并提供下载

经过一天半的学习，终于采用poi方式从数据库中导出数据到execl并实现下载。具体代码如下：

package com.szwistar.metenoa.oaagent;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import weaver.conn.RecordSet;
import com.szwistar.common.ServerAgentBase;
import com.szwistar.common.util.util4RecordSet;

/**
 *
 * @function 根据Action传过来的requestId查询明细表里面的数据，并将数据写入到根据requestId生成的excel文件中，并把execl打开或者下载保存。
 * @author huanglizhi
 * @creatTime 2010-5-6
 * @edit 2010-5-7
 *
 */
public class OAAgent_test_Excel extends ServerAgentBase {
 
 /**
  * 得到requestId，调用createExcel方法生成Excel文件
  */
 public void doGet(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  response.setContentType("text/html;charset=gbk");
  request.setCharacterEncoding("gbk");
  try {
   //获得requestid
   String requestId = "";   
   try {
    //得到requestId并对requestId进行处理
    requestId = request.getParameter("requestId");
    log.info("requestId xxx : " + requestId);
             if(null == requestId || "".equals(requestId)) {
              log.debug("requestId(null or blank): " + requestId);
              requestId = "";
          }
            } catch(Exception e) {
             requestId = "";
             log.error("requestId(exception): " + requestId);
            }
      //根据requestId生成EXCEL文件
      createExcel(requestId, request, response);
  } catch (Exception e) {
   log.error("OAAgent_test_Excel doget failed!  " + e.getMessage());
  }
 }

 public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  response.setContentType("text/html;charset=gbk");
  this.doGet(request, response);
 }
 
 /**
     * 从明细表里查询出EXCEL需要的信息
     * @param requestId
     * @return excelList
     */
 @SuppressWarnings("unchecked")
 protected static ArrayList getExcelList(String requestId) {
  RecordSet res = null;
     ArrayList excelList = null;
     try {
      //从明细表formtable_main_28_dt1里取出需要的字段来
      String sql = "select id,mainId,mx01,mx02,mx03,mx04 from formtable_main_28_dt1 where mainid in (select id from formtable_main_28 where requestId = " + requestId + ")";
      log.info("从明细表里查询出EXCEL需要的信息: " + sql);
      res = new RecordSet();
   res.executeSql(sql);
   //RecordSetToListObj方法返回的ArrayList为ArrayList<AList>结构 
   //其中AList又为 ArrayList<String>结构;第一个list为字段名 ,后续list为字段值
   excelList = util4RecordSet.RecordSetToListObj(res, null);
     } catch (Exception e) {
      log.error("从明细表里查询出EXCEL需要的信息: " + e.getMessage());
     }
     log.info("excelList:" + excelList);
     return excelList;
    }
 
    /**
     * 创建EXCEL文件
     * @param requestId
     */
  @SuppressWarnings("unchecked")
 protected void createExcel(String requestId, HttpServletRequest request, HttpServletResponse response) {
      log.info("enter createExcel......");
      FileOutputStream fout = null;  
         try {  
             fout = new FileOutputStream(new File("/opt/weaver50/ecology/excelFile/中文.xls"));  
         } catch (FileNotFoundException e1) {  
          log.error(e1.getMessage() + ":" + fout);  
         }  
         //创建工作簿
      HSSFWorkbook hsshworkbook = new HSSFWorkbook();
      //由工作簿创建工作表，注意的一点是下标从0开始，就像数组一样
      HSSFSheet hsshsheet = hsshworkbook.createSheet();
      //设置工作表的名称，该方法需要三个参数，第一个是工作表在工作薄中的位置，第二个就是工作表的名称，第三个是字符编码
      hsshworkbook.setSheetName(0,"实验项目列表－－中文测试",HSSFWorkbook.ENCODING_UTF_16);
      //对应excel的行
      HSSFRow hssfrow = hsshsheet.createRow(0);
         //创建单元格,设置每个单元格的值（作为表头），对应excel的列
      //很多方法的参数是short而不是int，所以需要做一次类型转换
         HSSFCell hssfcell = hssfrow.createCell((short) 0);
         //字符编码，必须加上，否则中文乱码
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         //设定单元格的值，值的类型有：double,int,String,Date,boolean 
         hssfcell.setCellValue("编号");  
         hssfcell = hssfrow.createCell((short) 1);
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         hssfcell.setCellValue("报销人");  
         hssfcell = hssfrow.createCell((short) 2);
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         hssfcell.setCellValue("报销事项");  
         hssfcell = hssfrow.createCell((short) 3); 
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         hssfcell.setCellValue("报销金额");
         hssfcell = hssfrow.createCell((short) 4);
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         hssfcell.setCellValue("报销日期");
         hssfcell = hssfrow.createCell((short) 5);
         hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
         hssfcell.setCellValue("备注");     
      //处理后的EXCEL内容结果集
      ArrayList excelList = new ArrayList();
      try {       
       log.info("before getExcelList");     
          // 获得处理后的EXCEL内容结果集，excelList存放的是一条条完整的记录  
          excelList = getExcelList(requestId);
          log.info("excelList:" + excelList);         
         // arrayList存放的是每一条记录的所有列
          ArrayList arrayList = null;           
          //如果excelList没有记录 则返回
          if(excelList.size() < 1) {
           log.info("没有查询到记录....");
           return;
          }
          if(null != excelList) {           
           //得到结果集的记录条数
           int count = excelList.size();
           log.info("count:" + count);           
           //excelList得到的是含有表头的数据集，i=1表示除去表头后的第一条记录          
              for(int i = 1; i < count; i++) {
               log.info("enter for");
               arrayList = (ArrayList) excelList.get(i); 
               log.info("after arrayList");
               hssfrow = hsshsheet.createRow(i);  
               log.info("after hssfrow");
               for (int j = 0; j < arrayList.size(); j++) {  
                hssfcell = hssfrow.createCell((short) j); 
                hssfcell.setEncoding(HSSFCell.ENCODING_UTF_16);
                //将每一列的值插入到单元格中
                hssfcell.setCellValue(arrayList.get(j).toString());  
                  }  
              }
          }
          //将工作簿对象hsshworkbook写入到输出流      
             hsshworkbook.write(fout);
             fout.flush();
             fout.close();
       log.info("EXCEL文件已生成......");    
   } catch (IOException e) {
    log.error("createExcel error:" + e.getMessage());
    return;
   }
   log.info("before DownExcelFile");
   //下载已生成的execl文件
   DownExcelFile(response);
      log.info("EXCEL文件下载完成......");
    }
 
  /**
   * 下载execl文件
   */ 
  public void DownExcelFile(HttpServletResponse response) {
    String fileDownPath = "/opt/weaver50/ecology/excelFile/中文.xls";
    File file = new File(fileDownPath);
    if (file.exists()) {
     try {
      log.info("enter try");     
      // 要用servlet 来打开一个 EXCEL 文档，需要将 response 对象中 header 的 contentType 设置成"application/x-msexcel"。
      response.setContentType("application/x-msexcel");
      String fileName = fileDownPath;
      // 保存文件名称
      fileName = fileName.substring(fileName.lastIndexOf("/") + 1);
      // 处理中文文件名
      fileName = new String(fileName.getBytes("GB2312"), "ISO-8859-1");
      //servlet中，要在 header中设置下载方式
      response.setHeader("Content-Disposition","attachment; filename=" + fileName);
      //FileInputStream输入流
      //FileInputStream bis = new FileInputStream(file);
      //缓冲流(BufferedStream)可以一次读写一批数据，,缓冲流(Buffered Stream)大大提高了I/O的性能。
      BufferedInputStream  bis = new BufferedInputStream(new FileInputStream(file));
      //OutputStream输出流
      OutputStream bos = response.getOutputStream();
      byte[] buff = new byte[1024];
      int readCount = 0;
      //每次从文件流中读1024个字节到缓冲里。
      readCount = bis.read(buff);
      while (readCount != -1) {
       //把缓冲里的数据写入浏览器
       bos.write(buff, 0, readCount);
       readCount = bis.read(buff);
      }
      if (bis != null) {
       bis.close();
      }
      if (bos != null) {
       bos.close();
      }
      // 下载完毕，给浏览器发给完毕的头
      response.setStatus(HttpServletResponse.SC_OK);
      response.flushBuffer();
     } catch (Exception e) {
   log.error("DownExcelFile error:" + e.getMessage());
     }
    }
   }
  
}