My purpose is to copy conditional formatting rules from one sheet to another, where two sheets are not in same workbook via apache poi. For this purpose I am using the following code;
for(int i=0;i<sscf.getNumConditionalFormattings();i++){
ConditionalFormatting cf = sscf.getConditionalFormattingAt(i);
CellRangeAddress[] range = cf.getFormattingRanges();
//getting various properties from source
ConditionalFormattingRule rule = cf.getRule(0);
PatternFormatting pattern = rule.getPatternFormatting();
Color bgcolor = pattern.getFillBackgroundColorColor();
Color fgcolor = pattern.getFillForegroundColorColor();
Short fillPattern = pattern.getFillPattern();
FontFormatting font = rule.getFontFormatting();
int colorIndex = font.getFontColorIndex();
Color fontColor = font.getFontColor();
int fontHeight = font.getFontHeight();
short ulType = font.getUnderlineType();
//--------------------------------End of getting------------------------------
//setting properties from source
ConditionalFormattingRule newRule = dscf.createConditionalFormattingRule(rule.getFormula1());
PatternFormatting newPattern = newRule.createPatternFormatting();
FontFormatting newFont = newRule.createFontFormatting();
if(bgcolor != null)
newPattern.setFillBackgroundColor(bgcolor);
if(fgcolor != null)
newPattern.setFillForegroundColor(fgcolor);
if(fillPattern != 0)
newPattern.setFillPattern(fillPattern);
if(colorIndex != 0)
newFont.setFontColorIndex((short) colorIndex);
if(fontColor != null)
newFont.setFontColor(fontColor); //<-----This line
if(fontHeight != 0)
newFont.setFontHeight(fontHeight);
if(ulType != 0)
newFont.setUnderlineType(ulType);
dscf.addConditionalFormatting(range,newRule);
}
Everything is working fine except I can not set font color of conditional formatting cells in destination sheet.
Two things I noticed;
1st : colorIndex of source sheet always becomes 0 (Though my font color in source sheet is green or red)
2nd : I am getting an exception in line pointed with This line as follow
java.lang.IndexOutOfBoundsException
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontImpl.setColorArray(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFFontFormatting.setFontColor(XSSFFontFormatting.java:123)
at processor.ExcelTemplateProcessor.copyConditionalFormattingRules(ExcelTemplateProcessor.java:799)
at processor.ExcelTemplateProcessor.copySheet(ExcelTemplateProcessor.java:163)
at processor.ExcelTemplateProcessor.copySheet(ExcelTemplateProcessor.java:128)
at processor.ExcelTemplateProcessor.createReport(ExcelTemplateProcessor.java:117)
at service.TemplateReportService.createReport(TemplateReportService.java:189)
at test.Test.main(Test.java:62)
I tried to search almost everywhere about the cause, but could not find out a solution.
Can you please mention if I am doing something wrong?