How to convert my xlsx sheet to java object using Apache POI

Question

can any one suggest me to convert my xlsx sheet to java object using Apache POI.

eq, my excel sheet contains two columns

• emp_no emp_name
• 01 anand
• 02 kumar

and my java object

Employee{
String empNo;
String empName; 
}

Now I want to convert my excel sheet to java object. I have tried in internet but most of the tutorials talks about iterate each row and assign values to each member sin the object. Is there any functionality like Marshaller and UnMarshaller in JAXB xml parser which convert directly.

Thanks in advance.

Answer 1

For the given Scenario, I am assuming that each row of the sheet is representing an employee of which say first Column is keeping employee Number and second column is keeping Employee Name. so you can use the following:

Employee{
  String empNo;
  String empName; 
}

Create a method of assigning the Employee information as

assignEmployee(Row row){
    empNo = row.getCell(0).toString();
    empName = row.getCell(1).toString();
}

or if you want you can create a constructor for the same.

Now you just need to iterate over each row to get/use the information using the above method.

Employee emp = new Employee();
Iterator<Row> itr = sheet.iterator();
    while(itr.hasNext()){
       Row row = itr.next();
       emp.assignEmployee(row);
      //  enter code here for the rest operation
}

Answer 2

Try this library internally using Apache POI for converting from excel to POJO.: Poji

Answer 3

Check the below repo. It was developed by keeping "ease of use" in head. https://github.com/millij/poi-object-mapper

Initial version has been published to Maven Central.

<dependency>
    <groupId>io.github.millij</groupId>
    <artifactId>poi-object-mapper</artifactId>
    <version>1.0.0</version>
</dependency>

Works similar to Jackson. Annotate your bean like below..

@Sheet
public class Employee {
    // Pick either field or its accessor methods to apply the Column mapping.
    ...
    @SheetColumn("Age")
    private Integer age;
    ...
    @SheetColumn("Name")
    public String getName() {
        return name;
    }
    ...
}

And to read..

...
final File xlsxFile = new File("<path_to_file>");
final XlsReader reader = new XlsReader();
List<Employee> employees = reader.read(Employee.class, xlsxFile);
...

As it stands, all primitive data types are supported. Still working on adding support for Date, Formula etc..

Hope this helps.

Answer 4

I'm using POI and I'm uploading a simple program. Hope this will help you.

Note: Remember to change filepath.

Jars details: dom4j-1.6.1.jar, poi-3.9.jar,poi-ooxml-3.9.jar, poi-ooxml-schemas-3.11.jar, xmlbeans-2.6.0.jar

My Data in Excel Table:

ID   NAME  LASTNAME 
1.0  Ena   Rana 
2.0  Meena Hanly 
3.0  Tina  Mounce 
4.0  Dina  Cobain 

Model or Pojo: NewEmployee.java

public class NewEmployee {
     private Double id;
     private String firstName;
     private String lastName;

     public NewEmployee(){}

    public NewEmployee(Double id, String firstName, String lastName) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public Double getId() {
        return id;
    }

    public void setId(Double id) {
        this.id = id;
    }

    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;
    }    
}

Main Method: ExcelToObject.java

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelToObject {

    public static void main(String[] args) {
         try
          {
              FileInputStream file = new FileInputStream(new File("/home/ohelig/eclipse/New Worksheet.xlsx"));

              //Create Workbook instance holding reference to .xlsx file
              XSSFWorkbook workbook = new XSSFWorkbook(file);

              //Get first/desired sheet from the workbook
              XSSFSheet sheet = workbook.getSheetAt(0);

              ArrayList<NewEmployee> employeeList = new ArrayList<>();
    //I've Header and I'm ignoring header for that I've +1 in loop
              for(int i=sheet.getFirstRowNum()+1;i<=sheet.getLastRowNum();i++){
                  NewEmployee e= new NewEmployee();
                  Row ro=sheet.getRow(i);
                  for(int j=ro.getFirstCellNum();j<=ro.getLastCellNum();j++){
                      Cell ce = ro.getCell(j);
                    if(j==0){  
                        //If you have Header in text It'll throw exception because it won't get NumericValue
                        e.setId(ce.getNumericCellValue());
                    }
                    if(j==1){
                        e.setFirstName(ce.getStringCellValue());
                    }
                    if(j==2){
                        e.setLastName(ce.getStringCellValue());
                    }    
                  }
                  employeeList.add(e);
              }
              for(NewEmployee emp: employeeList){
                  System.out.println("ID:"+emp.getId()+" firstName:"+emp.getFirstName());
              }
              file.close();
          } 
          catch (Exception e) 
          {
              e.printStackTrace();
          }
      }
}

Answer 5

You may also consider to use this tiny library excelorm

Answer 6

Just found two libraries:

