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

代码报错
java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1158)
at cn.wxdl.agencysys.util.ExportExcel.cteateCellWithType(ExportExcel.java:228)
at cn.wxdl.agencysys.restful.server.webwork.confirmation.TicketConfirmController.exportExcel(TicketConfirmController.java:821)
at cn.wxdl.agencysys.restful.server.webwork.confirmation.TicketConfirmController.exportTicketConfirm(TicketConfirmController.java:685)
at sun.reflect.GeneratedMethodAccessor6691.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
原因和错误描述一致,很明显:代码创建的cellSyle太多了。可以查一下自己代码,是不是在循环中调用了HSSFCellStyle cellStyle = wb.createCellStyle();如果有,不要放到循环中,在循环外面创建好了,再放到代码中引用。
下面是我的错误代码:
private void exportOrderStatememtYear(List<ExportRoutesIndexStatisticVo> list, HttpServletResponse response, RouteIndexTrendVO routeIndexTrendVO) {
// 循环创建中间的单元格的各项的值
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 6);
exportExcel.cteateCellWithType(wb, row, 0, HSSFCellStyle.ALIGN_CENTER_SELECTION, dataFormat.format(list.get(i).getStatisticDate1()), wb
.createDataFormat().getFormat("@"));
exportExcel.cteateCellWithType(wb, row, 1, HSSFCellStyle.ALIGN_CENTER_SELECTION, list.get(i).getIp(), (short) 1);
if (0 == list.get(i).getIp()) {
exportExcel.cteateCellWithType(wb, row, 2, HSSFCellStyle.ALIGN_CENTER_SELECTION, "", (short) 1);
} else {
exportExcel.cteateCellWithType(wb, row, 2, HSSFCellStyle.ALIGN_CENTER_SELECTION, list.get(i).getPageViewIndex(), (short) 1);
}
}
}
public void cteateCellWithType(HSSFWorkbook wb, HSSFRow row, int col, short align, Object val, short cellType) {
HSSFCell cell = row.createCell(col);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(align);
cellStyle.setWrapText(true);// 指定单元格自动换行
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
}
修复的代码:
private void exportExcel(List<AgencyTouristStatisticNewStore> list, HttpServletResponse response) throws Exception {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
cellStyle1.setWrapText(true);// 指定单元格自动换行
cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
cellStyle1.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
// 循环创建中间的单元格的各项的值
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
AgencyTouristStatisticNewStore store = list.get(i);
exportExcel.cteateCellWithType(wb, row,cellStyle1, (short) 0, store.getResId());
}
}
public void cteateCellWithType(HSSFWorkbook wb, HSSFRow row,HSSFCellStyle cellStyle, int col, Object val) {
...
cell.setCellStyle(cellStyle);
}