java 无法使用 POI 为 Excel 工作表中的空值插入数据

Unable to insert data using POI for null values in excel sheet

提问by JavaGeek

I'm trying to find a blank value in excel (xlsx) sheet and replace with some string like "ENTER VALUE" using my program with Apache POI library as below

我试图在 excel (xlsx) 工作表中找到一个空白值，并使用我的程序和 Apache POI 库替换为一些字符串，如“ENTER VALUE”，如下所示

I'm able to read and identify blank/null values in excel sheet but couldnt insert any value into those cells

我能够读取和识别 Excel 工作表中的空白/空值，但无法在这些单元格中插入任何值

public static void main(String args[]) throws IOException, InvalidFormatException
    {



                FileInputStream inputFile = new FileInputStream("//Users//suk//Documents/tes//testexcel.xlsx");

        //now initializing the Workbook with this inputFie


        // Create workbook using WorkbookFactory util method

         Workbook wb = WorkbookFactory.create(inputFile);

         // creating helper for writing cells

         CreationHelper createHelper = wb.getCreationHelper();

        // setting the workbook to handle null

         wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);


        Sheet sheet = wb.getSheetAt(0);


        for (Row row : sheet) {
            int lastCellNo =row.getLastCellNum();
            int firstCellNo=row.getFirstCellNum();

            int rowNo =row.getRowNum();
            System.out.println(" row number = "+rowNo);
            System.out.println(" last cell no = "+lastCellNo);


            for(int i=firstCellNo;i<lastCellNo;i++){
                System.out.println("************");

                Cell cell = row.getCell(i);
                int colIndex =cell.getColumnIndex();
                if(cell==null)
                {
                    System.out.println(" The Cell:"+colIndex+" for row "+row.getRowNum()+" is NULL");
                }




              System.out.println(" column  index  = "+colIndex);


             int cellType = cell.getCellType();
             System.out.println(" cell type ="+cellType);

             // writing a switch case statement

             switch(cellType)
             {

             case 0:
             {
                double numValue = cell.getNumericCellValue();
                //System.out.println(numValue);
                 break;
             }
             case 1:
             {
                 String cellString = cell.getStringCellValue();
                 System.out.println("----String value = "+cellString+"---String length ="+cellString.length());

                 // here checking null consition

            /*   if(cellString == null || cellString.equalsIgnoreCase("") || cellString.length()<=0 || cellString.equalsIgnoreCase(" "))
                 {
                     // here creating the cell value after last cell


                 } */
                 break;
             }
             case 4:
             {
                  boolean bolValue =cell.getBooleanCellValue();
                  //System.out.println(bolValue);
                  break;
             }

             // for case of blank cell

             case 3:
             {

                 //int lastCellPlus =lastCellNo+1;
                 //System.out.println("last+1 column ="+lastCellPlus);
                 //row.createCell(lastCellPlus).setCellValue(" NULL VALUE..PLEASE ENTER VALUE ");

                 System.out.println(" cell details = "+cell.getColumnIndex()+" row details ="+row.getRowNum());
                 cell.setCellValue(createHelper.createRichTextString("ENTER VALUE"));
                 System.out.println(" Sucessfully written error");

                  break;
             }


             default: 
                 System.out.println(" unknown format");
                 break;

             }// switch

          } // row and cell iterator


          }
        }

}

}

Below is the bit of code by which im identifying its Blank/Null value and trying to insert String but its not writing to that cell

下面是一段代码，我通过它识别其空白/空值并尝试插入字符串但不写入该单元格

case 3:
             {

                 //int lastCellPlus =lastCellNo+1;
                 //System.out.println("last+1 column ="+lastCellPlus);
                 //row.createCell(lastCellPlus).setCellValue(" NULL VALUE..PLEASE ENTER VALUE ");

                 System.out.println(" cell details = "+cell.getColumnIndex()+" row details ="+row.getRowNum());
                 cell.setCellValue(createHelper.createRichTextString("ENTER VALUE"));
                 System.out.println(" Sucessfully written error");

                  break;
             }

回答by swamy

if(row.getCell(0))==null || getCellValue(row.getCell(0)).trim().isEmpty()){
   cell.setCellValue("ENTER VALUE");

}

}

private String getCellValue(Cell cell) {
    if (cell == null) {
        return null;
    }
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return cell.getNumericCellValue() + "";
    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return cell.getBooleanCellValue() + "";
    }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
        return cell.getStringCellValue();
    }else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
        return cell.getErrorCellValue() + "";
    } 
    else {
        return null;
    }
}

回答by Gagravarr

This should work fine:

这应该可以正常工作：

Sheet s = wb.getSheetAt(0);
for (int rn=0; rn<s.getLastRowNum(); rn++) {
    Row r = s.getRow(rn);
    if (r == null) {
       // No values exist in this row
       r = s.createRow(rn);
    }

    int minColumnsPerRow=10; // Fix for your needs
    for (int cn=0; cn<minColumnsPerRow; cn++) {
       Cell c = r.getCell(cn);
       if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
           // This cell is empty
           if (c == null) { 
              c = r.createCell(cn, Cell.CELL_TYPE_STRING);
           }
           cell.setCellValue(createHelper.createRichTextString("ENTER VALUE"));
       }
    }
 }

Remember that cells can be blank or null (never used), and rows can be null (never used)

请记住，单元格可以为空或为空（从未使用过），行可以为空（从未使用过）