如何使用 Apache POI 将我的 xlsx 表转换为 java 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22015660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert my xlsx sheet to java object using Apache POI
提问by Anand
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
- 01 anand
- 02 kumar
- emp_no emp_name
- 01 阿南德
- 02 库马尔
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 对象。我曾在互联网上尝试过，但大多数教程都讨论了迭代每一行并为对象中的每个成员分配值。JAXB xml 解析器中是否有任何像 Marshaller 和 UnMarshaller 这样的功能可以直接转换。
Thanks in advance.
提前致谢。
回答by Sankumarsingh
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
}
回答by maxxyme
Just found two libraries:
刚刚找到两个库：
jxls-reader(but configuration is done entirely in XML... ugh!!!) http://jxls.sourceforge.net/reference/reader.htmlexcel-object-mappingthis 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)
jxls-reader（但配置完全在 XML 中完成......呃！！！）http://jxls.sourceforge.net/reference/reader.htmlexcel-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.
希望它会帮助某人。
回答by Balaban Mario
Try this library internally using Apache POI for converting from excel to POJO.: Poji
在内部使用 Apache POI 尝试将这个库从 excel 转换为 POJO。：Poji
回答by Yanish Pradhananga
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
jar 详细信息：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
模型或 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();
}
}
}
回答by Shessuky
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)
我有同样的问题，我知道通过标准（Apache POI）实现为什么要花费这么多时间，所以在搜索和环顾四周后，我找到了一个更好的原因（JXLS-Reader）
first of all use/import/include the library jxls-reader
首先使用/导入/包含库 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 文件，用于列和您的对象属性之间的对应关系，该 XML 将一个初始化列表作为参数，通过从 Excel 文件中提取的数据（员工对象）填充它，在您的示例中，它将看起来像 ：
<?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:
然后在Java中，初始化Employees的列表（解析结果将被包含在其中），然后通过输入的Excel文件和XML映射调用JXLS阅读器，它看起来像：
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 !
希望这可以帮助任何遇到此类问题的人！
回答by Milli
Check the below repo. It was developed by keeping "ease of use" in head. https://github.com/millij/poi-object-mapper
检查下面的回购。它是通过保持头脑中的“易用性”而开发的。 https://github.com/millij/poi-object-mapper
Initial version has been published to Maven Central.
初始版本已发布到Maven Central。
<dependency>
<groupId>io.github.millij</groupId>
<artifactId>poi-object-mapper</artifactId>
<version>1.0.0</version>
</dependency>
Works similar to Hymanson. Annotate your bean like below..
工作类似于Hyman逊。像下面这样注释你的bean..
@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, Formulaetc..
就目前而言，支持所有原始数据类型。仍在加入新的支持Date，Formula等等。
Hope this helps.
希望这可以帮助。
回答by zpavel
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 :
我想找到一种将 xls/xlsx 文件解析为 pojo 列表的简单方法。经过一番搜索，我没有找到任何方便的东西，而是更喜欢快速开发它。现在我可以通过简单地调用来获得 pojos：
InputStream is = this.getClass().getResourceAsStream("/ExcelUtilsTest.xlsx");
List<Pojo> pojos = ExcelToPojoUtils.toPojo(Pojo.class, is);
If interested take a look on it :
如果有兴趣看看它：
回答by Mohammad Awwaad
Set a look on that examplethat uses Apache POI to bind xlsx sheet to list of objects.
查看该示例，该示例使用 Apache POI 将 xlsx 表绑定到对象列表。
It is a very simple example to show how to convert Microsoft Excel (xlsx) sheet to list of objects using Apache POI.
这是一个非常简单的示例，用于展示如何使用 Apache POI 将 Microsoft Excel (xlsx) 工作表转换为对象列表。
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.
这个想法只是在要将工作表列映射到的字段上定义注释@ExcelCellInfo。然后将根据注释属性使用反射绑定工作表单元格。
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
}
