Hello Everyone,
I output report to an Excel file and I need to set background color for the cells. But no matter how hard I try, I can't do anything - background color remains white. I ask you to help me with advice on how to solve this problem? Here is a fragment of my code:
.....
var orderCellStyle = oO.createCellStyle(orderFont, 0, 0, 0, 0, 0, 0, 0, 0, Constants.ALIGN_LEFT, Constants.ALIGN_CENTER, 0, 0, Constants.NO_FILL);
orderCellStyle.setVerticalAlignment(Constants.VERTICAL_TOP);
orderCellStyle.setWrapText(true);
orderCellStyle.setBorderBottom(Constants.BORDER_THIN);
orderCellStyle.setBorderTop(Constants.BORDER_THIN);
orderCellStyle.setBorderLeft(Constants.BORDER_THIN);
orderCellStyle.setBorderRight(Constants.BORDER_THIN);
orderCellStyle.setFillBackgroundColor(10); //10 - for example
......
sheet1.cell(0,0).setCellValue("Example");
sheet1.cell(0,0).setCellStyle(orderCellStyle);
All cell appearance settings are applied except for the background color :( I've also tried changing the parameters in oO.createCellStyle(....), but that doesn't work either.
I hope for your help.
Hi,
you have to create a cell style and then set it to the cell:
oStyle = _oWorkbook.createCellStyle(_fStyle, lTopBorder, lRightBorder, lBottomBorder, lLeftBorder, lTopBorderColor, lRightBorderColor, lBottomBorderColor, lLeftBorderColor, lHorizontalAlignment, lVerticalAlignment, lBackgroundColor, lForegroundColor, lFillPattern)
oCell = _oOutSheet.cell(i, j);
oCell.setCellValue("blabla");
oCell.setCellStyle(oStyle);
BR Robert
Robert Goldenbaum, thank you so much for your reply!
Hi,
Unfortunately, it didn't work for me anyway. Below is the edited code. I do all the same things, but not through objects. And still the color of the cell in Excel does not change. Remains white.But all other cell settings work, the script is executed without errors.
var oO = Context.createExcelWorkbook("Example filename.xls");
var sheet1 = oO.createSheet("Sheet 1");
var orderFont = oO.createFont();
orderFont.setFontName("Arial");
var orderNewCellStyle = oO.createCellStyle(orderFont,1,1,1,1,0,0,0,0,Constants.ALIGN_LEFT,Constants.VERTICAL_TOP,Constants.C_RED,Constants.C_RED,Constants.NO_FILL,Constants.XL_CELL_DATAFORMAT_D_MMM_YY,false,0,false,0,true);
sheet1.cell(i,j).setCellValue("Example value");
sheet1.cell(i,j).setCellStyle(orderNewCellStyle);
Constants.NO_FILL
There is your problem, right in your createCellStyle method call. Set that to
Constants.SOLID_FOREGROUND
or some other acceptable fill pattern and it'll have a background color. (Foreground is a bit missleading here - backgrounds have a foreground and background color, none of them will be in front of the cell content)
Kay Fischbach, thank you very much! It's working!
I tried changing this parameter earlier, but the cell became completely black. Apparently I made a mistake somewhere else. But now I have combined your answer and Robert Goldenbaum's answer - and everything worked out.