CellStyle applied to all cells in the Worksheet unexpectedly - NPOI?

Question

I don't understand why this can happen, first I tried applying a bold text to my column headers in the first row, then I want to set my header cells' borders to MEDIUM, but this MEDIUM border style is applied to all cells in the sheet. There are more problems in the same code below:

1. The text in my column headers (in the first row) is not bold as I want.
2. The text color in my column headers is not red as I want.

Here is my code (handling with NPOI library):

private void CreateATest(string filename)
    {
        FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write);
        HSSFWorkbook wb = new HSSFWorkbook();
        ISheet sheet = wb.CreateSheet("NPOI");
        IRow row = sheet.CreateRow(0);
        row.RowStyle = wb.CreateCellStyle();
        row.RowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

        row.RowStyle.VerticalAlignment = VerticalAlignment.CENTER;            
        row.RowStyle.WrapText = true;
        IFont font = wb.CreateFont();
        font.Boldweight = 3;
        font.Color = (short) ColorTranslator.ToWin32(Color.Red);
        font.FontHeight = 30;
        row.RowStyle.SetFont(font);
        int i = 0;
        foreach (string header in new string[] { "ID", "Name", "Age" })
        {
            row.CreateCell(i++).SetCellValue(header);
            row.Cells[i - 1].CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
            row.Cells[i - 1].CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
            row.Cells[i - 1].CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        }
        row.Cells[i - 1].CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        Random rand = new Random();
        for (i = 1; i < 1000; i++)
        {
            IRow row1 = sheet.CreateRow(i);
            for (int j = 0; j < 3; j++)
            {
                row1.CreateCell(j).SetCellValue(rand.Next(100));
            }
        }
        wb.Write(fs);
        fs.Close();
    }

Please fix it for me, I'm very new to NPOI, have just tried using it. Your help would be highly appreciated. Thanks. (<--- I don't know why this 'Thanks' can't jump to the next line even I typed Enter before typing it)

Answer 1

The formatting issue is due to the way Excel formats inserted rows. They take their style information from the row above. You can test this by formatting a row as bold, then inserting a row immediately below - the new row will be bolded as well. You could try inserting the rest of the rows first, then doing the formatting of the header row afterwards. Unfortunately I don't have enough reputation to make this a comment rather than an answer, because I can't help you with the other two issues.

Answer 2

Here is the solution - I had the same issue. You need to create a discrete ICellStyle and assign it to the cell's CellStyle, instead of just calling "SetFont()" on the cell's current CellStyle.

I was getting the same issue where the style was applied to all cells. I commented the line that was causing it, the next line is the one that worked (and the ICellStyle part):

        let workbook:HSSFWorkbook = new HSSFWorkbook()
        let worksheet:ISheet =  workbook.CreateSheet(sheetName)

        let fontbold = workbook.CreateFont()
        fontbold.Boldweight <- (int16 FontBoldWeight.Bold)
        fontbold.FontHeightInPoints <- 10s
        fontbold.FontName <- "Arial"

        let cellstylebold:ICellStyle = workbook.CreateCellStyle()
        cellstylebold.SetFont(fontbold)

        let row:IRow = worksheet.CreateRow(0)

        for h in 0..headers.Length-1 do
            let cell = row.CreateCell(h)
            cell.SetCellValue(headers.[h])
            //cell.CellStyle.SetFont(fontbold)
            cell.CellStyle <- cellstylebold