Java Programming

To the experienced:

I have never dealt with Microsoft Excel files using java, and have just started using the POI library to read data from Excel files. In the .xls file I receive, there are cells that are found to be left blank though they are not supposed to. So I have to catch such blank cells and process them accordingly.

To find out how to catch such blank cells, I created a one-row Excel sheet for testing and wrote the following class to try to catch blank cells.

The one row in the sheet has 5 columns as shown next. I entered <tt>'0934</tt> into column D with a leading apostrophe to make sure it is stored as text.

A     B        C      D     E
----  -------  -----  ----  ---------
John  (BLANK)  123.4  0923  4/21/2011

And here is the test class to examine these cells. Pardon me that it is quick and dirty for testing and I do not use try/catch and I simply let the date column be printed out as a number:

public class ExcelTest {
    public static void main(String[] args) throws IOException {
        // The one-row test file:
        String inputFilename = "c:\\test_data\\test_sheet.xls";
        FileInputStream fis = new FileInputStream(inputFilename);

        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet worksheet = workbook.getSheetAt(0);

        // Get the one row
        HSSFRow theRow = worksheet.getRow(0);
        // and create an iterator
        Iterator cells = theRow.cellIterator();

        int columnNo = 0;
        
        // Go through the cells in the row:
        while (cells.hasNext()) {
            // Get the next cell:
            HSSFCell cell = (HSSFCell)cells.next();
            
            int cellType = cell.getCellType();
            String cellTypeDesc = null;
            String cellValue = null;
            
            switch (cellType) {
            case 0:
                cellTypeDesc = "NUMERIC";
                Double doubleValue = cell.getNumericCellValue();
                cellValue = doubleValue.toString();
                break;
            case 1:
                cellTypeDesc = "STRING";
                cellValue = cell.getStringCellValue();
                break;
            case 2:
                cellTypeDesc = "FORMULA";
                cellValue = cell.getCellFormula();
                break;
            case 3:
                cellTypeDesc = "BLANK";
                cellValue = "BLANK";
                break;
            case 4:
                cellTypeDesc = "BOOLEAN";
                boolean booleanValue = cell.getBooleanCellValue();
                cellValue = ""+booleanValue;
                break;
            case 5:
                cellTypeDesc = "ERROR";
                byte byteValue = cell.getErrorCellValue();
                cellValue = ""+byteValue;
                break;
            }
            System.out.println("Column " + ++columnNo + " has value " + cellValue + " of type " + cellTypeDesc);
        }
    }
}

I expected that the class would give me some clue about the blank cell. But it turned out that the program returned only 4 columns, and the blank cell is skipped altogether as of it did not exist at all. This is what the program returned:

Column 1 has value John of type STRING
Column 2 has value 123.4 of type NUMERIC
Column 3 has value 0923 of type STRING
Column 4 has value 40654.0 of type NUMERIC

Is there a way to catch blank cells?

Thanks a lot for helping!


Newman