如何用Java Apache POI填充Excel空白单元格并计算第11列数值

Hey there! Let's work through your two Apache POI Excel handling challenges, using your existing code as a starting point. First, let's recap your code for context:

FileInputStream file= new FileInputStream(new File(path)); //Read the spreadsheet that needs to be updated 
HSSFWorkbook wb = new HSSFWorkbook(file); //Access the workbook 
HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it. 
Cell cell = null; // declare a Cell object 

/////////////////////////////////// PRENDRE LES LIGNE ET LES COLLOLNES A MODIFER 
int nbrow=Integer.parseInt(tx1.getText()); 
int nbClmn=Integer.parseInt(tx2.getText()); 
// int nbClmn = worksheet.getRow(0).getPhysicalNumberOfCells(); 
System.out.println(nbClmn); 

for(int j=0;j<nbrow;j++){ 
    for(int i=0;i<nbClmn;i++){ 
        cell = worksheet.getRow(j).getCell(i);
    } 
}

for(int i=1;i<=nbClmn;i++){ 
    for(int j=1;j<nbrow;j++){ 
        System.out.println(wb.getSheetAt(0).getRow(j).getCell(i)); 
        String r=wb.getSheetAt(0).getRow(j).getCell(i).toString(); 
        if(r.equals("BT") || r.equals("CB") || r.equals("CX") || r.equals("EB") || r.equals("EC") || r.equals("EP") || r.equals("NA") || r.equals("PP")){ 
            cell = worksheet.getRow(j).getCell(i); 
            cell.setCellValue("BT"); 
        }else{ 
            cell = worksheet.getRow(j).getCell(i); 
        } 
        if(r.equals("CM") || r.equals("EM") || r.equals("GC") || r.equals("HT") || r.equals("MT")){ 
            cell = worksheet.getRow(j).getCell(i); 
            cell.setCellValue("MT"); 
        }else{ 
            cell = worksheet.getRow(j).getCell(i); 
        }
    }
}

file.close(); //Close the InputStream 
FileOutputStream output_file =new FileOutputStream(new File(export)); //Open FileOutputStream to write updates 
wb.write(output_file); 
output_file.close(); 

1. Filling All Blank Cells with "Not Available" #

Blank cells in Excel can be tricky—they might be non-existent (return null when you call getCell()), or exist but have empty content. We'll handle both cases by iterating through every row and column, and setting blank cells to your desired value.

Replace your existing loop logic with this code block to add the blank cell filling:

// First, fill all blank cells with "Not Available"
int totalColumns = 10; // You mentioned your Excel has 10 columns
for (int rowNum = 0; rowNum < worksheet.getPhysicalNumberOfRows(); rowNum++) {
    HSSFRow row = worksheet.getRow(rowNum);
    // If the row doesn't exist (empty row), create it to avoid null errors
    if (row == null) {
        row = worksheet.createRow(rowNum);
    }
    // Iterate through all 10 columns
    for (int colNum = 0; colNum < totalColumns; colNum++) {
        // Use CREATE_NULL_AS_BLANK to handle non-existent cells
        HSSFCell cell = row.getCell(colNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        // Check if cell is blank or has empty string content
        if (cell.getCellType() == CellType.BLANK || 
            (cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().isEmpty())) {
            cell.setCellValue("Not Available");
        }
    }
}

2. Adding a 11th Column with Calculated Values (Fixing Null Issues) #

Now that we've filled all blanks, we can safely add the 11th column. We'll assume you want to calculate a sum of the first 10 columns (adjust the calculation logic to fit your needs). We'll handle "Not Available" values by treating them as 0 for calculations.

Add this code right after the blank cell filling block:

// Add 11th column (index 10, since columns start at 0) with calculated values
// First, set the header for the new column
HSSFRow headerRow = worksheet.getRow(0);
if (headerRow == null) {
    headerRow = worksheet.createRow(0);
}
HSSFCell newHeader = headerRow.createCell(10);
newHeader.setCellValue("Calculated Total"); // Customize this header text as needed

// Iterate through data rows (skip header row, start at row 1)
for (int rowNum = 1; rowNum < worksheet.getPhysicalNumberOfRows(); rowNum++) {
    HSSFRow row = worksheet.getRow(rowNum);
    if (row == null) continue;
    
    double calculatedValue = 0.0;
    // Calculate using first 10 columns
    for (int colNum = 0; colNum < 10; colNum++) {
        HSSFCell cell = row.getCell(colNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        String cellContent = cell.getStringCellValue().trim();
        
        // Skip "Not Available" values (treat as 0)
        if (!cellContent.equals("Not Available")) {
            try {
                // Convert cell content to number (adjust if you need different data types)
                calculatedValue += Double.parseDouble(cellContent);
            } catch (NumberFormatException e) {
                // If cell isn't a number, treat as 0 (adjust this logic if needed)
                calculatedValue += 0.0;
            }
        }
    }
    
    // Set the calculated value in the 11th column
    HSSFCell resultCell = row.createCell(10);
    resultCell.setCellValue(calculatedValue);
}

Final Notes #

• Keep your existing file handling logic (opening/closing streams) intact—insert the above code blocks before you close the input stream and write to the output stream.
• If your calculation isn't a sum, modify the calculatedValue logic to match your needs (e.g., average, multiplication, conditional checks).
• The Row.MissingCellPolicy.CREATE_NULL_AS_BLANK is key to avoiding null pointer exceptions when dealing with empty rows/cells.

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