Reading cell values from excel file using Apache POI

Question

I'm using Selenium 2 and have set up a LoginTest.java file that reads from a data driven excel file. I read in username and password and run it on a login page. It actually runs fine. The data looks like the following:

user1 pw1
user2 pw2
user3 pw3
user4 pw4

The problem is this: if I modify the excel file and change the number of arguments to something like this:

user1 pw1
user2 pw2

and run the same script, the ReadExcel class file returns an error msg:

"There is no support for this type of cell".

What is happening I think, is that rows 3 and 4 used to contain data in their cells (user3 pw3 and user4 pw4) and now they don't...so there is something different about those previously used cells that my ExcelRead() class isn't catching to ignore. I'll bet the cell is now 'null' where it previously wasn't or vice versa. Something is different about the cells which previously held data vs cells that have never held data.

(I found ExcelRead on the internet and am using it. I didn't create it from scratch myself. It seems to work fine reading xcel files except for this one 'issue').

Thanks for any help.

public class ExcelRead {
    public Object[][] main( String[] args) throws Exception{        
    File excel = new File(args[0]);
    FileInputStream fis = new FileInputStream(excel);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet(args[1]);

    int rowNum = ws.getLastRowNum() + 1;
    int colNum = ws.getRow(0).getLastCellNum();
    String[][] data = new String[rowNum][colNum];

    for (int i = 0 ; i < rowNum ; i++) {
        HSSFRow row = ws.getRow(i);
            for (int j = 0 ; j < colNum ; j++) {
                HSSFCell cell = row.getCell(j);
                String value = cellToString(cell);
                data[i][j] = value ;
                //System.out.println("the value is " + value);
            }
        }
    return data;
    }

public static String cellToString(HSSFCell cell) {  
    int type;
    Object result;
    type = cell.getCellType();

    switch (type) {

        case 0: // numeric value in Excel
            result = cell.getNumericCellValue();
            break;
        case 1: // String Value in Excel 
            result = cell.getStringCellValue();
            break;
        default:  
            throw new RuntimeException("There is no support for this type of cell");                        
    }

    return result.toString();
}

Answer 1

Read Data from Excel using Java you need below jars. Download it from this link

poi-4.1.0.jar 
poi-ooxml-4.1.0.jar
xmlbeans-3.1.0.jar 
ooxml-schemas-1.4.jar 
commons-compress-1.18.jar
xlsx-streamer-2.1.0.jar

To Read Excel file place it in downloads or user desired location. Below program Uses following Excel file.

Excel file may contains different types of CellTypes. While you read the columns data you need to mention mostly available CellTypes which is supported by Apache POI.

I refer following cases BOOLEAN, STRING, NUMERIC, FORMULA, BLANK, _NONE, ERROR, and if none of the case supports it goes to default in getCellValueAsString method.

package com.java.file.excel;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.monitorjbl.xlsx.StreamingReader;

/**
 * 
 * @author udaykiran p
 *
 */
public class ReadExcelFile {

    public static void main(String[] args) {
        try {
            FileInputStream inputStream = new FileInputStream("C:\\Users\\udaykiranp\\Downloads\\Users.xlsx");// Read Excel file from this location
            if (inputStream != null) {
                Workbook wb = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (default to 10)
                        .bufferSize(4096) // buffer size is to use when reading InputStream to file (defaults to 1024)
                        .open(inputStream);
                Sheet sheet = wb.getSheetAt(0);//reading first sheet. You can pass argument as well.
                System.out.println("Excel Reading - Number Of Sheets: "+ wb.getNumberOfSheets() +", Active Sheet: "+ sheet.getSheetName());
                Map<String, String> map = new HashMap<String, String>();
                Iterator<Row> iterator = sheet.iterator();
                int rowCount = 0;
                while(iterator.hasNext()) {
                    Row row = iterator.next();
                    rowCount = row.getRowNum();
                    rowCount++;
                    int columnNum = 0;
                    String key = null, value = null;
                    for(Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                        Cell cell = cellIterator.next();
                        columnNum = cell.getColumnIndex();
                        String cellData = getCellValueAsString(cell);
                        System.out.println("RowNumber: "+ rowCount +", CellData: "+ cellData +", CellNumber: "+ columnNum);
                        //Reading data from Excel upto 6 rows only
//                      if (rowCount == 6) {
                            //rowCount == 1 Headers Section(User ID, User Name)
                            if (rowCount > 1) {
                                if (columnNum == 0) {
                                    key = cellData;//User ID
                                }
                                if (columnNum == 1) {
                                    value = cellData;//User Name
                                }
                            }
//                      }
                    }
                    if (key != null && value != null) {
                        map.put(key, value);
                    }
                }
                String userID = "1";
                System.out.println("User ID: "+ userID +", User Name: "+ map.get("1"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        String cellValue = null;
        switch(cell.getCellType()) {
        case BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case STRING:
            cellValue = String.valueOf(cell.getRichStringCellValue().toString());
            break;
        case NUMERIC:
            Double value = cell.getNumericCellValue();
            if (value != null) {
                String valueAsStr = value.toString();
                int indexOf = valueAsStr.indexOf(".");
                if (indexOf > 0) {
                    cellValue = valueAsStr.substring(0, indexOf);//decimal numbers truncated
                } else {
                    cellValue = value.toString();
                }
            }
            break;
        case FORMULA:
            //if the cell contains formula, this case will be executed.
            cellValue = cell.getStringCellValue();
            break;
        case BLANK:
            cellValue = "";
            break;
        case _NONE:
            cellValue = "";
            break;
        case ERROR:
            throw new RuntimeException("There is no support for this type of cell");
        default:
            cellValue = "";
        }
        return cellValue;
    }
}

OutPut:

DEBUG [main] (StreamingWorkbookReader.java:89) - Created temp file 

[C:\Users\UDAY~1\AppData\Local\Temp\tmp-6803178981463652112.xlsx]
Excel Reading - Number Of Sheets: 1, Active Sheet: Sheet1
RowNumber: 1, CellData: User ID, CellNumber: 0
RowNumber: 1, CellData: User Name, CellNumber: 1
RowNumber: 2, CellData: 1, CellNumber: 0
RowNumber: 2, CellData: Steve Jobs, CellNumber: 1
RowNumber: 3, CellData: 2, CellNumber: 0
RowNumber: 3, CellData: Bill Gates, CellNumber: 1
RowNumber: 4, CellData: 3, CellNumber: 0
RowNumber: 4, CellData: Sergey Brin, CellNumber: 1
RowNumber: 5, CellData: 4, CellNumber: 0
RowNumber: 5, CellData: Fritz Sennheiser, CellNumber: 1
RowNumber: 6, CellData: 5, CellNumber: 0
RowNumber: 6, CellData: Thomas Olsen, CellNumber: 1
User ID: 1, User Name: Steve Jobs

Answer 2

Add this.

case 3: break;

In fact you need to check for empty Cell Type (HSSFCell.CELL_TYPE_BLANK).

Also what you can do in case you find an empty cell, is stopping the for loop with i=rowNum.

There are more than two types of cell . The getCellType() method can return these integers:

Cell.CELL_TYPE_NUMERIC, Cell.CELL_TYPE_STRING, Cell.CELL_TYPE_FORMULA, Cell.CELL_TYPE_BLANK, Cell.CELL_TYPE_BOOLEAN, Cell.CELL_TYPE_ERROR

Edited:

As per the observation of Faiz, this will get an exception as Object result is not initialized and unassigned. Instead, write case 3: return ""; it will return an empty string when a blank cell is found. Anyway, your algorithm is not the most efficient. Instead of searching through a pre-defined large area, you should search the cells until you find empty cells (if there is no empty row in your sheet). You should be able to use ArrayList instead of Array, and thus completely avoiding to process empty string during the password checking process.

Answer 3

The code you have downloaded uses the Java Apache POI library to read the excel file. If you go through the code, you will see that the cellToString() method does not handle all types of cell types - it looks for only numeric and string cells, and throws the exception you see otherwise.

Once you removed the cell values from the rows, the cell values are now blank and you get a cell type of CELL_TYPE_BLANK.

You need to extend the switch statement in the cellToString() method to handle other cell types such as Cell.CELL_TYPE_BLANK, Cell.CELL_TYPE_BOOLEAN etc.

Refer to the Apache POI documentation on the Cell interface to see what the different cell types are and how to handle each.

public static String cellToString(HSSFCell cell) {  
    int type;
    Object result;
    type = cell.getCellType();

    switch (type) {

        case Cell.CELL_TYPE_NUMERIC: // numeric value in Excel
        case Cell.CELL_TYPE_FORMULA: // precomputed value based on formula
            result = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING: // String Value in Excel 
            result = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            result = "";
        case Cell.CELL_TYPE_BOOLEAN: //boolean value 
            result: cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_ERROR:
        default:  
            throw new RuntimeException("There is no support for this type of cell");                        
    }

    return result.toString();
}