Document Solutions for Excel, .NET Edition | Document Solutions
Features / Styles / Set Sheet Styling
In This Topic
    Set Sheet Styling
    In This Topic

    You can apply styling to your worksheets by performing actions like setting different fill styles for a cell, customizing the cell border and configuring the fonts for the spreadsheets etc.

    Set Fill

    You can set the fill style for a cell by using the Interior property of the IRange interface. A cell interior can be of three types, namely, solid fill, pattern fill and gradient fill.

    Solid Fill

    You can specify the fill style for the cell as solid by setting the Pattern property of the IInterior interface.

    Refer to the following example code to set solid fill:

    C#
    Copy Code 
    // Solid Fill for B5
worksheet.Range["B5"].Interior.Pattern = Pattern.Solid;
worksheet.Range["B5"].Interior.Color = Color.FromArgb(255, 0, 255);

     

    Pattern Fill

    You can integrate pattern fill in cells using the Pattern property of the IInterior interface to one of the valid pattern types. Pattern fill consists of two parts - background Color and foreground Color.

    In order to set the background color, you can use the Color, ColorIndex, ThemeColor and TintAndShade properties of the IInterior interface. In order to set the foreground color, you can use the PatternColor, PatternColorIndex, PatternThemeColor, PatternTintAndShade properties of the IInterior interface.

    Note: For the TintAndShade property, it is important to enter a number only from -1(darkest) to 1(lightest). If any value less than -1 or greater than 1 is provided, it will be treated as invalid and an exception will be thrown at runtime. The value zero (0) refers to neutral. Also, the TintAndShade property works only with the ThemeColor property.

    Refer to the following example code to set pattern fill:

    C#
    Copy Code 
    // Pattern Fill for A1
worksheet.Range["A1"].Interior.Pattern = Pattern.LightDown;
worksheet.Range["A1"].Interior.Color = Color.FromArgb(255, 0, 255);
worksheet.Range["A1"].Interior.PatternColorIndex = 5;

    Gradient Fill

    You can integrate gradient fill in cells using the Gradient property of the IInterior interface.

    Gradient fill can be of two types - Linear Gradient Fill and Rectangle Gradient Fill.

    Linear Gradient Fill

    You can set the linear gradient fill using the properties and methods of the ILinearGradient interface.

    Refer to the following example code to set linear gradient fill:

    C#
    Copy Code 
    // Gradient Fill for C1
worksheet.Range["C1"].Interior.Pattern = Pattern.LinearGradient;
(worksheet.Range["C1"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromArgb(255, 0, 0);
(worksheet.Range["C1"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromArgb(255, 255, 0);

(worksheet.Range["C1"].Interior.Gradient as ILinearGradient).Degree = 90;

    Rectangular Gradient Fill

    You can also set the rectangular gradient fill using the properties and methods of the IRectangularGradient interface.

    Refer to the following example code to set rectangular gradient fill:

    C#
    Copy Code 
    // Rectangular Gradient Fill for E1
worksheet.Range["E1"].Interior.Pattern = Pattern.RectangularGradient;
(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).ColorStops[0].Color = Color.FromArgb(255, 0, 0);
(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).ColorStops[1].Color = Color.FromArgb(0, 255, 0);

(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).Bottom = 0.2;
(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).Right = 0.3;
(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).Top = 0.4;
(worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).Left = 0.5;

    Set Font

    You can customize the font of a worksheet using the Font property of IRange interface.

    Refer to the following example code to set font style in your worksheet:

    C#
    Copy Code 
    // Set Font
worksheet.Range["A1"].Value = "Excel";
worksheet.Range["A1"].Font.ThemeColor = ThemeColor.Accent1;
worksheet.Range["A1"].Font.TintAndShade = -0.5;
worksheet.Range["A1"].Font.ThemeFont = ThemeFont.Major;
worksheet.Range["A1"].Font.Bold = true;
worksheet.Range["A1"].Font.Size = 20;
worksheet.Range["A1"].Font.Strikethrough = true;

    Set Border

    You can customize the border of a worksheet using the Borders property of the IRange interface.

    Refer to the following example code to set border in your worksheet:

    C#
    Copy Code 
    // Set Border
worksheet.Range["A1:B5"].Borders.LineStyle = BorderLineStyle.DashDot;
worksheet.Range["A1:B5"].Borders.ThemeColor = ThemeColor.Accent1;

worksheet.Range["A1:B5"].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Double;
worksheet.Range["A1:B5"].Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent2;
worksheet.Range["A1:B5"].Borders[BordersIndex.DiagonalDown].LineStyle = BorderLineStyle.Double;
worksheet.Range["A1:B5"].Borders[BordersIndex.DiagonalDown].ThemeColor = ThemeColor.Accent5;

    Set Number Format

    You can set the number format in a worksheet using the NumberFormat property of the IRange interface.

    Refer to the following example code to set number format in your worksheet:

    C#
    Copy Code 
    // Set Number format
worksheet.Range["A5"].Value = 12;
worksheet.Range["A5"].NumberFormat = "$#,##0.00";

    Set Alignment

    You can customize the alignment of cells using the following properties: HorizontalAlignment, VerticalAlignment, AddIndent, and ReadingOrder of IRange interface. HorizontalAlignment property sets the horizontal alignment of a cell, whereas VerticalAlignment property sets the vertical alignment of a cell. Furthermore, AddIndent property sets the indent of text when the horizontal or vertical alignment is set to Distributed. ReadingOrder property sets the direction in which the content within a cell or range of cells will be read.

    The following table lists the alignment options in HorizontalAlignment and VerticalAlignment properties:

    Property Option Example
    HorizontalAlignment Center
    CenterContinuous
    Distributed
    Fill
    General
    Justify
    Left
    Right
    VerticalAlignment Bottom
    Center
    Distributed
    Justify
    Top

    Refer to the following example code to set alignment in your worksheet:

    C#
    Copy Code 
    // Set Alignment
worksheet.Range["B8"].HorizontalAlignment = HorizontalAlignment.Distributed;
worksheet.Range["B8"].AddIndent = true;
worksheet.Range["B8"].VerticalAlignment = VerticalAlignment.Top;
worksheet.Range["B8"].ReadingOrder = ReadingOrder.RightToLeft;

    Note:

    • AddIndent will work in East Asian languages, as different languages have different split word strategies and the effects will be different.
    • Distributed layout will only take effect if the text orientation is set to 0, 90, -90, and 255. Any value less than 0 will display as the “right“ horizontal alignment with wrap text, and any value above 0 will display as the “left“ horizontal alignment with wrap text.