Friday, 29 July 2011 02:44

Insert Interior in Excel with C#, VB.NET

Written by  support iceblue
Rate this item
(0 votes)

How to Insert Interior in Excel

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:

    Excel Interior

    Here comes to the full code in C# and VB.NET.

    [C#]
    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");
    
                //Launch the file
                System.Diagnostics.Process.Start("Sample.xls");
            }
        }
    }
              
    [VB.NET]
    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")
    
            '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.


    More about Spire.XLS

    Additional Info

    • tutorial_title: Insert Interior in Excel
    Last modified on Wednesday, 08 January 2014 02:43