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