分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; }