How to Format Excel Cells (Set Cell Style) with C#

We often format cells after exporting data to Excel, for example, set different color or font size for important data and title. Also, a well formatted Excel file is more appealed to viewers.

Sometimes, in order to separate title from data information, we may set different style for title row and data rows. In this post, I will show my method to set cell style with C#.

In my example, I have an Excel file which displays parts of employee information. The first row shows title, including first name, last name, hire date and other information. What I will do is to set different styles for title and the following data. The style contains font style, background color and borders.

Employee

At the beginning, add essential references in project. Because I use a .NET Excel component Spire.XLS for .NET, so add its dll file as reference firstly. Then add system.drawing for setting colors.

Steps:

  1. Load employee.xlxs from my computer and initialize the worksheet.
  2. Add title style and cell style. Then add details format settings.
  3. Apply the two styles to title range and data range.
  4. Save and launch file.

Coding:

using System;

using System.Drawing;

using Spire.Xls;

 

namespace Cell

{

    class Style

    {

        static void Main(string[] args)

        {

            //Load File

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”D:\work\My Documents\employee.xlsx”);

            Worksheet worksheet = workbook.Worksheets[0];

 

            //Add TitleStyle

            CellStyle titlestyle = workbook.Styles.Add(“style”);

            titlestyle.Font.FontName = “Times New Roman”;

            titlestyle.Font.Size = 12;

            titlestyle.Font.IsBold = true;

            titlestyle.Font.IsItalic = true;

            titlestyle.Color = Color.GreenYellow;

            titlestyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeLeft].Color = Color.DarkCyan;

            titlestyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeRight].Color = Color.DarkCyan;

            titlestyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeTop].Color = Color.DarkCyan;

            titlestyle.HorizontalAlignment = HorizontalAlignType.Center;

            titlestyle.VerticalAlignment = VerticalAlignType.Center;

 

            //Add CellStyle

 

            CellStyle cellstyle = workbook.Styles.Add(“cellstyle”);

            cellstyle.Font.FontName = “Calibri”;

            cellstyle.Font.Size = 12;

            cellstyle.Color = Color.WhiteSmoke;

            cellstyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeBottom].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeLeft].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeRight].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeTop].Color = Color.DarkCyan;

            cellstyle.HorizontalAlignment = HorizontalAlignType.Center;

            cellstyle.VerticalAlignment = VerticalAlignType.Center;

 

            //Apply Style

            worksheet.Range[“A1:F1”].Style = titlestyle;

            worksheet.Range[“A2:F19”].Style = cellstyle;

 

            //Save and Luanch File

            workbook.SaveToFile(“style.xlsx”, ExcelVersion.Version2010);

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

        }

    }

}

Result:

Download Spire.XLS for .NET Here

Advertisements

3 thoughts on “How to Format Excel Cells (Set Cell Style) with C#

  1. black weatherproof microfiber classic says:

    Just wish to say your article is as astonishing. The clarity in your
    post is just spectacular and i could assume you are an expert on this
    subject. Fine with your permission let me to grab your feed
    to keep up to date with forthcoming post. Thanks a
    million and please keep up the rewarding work.

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