分sheet导出excel 大数据量导出

    我在做一个大量数据分成多个sheet进行excel导出的功能，但是数据量为40000条的时候，就会抛一个错误

java.lang.IllegalStateException: getOutputStream() has already been called for this response
一直没有得到解决，不过，数据量小的时候还是可以导出的，先存下来，以后知道改进方法再来更新这个日志

 

一、准备数据

//项目信息导出，准备导出的表头信息if(contactQuestionId == null && corpQuestionId == null){reportTitle = "项目信息导出_"+sdf.format(new Date()) + ".xls"; header.addAll(Constant.CORP_BASE_INFO_HEADER); header.addAll(Constant.CUSTOMER_REGION_INFO_HEADER); header.addAll(Constant.CUSTOMER_TRADE_INFO_HEADER); header.addAll(Constant.CORP_DIAL_INFO_HEADER); header.addAll(Constant.CONTACT_BASE_INFO_HEADER); header.addAll(Constant.LEADS_INFO_HEADER); header.addAll(Constant.CONTACT_DIAL_INFO_HEADER);}//导出6万以上数据时经过测试会抛错，导出小数据量时适用ExcelParser parser = new ExcelParser();HSSFWorkbook workBook = new HSSFWorkbook();//设置每页的容量int pageSiz= 40000;//查询要导出的总条数 int count = getQuestionManageService().getAllInfoCount(Long.parseLong(caseId)); int page; //计算分页 page = count/pageSiz+(count%pageSiz > 0 ? 1:0); for(int i = 0; i < page; i++){//获取每次分页的数据RowSelection rows = new RowSelection(i,pageSiz);List<Object[]> values = getQuestionManageService().getAllInfo(Long.parseLong(caseId),rows);excelInfo.add(header.toArray());excelInfo.addAll(values);workBook = parser.getWriteBook(workBook, "第"+i+1+"页", values);}OutputStream os = Utils.outExcel(response,reportTitle);workBook.write(os);在执行这句的时候jsp抛出错误信息了，郁闷if(os != null){os.flush();os.close();}}catch(Exception e){e.printStackTrace();}//最初引用以下创建方式，适用于数据量不超过30000的数据导出//ExcelParser parser = new ExcelParser();//HSSFWorkbook workBook = new HSSFWorkbook();//HSSFWorkbook workBook = parser.getWriteBook(excelInfo);//workBook.write(Utils.outExcel(response,reportTitle));return null;}

二、向excel中写入数据

public HSSFWorkbook getWriteBook(HSSFWorkbook wb,String sheetName,List<Object[]> values) throws IOException,FileNotFoundException {HSSFSheet sheet = wb.createSheet(sheetName);int row = 0;for (Object[] objs : values) {HSSFRow row0 = sheet.createRow(row);int col = 0;for (Object obj : objs) {row0.createCell(col).setCellValue(new HSSFRichTextString(obj == null|| "null".equals(obj.toString()) ? "": String.valueOf(obj)));col++;if(col>255)break;}row++;}return wb;}

三、设置导出，输出流

public static OutputStream outExcel(HttpServletResponse response,String fileName)throws Exception{ StringBuffer sb = new StringBuffer(); sb.append("attachment; filename=").append(fileName); OutputStream os = response.getOutputStream(); response.setHeader("Expires", "0"); response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0"); response.setHeader("Pragma", "public"); response.setContentType("application/x-msdownload;charset=UTF-8"); response.setHeader("Content-Disposition", new String( sb.toString().getBytes(), "ISO8859-1")); // response.flushBuffer(); return os; }