read excel file using Apache POI

问题:

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

}

回答1:

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.

回答2:

Check this library that I've created for reading both XLSX, XLS and CSV files easily using lambdas with Java8 which uses Apache POI inside.

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
...

回答3:

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.