POI 中的 Java Excel/POJO 映射

Question

此处的 Java 8 使用 Apache POI 4.1 将 Excel (XLSX) 文件加载到内存中，并将 Java bean/POJO 列表写回新的 Excel 文件。

对我来说，一个 Excel 文件(至少是我正在使用的文件)实际上是一个 POJO 列表，每一行都是 P​​OJO 的一个不同实例，每一列都是该实例的不同字段值。观察:

这里我可能有一个名为 Car 的 POJO ，上面的示例电子表格是 List<Car> :

@Getter
@Setter
public class Car {

  private String manufacturer;
  private String model;
  private String color;
  private String year;
  private BigDecimal price;

}

所以我有运行的代码，可以将Excel文件(“new-cars.xlsx”)读入List<Car> ，处理该列表，然后将处理后的列表写回输出文件，例如“processed-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(或其他一些库)可能会提供一个“< em>mapper-esque”解决方案，它允许我以最少的代码和最大的优雅将 Excel 数据映射/绑定(bind)到 POJO 列表或从 POJO 列表映射/绑定(bind)。但是，我无法在任何地方找到任何此类功能。也许这是因为它不存在，或者也许我只是没有搜索正确的关键字。

理想情况下，应该遵循以下原则:

// 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");

POI-land 中是否存在这样的东西？还是我坚持我得到的东西？

Answer 1

到目前为止，Apache POI 还没有这样的功能。您可以检查外部库。我在下面提供了一些库。

https://github.com/ozlerhakan/poiji

该库在 mvnrepository 中可用，链接如下。该库仅提供一种绑定(bind)方式，例如从 excel 表绑定(bind)到 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();

您必须针对您的用例评估这两个库。