POI中的Java Excel/POJOMap

bksxznpy 于 2022-12-01 发布在 Java

关注(0)|答案(5)|浏览(194)

Java 8在这里使用Apache POI 4.1将Excel（XLSX）文件加载到内存中，并将Java bean/POJO列表写回到新的Excel文件中。
对我来说，Excel文件（至少是我正在使用的文件）实际上是一个POJO列表，每行是POJO的一个不同示例，每列是该示例的一个不同字段值。

这里我可能有一个名为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();

虽然这可以工作，但在我看来，它看起来真的很难看/讨厌。（Jackson、GSON等）、XMLMap器（XStream）和OR/M工具（休眠）多年，我突然想到POI的API（或其他一些库）可能会提供一个“mapper-esque”解决方案，允许我以最少的代码和最大的优雅程度将Excel数据Map到POJO列表或从POJO列表绑定Excel数据。我在任何地方都找不到这样的功能。也许这是因为它不存在，或者也许我只是没有搜索正确的关键字。

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

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

"在警察的地盘上有这样的东西吗？还是我被我得到的东西卡住了？"

excel

来源：https://stackoverflow.com/questions/58981017/java-excel-pojo-mapping-in-poi

5条答案

按热度按时间

k4emjkb11#

到目前为止，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();

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

赞(0）回复(0）举报 2022-12-01

vc6uscn92#

我会考虑编写我自己的apache poi to/from POJO mapper包，而不是简单地搜索任何可用的包。这样做可以更灵活地扩展功能，因为这样你就知道它是如何工作的，而不需要深入研究其他人写的代码，这些代码被分成很多类和方法。试图理解这样的代码真的很困难。更不用说知道在哪里放置自己想要的扩展了。
首先，这里有一个包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必须在第一个工作表中包含示例汽车表。列的顺序是灵活的。只有标题必须与类Car中的ExcelColumn注解的名称相对应。

赞(0）回复(0）举报 2022-12-01

hgncfbus3#

我建议使用 oCell 库将ExcelMap到POJO以及将POJOMap到Excel。
https://github.com/rushuat/ocell

<dependency>
  <groupId>io.github.rushuat</groupId>
  <artifactId>ocell</artifactId>
  <version>0.1.7</version>
</dependency>

另外，这个库支持几种类型的注解（即oCell、Jackson、JAXB、JPA）和其他Map特性（例如数据转换、单元格格式化、字段忽略等）。
汽车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 DocumentOOXML()) {
  List<Car> cars = Arrays.asList(hondaCar, chevyCar, toyotaCar);
  document.addSheet(cars);
  document.toFile("cars.xlsx");
}

try (Document document = new DocumentOOXML()) {
  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();
  }
}

@字段格式来源：
Basic Excel currency format with Apache POI

赞(0）回复(0）举报 2022-12-01

3bygqnnd4#

与@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列具有逗号分隔的值。
例如：

你好

@Data
public  class TestProduct{
    @ExcelColumn(name = "Product Name")
    private String productName;
    @ExcelColumn(name = "Image Urls")
    private Set<String> mediaUrls;
}

和Excel工作表：

赞(0）回复(0）举报 2022-12-01

r9f1avp55#

我想找到一个简单的方法来解析一个xls/xlsx文件到一个pojo列表。经过一些搜索，我没有找到任何方便的东西，更喜欢快速开发它。现在我可以通过简单的调用来获得pojo：

InputStream is = this.getClass().getResourceAsStream("/ExcelUtilsTest.xlsx");
List<Pojo> pojos = ExcelToPojoUtils.toPojo(Pojo.class, is);

如果有兴趣的话就看看吧：
https://github.com/ZPavel/excelToPojo

赞(0）回复(0）举报 2022-12-01

我来回答

POI中的Java Excel/POJOMap

5条答案

相关问题

热门标签

最新问答