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

Anton Antonov on January 11, 2023

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.

Bookmark

Notify Moderator

Best Reply

Kay Fischbach on January 17, 2023 - 13:37 as Reply to Anton Antonov

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)

or register to reply.

Show in context Notify Moderator

5 Replies

Robert Goldenbaum on January 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

or register to reply.

Notify Moderator

Anton Antonov Author on January 16, 2023 - 06:08

Robert Goldenbaum, thank you so much for your reply!

or register to reply.

Notify Moderator

Anton Antonov Author on January 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);

or register to reply.

Notify Moderator

Kay Fischbach on January 17, 2023 - 13:37 as Reply to Anton Antonov

Best reply

Constants.NO_FILL

There is your problem, right in your createCellStyle method call. Set that to

Constants.SOLID_FOREGROUND

or register to reply.

Notify Moderator

Anton Antonov Author on January 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.

or register to reply.

Notify Moderator

Best Reply

Uitnodiging voor de AGNL-thema-sessie: Process Excellence