How can I return blank as null for all cells in a .xlsx file using Apache POI?

5
votes

After Googling and searching on StackOverflow I haven't found a place related to this question. Currently I am able to read blank cells as null, but only for one cell at a time. So I have to write something like this:

data.setAddress(row.getCell(10, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(10).getStringCellValue());
    data.setClientType(row.getCell(11, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(11).getNumericCellValue());
    data.setClientType0Charge(row.getCell(12, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(12).getNumericCellValue());
    data.setClientType1Charge(row.getCell(13, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(13).getNumericCellValue());
    data.setCooYears(row.getCell(14, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(14).getNumericCellValue());
    data.setConYears(row.getCell(15, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(15).getNumericCellValue());
    data.setProjectType(row.getCell(16, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(16).getNumericCellValue());
    data.setProjectTypeNotes(row.getCell(17, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(17).getStringCellValue());
    data.setChargeMethod(row.getCell(18, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(18).getNumericCellValue());
    data.setFixedPrice(row.getCell(19, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(19).getNumericCellValue());
    data.setHighTime(row.getCell(20, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(20).getStringCellValue());
    data.setHighPrice(row.getCell(21, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(21).getNumericCellValue());
    data.setPeakTime(row.getCell(22, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(22).getStringCellValue());
    data.setPeakPrice(row.getCell(23, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(23).getNumericCellValue());
    data.setPlainTime(row.getCell(24, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(24).getStringCellValue());
    data.setPlainPrice(row.getCell(25, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(25).getNumericCellValue());
    data.setValleyTime(row.getCell(26, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(26).getStringCellValue());
    data.setValleyPrice(row.getCell(27, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(27).getNumericCellValue());
    data.setServFee(row.getCell(28, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(28).getNumericCellValue());
    data.setServFeePercent(row.getCell(29, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(29).getNumericCellValue());
    data.setServFeeFixed(row.getCell(30, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(30).getNumericCellValue());
    data.setPropertyType(row.getCell(31, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(31).getNumericCellValue());
    data.setPropertyOwner(row.getCell(32, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(32).getStringCellValue());
    data.setPropertyManager(row.getCell(33, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(33).getStringCellValue());
    data.setAcquireMethod(row.getCell(34, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(34).getNumericCellValue());
    data.setAcquireMethodNotes(row.getCell(35, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(35).getStringCellValue());
    data.setSiteAddress(row.getCell(36, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(36).getStringCellValue());
    data.setSiteType(row.getCell(37, Row.RETURN_BLANK_AS_NULL) == null ? null : (int) row.getCell(37).getNumericCellValue());
    data.setSiteTypeNotes(row.getCell(38, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(38).getStringCellValue());

This doesn't look too good for me because of a sense of resentment for repetition. Can we just set the Row.RETURN_BLANK_AS_NULL somewhere universally for cells in the whole sheet or even the whole workbook?

2
javaexcelapache-poi

2 Answers

7
votes

you can set this in missing cell policy at workbook level :

    XSSFWorkbook book = new XSSFWorkbook("h:\\excel.xlsx");
    XSSFSheet sheet = book.getSheetAt(0);
    XSSFRow row = sheet.getRow(0);
    book.setMissingCellPolicy(Row.RETURN_BLANK_AS_NULL);

this works because the definition of the getcell with only cell number is like this(from POI source code):

 @Override
    public XSSFCell getCell(int cellnum) {
        return getCell(cellnum, _sheet.getWorkbook().getMissingCellPolicy());
    }
1
votes

I don't know about such global parameters, but maybe you can wrap your logic in an utility class. For example for getting String values you can do as below (maybe adding some controls)

public static String getStringCellValue (Row row, int cellNum){
   return row.getCell(cellNum, Row.RETURN_BLANK_AS_NULL) == null ? null : row.getCell(cellNum).getStringCellValue();

}

so your code becomes

 data.setAddress(PoiUtils.getStringCellValue (row,10));