#Frequently asked questions

What do I need to try this out

The only thing you need is an Excel file, this library and an Apache POI library (which is currently the only required dependency).

How to read String columns

Excel file:

Create processing class:

public class EmployeeParser {

    @ColumnValue(headerText = "Name")
    private String name;

    @ColumnValue(headerText = "Surname")
    private String surname;

    @ColumnValue(headerText = "Employee ID")
    private String employeeId;


    @Perform(when = AFTER, processed = ROW)
    public void printRow() {
        System.out.println("name: " + name);
        System.out.println("surname: " + surname);
        System.out.println("employeeId: " + employeeId);

        System.out.println("-------------------------");
    }
}

Execute it calling ExtractionUtil.processSheet( ... ):

    final HSSFWorkbook workbook = PoiUtil.readWorkbook(dirName + fileName);
    final HSSFSheet sheet = workbook.getSheet(sheetName);

    ExtractionUtil.processSheet(
            sheet,
            new EmployeeParser()
    );

the result will be:

name: Peter
surname: Smith
employeeId: 09-38-722
-------------------------
name: Paul
surname: Duff
employeeId: 11-94-032
-------------------------
name: Simon
surname: Peterson
employeeId: 09-38-123
-------------------------

How to read nonString columns

Excel file:

As you can see, there is no difference between String and nonString fields:

public class EmployeeParser {

    @ColumnValue(headerText = "Birth date")
    private Date birthDate;

    @ColumnValue(headerText = "Children count")
    private Integer childCount;

    @ColumnValue(headerText = "Salary")
    private BigDecimal salary;


    @Perform(when = AFTER, processed = ROW)
    public void printRow() {
        System.out.println("birthDate: " + birthDate);
        System.out.println("childCount: " + childCount);
        System.out.println("salary: " + salary);

        System.out.println("-------------------------");
    }
}

the result will be:

birthDate: Mon Mar 15 00:00:00 CET 1976
childCount: 2
salary: 2170
-------------------------
birthDate: Fri Oct 07 00:00:00 CET 1983
childCount: 1
salary: 1850
-------------------------
birthDate: Mon Jan 12 00:00:00 CET 1981
childCount: 0
salary: 1900
-------------------------

Custom conversion when internal conversion fails

Excel file:

We can define one method that will be called, when the standardly provided conversion fails (for example in this case our library can't decide what Integer value should be used instead of ??? that is located in Children count column)

the code to handle this is as follows:

public class EmployeeParser {

    @ColumnValue(headerText = "Name")
    private String name;

    @ColumnValue(headerText = "Surname")
    private String surname;

    @ColumnValue(headerText = "Children count")
    private Integer childCount;


    @ConversionIssueHandler
    public Object convertIfConversionException(Cell cell, String headerText, Class<?> expectedValueClass) {
        if ("Children count".equals(headerText) && Integer.class.equals(expectedValueClass)) {
            return 0;
        } else {
            return null;
        }
    }


    @Perform(when = AFTER, processed = ROW)
    public void printRow() {
        System.out.println("name: " + name);
        System.out.println("surname: " + surname);
        System.out.println("childCount: " + childCount);

        System.out.println("-------------------------");
    }
}

the result will be:

name: Tina
surname: Marshal
childCount: 0
-------------------------

Custom column processing

Excel file:

For more complex tasks you can define your own method for processing each required column (in this case for example we want to remove all minus signs from the Employee Id):

public class EmployeeParser {

    @ColumnValue(headerText = "Name")
    private String name;

    @ColumnValue(headerText = "Surname")
    private String surname;

    // no @ColumnValue for this field
    private String employeeId;


    @ProcessColumn(headerText = "Employee ID")
    public void setEmployeeId(Cell cell) {
        final String value = cell.getStringCellValue();

        employeeId = value.replaceAll("-", "");
    }


    @Perform(when = AFTER, processed = ROW)
    public void printRow() {
        System.out.println("name: " + name);
        System.out.println("surname: " + surname);
        System.out.println("employeeId: " + employeeId);

        System.out.println("-------------------------");
    }
}

the result will be:

name: Tina
surname: Marshal
employeeId: 0938234
-------------------------

I have a great idea how to improve this

If you have some ideas how to improve this utility or just would like to have this or that additional feature just post it into our discussion group