• jxls-reader (but configuration is done entirely in XML... ugh!!!) http://jxls.sourceforge.net/reference/reader.html
• excel-object-mapping this one is annotation only but unfortunately not really supported by its creator, e.g. still in "1.0-SNAPSHORT" or see the only pull request https://github.com/jittagornp/excel-object-mapping (Edit: repo has been removed since then, I found a clone here: https://github.com/pramoth/excel-object-mapping )

Hoping it'll help someone.

Answer 7

I had the same issue, i've was aware that the implementation via the standard (Apache POI) why cost so much time so after searching and looking around, i have found a better why (JXLS-Reader)

first of all use/import/include the library jxls-reader

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls-reader</artifactId>
        <version>2.0.3</version>
    </dependency>

then create an XML file used by the library for the correspondence between the columns and the your object attributes, this XML take as parameter an initialized list to fill it by extracted data (Employee objects) from the Excel file, in your example, it will look like :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook>
    <worksheet idx="0">
        <section startRow="0" endRow="0" />
        <loop startRow="1" endRow="1" items="employeeList" var="employee" varType="com.department.Employee">
            <section startRow="1" endRow="1">
            <mapping row="1"  col="0">employee.empNo</mapping>
            <mapping row="1"  col="1">employee.empName</mapping>
            </section>
            <loopbreakcondition>
                <rowcheck offset="0">
                    <cellcheck offset="0"></cellcheck>
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>

Then in Java, initialize the list of the Employees (where the result of parsing will be included), then call the JXLS reader by the input Excel file and the XML mapping, it will look like:

package com.department;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.IOUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.ReaderConfig;
import org.jxls.reader.XLSReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.SAXException;


public class ExcelProcessor {

    private static Logger logger = LoggerFactory.getLogger(ExcelProcessor.class);

    public void parseExcelFile(File excelFile) throws Exception{
        final List<Employee> employeeList = new ArrayList<Employee>();
        InputStream xmlMapping = new BufferedInputStream(ExcelProcessor.class.getClassLoader().getResourceAsStream("proBroMapping.xml"));
        ReaderConfig.getInstance().setUseDefaultValuesForPrimitiveTypes(true);
        ReaderConfig.getInstance().setSkipErrors(true);
        InputStream inputXLS;
        try{
            XLSReader mainReader = ReaderBuilder.buildFromXML(xmlMapping);
            inputXLS = new BufferedInputStream(new FileInputStream(excelFile));
            final Map<String, Object> beans = new HashMap<String, Object>();
            beans.put("employeeList", employeeList);
            mainReader.read(inputXLS, beans);
            System.out.println("Employee data are extracted successfully from the Excel file, number of Employees is: "+employeeList.size());
        } catch(java.lang.OutOfMemoryError ex){
            // Case of a very large file that exceed the capacity of the physical memory
               ex.printStackTrace();
            throw new Exception(ex.getMessage());
        } catch (IOException ex) {
            logger.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } catch (SAXException ex) {
            logger.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } catch (InvalidFormatException ex) {
            logger.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            IOUtils.closeQuietly(inputStream);
        }

    }

}

Hope this helps anyone having such a problem !

Answer 8

I wanted to find a simple way to parse a xls/xlsx file to a list of pojo. After some searching i didn't find anything convenient and preferred to develop it quickly. Now i am able to get pojos by simply calling :

InputStream is = this.getClass().getResourceAsStream("/ExcelUtilsTest.xlsx");
List<Pojo> pojos = ExcelToPojoUtils.toPojo(Pojo.class, is);

If interested take a look on it :

https://github.com/ZPavel/excelToPojo

Answer 9

Set a look on that example that uses Apache POI to bind xlsx sheet to list of objects.

It is a very simple example to show how to convert Microsoft Excel (xlsx) sheet to list of objects using Apache POI.

The idea is simply to define annotation @ExcelCellInfo on the fields you want to map the sheet columns to. Then the sheet cells will be bound using reflection according to annotation attributes.

Usage example:

ExcelSheetDescriptor<RowClassSample> sheetDescriptor = new ExcelSheetDescriptor<>(RowClassSample.class).setHasHeader();
List<RowClassSample> rows = ExcelUtils.readFirstSheet("pathToFile.xlsx", sheetDescriptor);

And the class to bind to:

public class RowClassSample {

    @ExcelCellInfo(index = 0)
    private long serial;

    @ExcelCellInfo(index = 1)
    private String name;

    @ExcelCellInfo(index = 2, cellParser = CellNumericAsStringParser.class)
    private String registrationNumber;

    @ExcelCellInfo(index = 3, cellParser = CellPercentageParser.class)
    private Double percentage;

    @ExcelCellInfo(index = 6)
    private String reason;

    @ExcelCellInfo(index = 4)
    private String notes;

    @ExcelCellInfo(index = 5, cellParser = CellBooleanYesNoArParser.class)
    private boolean approval;

    // getters & setters
}