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?
As of now Apache POI does not have such a feature. There are external libraries which you can check. I provide a few libraries below.
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.