Simplify Data Conversion: Excel and CSV to Java Objects with Reflection

Converting Excel or CSV files into Java objects (POJOs) and vice versa can be a complex process, but with the right tools and techniques, it becomes much more manageable. In this guide, we’ll explore a powerful library that leverages Java reflection. Java First, we add the dependency to Maven. com.adnanebk excel-csv-converter 0.0.1-SNAPSHOT Understanding the POJO Class Before we dive into the library, let’s take a close look at a sample Java class that serves as our data model: @Data @Builder @AllArgsConstructor @NoArgsConstructor @SheetDefinition(datePattern = "dd/MM/yyyy") public class Product { @CellDefinition(0) private String name; @CellDefinition(1) private long price; @CellDefinition(2) @CellBoolean(trueValue = "yes",falseValue = "no") private boolean active; @CellDefinition(value = 3, title = "Promo price") private double promoPrice; // Additional fields... @CellEnum(enumsMapperMethod = "categoryMap") @CellDefinition(10) private Category category; @CellDefinition(11) private LocalDateTime localDateTime; private Map categoryMap(){ return Map.of(Category.A,"Formatted A", Category.B,"Formatted B"); } } This class is annotated with various annotations that play a crucial role in the conversion process. Each field is annotated with , indicating its position in the . Product @CellDefinition Excel or CSV file we can also define the title of the of the cell, by default, it will convert the camel case name of the field to a name with spaces (ex: firstName=>First name) The annotation provides additional information like date formatting patterns that will be used during the conversion of date field types. @SheetDefinition The Enum Annotation: @CellEnum(enumsMapperMethod = “categoryMap”) In the class, we make use of the annotation in the enum Category field. The enumsMapperMethod argument allows us to define a method name; this method should return a map that defines the mapping (conversions) between the enum constants and the formatted values in the Excel/CSV cells (by default, the enum constants will be used), note that the method name must much the enumsMapperMethod argument value. Product @CellEnum The Boolean Annotation: @CellBoolean(trueValue = “yes”,falseValue = “no”) we make use of the @CellBoolean annotation in the boolean active field, it has two arguments that represent the formatting values we want to use in the Excel/CSV fields. Now, let’s introduce an updated version of our POJO class, : ProductV2 @Data @Builder @AllArgsConstructor @NoArgsConstructor @SheetDefinition(includeAllFields = true,titles={"Name","Category","Date"}) public class ProductV2 { private String name; // Additional fields... private Category category; @IgnoreCell private LocalDateTime localDateTime; } Converting Excel files to POJOs becomes even simpler with the annotation provided in the class to map the fields to corresponding cells in the Excel file. @SheetDefinition When the includeAllFields argument is set to true, the fields are automatically included and mapped in the cells based on their declared order and ignore fields that are annotated with @IgnoreCell annotation. We can define the titles in the titles argument with the condition that they must be in the same order as the fields. Converting Excel/CSV to POJOs and vice versa @RestController @RequestMapping("excel/products") public class ExcelFieldsController { private final ExcelHelper excelHelper = ExcelHelper.create(Product.class); @GetMapping public List excelToProducts(@RequestBody MultipartFile file){ return excelHelper.toStream(file.getInputStream()).toList(); } @GetMapping("/download") public ResponseEntity downloadExcelFromProducts() { String filename = "products-" + LocalDate.now() + ".xlsx"; InputStreamResource file = new InputStreamResource(excelHelper.toExcel(getProducts())); return ResponseEntity.ok() .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename) .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) .body(file); } } the same is applicable for converting CSV files, except we need to define the delimiter that will be used private final CsvHelper csvHelper = CsvHelper.create(ProductV2.class,";"); The Class: Dynamic Class Examination ReflectionUtil The ReflectionUtil class serves as the backbone of this Java library, facilitating dynamic class examination and manipulation through the power of Java reflection. One noteworthy feature of the ReflectionUtil class is the optimization applied to enhance performance. During initialization, all getters, setters, and fields are eagerly loaded and encapsulated in the SheetField record. This deliberate action minimizes the need for reflection lookups in subsequent operations and boosts overall efficiency. Record Overview SheetField The Field record is a fundamental component of the library, designed to encapsulate information about the field of a class. public record SheetField (String typeName, String title, Function getter, BiConsumer setter, int cellIndex) Key methods : : Retrieves the value of the field from an object using its getter method. If the field is an enum, it provides formatted values based on defined enum mappings. public Object getValue(T obj) : Sets the value of the field in an object using its setter method. It handles enum values and ensures proper conversion. public void setValue(T obj, Object value) Conclusion By leveraging this custom library, developers can significantly simplify the process of converting Excel and CSV files to . The integration of Java reflection, along with thoughtful design considerations, empowers dynamic mapping, making it a valuable tool for data processing tasks. POJOs in Java GitHub repo https://github.com/adnanebk/excel-pojo-converter