read excel file using Apache POI

Question

I have created this code to read the contents of excel files using Apache POI. I am using eclipse as editor but when i ran the code i have problem in the line that I have in bold. What's the problem? The content of excel is the following:

Emp ID  Name    Salary

 1.0    john    2000000.0

 2.0    dean    4200000.0

 3.0    sam     2800000.0

 4.0    cass    600000.0

---

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;



public class ExcelRead {

public static void main(String[] args) throws Exception {
    File excel = new File ("C:\\Users\\Efi\\Documents\\test.xls");
    FileInputStream fis = new FileInputStream(excel);

    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet("Input");

    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);

            }
       }
    }

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;
    case 2: //boolean value in excel
        result = cell.getBooleanCellValue ();
        break;
    default:
        ***throw new RunTimeException("There are not support for this type of               
       cell");***
        }

return result.toString();
}

}

Answer 1

Check this library that I've created for reading both XLSX, XLS and CSV files pretty easily. It uses Apache POI for processing excel files and converts excel rows into a list of Java beans based on your configuration.

Here is an example:

RowConverter<Country> converter = (row) -> new Country(row[0], row[1]);

ExcelReader<Country> reader = ExcelReader.builder(Country.class)
     .converter(converter)
     .withHeader()
     .csvDelimiter(';')
     .sheets(1)
     .build();

List<Country> list;
list = reader.read("src/test/resources/CountryCodes.xlsx");
list = reader.read("src/test/resources/CountryCodes.xls");
list = reader.read("src/test/resources/CountryCodes.csv");

With following excel and bean files:

public static class Country {
  public String shortCode;
  public String name;

  public Country(String shortCode, String name) {
    this.shortCode = shortCode;
    this.name = name;
  }
}

Excel:

    Code    Country
ad  Andorra
ae  United Arab Emirates
af  Afghanistan
ag  Antigua and Barbuda
...

Answer 2

Using XSSFWorkbook and XSSFSheet did not help me read .xls, but I used this code and it helps me read the .xls and xlsx files:

  public static void readExcelFile(File file) throws IOException, InvalidFormatException {
Workbook workbook = WorkbookFactory.create(new File(file.toString()));
        Integer sheet = workbook.getNumberOfSheets();
        DataFormatter dataFormatter = new DataFormatter();
        for (int i = 0; i < sheet; i++) {
            Sheet s = workbook.getSheetAt(i);
            Iterator<Row> rowIterator = s.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    printCellValue(cell);
                    // both work perfect
                    // printCellValue(cell);
                    /*String cellValue = dataFormatter.formatCellValue(cell);
                    System.out.print(cellValue + "\t");*/
                }
                System.out.println();
            }

        }
    }

public static void printCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.print(cell.getDateCellValue());
                } else {
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                System.out.print(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK:
                System.out.print("  ");
                break;
            default:
                System.out.print("");
        }

        System.out.print("\t");
    }

Answer 3

There are additional cell types besides the ones you are capturing in your switch statement. You have cases for 0 (CELL_TYPE_NUMERIC), 1 (CELL_TYPE_STRING), and 2, but 2 is CELL_TYPE_FORMULA. Here are the additional possible values:

• 3: CELL_TYPE_BLANK
• 4: CELL_TYPE_BOOLEAN
• 5: CELL_TYPE_ERROR

Use the Cell constants for the cell type in your switch statement instead of integer literals, and use all 6 of them to capture all possible cases.

And as @Vash has already suggested, include the actual cell type in your RuntimeException message.

Answer 4

You should amend that RuntimeException with information about what type is not supported with your switch statement. Then you will be able to add support for it, so no exception will be thrown.

So to see the picture of what your program is doing instead of

throw new RunTimeException("There are not support for this type of cell");

you should add

throw new RunTimeException("There are not support for type with id ["+type+"] of cell");

This will only, inform you what do you miss. How to handle this situation is up to you.