Better way to read data from Excel file

Question

I have to read data from an Excel file which has about 40 columns and I am reading it one by one using column index. i.e.:

Cell cell = row.getCell(0);
if (!(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // set in setter
        }

But this approach is tightly coupled with structure of Excel file because if any new column is added in between then major code would be required (index values of columns).

Kindly suggest me any efficient way to read data from Excel file which should be loosely coupled with structure of the Excel OR if there is any other way in which I can provide binding of columns with fields of Java object.

Answer 1

Created the utility that will read each row from Excel and create a custom java object for each row. Make sure you read the limitation at the bottom before use.

ExcelUtils.java:

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Constructor;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

    public static <T>  List<T> read(String filePath,Class<T> objClass, Map<String,String> headersToPropertyMap){
         try {
                FileInputStream file = new FileInputStream(new File(filePath));

                //Create Workbook instance holding reference to .xlsx file
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                XSSFSheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.iterator();
                List<T> retList = new LinkedList<T>();
                Constructor<T> constructor =objClass.getConstructor();
                Map<Integer,String> columnIndexToProperty = null;
                if(rowIterator.hasNext()){
                    Row row = rowIterator.next();
                    columnIndexToProperty = getCorrespondingColumnIndex(headersToPropertyMap,row);
                }

                while (rowIterator.hasNext())
                {
                    T obj = constructor.newInstance();
                    Row row = rowIterator.next();
                    setObjectFromRow(obj,row,columnIndexToProperty);
                    retList.add(obj);
                }
                file.close();
                return retList;
            } catch (Exception e) {
                e.printStackTrace();
            }
        return new LinkedList<T>();
    }
    private static <T> void setObjectFromRow(T obj, Row row, Map<Integer,String> columnIndexToProperty){
        int numColumns = row.getPhysicalNumberOfCells();
        for(int i=0;i<numColumns;i++){
            Object value = getCellValue(row.getCell(i));
            ReflectUtils.set(obj, columnIndexToProperty.get(i), value);
        }
    }
    private static Map<Integer,String> getCorrespondingColumnIndex(Map<String,String> headersToPropertyMap,Row row){
        int numColumns = row.getPhysicalNumberOfCells();
        Map<Integer,String> columnIndexToProperty = new HashMap<Integer,String>();
        for(int i=0;i<numColumns;i++){
            Cell cell =row.getCell(i);
            String header = cell.getStringCellValue();
            String property = headersToPropertyMap.get(header);
            if(property==null)
                System.out.println("Warning: not able to find property with header: "+header);
            columnIndexToProperty.put(i, property);
        }
        return columnIndexToProperty;
    }

    private static Object getCellValue(Cell cell ){
        switch (cell.getCellType()) 
        {
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
        }
        return null;
    }
}

ReflectUtils.java:

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;

import com.google.common.base.Optional;

public class ReflectUtils {

    public static boolean set(Object object, String fieldName, Object fieldValue) {
        if(fieldName==null)
            return false;
        Class<?> clazz = object.getClass();
        while (clazz != null) {
            try {
                Field field = clazz.getDeclaredField(fieldName);
                field.setAccessible(true);
                Type pt=null;
                try{
                    pt = field.getGenericType();
                }catch(Exception e){
                    e.printStackTrace();
                }
                if(pt!=null && pt.getTypeName().equals("com.google.common.base.Optional<java.lang.String>"))
                    field.set(object, Optional.fromNullable(fieldValue));
                else if(pt!=null && pt.getTypeName().equals("java.lang.String"))
                    if(fieldValue instanceof Double)
                        field.set(object, String.valueOf(((Double)fieldValue).intValue()));
                    else
                        field.set(object, String.valueOf(fieldValue));
                else if(pt!=null && (pt.getTypeName().equals("java.lang.Integer") || pt.getTypeName().equals("int")))
                    if(fieldValue instanceof Double)
                        field.set(object, ((Double) fieldValue).intValue());
                    else
                        field.set(object, Integer.parseInt(String.valueOf(fieldValue)));
                else
                    field.set(object, fieldValue);
                return true;
            } catch (NoSuchFieldException e) {
                clazz = clazz.getSuperclass();
            } catch (Exception e) {
                throw new IllegalStateException(e);
            }
        }
        return false;
    }
}

Usage:

        Map<String,String> headersToPropertyMap = new HashMap<String,String>();
        //The header column name in excel-First, the property you wish to assign the value-firstName
        headersToPropertyMap.put("First", "firstName");
        headersToPropertyMap.put("Last", "lastName");
        headersToPropertyMap.put("Email", "email");
        headersToPropertyMap.put("orgNodeId", "companyname");
        headersToPropertyMap.put("Company Name", "companynameString");
        headersToPropertyMap.put("EULA", "eula");
        headersToPropertyMap.put("Email Notification", "emailNotification");
        return ExcelUtils.read("path to excel file",CUSTOM.class,headersToPropertyMap);

Limitations:

• Only supports String, Integer and Boolean for Java properties.
• Only supports String, Double and boolean from excel.
• Have to modify ReflectUtils to add your own custom object. For example Optional object I added into ReflectUtils.

Answer 2

There are two options:

• First/Last
• Iterator

First/Last

POI provides Sheet.getFirstRowNum()/getLastRowNum() to be able to step from the first row up to the last one and Row.getFirstCellNum()/getLastCellNum() for the Cells.

Note that you still may encounter null-rows/cells if some are not populated.

Iterator

Both Sheet and Row implement the Iterable interface, so you can do something like

for(Row row : sheet) {
     for(Cell cell : row) {
          ...

Which allows to walk all available rows/cells without encountering any null-items.

Answer 3

I would suggest adding a header row with column info (ie names) and process columns (ie map them to java objects) accordingly. Probably you could even use reflection API to deserialize objects. Something like that is used to save java objects to databases, I'm not good here but you can google and check.

That header row can be hidden in XL.

Or you can put mapping info in your java code (to not modify original XL files) - just define a data structure for that instead of hardcoding constants like in row.getCell(0) - it should be changed to interpret your meta-data about columns in XL file.

In other words, you will have data definitions per each XL file you're processing, and an universal code processing XL files per that definitions. You should have a routine taking XL file name and definition file as parameters.