问题：
POI中的JavaExcel/POJO映射
伍光济
Java 8在这里使用Apache POI 4.1将Excel（XLSX）文件加载到内存中，并将Java Bean/POJO列表写回新的Excel文件。
对我来说，一个Excel文件（至少是我正在处理的文件）实际上是一个POJO列表，每一行都是POJO的不同实例，每一列都是该实例的不同字段值。观察：
在这里，我可能有一个名为Car的POJO，上面的示例电子表格是一个列表
@Getter
@Setter
public class Car {
private String manufacturer;
private String model;
private String color;
private String year;
private BigDecimal price;
}
因此，我有一个功能代码，可以将Excel文件（“new cars.xlsx”）读取到一个列表中
// 1. Load excel file into a List<Car>
InputStream inp = new FileInputStream("new-cars.xlsx");
Workbook workbook = WorkbookFactory.create(inp);
Iterator<Row> iterator = workbook.getSheetAt(0).iterator();
List<Car> carsInventory = new ArrayList<>();
while (iterator.hasNext()) {
Car car = new Car();
Row currentRow = iterator.next();
// don't read the header
if (currentRow.getRowNum() == 0) {
continue;
}
Iterator<Cell> cellIterator = currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
CellAddress address = currentCell.getAddress();
if (0 == address.getColumn()) {
// 1st col is "Manufacturer"
car.setManufacturer(currentCell.getStringCellValue());
} else if (1 == address.getColumn()) {
// 2nd col is "Model"
car.setModel(currentCell.getStringCellValue());
} else if (2 == address.getColumn()) {
// 3rd col is "Color"
car.setColor(currentCell.getStringCellValue());
} else if (3 == address.getColumn()) {
// 4th col is "Year"
car.setYear(currentCell.getStringCellValue());
} else if (4 == address.getColumn()) {
// 5th col is "Price"
car.setPrice(BigDecimal.valueOf(currentCell.getNumericCellValue()));
}
}
carsInventory.add(car);
}
// 2. Process the list of Cars; doesn't matter what this does
List<Car> processedInventory = processInventory(carsInventory);
// 3. Output to "processed-cars.xlsx"
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Processed Inventory");
int rowNum = 0;
// create headers
Row headerRow = sheet.createRow(rowNum);
headerRow.createCell(0).setCellValue("Manufacturer");
headerRow.createCell(1).setCellValue("Model");
headerRow.createCell(2).setCellValue("Color");
headerRow.createCell(3).setCellValue("Year");
headerRow.createCell(4).setCellValue("Price");
rowNum++;
// rip through the cars list and convert each one into a subsequent row
for (Car processedCar : processedInventory) {
Row nextRow = sheet.createRow(rowNum);
nextRow.createCell(0).setCellValue(processedCar.getManufacturer());
nextRow.createCell(1).setCellValue(processedCar.getModel());
nextRow.createCell(2).setCellValue(processedCar.getColor());
nextRow.createCell(3).setCellValue(processedCar.getYear());
nextRow.createCell(4).setCellValue(processedCar.getPrice().doubleValue());
rowNum++;
}
FileOutputStream fos = new FileOutputStream("processed-cars.xlsx");
workbook.write(fos);
workbook.close();
虽然这是可行的，但对我来说它看起来真的很难看/令人讨厌。我已经使用JSON映射器（Jackson、GSON等）、XML映射器（XStream）和OR/M工具（Hibernate）多年了，我突然想到POI的API（或其他一些库）可能会提供一个“映射器式”解决方案，允许我用最少的代码和最优雅的方式将Excel数据映射到POJO列表中。然而，我在任何地方都找不到这样的功能。也许这是因为它不存在，或者我只是没有搜索正确的关键字。
理想情况下，符合以下几点：
// Annotate the fields with something that POI (or whatever tool) can pick up
@Getter
@Setter
public class Car {
@ExcelColumn(name = "Manufacturer", col = 0)
private String manufacturer;
@ExcelColumn(name = "Model", col = 1)
private String model;
@ExcelColumn(name = "Color", col = 2)
private String color;
@ExcelColumn(name = "Year", col = 3)
private String year;
@ExcelColumn(name = "Price", col = 4)
private BigDecimal price;
}
// 2. Now load the Excel into a List<Car>
InputStream inp = new FileInputStream("new-cars.xlsx");
List<Car> carsInventory = WorkbookFactory.create(inp).buildList(Car.class);
// 3. Process the list
List<Car> processedInventory = processInventory(carsInventory);
//4. Write to a new file
WorkbookFactory.write(processInventory, "processed-cars.xlsx");
波伊岛上有这样的东西吗？还是我被我得到的东西困住了？
共有3个答案
弘焕
也许，使用oCell库将Excel映射到POJO和POJO映射到Excel会更容易。
https://github.com/rushuat/ocell
<dependency>
<groupId>io.github.rushuat</groupId>
<artifactId>ocell</artifactId>
<version>0.1.4</version>
</dependency>
此外，这个库支持几种类型的注释（oCell、Jackson、JPA）和其他用于映射的功能（数据转换、单元格格式、字段忽略）。
汽车POJO：
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Car {
@FieldName("Manufacturer")
private String manufacturer;
@FieldName("Model")
private String model;
@FieldName("Color")
private String color;
@FieldAlignment(horizontal = "right")
@FieldConverter(YearConverter.class)
@FieldName("Year")
private String year;
@FieldAlignment(horizontal = "right")
@FieldFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")
@FieldConverter(PriceConverter.class)
@FieldName("Price")
private BigDecimal price;
}
读/写Excel：
Car hondaCar = new Car("Honda", "Pilot", "White", "2019", new BigDecimal(39000));
Car chevyCar = new Car("Chevy", "Silverado", "Green", "2018", new BigDecimal(34000));
Car toyotaCar = new Car("Toyota", "Corolla", "Silver", "2002", new BigDecimal(4000));
try (Document document = new Document()) {
List<Car> cars = Arrays.asList(hondaCar, chevyCar, toyotaCar);
document.addSheet(cars);
document.toFile("cars.xlsx");
}
try (Document document = new Document()) {
document.fromFile("cars.xlsx");
List<Car> cars = document.getSheet(Car.class);
}
磁场转换器：
public class YearConverter implements ValueConverter<String, Number> {
@Override
public String convertInput(Number value) {
return value == null ? null : String.valueOf(value.intValue());
}
@Override
public Number convertOutput(String value) {
return value == null ? null : Integer.valueOf(value);
}
}
public class PriceConverter implements ValueConverter<BigDecimal, Number> {
@Override
public BigDecimal convertInput(Number value) {
return value == null ? null : new BigDecimal(value.longValue());
}
@Override
public Number convertOutput(BigDecimal value) {
return value == null ? null : value.longValue();
}
}
@FieldFormat来源：
带有Apache POI的基本Excel货币格式
高运诚
我会考虑编写我自己的apache poito/fromPOJOmapper包，而不是简单地搜索任何可用的包。这样做您在扩展功能时更加灵活，因为您无需深入研究其他人编写的代码即可了解它的工作原理，这些代码被严重分为类和方法。试图理解这样的代码可能真的很困难。更不用说知道在哪里放置您自己想要的扩展了。
首先，这里有一个包PoiPOJO，到目前为止它只包含两个类PoiPOJOUtils，提供两种静态方法。一个是sheetToPOJO，一个是pojoToSheet。和ExcelColumn，这是一个可用于POJO类的注释接口。
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);
}
}
}
}
和
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;
}
}
和
测试Poi POJ O. 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必须在第一页包含您的示例cars表。列的顺序是灵活的。只有标题必须与classCar中的注释的名称相对应。
朱渝
到目前为止，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和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();
您必须为您的用例评估这两个库。
类似资料：
-
我试图通过jackson将以下json映射到pojo。我让第一部分工作（直到第节）。然而，第二部分我不确定如何将“section1”映射到这里可能包含的pojo或最佳实践？ Json通过Rest电话收到： 波乔：
-
我和jackson一起绘制地图，我有点迷茫。我的Json具有以下结构 我确实找到了很多处理数组的教程，但我在第一个标记“d”时就已经失败了。而且根本不需要所有的“_元数据”标记。 我创建了一个包含lastName等属性和一个集合附件的pojo。但我的代码在标记“d”或“_元数据”时总是失败 还有杰克逊的读者 任何提示都将不胜感激。 问候马蒂亚斯
-
我希望将XML反序列化为Java Pojo，但我不需要所有元素，并且我希望避免创建样板包装类来获取一些子元素 例如-->现在不起作用
-
我想我必须从POJO注册一个自定义转换器来记录。有人知道怎么做吗？
-
我一直在研究Jackson，但似乎必须将映射转换为JSON，然后将生成的JSON转换为POJO。 有没有一种方法可以将地图直接转换成POJO？
-
当我生成JOOQ pojo时，构造函数遵循与数据库表中字段相同的参数顺序。 当查询表并使用fetchInto时，只要POJO构造函数参数的顺序和数据库表中字段的顺序相同，这就可以正常工作。 如何将上面的查询映射到而不管构造函数参数顺序如何？例如，我可以将mapstruct之类的东西与JOOQ结合使用吗？