Thursday, 30 March 2017

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.

10 comments:

  1. Thanks for the blog. How can we apply it to excel with multiple sheets ?

    ReplyDelete
    Replies
    1. You cannot cast them into different beans based on different sheets.
      However you can do cast them into same bean but then there is no point in keeping multiple sheets.

      Delete
  2. java.lang.ClassNotFoundException: com.gizbel.excel.excel-extractor Getting this error

    ReplyDelete
    Replies
    1. Are you using maven build? can you show us your POM?

      Delete
  3. What if my Example Bean has a bean inside of it? how can I set the value of the "AnotherBean" class inside the "Example" ?
    @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;

    private AnotherBean anotherBean;
    }

    public class AnotherBean {
    @ExcelColumnHeader(columnHeader = "col4", dataType = "string")
    private String col4;
    }

    ReplyDelete
    Replies
    1. I think that will be a good feature to add, currently you have to cast that bean separately from the same file.

      Delete
  4. Hi, Kumar, thank you for your contribution, I have two suggestions in parser.java:
    1. change SimpleDateFormat's arg from dd-MM-yyyy to dd-MM-yyyy HH:mm:ss in order to support more wide range.
    2. add more formats to adjust to different kind of formats(eg:date string format) dateParser, eg: String[] formats = new String[] { "dd-MM-yyyy HH:mm:ss", "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss" };

    ReplyDelete
  5. In Parser.java, in parse(File file) method, you havent close Workbook resource, dont you think so you should close it ?

    ReplyDelete