到目前为止，Apache POI还没有这种功能。您可以检查一些外部库。我在下面提供一些库。
https://github.com/ozlerhakan/poiji
该库在mvnrepository中可用，链接在下面给出。该库仅提供一种从excel表格到Java pojo的绑定方式。
https://mvnrepository.com/artifact/com.github.ozlerhakan/poiji/2.2.0
如上所述，您可以执行以下操作。
public class Employee {
@ExcelRow
private int rowIndex;
@ExcelCell(0)
private long employeeId;
@ExcelCell(1)
private String name;
@ExcelCell(2)
private String surname;
@ExcelCell(3)
private int age;
}
要将信息从excel工作表获取到java对象，您必须按照以下方式进行操作。
List<Employee> employees = Poiji.fromExcel(new File("employees.xls"),Employee.class);
还有另一个库可以同时执行excel和java以及excel等功能。
我在链接下方提供。
https://github.com/millij/poi-object-mapper
根据上述库，您可以执行以下操作。
@Sheet
public class Employee {
@SheetColumn("Age")
private Integer age;
@SheetColumn("Name")
public String getName() {
return name;
}
}
要从xlsx文件中获取数据，您必须像这样编写。
final File xlsxFile = new File("<path_to_file>");
final XlsReader reader = new XlsReader();
List<Employee> employees = reader.read(Employee.class,xlsxFile);
要将数据写入excel工作表，您必须这样做。
List<Employee> employees = new ArrayList<Employee>();
employees.add(new Employee("1","foo",12,"MALE",1.68));
SpreadsheetWriter writer = new SpreadsheetWriter("<output_file_path>");
writer.addSheet(Employee.class,employees);
writer.write();
您必须为您的用例评估两个库。
,
我会考虑将自己的apache poi写入POJO映射程序包中，而不是简单地搜索任何可用的程序包。这样一来，您可以更灵活地扩展功能，因为您随后便知道了它的工作原理，而无需深入研究其他人编写的代码，而这些代码又被大量划分为类和方法。试图理解这样的代码可能真的很困难。不用说知道您自己想要的扩展名放在哪里了。
首先，这里是一个软件包PoiPOJO，到目前为止，该软件包仅由两个类组成。 PoiPOJOUtils提供了两种静态方法。一个sheetToPOJO和一个pojoToSheet。然后ExcelColumn是Annotation类中可以使用的POJO接口。
PoiPOJOUtils.java：
package PoiPOJO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import java.util.*;
import java.lang.reflect.*;
public class PoiPOJOUtils {
public static <T> List<T> sheetToPOJO(Sheet sheet,Class<T> beanClass) throws Exception {
DataFormatter formatter = new DataFormatter(java.util.Locale.US);
FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
int headerRowNum = sheet.getFirstRowNum();
// collecting the column headers as a Map of header names to column indexes
Map<Integer,String> colHeaders = new HashMap<Integer,String>();
Row row = sheet.getRow(headerRowNum);
for (Cell cell : row) {
int colIdx = cell.getColumnIndex();
String value = formatter.formatCellValue(cell,evaluator);
colHeaders.put(colIdx,value);
}
// collecting the content rows
List<T> result = new ArrayList<T>();
String cellValue = "";
java.util.Date date = null;
Double num = null;
for (int r = headerRowNum + 1; r <= sheet.getLastRowNum(); r++) {
row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
T bean = beanClass.getDeclaredConstructor().newInstance();
for (Map.Entry<Integer,String> entry : colHeaders.entrySet()) {
int colIdx = entry.getKey();
Cell cell = row.getCell(colIdx); if (cell == null) cell = row.createCell(colIdx);
cellValue = formatter.formatCellValue(cell,evaluator); // string values and formatted numbers
// make some differences for numeric or formula content
date = null;
num = null;
if (cell.getCellType() == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) { // date
date = cell.getDateCellValue();
} else { // other numbers
num = cell.getNumericCellValue();
}
} else if (cell.getCellType() == CellType.FORMULA) {
// if formula evaluates to numeric
if (evaluator.evaluateFormulaCell(cell) == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) { // date
date = cell.getDateCellValue();
} else { // other numbers
num = cell.getNumericCellValue();
}
}
}
// fill the bean
for (Field f : beanClass.getDeclaredFields()) {
if (!f.isAnnotationPresent(ExcelColumn.class)) {
continue;
}
ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
if(entry.getValue().equals(ec.name())) {
f.setAccessible(true);
if (f.getType() == String.class) {
f.set(bean,cellValue);
} else if (f.getType() == Double.class) {
f.set(bean,num);
} else if (f.getType() == java.util.Date.class) {
f.set(bean,date);
} else { // this is for all other; Integer,Boolean,...
if (!"".equals(cellValue)) {
Method valueOf = f.getType().getDeclaredMethod("valueOf",String.class);
f.set(bean,valueOf.invoke(f.getType(),cellValue));
}
}
}
}
}
result.add(bean);
}
return result;
}
public static <T> void pojoToSheet(Sheet sheet,List<T> rows) throws Exception {
if (rows.size() > 0) {
Row row = null;
Cell cell = null;
int r = 0;
int c = 0;
int colCount = 0;
Map<String,Object> properties = null;
DataFormat dataFormat = sheet.getWorkbook().createDataFormat();
Class beanClass = rows.get(0).getClass();
// header row
row = sheet.createRow(r++);
for (Field f : beanClass.getDeclaredFields()) {
if (!f.isAnnotationPresent(ExcelColumn.class)) {
continue;
}
ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
cell = row.createCell(c++);
// do formatting the header row
properties = new HashMap<String,Object>();
properties.put(CellUtil.FILL_PATTERN,FillPatternType.SOLID_FOREGROUND);
properties.put(CellUtil.FILL_FOREGROUND_COLOR,IndexedColors.GREY_25_PERCENT.getIndex());
CellUtil.setCellStyleProperties(cell,properties);
cell.setCellValue(ec.name());
}
colCount = c;
// contents
for (T bean : rows) {
c = 0;
row = sheet.createRow(r++);
for (Field f : beanClass.getDeclaredFields()) {
cell = row.createCell(c++);
if (!f.isAnnotationPresent(ExcelColumn.class)) {
continue;
}
ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
// do number formatting the contents
String numberFormat = ec.numberFormat();
properties = new HashMap<String,Object>();
properties.put(CellUtil.DATA_FORMAT,dataFormat.getFormat(numberFormat));
CellUtil.setCellStyleProperties(cell,properties);
f.setAccessible(true);
Object value = f.get(bean);
if (value != null) {
if (value instanceof String) {
cell.setCellValue((String)value);
} else if (value instanceof Double) {
cell.setCellValue((Double)value);
} else if (value instanceof Integer) {
cell.setCellValue((Integer)value);
} else if (value instanceof java.util.Date) {
cell.setCellValue((java.util.Date)value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean)value);
}
}
}
}
// auto size columns
for (int col = 0; col < colCount; col++) {
sheet.autoSizeColumn(col);
}
}
}
}
和
ExcelColumn.java：
package PoiPOJO;
import java.lang.annotation.*;
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
String name();
String numberFormat() default "General";
}
然后可以使用...
Car.java：
import PoiPOJO.ExcelColumn;
public class Car {
@ExcelColumn(name = "Manufacturer")
public String manufacturer;
@ExcelColumn(name = "Model")
public String model;
@ExcelColumn(name = "Color")
public String color;
@ExcelColumn(name = "Year",numberFormat = "0")
public Integer year;
@ExcelColumn(name = "Price",numberFormat = "$#,##0.00")
public Double price;
@ExcelColumn(name = "Date",numberFormat = "YYYY-MM-DD")
public java.util.Date date;
@ExcelColumn(name = "Available")
public Boolean available;
public String toString() {
String result = ""
+"Manufacturer=" + this.manufacturer
+" Model=" + this.model
+" Color=" + this.color
+" Year=" + this.year
+" Price=" + this.price
+" Date=" + this.date
+" Available=" + this.available
+"";
return result;
}
}
和
TestPoiPOJO.java：
import PoiPOJO.PoiPOJOUtils;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
public class TestPoiPOJO {
public static void main(String[] args) throws Exception {
Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelCars.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
List<Car> cars = PoiPOJOUtils.sheetToPOJO(sheet,Car.class);
System.out.println(cars);
Car car = new Car();
car.manufacturer = "Mercedes-Benz";
car.model = "S 560 4Matic";
car.color = "Bordeaux";
car.year = 2019;
car.price = 78456.78;
car.date = new java.util.Date();
car.available = true;
cars.add(car);
sheet = workbook.createSheet();
PoiPOJOUtils.pojoToSheet(sheet,cars);
FileOutputStream out = new FileOutputStream("ExcelCarsNew.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}
ExcelCars.xlsx必须在第一张表中包含您的示例车表。列的顺序是灵活的。只有标题必须与类ExcelColumn中的Car注释的名称相对应。
,
我想找到一种将xls / xlsx文件解析为pojo列表的简单方法。经过一番搜索，我发现没有任何方便和可取之处，因此希望快速开发它。现在，我可以通过简单的调用来获得pojos了：
InputStream is = this.getClass().getResourceAsStream("/ExcelUtilsTest.xlsx");
List<Pojo> pojos = ExcelToPojoUtils.toPojo(Pojo.class,is);
如果有兴趣的话，请看一下它：
https://github.com/ZPavel/excelToPojo
,
对于@Axel Ritcher的答案略有不同，使用并行流以及具有Set Field（且没有公式求值）的Java对象：
public class ExcelFileUtils {
@SneakyThrows
// Call this using ExcelFileUtils.sheetToPOJO(new FileInputStream("yourExcl.xlsx"),YourPojo.class)
public static <T> List<T> sheetToPOJO(InputStream is,Class<T> beanClass) {
Workbook workbook = WorkbookFactory.create(is);
Sheet sheet=workbook.getSheetAt(0);
Map<Integer,String> colHeadersByColIdx = getColHeadersByCoIndex(sheet);
Map<String,Field> beanFieldsByExlColName=beanFieldsByExlColName(beanClass);
return IntStream.range(sheet.getFirstRowNum()+1,sheet.getLastRowNum())
.parallel()
.mapToObj(rowNum->{
T bean = null;
try {
bean =beanClass.getDeclaredConstructor().newInstance();
Row currentRow=sheet.getRow(rowNum);
if(Objects.isNull(currentRow)) currentRow=sheet.createRow(rowNum);
Row finalCurrentRow = currentRow;
T finalBean = bean;
colHeadersByColIdx.keySet().parallelStream()
.forEach(colIdx->{
String colName=colHeadersByColIdx.get(colIdx);
Cell cell=finalCurrentRow.getCell(colIdx);
if(Objects.isNull(cell))cell=finalCurrentRow.createCell(colIdx);
String cellValue=cell.getStringCellValue();
Field fieldForColName=beanFieldsByExlColName.get(colName);
fieldForColName.setAccessible(true);
try {
if (fieldForColName.getType() == String.class) {
fieldForColName.set(finalBean,cellValue);
}
if(fieldForColName.getType() == Double.class){
fieldForColName.set(finalBean,cell.getNumericCellValue());
}
if(fieldForColName.getType() == Set.class ){
fieldForColName.set(finalBean,Arrays.stream(cellValue.split(",")).collect(Collectors.toSet()));
}
}catch (IllegalAccessException ex){
throw new ResponseStatusException(HttpStatus.INTERNAL_SERVER_ERROR,ex.getMessage());
}
});
} catch (InstantiationException | IllegalAccessException | InvocationTargetException |NoSuchMethodException e) {
throw new ResponseStatusException(HttpStatus.BAD_REQUEST,e.getMessage());
}
return bean;
}).collect(Collectors.toList());
}
private static <T> Map<String,Field> beanFieldsByExlColName(Class<T> beanClass){
Map<String,Field> beanFieldsByExlColName=new HashMap<>();
Arrays.stream(beanClass.getDeclaredFields())
.parallel()
.filter(field -> field.isAnnotationPresent(ExcelColumn.class))
.forEach(field -> {
ExcelColumn ec = field.getAnnotation(ExcelColumn.class);
beanFieldsByExlColName.put(ec.name(),field);
});
return beanFieldsByExlColName;
}
private static Map<Integer,String> getColHeadersByCoIndex(Sheet sheet){
Map<Integer,String> colHeadersByColIdx = new HashMap<Integer,String>();
Row row1 = sheet.getRow(sheet.getFirstRowNum());
for(Cell cell : row1){
int colIdx=cell.getColumnIndex();
colHeadersByColIdx.put(colIdx,cell.getStringCellValue());
}
return colHeadersByColIdx;
}
}
请注意，此示例假定您在pojo中具有String，Double和Set，并且与Set相对应的excel列具有逗号分隔的值。
例如：
POJO：
@Data
public class TestProduct{
@ExcelColumn(name = "Product Name")
private String productName;
@ExcelColumn(name = "Image Urls")
private Set<String> mediaUrls;
}
然后是Excel工作表：
