Core Java

Parsing an Excel File into JavaBeans using jXLS

This post shows how you can use jXLS to parse an Excel file into a list of JavaBeans.

Here is a generic utility method I wrote to do that:
 
 
 
 
 
 
 
 

/**
* Parses an excel file into a list of beans.
*
* @param <T> the type of the bean
* @param xlsFile the excel data file to parse
* @param jxlsConfigFile the jxls config file describing how to map rows to beans
* @return the list of beans or an empty list there are none
* @throws Exception if there is a problem parsing the file
*/
public static <T> List<T> parseExcelFileToBeans(final File xlsFile,
                                                final File jxlsConfigFile)
                                                throws Exception {
  final XLSReader xlsReader = ReaderBuilder.buildFromXML(jxlsConfigFile);
  final List<T> result = new ArrayList<>();
  final Map<String, Object> beans = new HashMap<>();
  beans.put("result", result);
  try (InputStream inputStream = new BufferedInputStream(new FileInputStream(xlsFile))) {
    xlsReader.read(inputStream, beans);
  }
  return result;
}

Example:

Consider the following Excel file containing person information:

FirstNameLastNameAge
JoeBloggs25
JohnDoe30

 
Create the following Person bean to bind each Excel row to:

package model;

public class Person {

  private String firstName;
  private String lastName;
  private int age;

  public Person() {
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public int getAge() {
    return age;
  }
  public void setAge(int age) {
    this.age = age;
  }
}

Create a jXLS configuration file which tells jXLS how to process your Excel file and map rows to Person objects:

<workbook>
  <worksheet name="Sheet1">
    <section startRow="0" endRow="0" />
    <loop startRow="1" endRow="1" items="result" var="person" varType="model.Person">
      <section startRow="1" endRow="1">
        <mapping row="1" col="0">person.firstName</mapping>
        <mapping row="1" col="1">person.lastName</mapping>
        <mapping row="1" col="2">person.age</mapping>
      </section>
      <loopbreakcondition>
        <rowcheck offset="0">
          <cellcheck offset="0" />
        </rowcheck>
      </loopbreakcondition>
    </loop>
  </worksheet>
</workbook>

Now you can parse the Excel file into a list of Person objects with this one-liner:

List<Person> persons = Utils.parseExcelFileToBeans(new File("/path/to/personData.xls"),
                                                   new File("/path/to/personConfig.xml"));

Related posts:

Parsing a CSV file into JavaBeans using OpenCSV

Reference: Parsing an Excel File into JavaBeans using jXLS from our JCG partner Fahd Shariff at the fahd.blog blog.

Fahd Shariff

Fahd is a software engineer working in the financial services industry. He is passionate about technology and specializes in Java application development in distributed environments.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
A
A
8 years ago

this approach gives OOM for large excel files

Back to top button