Class with CellStyle static values for NPOI

Question

[Update at the bottom]

What I want to do: Have a class with static readonly values for the CellStyles so I can have my code that builds the excel file be like this:

ICellStyle headerStyle1 = workbook.CreateCellStyle();
headerStyle1 = ExcelStyles.header1;

Instead of like this:

headerStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

Which would make it much easier to read, understand and maintain later on.

What I have so far: I made a class called ExcelStyles.cs in which I'm planning on having public static readonly variables so I can call the ones that I need, I know how to do it with methods, but having them being the CellStyle object directly should in theory make things easier later on. This code is what I was trying to do, but obviously doesn't work because it's not the correct syntax/way to do it.

class ExcelStyles
{
   public static readonly ICellStyle header1 = 
   { 
      header1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
      header1.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
   }
}

The issue: I don't know how to do this correctly and I've been hitting my head into a wall for a while trying to figure out how should I search for this without much success, and I don't know if it is possible to do with NPOI

What I'm using: Visual Studio 2019, Windows Forms, C#, .NET Framework 4.7.2, NPOI 2.5.3, it is a Desktop Application

Update: After fiddling a bit more I got to this code:

class ExcelStyles
{
    public static readonly ICellStyle header1 = ((XSSFWorkbook)new XSSFWorkbook()).CreateCellStyle();
    static ExcelStyles()
    {
        header1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
        header1.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
        header1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
        header1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        header1.FillPattern = FillPattern.SolidForeground;
        header1.VerticalAlignment = VerticalAlignment.Center;
    }
}

And I'm calling it like this:

ICellStyle headerStyle1 = workbook.CreateCellStyle();
headerStyle1.CloneStyleFrom(ExcelStyles.header1);

So the question changes, with testing so far this has worked just as expected, but I worry that ((XSSFWorkbook)new XSSFWorkbook()).CreateCellStyle() might cause unexpected issues. Is there a cleaner way to do this?

score 0 · Accepted Answer · answered May 12 '21 at 15:23

In the end doing this was the solution.

public static readonly ICellStyle header1 = new XSSFWorkbook().CreateCellStyle();

static ExcelStyles()
{
   header1.FillPattern = FillPattern.SolidForeground;
   header1.FillForegroundColor = color;
   header1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
   header1.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
   header1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
   header1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
   header1.VerticalAlignment = VerticalAlignment.Center;
   header1.Alignment = HorizontalAlignment.Left;
}

and then this to apply it

ICellStyle headerStyle1 = workbook.CreateCellStyle();
headerStyle1.CloneStyleFrom(ExcelStyles.header1);```

Ruslan_K · Answer 2 · 2021-09-06T09:21:06.487

One more option is to use enum and dictionary.

public enum TextType
{
    Header1,
    ...
}


public class ExcelStyles
{
    public static Dictionary<TextType, CellStyle> CreateDefaultStyels(HSSFWorkbook hssfworkbook)
    {
        {
                CellStyle style = hssfworkbook.CreateCellStyle();
                style.SetFont(getFont(12, 3000, false, false));
                style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                style.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style.VerticalAlignment = VerticalAlignment.CENTER;

                styles.Add(TextType.Header1, style);
        }
    }
}

And you can use it like this:

public MyExcelReport
{
    private Dictionary<TextType, CellStyle> Styles { get; set; }

    MyExcelReport()
    {
        Styles = ExcelStyles.CreateDefaultStyels(Hssfworkbook);
    }
    
    public void Set()
    {
        AddCell("Simple text", 0, NextRow, Styles[TextType.Header1]);
    }
}

Class with CellStyle static values for NPOI

2 Answers2