Reading Excel file in java doesn't read or count blank cell as null

Question

I'm having an issue on my excel reader in java. It doesn't read empty or blank cells and skipping to the next cell that has a value.

example..

header1 | header2 | header3 | header4
row1    |         | row3    | row4
row1    | row2    |         | row4
        | row2    | row3    | 

results:

header1 | header2 | header3 | header4
row1    | row3    | row4    | 
row1    | row2    | row4    | 
row2    | row3    |         | 

expected to happen:

header1 | header2 | header3 | header4
row1    | null    | row3    | row4
row1    | row2    | null    | row4
null    | row2    | row3    | null

null or " " (empty string)

Here's my code:

my readXLSFile method:

private boolean readXLSFile(String batchRunNbr, String filename) throws IOException, ParseException {

        List sheetData = new ArrayList();
        FileInputStream fis = null;
        try {
            if ((filename.endsWith(".xlsx")) || (filename.endsWith(".XLSX"))) {
                log.info("Reading xlsx file...");
                fis = new FileInputStream(filename);
            } else{
                this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
                        "Unable to process the file. Please save the file to the latest Excel *.xlsm or *.xlsx file.";
                return true;
            }


            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            int counter = 0;
            while (rows.hasNext()) {
                counter++;
                XSSFRow row = ((XSSFRow) rows.next());
                Iterator<Cell> cells = row.cellIterator();

                List data = new ArrayList();
                while (cells.hasNext()) {
                    Cell cell = (XSSFCell) cells.next();

                    data.add(cell);
                }
                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
            this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
                    e.getMessage()+this.htmlNextLine+e.getCause();
            return true; //There is an error, return true.
        } finally {
            if (fis != null) {
                fis.close();
            }
        }

        processExcelSheet(sheetData, batchRunNbr, filename);

Here's my processExcelSheeet method:

private void processExcelSheet(List sheetData, String batchRunNbr, String inputFileName) {
        DateFormat formatter;
        formatter = new SimpleDateFormat("dd.MM.yyyy");
        boolean firstTime = true;
        try {
            for (int i = 0; i < sheetData.size(); i++) {
                List list =  (List) sheetData.get(i);
                if (firstTime) {
                    firstTime = false;
                } else {
                    for (int ii = 0; ii < list.size(); ii++) {

                        XSSFCell cell = (XSSFCell) list.get(ii);
                        switchCase(formatter, ii, cell);
                    }

                    insertToStaging(batchRunNbr, inputFileName);//This method inserts the data to the database based on what it reads above.
                }
                //log.info("COMPLETED!");
            }

        } catch (Exception e) {
            log.info("loadStagingTable Error:" + e);
            //this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
            //      "Error Loading to KLTL Data Staging. "+e.getMessage()+","+e.getCause();
            e.printStackTrace();
        }
    }

I suspect that the problem is on the row.hasNext? Please help.. Thank you so much. And if you need more details please do comment it below.

Answer 1

That's pretty cool code so far!

Just a wild guess: I bet that when you get the "sheet.rowIterator()" you lose your nulls. This would make sense b/c "Iterators" often ignore empty values.

So... if that's the case... how do you fix it?

Perhaps this will work:

after you define "sheet" try doing something like

for(int i = 0; i<sheet.getLastRowNum(); i++){
  XSSFRow row = ((XSSFRow) sheet.getRow(i));
  for(int j = 0; j<row.getLastCellNum(); j++){
    if(row!=null && row.getCell(j) != null ){
      String val = row.getCell(j).getStringCellValue();
      if(val.isEmpty()){
        System.out.println("empty" );
      }
      else{
        System.out.println(val);
      }
    }
    else{
      System.out.println("blank cell");
    }
  }
}

;) happy coding!