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:
| FirstName | LastName | Age |
| Joe | Bloggs | 25 |
| John | Doe | 30 |
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
