POI3.8 导出大数据excel

今天项目需要导出站点对应的频道展示，导出时使用的poi3.8中的HSSFWorkbook workBook =new HSSFWorkbook()；来创建workBook，但是在循环样式的时候，会报：

java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

详情见：

报错解决办法

SXSSFWorkbook workBook =new SXSSFWorkbook();

   @RequestMapping(value="/api/uploadExcelByPoi",method=RequestMethod.GET)
    public void uploadExcelByPoi(@RequestParam(value="chSiteId", required=true) Integer chSiteId,HttpServletRequest request,HttpServletResponse response){//查询需要的集合    List<FocChannel> list = focChannelsService.uploadExcelByPoi(chSiteId);     //3.创建workbook      SXSSFWorkbook workBook =new SXSSFWorkbook();        //根据workBook创建sheet          Sheet sheet = workBook.createSheet("站点频道展示");        //根据sheet创建行          Row rowHead = sheet.createRow(0);          //创建excel的头部标题行,及标题行的样式的设置        createTitleCell(workBook,rowHead,sheet);         //创建主题内容       buildMainbody(list, workBook, sheet);       //导出Excel     downLoadExcel(request, response, workBook);           }private void downLoadExcel(HttpServletRequest request,HttpServletResponse response, SXSSFWorkbook workBook) {SimpleDateFormat sim=new SimpleDateFormat("yyyyMMddhhmmss");     String strDate = sim.format(new Date());     //随机数     RandomStringUtils randomStringUtils=new RandomStringUtils();     //生成指定长度的字母和数字的随机组合字符串     String randomStr = randomStringUtils.randomAlphanumeric(5);   String xlsName=strDate+randomStr+"站点频道信息表.xls";   FileUtil.downloadXLSFile(request, response, workBook, xlsName);}private void buildMainbody(List<FocChannel> list, SXSSFWorkbook workBook,Sheet sheet) {for (int i = 0; i < list.size(); i++) {buildMainBodyAandMainBodyStyle(list, workBook, sheet, i);   }}private void buildMainBodyAandMainBodyStyle(List<FocChannel> list,SXSSFWorkbook workBook, Sheet sheet, int i) { CreationHelper createHelper = workBook.getCreationHelper();       CellStyle cellStyle = workBook.createCellStyle();       cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));  Row rowBody = sheet.createRow(i+1);//创建列FocChannel foc = list.get(i);Cell idCell= rowBody.createCell(0);idCell.setCellValue(i+1);Cell timeCell = rowBody.createCell(1);SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd");timeCell.setCellValue(sim.format(new Date()));Cell inspectCell = rowBody.createCell(2);inspectCell.setCellValue("新增");Cell websiteCell= rowBody.createCell(3);websiteCell.setCellValue("网站");Cell webSiteNameCell= rowBody.createCell(4);webSiteNameCell.setCellValue(foc.getChCascadeName());Cell accountCell = rowBody.createCell(5);accountCell.setCellValue("频道");Cell urlCell= rowBody.createCell(6);urlCell.setCellValue(foc.getChUrl());Cell newUrlCell=rowBody.createCell(7);        newUrlCell.setCellValue("");        Cell causeCell=rowBody.createCell(8);        causeCell.setCellValue("");CellStyle fontStyle = bulidMainFontStyleHead(workBook);idCell.setCellStyle(fontStyle);//timeCell.setCellStyle(cellStyle);timeCell.setCellStyle(fontStyle);inspectCell.setCellStyle(fontStyle);websiteCell.setCellStyle(fontStyle);webSiteNameCell.setCellStyle(fontStyle);accountCell.setCellStyle(fontStyle);urlCell.setCellStyle(fontStyle);newUrlCell.setCellStyle(fontStyle);causeCell.setCellStyle(fontStyle);}/** * 创建excel的头部标题行 * @param rowHead * @param sheet  */private void createTitleCell(SXSSFWorkbook workBook,Row rowHead, Sheet sheet) {//根据row创建cll          Cell idCell = rowHead.createCell(0);          idCell.setCellValue("序号");          Cell timeCell = rowHead.createCell(1);          timeCell.setCellValue("反馈时间");          Cell inspectCell = rowHead.createCell(2);          inspectCell.setCellValue("新增/核查");          Cell websiteCell = rowHead.createCell(3);          websiteCell.setCellValue("网站/论坛");          Cell webSiteNameCell = rowHead.createCell(4);          webSiteNameCell.setCellValue("名称");          Cell accountCell = rowHead.createCell(5);          accountCell.setCellValue("频道/账号");         Cell urlCell = rowHead.createCell(6);          urlCell.setCellValue("频道链接");        Cell newUrlCell=rowHead.createCell(7);        newUrlCell.setCellValue("涉及新闻链接");        Cell causeCell=rowHead.createCell(8);        causeCell.setCellValue("核查原因");        CellStyle buildStyleTitle = bulidFontStyleHead(workBook);        buildStyleTitle.setWrapText(true);//设置自动换行        idCell.setCellStyle(buildStyleTitle);        timeCell.setCellStyle(buildStyleTitle);        inspectCell.setCellStyle(buildStyleTitle);        websiteCell.setCellStyle(buildStyleTitle);        accountCell.setCellStyle(buildStyleTitle);        urlCell.setCellStyle(buildStyleTitle);        webSiteNameCell.setCellStyle(buildStyleTitle);        causeCell.setCellStyle(buildStyleTitle);        newUrlCell.setCellStyle(buildStyleTitle);      //设置列宽（给时间的单元格的宽度给大点，防止时间显示格式错误！）          sheet.setColumnWidth(0, 20*256);          sheet.setColumnWidth(1, 20*300);          sheet.setColumnWidth(2, 20*256);          sheet.setColumnWidth(3, 20*256);          sheet.setColumnWidth(4, 20*256);          sheet.setColumnWidth(5, 20*256);          sheet.setColumnWidth(6, 20*500);        sheet.setColumnWidth(7, 20*400);         sheet.setColumnWidth(8, 20*400);         }private CellStyle bulidMainFontStyleHead(SXSSFWorkbook workBook) {//设置样式CellStyle style = workBook.createCellStyle();Font font  = workBook.createFont(); font.setFontHeightInPoints((short) 11);//字号       //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗   //font.setColor(HSSFColor.RED.index);//设置字体颜色font.setFontName("微软雅黑"); // 将“黑体”字体应用到当前单元格上style.setFont(font);      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//内容左右居中    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//内容上下居中return style;}private CellStyle bulidFontStyleHead(SXSSFWorkbook workBook) {//设置样式CellStyle style = workBook.createCellStyle();Font font  = workBook.createFont(); font.setFontHeightInPoints((short) 11);//字号       font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗   //font.setColor(HSSFColor.RED.index);//设置字体颜色font.setFontName("黑体"); // 将“黑体”字体应用到当前单元格上style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//北京颜色style.setFillPattern(CellStyle.SOLID_FOREGROUND);//style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);style.setFont(font);      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//内容左右居中    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//内容上下居中return style;}