Applying a style to a cell or set of cells is a very common operation while working with workbooks in Excel. By cell styling, we mean applying a defined set of formattings such as fonts, font sizes, number formats, cell borders, and cell shading. This helps in improved and better visualization of data for quick referencing.
As a .NET application developer, you may be interested in providing the functionality of setting cell style in Excel workbooks from within your .NET applications. You can achieve this using NPOI API in your application using C# or VB.NET as shown in this article.
Apply Set Style using Microsoft Excel
It will be helpful to have a look at how styling is applied to a cell using Microsoft Excel first.
Steps to Apply Cell Style using Microsoft Excel
If you want to apply styling to a cell using Microsoft Excel, there are several options offered by Excel for this purpose as explained below.
Using Default Styling Groups
- Select the cells that you want to format.
- On the Home tab, in the Styles group, click the More dropdown arrow in the style gallery, and select the cell style that you want to apply.
Create a Custom Cell Style
- Select the New Cell Style option from the More dropdown arrow in the style gallery on the Home tab
- Enter an appropriate name for the new cell style in the Style name box
- Click Format
- Select the formatting that you want from the Format Cells dialog box on the various tabs and click OK
Apply Cell Style using NPOI in C#
Now that we have seen how to apply a cell style using Microsoft Excel, let us now have a look at how we can do the same in our .NET application. We’ll be using the open-source API NPOI for .NET for this purpose and will write the code in C#.
But before that, we need to install NPOI in our .NET project to get started. You can learn about this in our detailed instructions guide for Installing NPOI for .NET.
Set Cell Style using C#
Now that your development environment is ready, you can create a simple console-based application and use the following code.
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("MySheet");
//Create cell and set its value
ICell cell = ws.CreateRow(1).CreateCell(3);
ICellStyle style = wb.CreateCellStyle();
//Set border style
style.BorderBottom = BorderStyle.Double;
style.BottomBorderColor = HSSFColor.Yellow.Index;
//Set font style
IFont font = wb.CreateFont();
font.Color = HSSFColor.Blue.Index;
font.FontName = "Arial";
font.FontHeight = 15;
font.IsItalic = true;
//Set background color
style.FillBackgroundColor = IndexedColors.Black.Index;
style.FillPattern = FillPattern.SolidForeground;
//Apply the style
cell.CellStyle = style;
//Save the file
FileStream file = File.Create("CellStylingFileFormat.xlsx");
In this article, we showed how to apply styling to a cell in an Excel worksheet using NPOI API in C#. You can further explore the API functionality by studying the API documentation. If you would like to know more about working with Excel workbooks using NPOI, stay tuned for more examples in this section.