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.