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.
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.
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.FromRGB(255, 0, 255); | |
After you set the fill style for the cells, if you also want to modify the background color of the cells, refer to Set Color in this documentation.
You can integrate pattern fill in cells using the Pattern property of the IInterior interface to one of the valid pattern types.
Pattern fill also consists of two parts - background Color and foreground Color.
You can use any of the Color, ColorIndex, ThemeColor and TintAndShade properties of the IInterior interface to set the background color. Also, you can use any of the PatternColor,PatternColorIndex, PatternThemeColor,PatternTintAndShade properties to set the foreground color.
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.FromRGB(255, 0, 255); worksheet.Range["A1"].Interior.PatternColorIndex = 5; | |
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.FromRGB(255, 0, 0); (worksheet.Range["C1"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromRGB(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.FromRGB(255, 0, 0); (worksheet.Range["E1"].Interior.Gradient as IRectangularGradient).ColorStops[1].Color = Color.FromRGB(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; | |
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 = "Spread"; 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; | |
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; | |
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"; | |
You can customize the alignment of a worksheet using any of the properties : HorizontalAlignment property, VerticalAlignment property, AddIndent property and ReadingOrder property of the IRange interface.
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; | |
You can set protection for your worksheet using the FormulaHidden property and Locked property of the IRange interface.
Refer to the following example code to set protection for your worksheet.
| C# |
Copy Code |
|---|---|
//Set Protection worksheet.Range["C4"].Locked = true; worksheet.Range["C4"].FormulaHidden = true; | |