Java Programming

To the experienced:

I am new to dealing with Microsoft Excel files using Java. I am now doing it using Apache POI library in JDeveloper 11.1.1.3, and have some problem verifying if a cell is blank or not.

The spreadsheet has many columns and I am working with and extracting only 5 of them. In the spreadsheet I received, I found blank cells in two columns by visually going through the rows in these columns. The handling of a cell will be different depending on whether the cell is blank. Therefore I have to be able to find out whether a cell is blank.

The interesting (and frustrating) thing is that blank cells in these two columns are quite different.

Here is the code I to treat the cells:

loopingRows:
while (rows.hasNext()) {
   HSSFRow row = (HSSFRow)rows.next();

   String col11Value = null;

   // These are the columns I am interested in:
   int[] targetCols = { 5, 6, 7, 11, 26 };

   for (int colNum : targetCols) {
      HSSFCell cell = row.getCell(colNum);

      // Code specific to the column:
      switch (colNum) {
      case 5:
         // ...
         break;
      case 6:
         // ...
         break;
      case 7:
         // ...
         break;
      case 11:
         if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            System.out.println("Column 11 is blank.");
            continue loopingRows;
         } else {
            col11Value = cell.getStringCellValue();
         }
         break;
      case 26:
         if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            System.out.println("Column 26 is blank.");
            continue loopingRows;
         } else {
            // ...
         }
         break;
      }
   }
   System.out.println("Column 11 value is " + col11Value);
}

Columns 11 and 26 are the columns in which I am dealing with the blank cells. But <tt><font color="green">Cell.CELL_TYPE_BLANK</font></tt> works with neither column.

With column[26], the <tt><font color="green">if (cell.getCellType() == Cell.CELL_TYPE_BLANK)</font></tt> throws a NullPointerException, i.e., a blank cell is obviously considered null. That was gotten around by using <tt><font color="green">if (cell == null)</font></tt>. That is fine, as I can put the code handling a blank cell in the if{} block, and the code handling non-blank cells in the else{} block, and I can handle both blank and non-blank cells.

With column[11], however, neither does the <tt><font color="green">if (cell.getCellType() == Cell.CELL_TYPE_BLANK)</font></tt> throw an exception, nor does the code in this if{} block get executed. If the cell is not blank, the code in the else{} code block gets executed, and the "Column 11 value is ..." message gets printed. But if the cell is blank, the if{} block is simply quietly bypassed - the "Column 26 is blank." message is not printed to the console at all. But the <tt><font color="green">continue loopingRows;</font></tt> statement seems to be executed, because the "Column 11 value is ..." message is not printed. I need to process the blank cells in column 11 but I am stuck.

Two things are puzzling to me here:

1. What does <tt><font color="green">(cell.getCellType() == Cell.CELL_TYPE_BLANK)</font></tt> return for column 11 when a cell is blank? If it returns true, why println() does not print? If it is false, why is the "continue loopingRows;" statement is executed? How is Cell.CELL_TYPE_BLANK used?

2. Back in the spreadsheet and looking at the blank cells in columns 11 and 26, I rest the cursor on the cell at the top of the column and press Ctrl-down (down arrow). With column 26, the cursor stops at before the first blank cell it finds. But when I did the same with column 11, the cursor skips all the blank cells and stops at the very bottom of the column. I had to scroll row by row to visually catch the blank cells. If you examine each of the blank cells, indeed there is nothing there. But I wonder if that could be deceptive. Why does the Ctrl-down key behave differently with the two columns?

Thank you very much for your help!


Newman