Click or drag to resize

Setting Cell Styles in Excel spreadsheets from C#/VB.NET

The style of a cell or a range of cells be accessed by the Style property. This property can be assigned with a global style defined at the workbook level or it can be customized directly.

When many cells or ranges of cells have the same style is more efficient and more elegant to define a global style at workbook level to be assigned to cells and ranges.

Code Samples

The code sample below is an example of cell styles setting:

Copy
// Add a style used for all the cells containing the hello world text

ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
helloWorldStyle.Font.Size = 14;
helloWorldStyle.Font.Bold = true;

helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;

if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
{
    helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
    helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
    helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
    helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
}
else
{
    helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
    helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
    helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
    helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
}

worksheet["C5"].Text = "Hello World !!!";

// set the 'HelloWorldStyle' for the cell
worksheet["C5"].Style = helloWorldStyle;

The style can be also customized inline:

Copy
worksheet["A21"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
worksheet["A21"].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Top;
worksheet["A21"].Style.Font.Bold = true;
worksheet["A21"].Style.Font.Color = Color.Red;
worksheet["A21"].Value = "Top Left";