Here you are a minimal, reproducible example with the error.
I have a class CreateXSSFColor. Firstly, I define the styles estiloBasico and celdaVaciaGris. When the file excel is HSSFWorkbook type, the example works fine but if the type is XSSFWorkbook then the cell foregroundcolor in celdaVaciaGris style is always black. The problem seems to be in the double for at the bottom which prints the cell borders because if I comment this part then the example works fine again.
Thanks everyone.
public class CreateXSSFColor{
static private Workbook workbook;
static private Sheet sheet;
static private Color color;
static private CellStyle celdaVaciaGris;
static private CellStyle estiloBasico;
static private Font font;
public static void main(String[] args) throws IOException, DecoderException {
String fichero = "Excel.xlsx";
if(fichero.endsWith(".xls"))
workbook = new HSSFWorkbook();
else workbook = new XSSFWorkbook();
sheet = workbook.createSheet();
celdaVaciaGris = workbook.createCellStyle();
if(sheet instanceof HSSFSheet){
color = setHSSFColor(217, 217, 217);
celdaVaciaGris.setFillForegroundColor(((HSSFColor)color).getIndex());
}
else{
celdaVaciaGris = ((XSSFWorkbook)workbook).createCellStyle();
String rgbS = "D9D9D9";
((XSSFCellStyle)celdaVaciaGris).setFillForegroundColor(setXSSFColor(rgbS));
}
celdaVaciaGris.setFillPattern(FillPatternType.SOLID_FOREGROUND);
celdaVaciaGris.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00 €"));
celdaVaciaGris.setAlignment(HorizontalAlignment.RIGHT);
estiloBasico = workbook.createCellStyle();
estiloBasico.setAlignment(HorizontalAlignment.CENTER);
font = workbook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short)11);
estiloBasico.setFont(font);
int fila=0;
Row row = sheet.createRow(fila++);
Cell cell = row.createCell(0);
cell.setCellValue("yellow");
cell.setCellStyle(estiloBasico);
row = sheet.createRow(fila++);
cell = row.createCell(0);
cell.setCellValue("");
cell.setCellStyle(celdaVaciaGris);
row = sheet.createRow(fila++);
cell = row.createCell(0);
cell.setCellValue("yellow");
cell.setCellStyle(estiloBasico);
//The problem seems to be here
for(int i=0;i<1;i++){
for(int j=0;j<fila;j++){
CellRangeAddress cellRangeAddress = new CellRangeAddress(j, j, i, i);
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
}
}
FileOutputStream fileout = new FileOutputStream(fichero);
workbook.write(fileout);
fileout.close();
workbook.close();
}
private static XSSFColor setXSSFColor(String rgbS) throws DecoderException{
byte[] rgbB = Hex.decodeHex(rgbS);
XSSFColor xssfColor = new XSSFColor(rgbB, null);
return xssfColor;
}
private static HSSFColor setHSSFColor(int r, int g, int b){
HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette();
HSSFColor hssfColor = null;
try {
hssfColor= palette.findColor((byte)r, (byte)g, (byte)b);
if (hssfColor == null ){
palette.setColorAtIndex(IndexedColors.LAVENDER.getIndex(), (byte)r, (byte)g,(byte)b);
hssfColor = palette.getColor(IndexedColors.LAVENDER.getIndex());
}
} catch (Exception ex) {
ex.printStackTrace();
}
return hssfColor;
}
}