poi excel超出65536行数限制自动扩展Invalid row number (65536) outside allow

1.xls一个sheet只能装65536行，多余则报错

poi包导出或写入excel超出65536报错： 
java.lang.IllegalArgumentException: Invalid row number (65536) outside allow 

解决:每6w分一个sheet,关键代码红色部分

 

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;


public class ExcelUtil {
     public static HSSFWorkbook getHSSFWorkbook(String sheetName, JsonArray title,
            String[][] values, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // -个sheet限制存65536条，此处仅存60000
        System.out.println(values.length);
        final int sheetNum = (int) Math.ceil((float) values.length / 60000);
        for (int n = 1; n <= sheetNum; n++) {

            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            final HSSFSheet sheet = wb.createSheet(sheetName + "_" + n);
            System.out.println("sheetName" + sheetName + "_" + n);
            sheet.setDefaultColumnWidth(12);

            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row;

            // 第四步，创建单元格，并设置值表头样式
            final HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
            headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
            final Font fontStyle = wb.createFont(); // 字体样式
            fontStyle.setBoldweight(Font.BOLDWEIGHT_BOLD); // 加粗
            fontStyle.setFontName("黑体"); // 字体
            fontStyle.setFontHeightInPoints((short) 11); // 大小
            // 将字体样式添加到单元格样式中
            headerStyle.setFont(fontStyle);
            // 边框
            headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
            headerStyle.setBorderRight(CellStyle.BORDER_THIN);
            headerStyle.setBorderTop(CellStyle.BORDER_THIN);

            // 普通单元格样式,边框,水平居中
            final HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyle.setBorderRight(CellStyle.BORDER_THIN);
            // cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            final HSSFDataFormat df = wb.createDataFormat();  // 此处设置数据格式
            cellStyle.setDataFormat(df.getFormat("#,#0.0")); // 小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00"));
            // 声明列对象
            HSSFCell cell = null;

            // 创建标题
            JsonArray cellArray = new JsonArray();
            JsonObject object = new JsonObject();
            JsonObject temobj = new JsonObject();
            int rowSkip = 0, cellSkip = 0;

            String tempCell = "";
            for (int rowId = 0; rowId < title.size(); rowId++) {
                row = sheet.createRow(rowId);
                object = title.get(rowId).getAsJsonObject();
                cellArray = object.get("row").getAsJsonArray();

                // colId为excel列索引，cellId为行标题值的数组索引，cellId遇到当前单元格已使用时，填充至下一个可使用的单元格
                for (int colId = 0, cellId = 0; cellId < cellArray.size(); colId++) {
                    cell = row.createCell(colId);
                    cell.setCellStyle(headerStyle);

                    if (isMergedRegion(sheet, rowId, colId)) {
                        continue;
                    }
                    temobj = cellArray.get(cellId).getAsJsonObject();
                    tempCell = temobj.get("cellvalue").toString().replace("\"", "");
                    try {
                        // System.out.println(tempCell);
                        tempCell = new String(tempCell.getBytes("UTF-8"), "ISO-8859-1");
                        // System.out.println(tempCell);
                        tempCell = new String(tempCell.getBytes("ISO-8859-1"), "UTF-8");
                        // System.out.println(tempCell);
                    } catch (final Exception e) {
                        e.printStackTrace();
                    }
                    cell.setCellValue(tempCell);
                    // System.out.println(cell);
                    // System.out.println(cell.getStringCellValue());
                    cell.setCellStyle(headerStyle);
                    cellId++;

                    // 合并单元格
                    rowSkip = temobj.get("rowspan").getAsInt();
                    cellSkip = temobj.get("colspan").getAsInt();
                    // System.out.println(rowSkip + "=skip==" + cellSkip);
                    if (rowSkip > 1 && rowSkip-- > 0 || cellSkip > 1 && cellSkip-- > 0) {// 用于起始行列计算时需减1
                        final CellRangeAddress cra = new CellRangeAddress(rowId, rowId + rowSkip,
                                colId, colId + cellSkip); // 起始行, 终止行, 起始列, 终止列 // 终止行,
                        sheet.addMergedRegion(cra);
                    }
                }
            }
            // 创建内容 -个sheet只能存65536条
            for (int i = 0; i < 60000 && i < values.length - (n - 1) * 60000; i++) {
                row = sheet.createRow(title.size() + i);
                for (int j = 0; j < values[i].length; j++) {
                    // 将内容按顺序赋给对应的列对象
                    cell = row.createCell(j);
                    cell.setCellValue(values[(n - 1) * 60000 + i][j]);
                    cell.setCellStyle(cellStyle);

                }
            }
        }
        return wb;
    }

    public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
        final int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            final CellRangeAddress range = sheet.getMergedRegion(i);
            final int firstColumn = range.getFirstColumn();
            final int lastColumn = range.getLastColumn();
            final int firstRow = range.getFirstRow();
            final int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
}