How to color specific row in excel table?

Question

I create excel file using NPOI dll's.

I have this code that create excel table from List<someObjects> :

IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
IRow header = sheet1.CreateRow(0);

header.CreateCell(0).SetCellValue("Id");
header.CreateCell(1).SetCellValue("Name");
header.CreateCell(2).SetCellValue("E-Mail");
header.CreateCell(3).SetCellValue("PhoneNumber");

for (int i = 0; i < list.Count(); i++)
{
   IRow row = sheet1.CreateRow(i + 1);
   row.CreateCell(0).SetCellValue(list[i].id);
   row.CreateCell(1).SetCellValue(list[i].name);
   row.CreateCell(2).SetCellValue(list[i].email);
   row.CreateCell(3).SetCellValue(list[i].phoneNumber);
 }

Then I make each cell bordered in the table created above.

Here is the code:

    public void setBorderExcel()
    {

        XSSFCellStyle myStyle = (XSSFCellStyle)workbook.CreateCellStyle();
        myStyle.BorderBottom = BorderStyle.Medium;
        myStyle.BorderTop = BorderStyle.Medium;
        myStyle.BorderLeft = BorderStyle.Medium;
        myStyle.BorderRight = BorderStyle.Medium;

        for (int i = 0; i < 7; i++)
        {
            for (int j = 0; j < 4; j++)
            {
                workbook.GetSheetAt(0).GetRow(i).GetCell(j).CellStyle = myStyle;
            }
        }
    }

Then I make each odd row in the table created above colored.

Here is the code:

    public void setColorExcel()
    {
        for (int i = 0; i < 7; i++)
        {
            for (int j = 0; j < 4; j++)
            {
                if (i % 2 == 0) continue;

                workbook.GetSheetAt(0).GetRow(i).GetCell(j).CellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                workbook.GetSheetAt(0).GetRow(i).GetCell(j).CellStyle.FillPattern = FillPattern.SolidForeground;

            }
        }
    }

And here is the result that I get:

As you can see the color applied to all rows in the table while, I wanted to color only the odd rows.

My question is why I get colored all rows? And how can I make colored only specific rows?

Answer 1

I think I understand it. You have applied the same XSSFCellStyle instance to all cells' Style property in (setBorderExcel). So, now they all have the same instance, so when you change a property on the CellStyle of one of the cells, it's changing the CellStyle instance which is associated with all cells.

You'll most likely need two XSSFCellStyle instances. One for odd rows and another for even rows.

Answer 2

I tried it out, and although I'm not sure why your way doesn't work, there is an easier way with less lines (simply declare another CellStyle with grey background, and use that instead of myStyle):

XSSFCellStyle myStyle = (XSSFCellStyle)workbook.CreateCellStyle();
myStyle.BorderBottom = BorderStyle.Medium;
myStyle.BorderTop = BorderStyle.Medium;
myStyle.BorderLeft = BorderStyle.Medium;
myStyle.BorderRight = BorderStyle.Medium;

XSSFCellStyle myStyleGrey = (XSSFCellStyle)workbook.CreateCellStyle();
myStyleGrey.BorderBottom = BorderStyle.Medium;
myStyleGrey.BorderTop = BorderStyle.Medium;
myStyleGrey.BorderLeft = BorderStyle.Medium;
myStyleGrey.BorderRight = BorderStyle.Medium;
myStyleGrey.FillForegroundColor = HSSFColor.Grey25Percent.Index;
myStyleGrey.FillPattern = FillPattern.SolidForeground;

for (int i = 0; i < 7; i++)
{
    for (int j = 0; j < 4; j++)
    {
        if (i % 2 == 0)
            workbook.GetSheetAt(0).GetRow(i).GetCell(j).CellStyle = myStyle;
        else
            workbook.GetSheetAt(0).GetRow(i).GetCell(j).CellStyle = myStyleGrey;
    }
}

You can remove your code for setColorExcel() and it should work as expected, setting odd rows to grey.