Converting excel file to list of java beans

Facing problems in parsing excel and saving data ? Yes, In this tutorial i am gonna show you people how you can easily parse excel files and get data in form java beans.



To be able to do that we need to add few libraries to our project. Open your pom.xml and add the following dependencies.

<dependency>
    <groupId>com.gizbel.excel</groupId>
    <artifactId>excel-extractor</artifactId>
    <version>1.0.2</version>
</dependency>

This library provides handy methods to solve our problem. You can fetch data based on the column index or column headers. It's also gives us flexibility to get data in the desired format as well as we can also put default values in case of null or empty cells.

How to use it ( Column index based )

Annotate your java class as shown below

@ExcelBean
public class Example {

    @ExcelColumnIndex(columnIndex = "0", dataType = "double", defaultValue = "2.356")
    private int col1;

    @ExcelColumnIndex(columnIndex = "1", dataType = "double")
    private Double col2;

    @ExcelColumnIndex(columnIndex = "2", dataType = "string")
    private String col3;
}

In your main file

public static void main(String[] args) throws Exception {
        Parser parser = new Parser(Example.class, ExcelFactoryType.COLUMN_INDEX_BASED_EXTRACTION);
        parser.setSkipHeader(true); //In case if you want to skip header
        List<object> result = parser.parse(new File("test/data.xlsx")); //Whatever excel file you want
        for (Object obj : result) {
            Example ex = (Example) obj;
            //Do your stuff
        }
}

How to use it ( Column header based)

@ExcelBean
public class Example {

    @ExcelColumnHeader(columnHeader = "col1", dataType = "double", defaultValue = "2.356")
    private int col1;

    @ExcelColumnHeader(columnHeader = "col2", dataType = "double")
    private Double col2;

    @ExcelColumnHeader(columnHeader = "col3", dataType = "string")
    private String col3;
}

In your main file

public static void main(String[] args) throws Exception {
        Parser parser = new Parser(Example.class, ExcelFactoryType.COLUMN_NAME_BASED_EXTRACTION);
        List<object> result = parser.parse(new File("test/data.xlsx")); //Whatever excel file you want
        for (Object obj : result) {
            Example ex = (Example) obj;
            //Do your stuff
        }
}

That's it folks. Run your code, get the bean and do whatever you want.