NPOI does not change cell´s font color

user_2753723 0
Viewed 7734

I am trying to change a cell's font color conditionally. This is my last try:

IWorkbook wb = null;

using (FileStream _fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
    wb = WorkbookFactory.Create(_fileStream);
    _fileStream.Close();
}



ISheet sheet = wb.GetSheet(sheetName);
IFont font = wb.CreateFont();
...
...

// within a loop
ICell cell = sheet.GetRow(r).GetCell(col);
if (integrity < 1)
{
    ICellStyle redStyle = cell.CellStyle;
    font.Color = IndexedColors.Red.Index;
    redStyle.SetFont(font);
    cell.CellStyle = redStyle;
}
else
{
    ICellStyle normalStyle = cell.CellStyle;
    font.Color = XSSFFont.DEFAULT_FONT_COLOR;
    normalStyle.SetFont(font);
    cell.CellStyle = normalStyle;
}

However, the font does not change when the condition is met. It seems like the style is applied for all cells instead of the cell I am getting within the loop. I have read some questions related to this problem but I can not make it work.

This new try is formatting all cells. No matter whether it meets the condition or not

ICellStyle redStyle = cell.CellStyle;
font.Color = IndexedColors.Red.Index;             
redStyle.SetFont(font);    

//This is how I am trying to change cells format 
if (integrity < 1)
{
    cell.CellStyle.SetFont(font);
}

Joao response would format all cells with a "normalStyle"

npoi c#
3 Answers

By default, every cell will be using the same CellStyle object. If you want different styles for different cells, you have to create distinct objects.

ICellStyle redStyle = wb.CreateCellStyle();
font.Color = IndexedColors.Red.Index;
redStyle.SetFont(font);

ICellStyle normalStyle = wb.CreateCellStyle();
font.Color = XSSFFont.DEFAULT_FONT_COLOR;
normalStyle.SetFont(font);

// within a loop
ICell cell = sheet.GetRow(r).GetCell(col);
if (integrity < 1)
{
    cell.CellStyle = redStyle;
}
else
{
    cell.CellStyle = normalStyle;
}

(Note: I have not tested this code at all. I forget whether CreateCellStyle works like that. But it should at least point you in the right direction.)

user_6949760

For XSSF format use this approach to create font and then assign it to cells

XSSFFont defaultFont = (XSSFFont)workbook.CreateFont();
defaultFont.FontHeightInPoints = (short)10;
defaultFont.FontName = "Arial";
defaultFont.Color = IndexedColors.Black.Index;
defaultFont.IsBold = false;
defaultFont.IsItalic = false;
defaultFont.Boldweight = 700;

XSSFCellStyle defaultStyle = (XSSFCellStyle)workbook.CreateCellStyle();
defaultStyle.SetFont(defaultFont);
user_70089220

So I end up defining a style beforehand which I set to the cells that meet the condition.

IFont redfont = wb.CreateFont();
redfont.Color = IndexedColors.Red.Index;
redfont.FontHeight = 8;
ICellStyle style = wb.CreateCellStyle();

...
if (integrity < 1)
{
    style.CloneStyleFrom(cell.CellStyle);
    style.SetFont(redfont);
    cell.CellStyle = style;
}

Thanks for the help!

user_27537230
Related