使用Java JXL库向XLS文件新增的第三张工作表写入数据失败如何解决

问题根因 #

• 第一个问题：counter变量未重置。你写入sheet3、sheet4时使用全局的counter作为行偏移，当sheet3写满65000行后，counter已经累计到65000，切换到sheet4时你依然用1+counter作为行号，直接超过XLS单表65535的最大行限制，触发报错。
• 第二个问题：逻辑分支设计错误。你将读取结果集的while(rs5.next())整个包在if-else分支里，一旦进入某个分支就会把所有剩余结果全部写入当前工作表，不会再动态判断行数切换工作表，只要剩余数据超过当前工作表剩余容量，就会触发行越界错误。

修复方案 #

调整逻辑，在循环内部动态判断当前写入位置所属的工作表，单独计算每个工作表内的行偏移，参考修正后的代码：

WritableSheet sheet2 = workbook.getSheet("ServiceSummary");
WritableSheet sheet3 = workbook.getSheet("ServiceSummary2");
WritableSheet sheet4 = workbook.getSheet("ServiceSummary3");

final int MAX_ROW_PER_SHEET = 65000; // 单表最大写入行数
while(rs5.next()){
    WritableSheet currentSheet;
    int rowInSheet;
    // 动态判断当前写入的工作表
    if (pos < MAX_ROW_PER_SHEET) {
        currentSheet = sheet2;
        rowInSheet = 1 + pos;
    } else if (pos < 2 * MAX_ROW_PER_SHEET) {
        currentSheet = sheet3;
        rowInSheet = 1 + (pos - MAX_ROW_PER_SHEET);
    } else {
        currentSheet = sheet4;
        rowInSheet = 1 + (pos - 2 * MAX_ROW_PER_SHEET);
    }
    // 写入单元格逻辑
    currentSheet.addCell(new Label(0, rowInSheet, customerAccount, arial10format));
    currentSheet.addCell(new Label(1, rowInSheet, rs5.getString(1), arial10format));
    currentSheet.addCell(new Label(2, rowInSheet, rs5.getString(2), arial10format));
    currentSheet.addCell(new Label(3, rowInSheet, rs5.getString(3), arial10format));
    currentSheet.addCell(new Label(4, rowInSheet, rs5.getString(4), arial10format));
    currentSheet.addCell(new Label(5, rowInSheet, rs5.getString(5), arial10format));
    currentSheet.addCell(new Label(6, rowInSheet, rs5.getString(6), arial10format));
    currentSheet.addCell(new Label(7, rowInSheet, rs5.getString(7), arial10format));
    try{
        currentSheet.addCell(new Number(8, rowInSheet, Double.parseDouble(rs5.getString(8)), arial10format));
    }catch(Exception e){
        currentSheet.addCell(new Label(8, rowInSheet, rs5.getString(8), arial10format));
    }
    try{
        currentSheet.addCell(new Number(9, rowInSheet, Double.parseDouble(rs5.getString(9)), arial10format));
    }catch(Exception e){
        currentSheet.addCell(new Label(9, rowInSheet, rs5.getString(9), arial10format));
    }
    try{
        currentSheet.addCell(new Number(10, rowInSheet, Double.parseDouble(rs5.getString(10)), arial10format));
    }catch(Exception e){
        currentSheet.addCell(new Label(10, rowInSheet, rs5.getString(10), arial10format));
    }
    pos++;
}

rs5.close();
rs5 = null;

内容的提问来源于stack exchange，提问作者Hamad