How to check if an excel cell is empty using Apache POI

Question

I am taking input from an excel sheet using Poi jar and wanted to know how to check if a cell is empty or not   Right now I m using the below code   cell   myRow getCell 3   if  cell    null        cell setCellType Cell CELL TYPE STRING          System out print cell getStringCellValue       t t        if  cell getStringCellValue                  depend p    Integer parseInt cell getStringCellValue

User · Answer

First to avoid NullPointerException you have to add this

Row.MissingCellPolicy.CREATE_NULL_AS_BLANK

This will create a blank cell instead of giving you NPE then you can check to make sure nothing went wrong just like what @Gagravarr have said.

Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) 
        // do what you want

User · Answer

Try below code  String empty    quot - quot   if  row getCell 3     null    row getCell 3  getCellType      Cell CELL TYPE BLANK        upld setValue empty     else       upld setValue row getCell 3  getStringCellValue

User · Answer

There is one other option also    row  Row  sheet getRow i           if  row    null    isEmptyRow row                 return            Iterator lt Cell gt  cells   row cellIterator        while  cells hasNext

User · Answer

Gagravarr s answer is quite good     Check if an excel cell is empty  But if you assume that a cell is also empty if it contains an empty String       you need some additional code  This can happen  if a cell was edited and then not cleared properly  for how to clear a cell properly  see further below    I wrote myself a helper to check if an XSSFCell is empty  including an empty String            Checks if the value of a given   link XSSFCell  is empty          param cell               The   link XSSFCell       return   code true  if the   link XSSFCell  is empty    code false             otherwise      public static boolean isCellEmpty final XSSFCell cell        if  cell    null       use row getCell x  Row CREATE NULL AS BLANK  to avoid null cells         return true             if  cell getCellType      Cell CELL TYPE BLANK            return true             if  cell getCellType      Cell CELL TYPE STRING  amp  amp  cell getStringCellValue   trim   isEmpty              return true             return false      Pay attention for newer POI Version  They first changed getCellType   to getCellTypeEnum   as of Version 3 15 Beta 3 and then moved back to getCellType   as of Version 4 0    Version  gt   3 15 Beta 3    Use CellType BLANK and CellType STRING instead of Cell CELL TYPE BLANK and Cell CELL TYPE STRING  Version  gt   3 15 Beta 3  amp  amp   Version  lt  4 0   Use Cell getCellTypeEnum   instead of Cell getCellType      But better double check yourself  because they planned to change it back in future releases     Example  This JUnit test shows the case in which the additional empty check is needed    Scenario  the content of a cell is changed within a Java program  Later on  in the same Java program  the cell is checked for emptiness  The test will fail if the isCellEmpty XSSFCell cell  function doesn t check for empty Strings    Test public void testIsCellEmpty CellHasEmptyString ReturnTrue            Arrange     XSSFCell cell   new XSSFWorkbook   createSheet   createRow 0  createCell 0        boolean expectedValue   true      boolean actualValue          Act     cell setCellValue  foo        cell setCellValue  bar        cell setCellValue           actualValue   isCellEmpty cell           Assert     Assert assertEquals expectedValue  actualValue         In addition  Clear a cell properly  Just in case if someone wants to know  how to clear the content of a cell properly  There are two ways to archive that  I would recommend way 1       way 1 public static void clearCell final XSSFCell cell        cell setCellType Cell CELL TYPE BLANK         way 2 public static void clearCell final XSSFCell cell        String nullString   null      cell setCellValue nullString        Why way 1  Explicit is better than implicit  thanks  Python   Way 1  sets the cell type explicitly back to blank  Way 2  sets the cell type implicitly back to blank due to a side effect when setting a cell value to a null String     Useful sources   Official Documentation   CellTypes XSSFCell setCellValue String  Cell setCellType CellType         Regards winklerrr

User · Answer

This is the safest and most concise way I see as of POI 3 1 7 up to POI 4   boolean isBlankCell   CellType BLANK    cell getCellTypeEnum    boolean isEmptyStringCell   CellType STRING    cell getCellTypeEnum    amp  amp  cell getStringCellValue   trim   isEmpty      if  isBlankCell    isEmptyStringCell              As of POI 4 getCellTypeEnum   will be deprecated if favor of getCellType   but the return type should stay the same

User · Answer

If you re using Apache POI 4 x  you can do that with   Cell c   row getCell 3    if  c    null    c getCellType      CellType Blank           This cell is empty     For older Apache POI 3 x versions  which predate the move to the CellType enum  it s   Cell c   row getCell 3    if  c    null    c getCellType      Cell CELL TYPE BLANK           This cell is empty     Don t forget to check if the Row is null though - if the row has never been used with no cells ever used or styled  the row itself might be null

User · Answer

As of Apache POI 3 17 you will have to check if the cell is empty using enumerations   import org apache poi ss usermodel CellType   if cell    null    cell getCellTypeEnum      CellType BLANK

User · Answer

getCellType      Cell CELL TYPE BLANK

User · Answer

Cell cell   row getCell x  Row CREATE NULL AS BLANK     This trick helped me a lot  see if it s useful for you

User · Answer

You can also use switch case like                       String columndata2                           if  cell getColumnIndex      1      To match column index                          switch  cell getCellType                                  case Cell CELL TYPE BLANK                                  columndata2                                       break                              case Cell CELL TYPE NUMERIC                                  columndata2        cell getNumericCellValue                                    break                              case Cell CELL TYPE STRING                                  columndata2   cell getStringCellValue                                    break                                                                       System out println  Cell Value    columndata2

User · Answer

Cell getCellType   is deprecated in the latest POI API  If you are using POI API version 3 17  use the below code   if  Cell getCellTypeEnum      CellType BLANK          do your stuff here

[java] How to check if an excel cell is empty using Apache POI?

Examples related to java

Examples related to apache-poi