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()); } } } } 原文出处：http://huanglz19871030.javaeye.com/blog/661360 本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/pingsuccess/archive/2010/12/28/6103134.aspx