C# – Set Cell Fill

Cell fill is one of format settings in MS Excel, which includes background color setting and pattern style setting. It is often used to highlight column/row titles or some important information. Also, it can make the appearance be more great and appealed.

In this post, I want to show my method about how to set cell fill for an Excel file by using C#. The file I will use in this example is one part of student information which has been used in the last post. Also, this method is still based on Spire.XLS. Therefore, download and install it at the beginning.

Steps:

1. Create a new project and add Spire.XLS DLL as reference. Then, add system.drawing for color setting.

2. Load file I want to set cell fill and initialize worksheet.

            Workbook workbook = new Workbook();      

            workbook.LoadFromFile(@"D:\work\My Documents\StInfo.xlsx");

            Worksheet sheet = workbook.Worksheets[0];

3. In order to make the column title to be more obvious, set format for the first row.

         sheet.Range["A1:E1"].Style.Font.IsBold = true;

         sheet.Range["A1:E1"].Style.Font.FontName = "Bodoni MT";

         sheet.Range["A1:E1"].Style.Font.Size = 14;

4. Then, set cell fill for title and other cells.
            sheet.Range["A1:E1"].Style.KnownColor = ExcelColors.Rose;

            sheet.Range["A2:E9"].Style.KnownColor = ExcelColors.SkyBlue;

            sheet.Range["A1:E1"].Style.FillPattern = ExcelPatternType.Percent625Gray;

            sheet.Range["A2:E9"].Style.FillPattern = ExcelPatternType.Solid;

5. Save and launch file finally.

 

Complete Coding:


using System;

usingSystem.Drawing;

usingSpire.Xls;

 

namespaceSetCellFill

{

    class CellFill

    {

        static void Main(string[] args)

        {

            Workbook workbook = new Workbook();

            //Get File to Set Cell Fill

            workbook.LoadFromFile(@"D:\work\My Documents\StInfo.xlsx");

            Worksheet sheet = workbook.Worksheets[0];

 

            //Set Column Title Format

            sheet.Range["A1:E1"].Style.Font.IsBold = true;

            sheet.Range["A1:E1"].Style.Font.FontName = "Bodoni MT";

            sheet.Range["A1:E1"].Style.Font.Size = 14;

 

            //Set Background Color for Column Title

            sheet.Range["A1:E1"].Style.KnownColor = ExcelColors.Rose;

 

            //Set Background Color for Other Cells

            sheet.Range["A2:E9"].Style.KnownColor = ExcelColors.SkyBlue;

 

            //Set Fill Pattern for Column Title

            sheet.Range["A1:E1"].Style.FillPattern = ExcelPatternType.Percent625Gray;

 

            //Set Fill Pattern for Other Cells

            sheet.Range["A2:E9"].Style.FillPattern = ExcelPatternType.Solid;

 

            //Save the file.

            workbook.SaveToFile("CellFill.xlsx",ExcelVersion.Version2010);

 

            //Launch the file.

            System.Diagnostics.Process.Start(workbook.FileName);

        }

    }

}

 

After running, we can get the following result:

———————————————————————————

Click Here to Learn More about Spire.XLS

Click Here to Download Spire.XLS

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s