Click or drag to resize

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

The Style of a cell or range (ExcelCellStyle) has the number format, the alignment, the font, the borders, the fill and the protection settings of the cells.

When many cells look the same, define a named style once in the workbook with AddStyle(String) and assign it to the cells. A named style is stored once in the file. Changing the style of a single cell after the assignment does not change the named style.

Gradient cell fills are an Excel 2007 or later feature: for Excel 97-2003 workbooks use solid or pattern fills.

A Named Style
ExcelCellStyle titleStyle = workbook.Styles.AddStyle("TitleStyle");
titleStyle.Font.Size = 14;
titleStyle.Font.Bold = true;
titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
// the same line style and color for the four borders
titleStyle.Borders.LineStyle = ExcelCellLineStyle.Medium;
titleStyle.Borders.Color = Color.Green;
if (workbook.Format == ExcelWorkbookFormat.Xlsx_2007)
{
    titleStyle.Fill.FillType = ExcelCellFillType.GradientFill;
    titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
    titleStyle.Fill.GradientFillOptions.Color2 = Color.White;
}
else
{
    titleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
    titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}

worksheet["A2:G3"].Merge();
worksheet["A2"].Text = "Sales Report";
worksheet["A2:G3"].Style = titleStyle;
The Style of a Range
// alignment and text rotation (in degrees, from -90 to 90)
worksheet["A5"].Value = "Rotated";
worksheet["A5"].Style.Alignment.Orientation = 90;
worksheet["B5"].Value = "A long text wrapped in the cell";
worksheet["B5"].Style.Alignment.WrapText = true;

// font
worksheet["C5"].Value = "Font";
worksheet["C5"].Style.Font.Name = "Verdana";
worksheet["C5"].Style.Font.Size = 12;
worksheet["C5"].Style.Font.Italic = true;
worksheet["C5"].Style.Font.Color = Color.Red;
worksheet["C5"].Style.Font.UnderlineType = ExcelCellUnderlineType.Double;

// a pattern fill
worksheet["A7:G7"].Style.Fill.FillType = ExcelCellFillType.PatternFill;
worksheet["A7:G7"].Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.DiagonalCrosshatch;
worksheet["A7:G7"].Style.Fill.PatternFillOptions.BackColor = Color.White;
worksheet["A7:G7"].Style.Fill.PatternFillOptions.PatternColor = Color.Orange;

// a different line style for each border
worksheet["B9"].Style.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thick;
worksheet["B9"].Style.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Double;
worksheet["B9"].Style.Borders[ExcelCellBorderIndex.DiagonalDown].LineStyle = ExcelCellLineStyle.Dot;
See Also