如何使用Apache POI将我的xlsx工作表转换为java对象

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

任何人都可以建议我使用Apache POI将我的xlsx工作表转换为java对象。

eq, my excel sheet contains two columns

eq，我的Excel工作表包含两列

• emp_no emp_name
• emp_no emp_name
• 01 anand
• 01 anand
• 02 kumar
• 02 kumar

and my java object

和我的java对象

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.

现在我想将我的Excel工作表转换为java对象。我曾尝试过互联网，但大多数教程都讨论了迭代每一行，并为每个成员分配值。是否有任何功能，如Marshaller和UnMarshaller在JAXB xml解析器中直接转换。

Thanks in advance.

提前致谢。

4 个解决方案

#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

创建一种将Employee信息分配为的方法

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
}

#2

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

使用Apache POI在内部尝试使用此库从excel转换为POJO：Poji

#3

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

我正在使用POI而且我正在上传一个简单的程序。希望这会帮助你。

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

Jars详细信息：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:

我在Excel表格中的数据：

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

Model or Pojo: NewEmployee.java

Model或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

主要方法：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();
          }
      }
}

#4

Just found two libraries:

刚刚找到两个库：

• jxls-reader (but configuration is done entirely in XML... ugh!!!) http://jxls.sourceforge.net/reference/reader.html
• jxls-reader（但配置完全用XML完成......呃!!!）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 )
• excel-object-mapping这个只是注释，但不幸的是它的创建者并不真正支持，例如仍然在“1.0-SNAPSHORT”或看到唯一的拉取请求https://github.com/jittagornp/excel-object-mapping（编辑：repo已被删除，从那时起，我在这里找到了一个克隆：https：// github。 com / pramoth / excel-object-mapping）

Hoping it'll help someone.

希望它能帮到某个人。