CODEWITHSUNDEEP
javaexcelapache-poi
Joseph Tesfaye
Joseph Tesfaye

Reputation: 874

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

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?

Upvotes: 6

Views: 7024

Answers (2)

ClumsyPuffin
ClumsyPuffin

Reputation: 4059

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());
    }

Upvotes: 8

Massimo Petrus
Massimo Petrus

Reputation: 1891

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));

Upvotes: 1

Related Questions