What is Excel Interior?
Excel provides essentially no support in worksheet functions for Working with cell colors. However, colors are often used in spreadsheets to indicate some sorts of value or category. Thus comes the need for functions that can work with colors on the worksheet. So it appears in the version in Excel 2007 as a new function. It contains all kinds of colors. Below I will show you how to insert interior in Excel with MS Excel and how to do this with Spire.XLS.
How to insert interior in Excel with MS Excel?
To insert interior in Excel with Microsoft Excel, you can follow the sections below:
- Open the worksheet in Excel
- Highlight the zones that you want to insert interior
- Rightclick and choose Setting Cell Format
- Choose Fill->Fill Effect in the dialog box of Setting Cell Format
- In the box, you can change the Color and the Shade Format to your desired effect
How to Insert Interior with Spire.XLS?
It's convenient to realize C#/.NET Excel Integration via Spire.XLS. In interior method, to realize interior you may set the color gradient by assigning sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.FillPattern property with ExcelPatternType.Gradient. You can set the BackKnownColor and ForeKnownColor of the sheet. What's more, you can set the gradient style, in the demo, we set the gradient style vertical. In order to reflect the effect, we merge the worksheet range from E to K. In this demo, we use Enum method to enumerate many kinds of colors and define a random object to fill the cell with a gradient color randomly.
First, let's preview the effect screenshot:
Here comes to the full code in C# and VB.NET.
using Spire.Xls;
using System.Drawing;
using System;
namespace Interior
{
class Program
{
static void Main(string[] args)
{
//Create a workbook
Workbook workbook = new Workbook();
//Initialize the worksheet
Worksheet sheet = workbook.Worksheets[0];
//Specify the version
workbook.Version = ExcelVersion.Version2007;
//Define the number of the colors
int maxColor = Enum.GetValues(typeof(ExcelColors)).Length;
//Create a random object
Random random = new Random((int)System.DateTime.Now.Ticks);
for (int i = 2; i < 40; i++)
{
//Random backKnownColor
ExcelColors backKnownColor = (ExcelColors)(random.Next(1, maxColor / 2));
sheet.Range["A1"].Text = "Color Name";
sheet.Range["B1"].Text = "Red";
sheet.Range["C1"].Text = "Green";
sheet.Range["D1"].Text = "Blue";
//Merge the sheet"E1-K1"
sheet.Range["E1:K1"].Merge();
sheet.Range["E1:K1"].Text = "Gradient";
sheet.Range["A1:K1"].Style.Font.IsBold = true;
sheet.Range["A1:K1"].Style.Font.Size = 11;
//Set the text of color in sheetA-sheetD
string colorName = backKnownColor.ToString();
sheet.Range[string.Format("A{0}", i)].Text = colorName;
sheet.Range[string.Format("B{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).R.ToString();
sheet.Range[string.Format("C{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).G.ToString();
sheet.Range[string.Format("D{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).B.ToString();
//Merge the sheets
sheet.Range[string.Format("E{0}:K{0}", i)].Merge();
//Set the text of sheetE-sheetK
sheet.Range[string.Format("E{0}:K{0}", i)].Text = colorName;
//Set the interior of the color
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.FillPattern = ExcelPatternType.Gradient;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.BackKnownColor = backKnownColor;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.ForeKnownColor = ExcelColors.White;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1;
}
//AutoFit Column
sheet.AutoFitColumn(1);
//Save the file
workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
//Launch the file
System.Diagnostics.Process.Start("Sample.xls");
}
}
}
Imports Spire.Xls
Imports System.Drawing
Imports System
Module Module1
Sub Main()
'Create a workbook
Dim workbook As New Workbook()
'Initialize the worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Specify the version
workbook.Version = ExcelVersion.Version2007
'Define the number of the colors
Dim maxColor As Integer = [Enum].GetValues(GetType(ExcelColors)).Length
'Create a random object
Dim random As New Random()
For i As Integer = 2 To 39
'Random backKnownColor
Dim backKnownColor As ExcelColors = DirectCast(random.[Next](1, maxColor \ 2), ExcelColors)
sheet.Range("A1").Text = "Color Name"
sheet.Range("B1").Text = "Red"
sheet.Range("C1").Text = "Green"
sheet.Range("D1").Text = "Blue"
'Merge the sheet"E1-K1"
sheet.Range("E1:K1").Merge()
sheet.Range("E1:K1").Text = "Gradient"
sheet.Range("A1:K1").Style.Font.IsBold = True
sheet.Range("A1:K1").Style.Font.Size = 11
'Set the text of color in sheetA-sheetD
Dim colorName As String = backKnownColor.ToString()
sheet.Range(String.Format("A{0}", i)).Text = colorName
sheet.Range(String.Format("B{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).R.ToString()
sheet.Range(String.Format("C{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).G.ToString()
sheet.Range(String.Format("D{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).B.ToString()
'Merge the sheets
sheet.Range(String.Format("E{0}:K{0}", i)).Merge()
'Set the text of sheetE-sheetK
sheet.Range(String.Format("E{0}:K{0}", i)).Text = colorName
'Set the interior of the color
sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.FillPattern = ExcelPatternType.Gradient
sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.BackKnownColor = backKnownColor
sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.ForeKnownColor = ExcelColors.White
sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical
sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1
Next
'AutoFit Column
sheet.AutoFitColumn(1)
'Save doc file.
workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003)
'Launching the MS Word file.
System.Diagnostics.Process.Start("Sample.xls")
End Sub
End Module
After running the demo, you will find color interior in your 2007 worksheet.
