Geek Answers Handbook

Cell style setting does not work

I am working with apache poi and XLSX file. I use xssf classes to dynamically create a spreadsheet. I would like to set the cell style in a for loop, but it doesn't seem to work ... here is my code:

for(int i=1;i<=gc.getActualMaximum(GregorianCalendar.DAY_OF_MONTH);i++,gc.add(GregorianCalendar.DATE, 1),righe++){ Row r = foglio.createRow(righe); if(getDayOfWeek(gc)== 6 || getDayOfWeek(gc) == 7){ XSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cs1.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f = wb.createFont(); f.setBold(true); f.setColor(IndexedColors.RED.getIndex()); cs1.setFont(f); Cell c1 = r.createCell(0); c1.setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1); c2.setCellValue(i); c2.setCellStyle(cs1); } r.createCell(0).setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno()); r.createCell(1).setCellValue(i);

... this is just part of the code ... I don’t understand why it doesn’t work. It seems that cellstyle is being ignored or overwritten ....

any clue?

+7
apache-poi xssf
source share
2 answers

You can use the following method, maybe this will solve your problem.

 public static void setCellColorAndFontColor(XSSFCell cell, IndexedColors FGcolor, IndexedColors FontColor ){ XSSFWorkbook wb = cell.getRow().getSheet().getWorkbook(); CellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); font.setColor(FontColor.getIndex()); style.setFont(font); style.setFillForegroundColor(FGcolor.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); }

When you call this method, the path should be like

 setCellColorAndFontColor(cell, IndexedColors.BLACK, IndexedColors.WHITE);

will create a bold and white color for the font text with the background color of the black cell on the sheet.

+6
source share

CellStyles is a workbook, and there is a hard limit that Excel imposes on the numbers that a file is allowed to have, so you need to make sure that you are creating a cell style outside of the loop.

Your code will look something like this:

 XSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cs1.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f = wb.createFont(); f.setBold(true); f.setColor(IndexedColors.RED.getIndex()); cs1.setFont(f); for(int i=1;i<=gc.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) i++,gc.add(GregorianCalendar.DATE, 1),righe++){ Row r = foglio.createRow(righe); if(getDayOfWeek(gc)== 6 || getDayOfWeek(gc) == 7){ Cell c1 = r.createCell(0); c1.setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1); c2.setCellValue(i); c2.setCellStyle(cs1); } }

If you are having problems with a style that doesn't look the way you expect, the best option is to style the cell as you want in Excel, save the file, read it in the POI and view the cell style that Excel wrote. Excel can sometimes do some strange things that get used to, so check what it does to understand what you need to do!

+4
source share

More articles:


All Articles