How to change cell color with NPOI

Question

using NPOI.XSSF.UserModel;
using NPOI.XSSF.Model;

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Model;

using NPOI.SS.UserModel;
using NPOI.SS.Util;


(...)

XSSFWorkbook hssfwb;

using (FileStream file = new FileStream(@"D:\VB\XLSX teste com NPOI\XLSX 1\Book1.xlsx", 
     FileMode.Open, FileAccess.Read))
{
    hssfwb = new XSSFWorkbook(file);
    file.Close();
}

ISheet sheet = hssfwb.GetSheetAt(0);
IRow row = sheet.GetRow(0);

ICell cell = row.CreateCell(5);
cell.SetCellValue("test");
cell.CellStyle.FillBackgroundColor = IndexedColors.BrightGreen.Index;
cell.CellStyle.FillPattern = FillPattern.SolidForeground;

using (FileStream file = new FileStream(@"D:\VB\XLSX teste com NPOI\XLSX 1\Book1ee22.xlsx", 
     FileMode.Create, FileAccess.Write))
{
    hssfwb.Write(file);
    file.Close();
}

Version of NPOI: 2.1.3.1 I have this code and it is changing the color for the hole sheet and not just the cell... What is the proper way to change the fill color of the cell?

Here is the working code, based on the answer that is marked as correct below:

XSSFWorkbook hssfwb;
        using (FileStream file = new FileStream(@"D:\Copy D\Tech\VB\XLSX teste com NPOI\XLSX 1\Book1.xlsx", FileMode.Open, FileAccess.Read))
        {
            hssfwb = new XSSFWorkbook(file);
            file.Close();
        }

        ISheet sheet = hssfwb.GetSheetAt(0);
        IRow row = sheet.GetRow(0);


        ICellStyle testeStyle = hssfwb.CreateCellStyle();
        testeStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
        testeStyle.FillForegroundColor = IndexedColors.BrightGreen.Index;
        testeStyle.FillPattern = FillPattern.SolidForeground;


        ICell cell = row.CreateCell(5);
        cell.SetCellValue("testeeerere");
        cell.CellStyle = testeStyle;


        using (FileStream file = new FileStream(@"D:\Copy D\Tech\VB\XLSX teste com NPOI\XLSX 1\Book1ee22.xlsx", FileMode.Create, FileAccess.Write))
        {
            hssfwb.Write(file);
            file.Close();
        }

Nino · Accepted Answer · 2016-05-05T14:19:03.867

31

take a look at this example:

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

(...)

Row row = sheet.CreateRow(0);

//styling
Font boldFont = workbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
ICellStyle boldStyle = workbook.CreateCellStyle();
boldStyle.SetFont(boldFont);

boldStyle.BorderBottom = CellBorderType.MEDIUM;
boldStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
boldStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;


for (int i = 0; i < columns.Length; i++)
{
    Cell cell = row.CreateCell(i);
    cell.SetCellValue(columns[i]);
    cell.CellStyle = boldStyle;
}

here you can see how to apply bold, background color and border for each cell in a row. In this example columns is string array representing columns data; use your values instead.

edited May 05 '16 at 14:19

answered May 05 '16 at 13:51

Nino

6,931
2
27
42

CellStyle is in `NPOI.SS.UserModel` namespace. I've edited my answer. – Nino May 05 '16 at 13:56
still cant declare it. added imports to question – meme May 05 '16 at 14:00
my example uses NPOI 1.2.3.0. In this version, you should find it in NPOI.SS.UserModel as you can see in this [APi overview](http://labs.ashmind.com/reuse/library/npoi/994/api). What's your version? – Nino May 05 '16 at 14:06
erm, i am at 2.1.3.1 – meme May 05 '16 at 14:10
1

Quick google search told me that you should declare it like `ICellStyle boldStyle = workbook.CreateCellStyle();`. I'm not on my PC anymore, try to find it by yourself. Do some littlw research, mate :-) – Nino May 05 '16 at 14:15
I was finally able to do it......... I did not change anything from your code. I just wrote it all by hand insted of crl+c... – meme May 05 '16 at 14:33
I have edited it, and put `ICellStyle`. I'm glad I helped you – Nino May 05 '16 at 14:39
oh, so it was that =) You only had `CellStyle`, did not even noticed after i wrote it by hand xD. Thank you again. – meme May 05 '16 at 14:46
Is there a way to set style for a range of cell at once? Say for Row 1 , I want to set style for columns 1 to 5. Other cells of the row will remain intact – Bimzee Feb 23 '18 at 04:30
@Bimzee just change loop like this: `for (int i = 0; i < 4; i++)`. That will just change cells with index 0 to 4 (from first to fifth) , not all in row – Nino Feb 23 '18 at 12:31
@Nino Sorry I dont want to iterate through each cell. All I wanted is to set style for cell range in a single statement Something like row.Cells[startCol , endCol].style = myStyle; is there any thing similar in NPOI c# – Bimzee Mar 15 '18 at 08:51
New enumerators has more C# than C style form. For example: `HSSFColor.Grey25Percent`, `FillPattern.SolidForeground` etc. Please refer NPOI documentation for details. – Martin Nov 04 '18 at 10:42
Apparently, fills in Excel are two-color, so when you want to change the fill, you HAVE to set a pattern, because the default is a cell with no fill and that means no FillPattern. SolidForeground is the basic fill pattern, and that means you have to set the foreground color of the fill, not the background color of the fill. – Ahmed_mag Mar 21 '23 at 15:49

How to change cell color with NPOI

1 Answers1