Reading blank cells of excel and returning List 2019-07-19 04:23发布 站内问答 / Java 1794 3 6

Question

my excel file has 4 rows and 2 columns like with continuous values having 3row 1st column as blank value like given below

phone       plan
------------------
85366667777 PLATINUM
85367890000 GOLD
85362524232 SILVER
null        SILVER
85362522436 DIAMOND

---

public List<List<String>> readExcelFile(String fileName) {

        try {
            FileInputStream fileInputStream = new FileInputStream(fileName);
            POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
            HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
            HSSFSheet hssfSheet = workBook.getSheetAt(0);
            Iterator<Row> rowIterator = hssfSheet.rowIterator();
            boolean headerRow = true;
            while (rowIterator.hasNext()) {
                if (headerRow == false) {
                String Rows = util.loadfile("config/ConfigValue.properties","NoOfRowsToSkip");
                int i = Integer.parseInt(Rows);
                    HSSFRow hssfRow = (HSSFRow) rowIterator.next();
                    if(hssfRow.getRowNum()==0||hssfRow.getRowNum()<=i){
                           continue; //just skip the rows if row number is 0 TO i
                          }
                    Iterator<Cell> iterator = hssfRow.cellIterator();
                    List cellTempList = new ArrayList();
                    while (iterator.hasNext()) {
                        HSSFCell hssfCell = (HSSFCell) iterator.next();

                    if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC)
                        {DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(hssfCell.getNumericCellValue());
                        cellTempList.add(cellValue.toString());

                        }
                         else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING)
                         {
                         cellTempList.add(hssfCell.toString());
                     }

                    }

                    cellDataList.add(cellTempList);
                } else {
                    headerRow = false;
                }
            }
        } catch (Exception e) {

            e.printStackTrace();
            logger.error("Other Exception : " + e.getStackTrace());
        }
        System.out.println(cellDataList); 
        return cellDataList;
    }
    }

output : [[85366667777, PLATINUM], [85367890000, GOLD], [85362524232, SILVER], [SILVER], [85362522436, DIAMOND]]

Why it is not printing [null,Silver] or [0,SILVER] when there is no value in its corresponding cell ?

Answer 1

You should use Missing Cell Policy. In some cases, when iterating, you need full control over how missing or blank cells are treated, and you need to ensure you visit every cell and not just those defined in the file. In cases such as you have mentioned, you should fetch the first and last column information for a row, then call getCell(int, MissingCellPolicy) to fetch the cell. Use a MissingCellPolicy to control how blank or null cells are handled.

Take an example where rows are like

Here A2 is null, insted of blank, as in your case. Here you need to assign cell value as Cell cell = row.getCell(int arg0, MissingCellPolicy arg1): Cell-Row

There are three types of MissingCellPolicy as

CREATE_NULL_AS_BLANK,CREATE_BLANK_AS_NULL and RETURN_NULL_AND_BLANK

Use it as following

for (int i = 0; i<3 ;i++){
    for(int j=0 ; j<2; j++){
        Row row = sheet1.getRow(i);
        Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
//              Cell cell = row.getCell(j, Row.RETURN_NULL_AND_BLANK);
//              if(row.getCell(j)==null){
//                  System.out.print("Null ");
//              }else
            System.out.print(cell.toString()+" - ");
    }
    System.out.println();
}

Here whenever any cell with null value is encountered, then Row.CREATE_NULL_AS_BLANK return the cell value with BLANK. instead of throwing NullPointerException. In this case the output will be

A1 - B1 - 
 - B2 - 
A3 - B3 - 

If you use Row.RETURN_NULL_AND_BLANK, then it will return Null in case of any cell is not defined. so you need to create a null check (As commented in code). in this case Output will be

 A1 - B1 - 
 Null B2 - 
 A3 - B3 - 

Answer 2

if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){
    DecimalFormat df = new DecimalFormat("#");
    String cellValue=df.format(hssfCell.getNumericCellValue());
    cellTempList.add(cellValue.toString());
} else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING){
    cellTempList.add(hssfCell.toString());
} else {
    cellTempList.add(null);  // or cellTempList.add("0");
}

Your empty cell is neither string nor numeric, so nothing is added to your array. Try checking for CELL_TYPE_BLANK.

Answer 3

You are using a Iterator to iterate over the cells. But if a cell never had a value, it likely does not exist. In this case, there is only one cell over which to iterate.

If you want to maintain an iterator, then you'll need to test the column index of the cell on which you're iterating. If there is no 0-column cell, then you can handle that case by adding in your own null or 0 first.

But it sounds easier to remove the iterator and directly access the cells with columns 0 and 1 with the getCell method that takes a MissingCellPolicy.

Cell zero = hssfRow.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
Cell one  = hssfRow.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

This will create a blank Cell if it did not exist. Then you can test if the cell has a CELL_TYPE_BLANK cell type and act accordingly.