5

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)

King King
  • 61,710
  • 16
  • 105
  • 130

3 Answers3

6

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.

Yuri Kopylovski
  • 380
  • 2
  • 5
  • 2
    thank you, you may have a very good knowledge of Excel, but that rule may apply to Excel Interop (Excel Automation), I'm using NPOI which edits and modifies the Excel file through another mechanism. I've referred to an example of NPOI library and the Cell style can be applied right after being created. The wrong point in my code is I didn't create new style for my cell, in that case CellStyle will be the RowStyle of the row which the cell is belong to. I've solved this myself, thank you, however you deserve my accept as an answer. Please keep sticking with stackoverflow :) – King King Apr 11 '13 at 08:22
  • What was the solution? – Rory Feb 16 '16 at 23:51
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
chuckc
  • 181
  • 6
  • The solution was in my comment under the accepted answer (I solved it myself) and actually what you answered here is that same way I used. However for your effort giving the answer, I'll give this an upvote. Thanks. – King King Aug 03 '17 at 08:31
0

A possible solution, request by Rory, is to set the CellStyle at the creation of the cell, only then set the value with GetCell(x).

I had the same error, because i was trying to change CellStyle after created it, and resolved changing the order, in this way:

//CREATE STYLE
ICellStyle styleCenter1 = hssfworkbook.CreateCellStyle();
styleCenter1.Alignment = HorizontalAlignment.Center;
styleCenter1.VerticalAlignment = VerticalAlignment.Center;
styleCenter1.WrapText = true;
styleCenter1.SetFont(font1);

//CREATE ROW
IRow row = sheet1.CreateRow(z);

//SETTING CELLSTYLE WHILE CREATING CELL
row.CreateCell(0).CellStyle = styleCenter1;

//SETTING CELLVALUE AFTER
row.GetCell(0).SetCellValue(listaSocieta[x]);

Ciao,

Raimondo

Rayan Hawkins
  • 1