spring mvc的excel报表文件下载时流的冲突解决
在jsp或者在servlet中有时要用到 response.getOutputStream()，但是此时会在后台报这个错误java.lang.IllegalStateException: getOutputStream() has already been called for this respons，这问题困扰了我好久都没解决，最近这个项目中我又遇到了，下定决心一定要解决掉，最后终于让我给找到解决的方法了，这个异常时因为 response.getOutputStream()跟response.getWriter()相冲突造成的，呵呵！现在记录下，发出来和大家共享 下，希望能帮到遇到同样问题的朋友们，解决方法如下：
out.clearBuffer();
out = pageContext.pushBody();
在调用response.getOutputStream()之前加上上面两代码，就ok了！
上面的是网上流行的解决方案, 在jsp中<% %>写入红色代码;
我的解决方案是将改下载方法的返回值为void或return时为null都可以, 我是用的是spring mvc 在Struts中的方法通常也会带有返回值;
code:
package com.piaomeng.b2bv5.stat.controller; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import com.piaomeng.b2bv5.core.auth.AuthPassport; import com.piaomeng.b2bv5.stat.service.IBStatisticsService; import com.piaomeng.b2bv5.stat.vo.PasteCount; import com.piaomeng.b2bv5.stat.vo.PasteCountExcel; import com.piaomeng.b2bv5.util.PrintWriterUtil; @Controller @Scope("prototype") @RequestMapping("/statistics") public class StickPointController { @Autowired private IBStatisticsService statisticsService; private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); /**销售跳转链接*/ @RequestMapping("/skipTieDianXsIndex") @AuthPassport(auth="/statistics/skipTieDianXsIndex", check=false) public String skipTieDianXsIndex(Map<String,Object> map){ map.put("beginDate", sdf.format(new Date())); map.put("endDate", sdf.format(new Date())); return "/jsp/stat/stickPointStatistics_xs"; } /**销售显示主要方法*/ @RequestMapping("/selectTieDianXsList") @AuthPassport(auth="/statistics/selectTieDianXsList", check=false) public String selectTieDianXsList(HashMap<String,Object> map, String salesId, String beginDate, String endDate){ map.put("beginDate", beginDate); map.put("endDate", endDate); map.put("salesId", salesId); // 根据日期判断是上半年/下半年分别查询, 例如2015-09-03 只截取月 09; String currentMonth = beginDate.substring(5, 7);// 开始日期和结束日期的月份相同, 页面js已经校验; String[] first_half_year = {"01", "02", "03", "04", "05", "06"}; String[] last_half_year = {"07", "08", "09", "10", "11", "12"}; List<String> fhy = Arrays.asList(first_half_year); List<String> lhy = Arrays.asList(last_half_year); // 设置动态表名 1-6 月 t_od_border1501 7-12 月 t_od_border1507; 上半年设置表后缀为01 (1-6月)下半年设置表后缀为07 (7-12月); String tableName = null; if (fhy.contains(currentMonth)) {// 上半年; tableName = "T_OD_BORDER" + beginDate.substring(2, 4) + "01"; } if (lhy.contains(currentMonth)) {// 下半年; tableName = "T_OD_BORDER" + beginDate.substring(2, 4) + "07"; } map.put("border", tableName); map.put("orderdesc", "T_OD_ORDERDESC".concat(beginDate.substring(2, 4)).concat(currentMonth)); map.put("passinfo", "T_OD_PASSINFO".concat(beginDate.substring(2, 4)).concat(currentMonth)); List<PasteCount> pasteCounts = statisticsService .selectPasteCountList(map); // 计算总计; PasteCount pc = null; int ticketCount = 0; double buyPrice = 0.00, levelPrice = 0.00; if (pasteCounts.size() > 0 && pasteCounts != null) { for (Iterator<PasteCount> iterator = pasteCounts.iterator(); iterator.hasNext();) { PasteCount item = iterator.next(); ticketCount += item.getTicketCount(); buyPrice += item.getBuyPrice(); levelPrice += item.getLevelPrice(); } pc = new PasteCount(); pc.setUsername("全部"); pc.setTicketCount(ticketCount); pc.setBuyPrice(buyPrice); pc.setLevelPrice(levelPrice); } // end map.put("pasteCounts", pasteCounts); map.put("totalCount", pc); return "/jsp/stat/stickPointStatistics_xs"; } /**销售的下载贴点明细statistics/selectTieDianXsListDownload*/ @RequestMapping("/selectTieDianXsListDownload") @AuthPassport(auth="/statistics/selectTieDianXsListDownload", check=false) public String selectTieDianXsListDownload(HashMap<String,Object> map, String salesId, String beginDate, String endDate, HttpServletResponse response){ try { map.put("beginDate", beginDate); map.put("endDate", endDate); map.put("salesId", salesId); // 根据日期判断是上半年/下半年分别查询, 例如2015-09-03 只截取月 09; String currentMonth = beginDate.substring(5, 7);// 开始日期和结束日期的月份相同, 页面js已经校验; String[] first_half_year = {"01", "02", "03", "04", "05", "06"}; String[] last_half_year = {"07", "08", "09", "10", "11", "12"}; List<String> fhy = Arrays.asList(first_half_year); List<String> lhy = Arrays.asList(last_half_year); // 设置动态表名 1-6 月 t_od_border1501 7-12 月 t_od_border1507; 上半年设置表后缀为01 (1-6月)下半年设置表后缀为07 (7-12月); String tableName = null; if (fhy.contains(currentMonth)) {// 上半年; tableName = "T_OD_BORDER" + beginDate.substring(2, 4) + "01"; } if (lhy.contains(currentMonth)) {// 下半年; tableName = "T_OD_BORDER" + beginDate.substring(2, 4) + "07"; } map.put("border", tableName); map.put("orderdesc", "T_OD_ORDERDESC".concat(beginDate.substring(2, 4)).concat(currentMonth)); map.put("passinfo", "T_OD_PASSINFO".concat(beginDate.substring(2, 4)).concat(currentMonth)); List<PasteCountExcel> pasteCountExcels = statisticsService .selectTieDianXsListDownload(map); if (pasteCountExcels.size() > 0 && pasteCountExcels != null) {// 如果数据不为空则写入Excel文件后下载; // 导出数据到报表; exportTdmxXs(selectListHeaderXs(), pasteCountExcels, response); }else{// 如果数据为空则不进行下载直接显示页面提示信息; map.put("tips", "当前数据为空!"); return "/jsp/stat/stickPointStatistics_xs"; } } catch (Exception e) { PrintWriterUtil.writeToClient(response, "下载失败!"); e.printStackTrace(); } return null; } //销售报表贴点具体方法; public static void exportTdmxXs(List<String> headers, List<PasteCountExcel> contents, HttpServletResponse response){ try { if (contents != null && contents.size() > 0) { String xlsFile = "tdmx_xs" + new SimpleDateFormat("yyyyMMddHHssmm") + ".xls"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("销售贴点明细"); HSSFRow nRow = null; HSSFCell nCell = null; // 表头 nRow = sheet.createRow(0);// 表头(共一行); for(int i = 0; i < headers.size(); i++){// 共?列; 日期, 采购ID, 所属人, 所属城市, 级别贴点订单票数, 级别贴点金额; // 为列 创建单元格对象 nCell = nRow.createCell(i); // 为列 创建单元格对象设置内容 nCell.setCellValue(headers.get(i)); /** 为表头每个列设置颜色*/ HSSFCellStyle curStyle = wb.createCellStyle(); //curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 单元格内容居中对齐 curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); curStyle.setFillForegroundColor((short) 10); //******** curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//粗实线 curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置字体; HSSFFont curFont = wb.createFont(); //设置字体 curFont.setFontName("微软雅黑"); //设置英文字体 curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); //设置中文字体，那必须还要再对单元格进行编码设置 curFont.setFontHeightInPoints((short)12); //字体大小 curFont.setBoldweight((short)5); curStyle.setFont(curFont); //******** nCell.setCellStyle(curStyle); /**end*/ } // 表内容 for (int i = 0; i < contents.size(); i++) { nRow = sheet.createRow(i+1); // 表内容(动态行); for(int j = 0; j < headers.size(); j++){// 共?列; 日期, 采购ID, 所属人, 所属城市, 级别贴点订单票数, 级别贴点金额; // 为列 创建单元格对象 nCell = nRow.createCell(j); // 为列 创建单元格对象设置内容 if ("日期".equals(headers.get(j))) { sheet.setColumnWidth(j, 3000); nCell.setCellValue(contents.get(i).getLastUpdateTime()); }else if ("采购ID".equals(headers.get(j))) { nCell.setCellValue(contents.get(i).getBuyerId()); }else if ("所属人".equals(headers.get(j))) { nCell.setCellValue(contents.get(i).getUsername()); }else if ("所属城市".equals(headers.get(j))) { sheet.setColumnWidth(j, 3000); nCell.setCellValue(contents.get(i).getBuyerCity()); }else if ("级别贴点订单票数".equals(headers.get(j))) { sheet.setColumnWidth(j, 5000); nCell.setCellValue(contents.get(i).getTicketCount()); }else if ("级别贴点金额".equals(headers.get(j))) { sheet.setColumnWidth(j, 4000); nCell.setCellValue(contents.get(i).getLevelPrice()); } // 为该列设置背景色 start HSSFCellStyle curStyle = wb.createCellStyle(); curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 单元格内容居中对齐 curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); curStyle.setFillForegroundColor((short) 40); //******** curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //******** nCell.setCellStyle(curStyle); // end } } //设置样式 //nCell.setCellStyle(leftStyle(wb)); // 准备下载; response.reset(); response.addHeader("Content-Disposition", "attachment;filename=" + xlsFile); response.setContentType("application/octet-stream");// .*（ 二进制流，不知道下载文件类型）, 不知道下载的类型具体是什么可以使用这个; //response.setContentType("application/vnd.ms-excel");// 定义输出类型 ServletOutputStream sos = response.getOutputStream(); wb.write(sos); sos.close(); } } catch (Exception e) { e.printStackTrace(); } } public static List<String> selectListHeaderXs(){ //日期, 采购ID, 所属人, 所属城市, 级别贴点订单票数, 级别贴点金额; List<String> lists = new ArrayList<String>(); lists.add("日期"); lists.add("采购ID"); lists.add("所属人"); lists.add("所属城市"); lists.add("级别贴点订单票数"); lists.add("级别贴点金额"); return lists; } }
点击下载时控制台不会报错:
java.lang.IllegalStateException: getOutputStream() has already been called for this response
没报错吧!:-D:)
完成;
