How to set a background color for a cell in Excel? Standart method doesn't work

Posted by Anton Golovanov

Posted on Wed, 01/11/2023 - 09:05 in Reports & Macros

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.

193

by Robert Goldenbaum

Posted on Wed, 01/11/2023 - 11:38

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

by Anton Golovanov Author

Posted on Mon, 01/16/2023 - 06:08

Robert Goldenbaum, thank you so much for your reply!

by Anton Golovanov Author

Posted on Tue, 01/17/2023 - 10:11

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);

by Kay Fischbach

Posted on Tue, 01/17/2023 - 13:37

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)

by Anton Golovanov Author

Posted on Wed, 01/18/2023 - 05:46

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.

How to set a background color for a cell in Excel? Standart method doesn't work

Featured achievement

Leaderboard

Follow us

View posts by tag