Java Excel/POJO Mapping in POI

Question

Java 8 here using Apache POI 4.1 to load Excel (XLSX) files into memory, and write lists of Java beans/POJOs back to new Excel files.

To me, an Excel file (at least the ones I'm working with) is really a list of POJOs, with each row being a different instance of the POJO, and each column a different field value for that instance. Observe:

Here I might have a POJO called Car, and the example spreadsheet above is a List<Car>:

@Getter
@Setter
public class Car {

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

}

So I have functioning code that will read an Excel file ("new-cars.xlsx") into a List<Car>, process that list, and then write the processed list back to an output file, say, "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();

While this works, it looks really ugly/nasty to me. I've used JSON mappers (Jackson, GSON, etc.), XML mappers (XStream) and OR/M tools (Hibernate) for years, and it occurred to me that POI's API (or some other library) might offer a "mapper-esque" solution that would allow me to map/bind the Excel data to/from a list of POJOs with minimal code and maximal elegance. However, I cannot find any such feature anywhere. Maybe this is because it doesn't exist, or maybe I'm just not searching for the right keywords.

Ideally, something along the lines of:

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

Does anything like this exist in POI-land? Or am I stuck with what I got?

Solution 1

As of now Apache POI does not such feature. There are external libraries which you can check. I provide below few libraries.

https://github.com/ozlerhakan/poiji

The library is available in mvnrepository, link is given below. This library provides only one way binding like from excel sheet to java pojo only.

https://mvnrepository.com/artifact/com.github.ozlerhakan/poiji/2.2.0

As per the above, you can do something like this.

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;
}

To get the information from excel sheet to java object, you have to do in the following manner.

List<Employee> employees = Poiji.fromExcel(new File("employees.xls"), Employee.class);

There is another library which can do both things like excel to java and java to excel. I provide below the link.

https://github.com/millij/poi-object-mapper

As per above library, you can do something like this.

@Sheet
public class Employee {

    @SheetColumn("Age")
    private Integer age;

    @SheetColumn("Name")
    public String getName() {
        return name;
    }

}

To get data from xlsx file, you have to write like this.

final File xlsxFile = new File("<path_to_file>");
final XlsReader reader = new XlsReader();
List<Employee> employees = reader.read(Employee.class, xlsxFile);

To write data to the excel sheet, you have to do like this.

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();

You have to evaluate both the libraries for your use cases.

Solution 2

I would consider writing my own apache poi to/from POJO mapper package instead of simply searching for any available packages. Doing this you are more flexible in extending the functionality then because you then know how it works without the need of dive deep into code others have wrote and which is heavily divided up into classes and methods. Trying to understand such code can be really difficult. No to mention to know where to place your own wanted extensions then.

To have a start, here is a package PoiPOJO which until now only consists of two classes. PoiPOJOUtils which provides two static methods. One sheetToPOJO and one pojoToSheet. And ExcelColumn which is an Annotation interface usable in POJO classes then.

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);
   }
  }
 }

}

and

ExcelColumn.java:

package PoiPOJO;

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
 String name();
 String numberFormat() default "General";
} 

This can be used then having ...

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;
 }
}

and

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();
 }
}

The ExcelCars.xlsx must contain your sample cars table in first sheet. The sequence of the columns is flexible. Only the headings must correspond to the names of the ExcelColumn annotations in class Car.

Solution 3

Probably, it would be easier to use oCell library for mapping Excel to POJO and POJO to Excel.

https://github.com/rushuat/ocell

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

Plus, this library supports few types of annotations (oCell, Jackson, JPA) and other features for mapping (data transformation, cell formatting, field ignoring).

Car 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;
}

Read/Write 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);
}

Field Converters:

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 Source:

Basic Excel currency format with Apache POI

Solution 4

A slight variation to @Axel Ritcher's answer, using parallel streams and for Java objects with a Set Field (and no formula evaluation) :

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;
    }
}

Please note that this example assumes that you have String, Double and Set in your pojo and the excel column that corresponds to the Set has comma separated values.

For example :

POJO :

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

And the Excel sheet :

View more solutions