使用POI导入数据到Excel数据量过大报错解决方案

来源：互联网发布：淘宝运营做什么的编辑：程序博客网时间：2024/05/06 02:52

导出excel报不能超过4000行错误

POI操作Excel中，导出的数据不是很大时，则不会有问题，而数据很多或者比较多时，

就会报以下的错误，是由于cell styles太多create造成，故一般可以把cellstyle设置放到循环外面报错如下：

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:1162)

错误示例

for (int i = 0; i < 10000; i++) {      Row row = sheet.createRow(i);       Cell cell = row.createCell((short) 0);       CellStyle style = workbook.createCellStyle();      Font font = workbook.createFont();       font.setBoldweight(Font.BOLDWEIGHT_BOLD);       style.setFont(font);       cell.setCellStyle(style);  }

改正后正确代码

CellStyle style = workbook.createCellStyle();  Font font = workbook.createFont();  font.setBoldweight(Font.BOLDWEIGHT_BOLD);  style.setFont(font);  for (int i = 0; i < 10000; i++) {       Row row = sheet.createRow(i);       Cell cell = row.createCell((short) 0);       cell.setCellStyle(style);  }

以下是生成excel核心代码：

public static void export( ExcelBean excelBean, HttpServletResponse response) throws IOException {        String filename = excelBean.getName();        filename = new String(filename.replaceAll("\\s|;", "").getBytes("gbk"), "ISO8859-1");        response.setContentType("application/octet-stream;charset=utf-8");        response.setCharacterEncoding("utf-8");        response.setContentType("application/vnd.ms-excel");        response.setHeader("Content-disposition", "attachment;filename=" + filename);        OutputStream outputStream = response.getOutputStream();        createExcel(excelBean, outputStream);        outputStream.flush();        outputStream.close();     }

public static void createExcel(ExcelBean excelBean, OutputStream outputStream) throws IOException{        // 声明一个工作薄        HSSFWorkbook wb = new HSSFWorkbook();        // 生成一个表格        HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());        HSSFRow row0 = sheet.createRow(0);        int descIndexBegin = 9;        // int descIndexEnd = 12;        HSSFRow row9 = sheet.createRow(descIndexBegin);        HSSFRow row = sheet.createRow(1);        row.setHeightInPoints(25.0f);//行高度：25像素        //设置样式        HSSFCellStyle style = wb.createCellStyle();        HSSFCellStyle descStyle = wb.createCellStyle();        // 设置水平居中样式        setAlignCenter(style);        // 设置标题栏样式        setTitleStyle(wb,style);        if(excelBean.isHeadBold()){            HSSFFont headfont = wb.createFont();            headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            style.setFont(headfont);        }        HSSFCell cell;        ExcelTitle[] titles = excelBean.getTitles();        //==========设置顶部标题(合并第一行多个列)==========        if(titles.length>=2){            sheet.addMergedRegion(new CellRangeAddress(0,0,0,titles.length-1));//起始行，结束行，起始列，结束列        }        cell = row0.createCell(0);        String topTitle = excelBean.getName();        String titleName = topTitle.substring(0,topTitle.lastIndexOf("."));        cell.setCellValue(titleName);//第一行标题        cell.setCellStyle(style);        setTopTitleStyle(wb,cell,titleName);        //==========设置顶部标题(合并第一行多个列)==========        //====================设置说明==========begin==========        String desc = excelBean.getDescription();        if(StringUtils.isNoneBlank(desc)){            if(titles.length>=2){                sheet.addMergedRegion(new CellRangeAddress(descIndexBegin,descIndexBegin,0,titles.length-1));//firstRow, lastRow, firstCol, lastCol            }            HSSFCell descCell = row9.createCell(0);            descCell.setCellValue("**说明："+desc+"\r\n 请删除说明文字所在的行后再上传");//第一行标题//            setAlignLeft(style);            setAlignLeftAndTop(descCell,descStyle);//设置合并后的左上对齐方式        }        //====================设置说明==========end==========        //====================设置第二标题====================        for(int i=0; i < titles.length; i++){            ExcelTitle title = titles[i];            cell= row.createCell(i);            //如果标题中包含'必填、唯一字段'，则设置为红色            String titleValue = title.getValue();            int beginIndex = titleValue.indexOf("(");            int endIndex = titleValue.indexOf(")");            String subTitle = titleValue.substring(beginIndex,endIndex);            if(subTitle.contains("必填") || subTitle.contains("唯一字段")){                HSSFRichTextString ts= new HSSFRichTextString(titleValue);                HSSFFont font = wb.createFont();                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);                font.setColor(HSSFColor.RED.index);                font.setFontHeightInPoints((short) 14);// 设置字体大小                HSSFFont font2 = wb.createFont();                font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);                font2.setFontHeightInPoints((short) 14);// 设置字体大小                ts.applyFont(0,beginIndex,font2);//普通字体                ts.applyFont(beginIndex,endIndex+1,font);//红色字体                cell.setCellValue(ts);            }else{                cell.setCellValue(titleValue);            }            cell.setCellStyle(style);            int columnWidth = title.getWidth() > 0 ? title.getWidth() : excelBean.getColumnWidth();            sheet.setColumnWidth(i, getColWidth(columnWidth));        }        //====================设置第二行标题====================        //====================设置tips:example====================        int rowIndex = 2;        int rowHeight = excelBean.getRowHeight();        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();        HSSFCellStyle cellStyle = wb.createCellStyle();        for(String[] data : excelBean.getDataList()){            row = sheet.createRow(rowIndex ++ );            if(rowHeight > 0){                row.setHeight((short) getRowHeight(rowHeight));            }            else{                rowHeight = 18;            }            for(int j=0; j<data.length; j ++){                String value = data[j];                cell = row.createCell(j);                /*if(isUrl(value)){                    if(isImage(value)){                        int columnWidth = titles[j].getWidth() > 0 ? titles[j].getWidth() : excelBean.getColumnWidth();                        insertImage(wb, patriarch, value, rowHeight, columnWidth, (short)(j), rowIndex);                    }                    else{                        setLink(wb, cell, value);                    }                }else{*/                    cell.setCellValue(value);                    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);                    cell.setCellStyle(cellStyle);               /* }*/            }        }        for (String[] tipsList : excelBean.getTipsList()) {            row = sheet.getRow(1);//在第一行设置批注            for(int j=0; j<tipsList.length; j ++){                String commentValue = tipsList[j];                if(StringUtils.isNotBlank(commentValue)){                    cell = row.getCell(j);                    HSSFComment comment=patriarch.createComment(new HSSFClientAnchor(0,0,0,0,(short)2,2,(short)5,6));                    //输入批注信息                    comment.setString(new HSSFRichTextString(commentValue));                    cell.setCellComment(comment);                }            }        }        wb.write(outputStream);        wb.close();    }

0 0