有人能否建议我如何使用Apache POI将我的xlsx表格转换为Java对象。
我的Excel表格包含两列：
而我的Java对象：
Employee{
String empNo;
String empName;
}
现在我想将我的Excel表格转换为Java对象。我在互联网上尝试过，但大多数教程都是讲解如何迭代每一行并将值分配给对象中的每个成员。是否有像JAXB XML解析器中的Marshaller和UnMarshaller这样的功能可以直接进行转换。
提前感谢。
对于给定的场景，我假设表格的每一行代表一个雇员，第一列是雇员编号，第二列是雇员姓名。因此您可以使用以下内容：
Employee{
String empNo;
String empName;
}
assignEmployee(Row row){
empNo = row.getCell(0).toString();
empName = row.getCell(1).toString();
}
或者，如果您希望，可以为其创建一个构造函数。
现在，您只需要使用上述方法迭代每一行以获取/使用信息即可。
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
}
<dependency>
<groupId>io.github.millij</groupId>
<artifactId>poi-object-mapper</artifactId>
<version>1.0.0</version>
</dependency>
工作方式类似于Jackson。将您的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;
}
...
}
并且要阅读...
...
final File xlsxFile = new File("<path_to_file>");
final XlsReader reader = new XlsReader();
List<Employee> employees = reader.read(Employee.class, xlsxFile);
...
我正在使用POI，并上传一个简单的程序。希望这可以帮到你。
注意：记得更改文件路径。
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
我的Excel表中的数据：
ID NAME LASTNAME
1.0 Ena Rana
2.0 Meena Hanly
3.0 Tina Mounce
4.0 Dina Cobain
模型或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();
}
}
}
刚发现了两个库：
jxls-reader（但是配置完全在XML中完成... 呃!!!）http://jxls.sourceforge.net/reference/reader.htmlexcel-object-mapping 这个只有注释，但不幸的是其创建者并没有真正支持它，例如仍处于“1.0-SNAPSHOT”状态或查看唯一的拉取请求https://github.com/jittagornp/excel-object-mapping（编辑：此后存储库已被删除，我在这里找到了一个克隆版本：https://github.com/pramoth/excel-object-mapping）希望对某些人有所帮助。
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.3</version>
</dependency>
然后创建一个XML文件，用于库与列和对象属性之间的对应关系，此XML以初始化列表作为参数，通过从Excel文件中提取的数据（Employee对象）填充它，在您的示例中，它将如下所示：
<?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>
然后在Java中，初始化员工列表（将解析结果包括在内），然后通过输入的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);
}
}
}
看一下这个示例，它使用Apache POI将xlsx表格绑定到对象列表。
这是一个非常简单的示例，展示了如何使用Apache POI将Microsoft Excel（xlsx）表格转换为对象列表。
想法很简单，只需在要映射工作表列的字段上定义注释@ExcelCellInfo。然后，根据注释属性，将使用反射绑定工作表单元格。
用法示例：
ExcelSheetDescriptor<RowClassSample> sheetDescriptor = new ExcelSheetDescriptor<>(RowClassSample.class).setHasHeader();
List<RowClassSample> rows = ExcelUtils.readFirstSheet("pathToFile.xlsx", sheetDescriptor);
绑定到的类：
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
}
我想找到一种简单的方法将xls/xlsx文件解析为pojo列表。经过一番搜索，我没有找到任何方便的方法，所以决定快速开发它。现在，我只需简单地调用以下代码即可获取pojos：
InputStream is = this.getClass().getResourceAsStream("/ExcelUtilsTest.xlsx");
List<Pojo> pojos = ExcelToPojoUtils.toPojo(Pojo.class, is);
如果您感兴趣，请看一下：