Multiple Styles to Excel Cell POI

Question

I want to apply colour to cell as well as Format Cell value(e.g. Date,Amount).But when I am applying two Cell Style only the last style is gets applied on cell.

//before this colourCellStyle and dateCellStyle are the formatting style
cell9 = row.createCell(9);
cell9.setCellValue(getLoadDate());
cell9.setCellStyle(colourCellStyle);
cell9.setCellStyle(dateCellStyle);

cell9.setCellStyle(dateCellStyle); is overriding cell9.setCellStyle(colourCellStyle); property — Lalit Rao, Sep 24 '15 at 11:20
http://stackoverflow.com/questions/26885834/how-to-create-cell-with-multiple-styles-in-excell-using-hssfsheet-apache-poi might be helpful — Lalit Rao, Sep 24 '15 at 11:32
I checked that thread but XSSFRichTextString is used for strings and it has applyFont method for styling. — rupesh_padhye, Sep 24 '15 at 12:52
A cell can only ever have one style applied. What happens if you create a third style that has both the colour and the date format on it? — Gagravarr, Sep 24 '15 at 13:47
I'm not getting what you want to ask?..Last applied style is persisted. I want to apply multiple style. — rupesh_padhye, Sep 25 '15 at 16:00

score 17 · Accepted Answer · answered Sep 25 '15 at 17:51

17

Multiple cell styles cannot be applied to a single Cell. The last cell style applied will overwrite any pre-existing cell style on the Cell. Setting multiple CellStyles won't combined the set attributes of each style.

The solution is to create another CellStyle that has the desired attributes of both of the other CellStyles. You can use the cloneStyleFrom method to start with the attributes of one CellStyle.

CellStyle combined = workbook.createCellStyle();
combined.cloneStyleFrom(colourCellStyle);
combined.setDataFormat(dateCellStyle.getDataFormat());
// You can copy other attributes to "combined" here if desired.

cell9.setCellStyle(combined);

This technique can be generalized to clone any existing cell style and copy individual attributes from a second existing cell style. As always, reuse any existing CellStyles, but if a different combination of attributes is required, then you must create and use a new CellStyle.

answered Sep 25 '15 at 17:51

rgettman

176,041
30
275
357

1

That's great if the combinations are limited. In my case I'm building a spread sheet and I don't necessarily know all combinations. The styles are driven by data. Wonder if this is a design issue with POI or if it's inherent with Excel's format. – David Bradley Apr 08 '20 at 16:42
Section "18.3.1.4 c (Cell)" of ECMA-376-1:2016 indicates "s (Style Index). The index of this cell's style. Style records are stored in the Styles Part. The possible values for this attribute are defined by the W3C XML Schema `unsignedInt` datatype." It looks like it allows only one style. – Ludovic Kuty Apr 28 '20 at 14:48
Note that you can dynamically create a style, then store it in a Map and if you need it again you use the same style. Naming might be an issue. BTW it looks like the number of styles is limited. – Ludovic Kuty Apr 28 '20 at 15:07

New Bee · Answer 2 · 2016-06-16T13:04:08.227

You can create a map of styles and then you can use different styles throughout the java program.

For example

Map<String, CellStyle> cellStyles = new HashMap<String, CellStyle>();
DataFormat dataFormat = workbook.createDataFormat();

XSSFCellStyle cellStyle;
XSSFFont font;

cellStyle = workbook.createCellStyle();

cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setAlignment(cellStyle.ALIGN_CENTER_SELECTION);   
font = workbook.createFont();
font.setFontHeightInPoints((short)16);
font.setFontName("Calibri");                                            
cellStyle.setFont(font);
cellStyles.put("header_cell_style", cellStyle);

cellStyle = workbook.createCellStyle(); 
cellStyle.setAlignment(cellStyle.ALIGN_CENTER_SELECTION);
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("Calibri");                   
cellStyle.setFont(font);
cellStyles.put("normal_cell_style", cellStyle);

cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(cellStyle.ALIGN_CENTER_SELECTION);       
cellStyle.setDataFormat(dataFormat.getFormat("dd-mmm-yyyy"));
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("Calibri");                   
cellStyle.setFont(font);
cellStyles.put("date_cell_style", cellStyle);

cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(cellStyle.ALIGN_CENTER_SELECTION);       
cellStyle.setDataFormat(dataFormat.getFormat("dd-mmm-yyyy"));
font = workbook.createFont();
font.setFontHeightInPoints((short)16);
font.setFontName("Calibri");                   
cellStyle.setFont(font);
cellStyles.put("header_date_cell_style", cellStyle);

return cellStyles;

and then use this map like

Map<String, CellStyle> multipleCellStyles = createMultipleExcelCellStyles(workbook);

headerCellD1.setCellStyle(multipleCellStyles.get("header_cell_style"));

cellB.setCellStyle(multipleCellStyles.get("normal_cell_style"));

cellC.setCellStyle(multipleCellStyles.get("date_cell_style"));

this is a nice way to organise the code but it doesn't help to apply more than one style to the same cell (e.g apply 2 defined XSSFCellStyle to the same cell)... — Cinn, Mar 16 '17 at 09:14

Multiple Styles to Excel Cell POI

2 Answers2

Linked

Related