Today, I will talk about my way of managing styles with Apache POI .
To add a style to a cell with POI you must create a CellStyle:
Workbook wb = new HSSFWorkbook(); CellStyle grey25ForeGroundColor = wb.createCellStyle(); grey25ForeGroundColor.setFillForegroundColor(IndexedColors.Grey_40_PERCENT.getIndex()); grey25ForeGroundColor.setFillPattern(CellStyle.SOLID_FOREGROUND);
It works fine but I can produce several Excel file with lots of different styles in each file. Rather than create a style for each cell, I told myself that I would pool the code in an abstract class. But then, it poses a big problem. Imagine that I wanted a gray cell as the one we just created.
So I create my style in my abstract class. Now I want a gray cell with centered content and another with centered content and borders . I will not create a style for each possibles combinations. What would have been nice is that you can add multiple styles in a cell.
Here is my solution that allows you to merge several styles together.
/** * Merges multiple CellStyle each other. * If two CellStyle have a style in common. * The following takes over the former. * @Param String A list of CellStyle to merge made * from a list contained in a HashMap. * @return CellStyle The CellStyle containing all styles.. */ protected CellStyle mergeCellStyle(String... cellStyles) { // Vacuum style that will serve as a basis for comparison. CellStyle defaultStyle = wb.createCellStyle(); // Base CellStyle that will serve to aggregate styles to merge. CellStyle currentStyle = wb.createCellStyle(); try { // We loop the CellStyles merge for(String cellStyleString:cellStyles) { // Is recovered CellStyle to merge with the PREV (s) CellStyle cellStyle = styles.get(cellStyleString) // We retrieves the list of methods of the CellStyle object // that we want to take into consideration when merging. Set<String> keys = mergeMethods.keySet(); // We loops through the list of these methods. for(String key : keys) Method method = cellStyle.getClass().getMethod(key); Class<?> returnType = method.getReturnType(); Object result = method.invoke(cellStyle); Object resultBase = methode.invoke(currentStyle); Object defaultStyle = method.invoke(defaultStyle); /* We compare if there is a change from the current style. * and if this change does not match the base style * to avoid backtracking. */ if(result.hashCode() != resultBase.hashCode() && result.hashCode() != resultDefault.hashCode()) { // If there is a change we modified the current style. currentStyle.getClass().getMethod(mergeMethods.get(key), returnType) .invoke(currentStyle, result); } } } } // The merged style is returned. return currentStyle; // Catch clause }
Note here that the hashcode is used for comparison. Indeed, as we use reflexivity, the result of the invocation of a method is of type Object. So the difference with the !=
will not give a relevant result. And as the types used by POI to change styles are primitive, a difference made with " ". Equals()
will not give the desired result either.
I give you the method to load the HashMap mergesMethods:
private void loadMergeMethod() { mergeMethods.put("getAlignment", "setAlignment"); mergeMethods.put("getBorderBottom", "getBorderBottom"); mergeMethods.put("getBorderLeft", "getBorderLeft"); mergeMethods.put("getBorderRight", "getBorderRight"); mergeMethods.put("getBorderTop", "getBorderTop"); mergeMethods.put("getBottomBorderColor", "getBottomBorderColor"); mergeMethods.put("getLeftBorderColor", "getLeftBorderColor"); mergeMethods.put("getRightBorderColor", "getRightBorderColor"); mergeMethods.put("getTopBorderColor", "getTopBorderColor"); mergeMethods.put("getDataFormat", "getDataFormat"); mergeMethods.put("getFillBackgroundColor", "getFillBackgroundColor"); mergeMethods.put("getFillForegroundColor", "getFillForegroundColor"); mergeMethods.put("getFillPattern", "getFillPattern"); mergeMethods.put("getHidden", "getHidden"); mergeMethods.put("getIndention", "getIndention"); mergeMethods.put("getLocked", "getLocked"); mergeMethods.put("getRotation", "getRotation"); mergeMethods.put("getShrinkToFit", "getShrinkToFit"); mergeMethods.put("getVerticalAlignment", "getVerticalAlignment"); mergeMethods.put("getWrapText", "getWrapText"); }
For those who ask why I do not use recursion to retrieve methods dynamically? The answer is that the MaClasse.getMethods () method returns the set of public, private ,_ protected methods of the class. We would therefore have to filter methods to exclude. So I preferred to include methods that i want to used.
private void createDefaultStyle() { // CellStyle with border CellStyle cellBordered = wb.createCellStyle(); // Add border cellBordered.setBorderBottom(CellStyle.BORDER_THIN); cellBordered.setBorderLeft(CellStyle.BORDER_THIN); cellBordered.setBorderRight(CellStyle.BORDER_THIN); cellBordered.setBorderTop(CellStyle.BORDER_THIN); // Set border color cellBordered.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellBordered.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellBordered.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellBordered.setTopBorderColor(IndexedColors.BLACK.getIndex()); styles.put(STYLE_CELL_BORDERED, cellBordered); // CellStyle with text centered CellStyle cellCentered = wb.createCellStyle(); cellCentered.setAlignment(CellStyle.ALIGN_CENTER); styles.put(STYLE_CELL_CENTERED, cellCentered); // CellStyle with light grey background CellStyle grey25BackgroundColor = wb.createCellStyle(); grey25BackgroundColor.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex() ); grey25BackgroundColor.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put( STYLE_CELL_GREY_25_BACKGROUND_COLOR, grey25BackgroundColor ); }
The variable styles is a Hashmap<String, CellStyle>
containing the list of available styles .
We just have to apply styles on the cell.
Sheet sheet = wb.createSheet("Feuille 1); Row row = sheet.createRow((short)0); Cell cell = row.createCell(0); cell.setCellValue("Test"); cell.setCellStyle(mergeCellStyle( STYLE_CELL_GREY_25_BACKGROUND_COLOR, STYLE_CELL_CENTERED, STYLE_CELL_BORDERED ));
And that is. A beautiful cell centered with border and gray background.
See you soon.