JSP中Excel上传Oracle空白值转Null、无列名导出及报错排查

Hey there, let's break down your JSP Excel-related issues step by step—we'll fix that annoying IndexOutOfBoundsException first, then tackle your two feature requirements.

1. Fixing the java.lang.IndexOutOfBoundsException: Index: 7, Size: 7 #

This error pops up because you're trying to access an index that doesn't exist in your collection (since list indexes start at 0, a size of 7 means the last valid index is 6). Looking at your upload code, this is almost certainly happening when reading Excel rows—you're assuming every row has a fixed number of columns, but some rows might have fewer columns than expected, leading to a failed index lookup.

Quick Fix:
When reading Excel cells, don't rely on fixed indexes without checking if the cell exists. Use POI's MissingCellPolicy to handle missing cells gracefully:

Row row = sheet.getRow(rowNum);
// Loop through expected columns, handling missing cells
for(int col = 0; col < yourExpectedColumnCount; col++){
    // Return null if cell doesn't exist or is blank
    Cell cell = row.getCell(col, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
    Object cellValue = null;
    
    if(cell != null){
        // Handle different cell types
        switch(cell.getCellType()){
            case STRING:
                // Convert empty strings to null
                cellValue = cell.getStringCellValue().trim().isEmpty() ? null : cell.getStringCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            // Add cases for other cell types (boolean, date, etc.) as needed
        }
    }
    CellArrayListHolder.add(cellValue);
}

Also, double-check any code where you access CellArrayListHolder by index later—make sure you're not using an index that's equal to or larger than the collection's size.

2. Converting Excel Blank Values to Null for Oracle Insert #

Your original upload error with blank values came from inserting empty strings into Oracle, which might conflict with column constraints (like NOT NULL) or just not match your intended data handling. With the cell reading logic above, we already convert blanks to null—now we just need to make sure the JDBC insert handles null correctly.

Updated Insert Logic:
Use PreparedStatement.setNull() instead of passing empty strings when the value is null:

// Example insert query (adjust columns to match your table)
String insertSql = "INSERT INTO your_table(col1, col2, col3, col4) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertSql);

for(int i = 0; i < CellArrayListHolder.size(); i++){
    Object value = CellArrayListHolder.get(i);
    if(value == null){
        // Use the correct SQL type for your column (e.g., Types.VARCHAR for text columns)
        pstmt.setNull(i + 1, Types.VARCHAR);
    } else {
        // Set the value based on its type
        if(value instanceof String){
            pstmt.setString(i + 1, (String) value);
        } else if(value instanceof Double){
            pstmt.setDouble(i + 1, (Double) value);
        }
        // Add more type checks as needed for your data
    }
}
pstmt.executeUpdate();

This ensures blank Excel values are inserted as NULL into Oracle, avoiding those insert errors.

3. Exporting Database Tables to Excel Without Specifying Columns #

To export a table dynamically (no hardcoded column names), we'll use JDBC's DatabaseMetaData to get the table's column names automatically, then build the Excel file with those columns as headers.

Dynamic Export Code:

// Replace with your target table name
String targetTable = "your_table_name";

// Get database metadata to fetch column names
DatabaseMetaData metaData = con.getMetaData();
ResultSet columnRs = metaData.getColumns(null, null, targetTable, null);

// Create Excel workbook and sheet
Workbook workbook = new XSSFWorkbook(); // Use HSSFWorkbook for .xls files
Sheet sheet = workbook.createSheet(targetTable);

// Build header row with column names
Row headerRow = sheet.createRow(0);
int colIdx = 0;
while(columnRs.next()){
    String colName = columnRs.getString("COLUMN_NAME");
    Cell headerCell = headerRow.createCell(colIdx);
    headerCell.setCellValue(colName);
    colIdx++;
}

// Fetch all data from the table
Statement stmt = con.createStatement();
ResultSet dataRs = stmt.executeQuery("SELECT * FROM " + targetTable);

// Populate data rows
int rowIdx = 1;
while(dataRs.next()){
    Row dataRow = sheet.createRow(rowIdx);
    colIdx = 0;
    
    // Reset column metadata cursor to get column names again
    columnRs.beforeFirst();
    while(columnRs.next()){
        String colName = columnRs.getString("COLUMN_NAME");
        Object cellValue = dataRs.getObject(colName);
        Cell dataCell = dataRow.createCell(colIdx);
        
        if(cellValue != null){
            // Handle different data types
            if(cellValue instanceof String){
                dataCell.setCellValue((String) cellValue);
            } else if(cellValue instanceof Number){
                dataCell.setCellValue(((Number) cellValue).doubleValue());
            } else if(cellValue instanceof Date){
                dataCell.setCellValue((Date) cellValue);
            }
            // Add more type handlers as needed
        }
        colIdx++;
    }
    rowIdx++;
}

// Send the Excel file to the client
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=" + targetTable + ".xlsx");
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
workbook.close();

This code automatically detects all columns in your target table, creates a matching Excel header, and fills in all the table data—no need to specify column names upfront.

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