Even with the multitude of libraries available today, it can sometimes be challenging to find one that offers the specific functionality needed for a particular task. Instead of spending time searching for the perfect library, I recommend crafting your own implementation; even if it's tailored specifically for one project. Once, I found myself in need of a library that could easily convert data classes into an Excel document for report generation. When I couldn't locate a suitable library, I decided to develop functionality tailored to my specific needs. My ambition was to design a library akin to Jackson, which would use annotations to transform a list of data classes into an Excel document as dictated by the annotations. I want to share the library I created, hoping it may benefit others or inspire them to create their own mapper for their unique tasks. Let's explore how to develop in Java such a data mapper to achieve this: from this: void demoReport() { var excelMapper = new ExcelMapperImpl(); var fileName = "demo-out-" + LocalTime.now() + ".xlsx"; List<Demo> demos = generateDemos(); try (Workbook workbook = excelMapper.createWorkbookFromObject(demos); var fileOutputStream = new FileOutputStream(fileName)) { workbook.write(fileOutputStream); } } Defining Annotations Let's identify the main elements essential for Excel mapping. At its core, we require an Excel column. This foundational component of the report should distinctly showcase the column name and the corresponding value in each row. Moreover, we must incorporate support for formula cells, allowing us to utilize values and present the results dynamically. At the column's end, a concluding formula is essential, whether it represents an average, sum, or any other pertinent metric for the end-user. Beyond mere data cells, we should also integrate features to easily manage cell styling. After identifying the essential elements, the next step is to craft the necessary annotations. The initial annotation will embed metadata about cell styling. This annotation will encompass fundamental attributes along with their default values: @Retention(RetentionPolicy.RUNTIME) public @interface ColumnExcelStyle { ExcelColumnDataFormat cellTypePattern() default ExcelColumnDataFormat.NONE; IndexedColors cellColor() default IndexedColors.AUTOMATIC; boolean isWrapText() default false; boolean isCentreAlignment() default false; boolean isFramed() default true; ExcelColumnFont fontName() default ExcelColumnFont.DEFAULT; short fontSize() default -1; boolean isFontBold() default false; ExcelColumnCellTextColor fontColor() default ExcelColumnCellTextColor.AUTOMATIC; } The primary style elements crucial for report creation are conveyed as attributes. Following this, the initiation of an Excel column annotation can be undertaken: @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface ColumnExcel { String[] applyNames() default {}; int position(); ColumnExcelStyle headerStyle() default @ColumnExcelStyle( fontColor = ExcelColumnCellTextColor.BLACK, isCentreAlignment = true, isFontBold = true, fontSize = 14, isWrapText = true); ColumnExcelStyle cellStyle() default @ColumnExcelStyle; } This annotation encompasses potential column names (for mapping from Excel) and a mandatory field - ' '. This will determine the column's placement and be instrumental in formula calculations. Additionally, it will detail the style of both the header and the cell. position Excellent. Now, let's formulate an annotation specific to Excel formulas. Anticipating a dynamic formula contingent on the row's position, this annotation will be exclusive to methods: @Target({ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface ColumnExcelFormula { String name() default ""; int position(); ColumnExcelStyle headerStyle() default @ColumnExcelStyle( fontColor = ExcelColumnCellTextColor.BLACK, isCentreAlignment = true, isFontBold = true, fontSize = 14, isWrapText = true); ColumnExcelStyle cellStyle() default @ColumnExcelStyle; } Lastly, let’s introduce an annotation for the culminating formula, which typically occupies the final row in Excel, summarizing or illustrating the cumulative result for a column. Given that the formula's annotation also mandates its applicability solely to methods: @Target({ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface ColumnExcelTotalFormula { boolean useValue() default false; int position(); ColumnExcelStyle cellStyle() default @ColumnExcelStyle; } Building an Excel Reporting Service With Apache POI After the creation of all necessary annotations, crafting a service class becomes the next step. The core library utilized will be , effective for working with .xls and .xlsx files. This class will use annotations’ attributes to generate an Excel report. Apache POI The primary method accepts a list of objects as input and returns a prepared workbook. For added flexibility, an overloaded method will be introduced to enable the specification of both the file name and the sheet name for report generation: <T> Workbook createWorkbookFromObject(List<T> reportObjects) { return createWorkbookFromObject(reportObjects, 0, "Report"); } <T> Workbook createWorkbookFromObject(List<T> reportObjects, int startRowNumber, String sheetName) { ... } To extract information about the class using reflection, any element from the array is selected. After accessing the class details, the first row can be established. Utilizing data from the annotation allows for the creation of cells with their respective names. If a name is absent, the class field's name can serve as an alternative: private <T> void createHeaderFromDeclaredExcelColumns(Row row, Class<T> clazz, PropertyDescriptor propertyDescriptor) { try { Field field = clazz.getDeclaredField(propertyDescriptor.getName()); ColumnExcel columnExcel = field.getDeclaredAnnotation(ColumnExcel.class); if (nonNull(columnExcel)) { String headerName = columnExcel.applyNames().length > 0 ? columnExcel.applyNames()[0] : field.getName(); createHeader(row, columnExcel.position(), headerName, columnExcel.headerStyle()); } } catch (NoSuchFieldException e) { log.debug(e.getLocalizedMessage()); } } When fashioning the header, remember to assign a style to every cell. Style parameters can be derived from the annotation: @ColumnExcelStyle private void createHeader(Row row, int position, String name, ColumnExcelStyle columnExcelStyle) { Cell cell = row.createCell(position); cell.setCellValue(name); setCellFormatting(cell, columnExcelStyle); row.getSheet().autoSizeColumn(cell.getColumnIndex()); } The process then turns to generating rows in the report based on data from the provided array. By iterating over the data, successive rows are formed: for (T report : reportObjects) { Row bodyRow = sheet.createRow(proceedRowNumber); createCellsFromDeclaredExcelColumns(bodyRow, report); proceedRowNumber++; } Property descriptors are procured to utilize getters instead of granting direct access to fields: private <T> void createCellsFromDeclaredExcelColumns(Row row, T tObject) { try { PropertyDescriptor[] propertyDescriptors = Introspector.getBeanInfo(tObject.getClass()).getPropertyDescriptors(); for (var propertyDescriptor : propertyDescriptors) { createCellFromDeclaredExcelColumns(row, tObject, propertyDescriptor); } } catch (IntrospectionException ex) { log.debug(ex.getLocalizedMessage()); } } With the property descriptor, a cell is formed: private <T> void createCellFromDeclaredExcelColumns(Row row, T tObject, PropertyDescriptor propertyDescriptor) { try { Field field = tObject.getClass().getDeclaredField(propertyDescriptor.getName()); Method readMethod = propertyDescriptor.getReadMethod(); ColumnExcel columnExcel = field.getDeclaredAnnotation(ColumnExcel.class); if (nonNull(columnExcel)) { Class<?> returnType = readMethod.getReturnType(); Cell cell = row.createCell(columnExcel.position()); Object invokeResult = readMethod.invoke(tObject); if (nonNull(invokeResult)) { defineAndAssignCellValue(returnType, cell, invokeResult, readMethod); } setCellFormatting(cell, columnExcel.cellStyle()); } } catch (NoSuchFieldException | InvocationTargetException | IllegalAccessException e) { log.debug(e.getLocalizedMessage()); } } Next, let’s turn our attention to processing the annotations. Crafting formulas proves slightly more complex than simply extracting a value from a field. A method is expected to generate a formula, which is subsequently assigned to the cell. @ColumnExcelFormula The method should consistently return a string and accept the row number as an argument, ensuring accurate data usage from adjacent cells. Thus, it falls on the handler to verify these conditions are met before forming the cell with the specified formula: private <T> void createCellFromDeclaredExcelFormula(Row row, T tObject, Method readMethod) throws IllegalAccessException, InvocationTargetException { ColumnExcelFormula columnExcelFormula = readMethod.getDeclaredAnnotation(ColumnExcelFormula.class); if (columnExcelFormula != null) { Class<?> returnType = readMethod.getReturnType(); Cell cell = row.createCell(columnExcelFormula.position()); if (returnType.isAssignableFrom(String.class)) { cell.setCellFormula((String) readMethod.invoke(tObject, row.getRowNum())); } else { log.debug(" Return type for the method: " + readMethod.getName() + " with @ColumnExcelFormula annotation has to be String " + "and now it's: " + returnType.getName() + " method is ignored for the reason"); } setCellFormatting(cell, columnExcelFormula.cellStyle()); } } The final step involves creating a row to display the concluding results. Importantly, this row should be generated only once, irrespective of the number of objects relayed to the handler. An annotation in a static method is anticipated for this purpose. This method receives both the number of the initial row and the current row where the cell will be instantiated as arguments. Provision of the initial row's number is vital, enabling the method to devise a formula that leverages the aggregate result for the entire column: private <T> void createTotalFormula(Class<T> tClazz, Row row, int firstRowNum) { Method[] methods = tClazz.getDeclaredMethods(); for (Method method : methods) { ColumnExcelTotalFormula columnExcelTotalFormula = method.getAnnotation(ColumnExcelTotalFormula.class); if (columnExcelTotalFormula != null && method.getReturnType().isAssignableFrom(String.class) && method.getParameters().length == 2 && Modifier.isStatic(method.getModifiers()) && !Modifier.isPrivate(method.getModifiers()) ) { String cellFormula = (String) method.invoke(tClazz, firstRowNum, row.getRowNum()); Cell cell = row.createCell(columnExcelTotalFormula.position()); cell.setCellFormula(cellFormula); if (columnExcelTotalFormula.useValue()) { cell = applyFormulasValue(cell); } setCellFormatting(cell, columnExcelTotalFormula.cellStyle()); } } } Making a Report The main functionality is now in place, and it's time to see it in action. Let's construct a simple report to demonstrate its operation. For this, let’s create a ' ' class and incorporate all the necessary annotations: Sales @Data @Accessors(chain = true) public class Sales { @ColumnExcel( position = 0, applyNames = {"Date"}, headerStyle = @ColumnExcelStyle( fontColor = WHITE, cellColor = DARK_BLUE, isCentreAlignment = true), cellStyle = @ColumnExcelStyle( cellColor = GREY_25_PERCENT, cellTypePattern = DATE)) private LocalDate date; @ColumnExcel( position = 1, applyNames = {"Sold"}, headerStyle = @ColumnExcelStyle( fontColor = WHITE, cellColor = DARK_BLUE, isCentreAlignment = true), cellStyle = @ColumnExcelStyle( cellColor = GREY_25_PERCENT)) private Integer sold; @ColumnExcel( position = 2, applyNames = {"Price Per Unit (USD)"}, headerStyle = @ColumnExcelStyle( fontColor = WHITE, cellColor = DARK_BLUE, isCentreAlignment = true), cellStyle = @ColumnExcelStyle( cellColor = GREY_25_PERCENT, cellTypePattern = USD)) private Double pricePerUnit; @ColumnExcelFormula( position = 3, name = "Total Sales (USD)", headerStyle = @ColumnExcelStyle( fontColor = WHITE, cellColor = DARK_BLUE, isCentreAlignment = true), cellStyle = @ColumnExcelStyle( cellColor = GREY_25_PERCENT, cellTypePattern = USD)) public String sales(int rowNum) { return new CellAddress(rowNum, 1).formatAsString() + "*" + new CellAddress(rowNum, 2).formatAsString(); } @ColumnExcelTotalFormula( position = 0, cellStyle = @ColumnExcelStyle( cellColor = LIGHT_BLUE)) public static String total(int firstRowNum, int lastRowNum) { return "CONCATENATE(\"Total\")"; } @ColumnExcelTotalFormula( position = 1, cellStyle = @ColumnExcelStyle( cellColor = LIGHT_BLUE)) public static String unitsSold(int firstRowNum, int lastRowNum) { return "SUM(" + new CellAddress(firstRowNum, 1).formatAsString() + ":" + new CellAddress(lastRowNum - 1, 1).formatAsString() + ")"; } @ColumnExcelTotalFormula( position = 3, cellStyle = @ColumnExcelStyle( isCentreAlignment = false, cellColor = LIGHT_BLUE, cellTypePattern = USD)) public static String totalSales(int firstRowNum, int lastRowNum) { return "SUM(" + new CellAddress(firstRowNum, 3).formatAsString() + ":" + new CellAddress(lastRowNum - 1, 3).formatAsString() + ")"; } } The class comprises three fields: , , and . Additionally, it has a sales formula and a concluding line with the totals: and . The fields utilize the annotation, denoting the column's position and name. date sold pricePerUnit unitsSold totalSales @ColumnExcel The annotation defines the style for both the header and individual data cells: @ColumnExcelStyle @ColumnExcel( position = 0, applyNames = {"Date"}, headerStyle = @ColumnExcelStyle( fontColor = WHITE, cellColor = DARK_BLUE, isCentreAlignment = true), cellStyle = @ColumnExcelStyle( cellColor = GREY_25_PERCENT, cellTypePattern = DATE) ) As previously discussed, when creating a formula, the method must accept a parameter indicating the row number. This requirement is evident in the method's signature: public String sales(int rowNum) { return new CellAddress(rowNum, 1).formatAsString() + "*" + new CellAddress(rowNum, 2).formatAsString(); } Given the row number and column indices, crafting any specific formula becomes feasible. Within the class, methods intended for the concluding formulas are static and require two parameters: the starting row's number and the ending row's number: public static String unitsSold(int firstRowNum, int lastRowNum) { return "SUM(" + new CellAddress(firstRowNum, 1).formatAsString() + ":" + new CellAddress(lastRowNum - 1, 1).formatAsString() + ")"; } Now, let's launch the method: void salesReport() { var excelMapper = new ExcelMapperImpl(); var fileName = "sales-out-" + LocalTime.now() + ".xlsx"; List<Sales> sales = List.of( new Sales().setDate(LocalDate.of(2023, 1, 1)) .setSold(50) .setPricePerUnit(10d), new Sales().setDate(LocalDate.of(2023, 1, 2)) .setSold(40) .setPricePerUnit(11d), new Sales().setDate(LocalDate.of(2023, 1, 3)) .setSold(55) .setPricePerUnit(9d); try (Workbook workbook = excelMapper.createWorkbookFromObject(sales); var fileOutputStream = new FileOutputStream(fileName)) { workbook.write(fileOutputStream); } } And examine the generated report: Conclusion Writing a specialized library for a specific task proved to be straightforward. The library crafted meets the requirements and includes the planned functionality. The approach utilizing annotations facilitates quick and convenient customization of cell styles, modification of formulas, and dynamic report creation from varied data sources. Hence, the next time a suitable library is elusive, considering the development of a personalized one might be beneficial. Naturally, presenting every line of code in this article was not feasible; thus, only the primary methods essential to the mapper's operation were highlighted. The complete code is available on my . GitHub page
