About 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.

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.
Related Whitepaper:

Bulletproof Java Code: A Practical Strategy for Developing Functional, Reliable, and Secure Java Code

Use Java? If you do, you know that Java software can be used to drive application logic of Web services or Web applications. Perhaps you use it for desktop applications? Or, embedded devices? Whatever your use of Java code, functional errors are the enemy!

To combat this enemy, your team might already perform functional testing. Even so, you're taking significant risks if you have not yet implemented a comprehensive team-wide quality management strategy. Such a strategy alleviates reliability, security, and performance problems to ensure that your code is free of functionality errors.Read this article to learn about this simple four-step strategy that is proven to make Java code more reliable, more secure, and easier to maintain.

Get it Now!  

Leave a Reply


6 − one =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books