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.
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;
// 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;