Set Sheet Styling

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

  • Solid fill

  • Pattern fill

  • Gradient fill

    • Linear gradient fill

    • Rectangular gradient fill

• Set font

• Set border

• Set number format

• Set alignment

• Set protection

Set Fill

You can set the fill style for a cell by using the IRange.Interior of the IRange . 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 IInterior.Pattern of the IInterior .

Refer to the following example code to set solid fill.

// 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 IInterior.Color , IInterior.ColorIndex , IInterior.ThemeColor and IInterior.TintAndShade properties of the IInterior . In order to set the foreground color, you can use the IInterior.PatternColor , IInterior.PatternColorIndex , IInterior.PatternThemeColor , IInterior.PatternTintAndShade properties of the IInterior .

Refer to the following example code to set pattern fill.

// 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 IInterior.Gradient of the IInterior .

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 .

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

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

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

// 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 IRange.Font of IRange interface.

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

// Set Font
worksheet.Range["A1"].Value = "GcExcel";
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 IRange.Borders of the IRange interface.

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

// 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 IRange.NumberFormat of the IRange interface.

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

// 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: IRange.HorizontalAlignment , IRange.VerticalAlignment , AddIndent, and IRange.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:

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

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

Set Protection

You can set protection for your worksheet using the IRange.FormulaHidden and IRange.Locked of the IRange interface.

Refer to the following example code to set protection for your worksheet.

//Set Protection
worksheet.Range["C4"].Locked = true;
worksheet.Range["C4"].FormulaHidden = true;