News Category

C#/VB.NET: Apply Fonts to Excel Cells

2022-03-24 08:36:00 Written by  Administrator
Rate this item
(2 votes)

When you’re creating or reviewing a worksheet, you may want to format text in some specific cells using font styles in order to make them stand out. For example, you can change the font type, font color, font size and make text bold. This article will show you how to apply fonts to individual cells or cell ranges by using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Apply Different Fonts to Different Cells

Spire.XLS provides the CellRange.Style.Font property which you can use to set or change the font name, color, size and style in a cell easily. The following are the steps to apply a font style to a specific cell using Spire.XLS for .NET.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.Worksheets[index] property.
  • Get a specific cell using Worksheet.Range[int Row, int Column] property.
  • Set the value of the cell using CellRange.Value property.
  • Set the font name, color, size and style of the cell value through the properties under the CellRange.Value.Font object.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using System.Drawing;

namespace ApplySingleFontInCellRange
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object 
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Set font name
            int row = 1;
            sheet.Range[row, 1].Value = "Font Name";
            sheet.Range[row, 2].Value = "Arial Black";
            sheet.Range[row, 2].Style.Font.FontName = "Arial Black";

            //Set font size
            sheet.Range[row += 2, 1].Value = "Font Size";
            sheet.Range[row, 2].Value = "15";
            sheet.Range[row, 2].Style.Font.Size = 15;

            //Set font color 
            sheet.Range[row += 2, 1].Value = "Font Color";
            sheet.Range[row, 2].Value = "Red";
            sheet.Range[row, 2].Style.Font.Color = Color.Red;

            //Make text bold
            sheet.Range[row += 2, 1].Value = "Bold";
            sheet.Range[row, 2].Value = "Bold";
            sheet.Range[row, 2].Style.Font.IsBold = true;

            //Make text italic 
            sheet.Range[row += 2, 1].Value = "Italic";
            sheet.Range[row, 2].Value = "Italic";
            sheet.Range[row, 2].Style.Font.IsItalic = true;

            //Underline text
            sheet.Range[row += 2, 1].Value = "Underline";
            sheet.Range[row, 2].Value = "Underline";
            sheet.Range[row, 2].Style.Font.Underline = FontUnderlineType.Single;

            //Strikethrough text 
            sheet.Range[row += 2, 1].Value = "Strikethrough ";
            sheet.Range[row, 2].Value = "Strikethrough ";
            sheet.Range[row, 2].Style.Font.IsStrikethrough = true;

            //Auto fit column width
            sheet.AllocatedRange.AutoFitColumns();
       
            //Save the workbook to an Excel file
            workbook.SaveToFile("ApplySingleFontInCell.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Apply Fonts to Excel Cells

Appy Multiple Fonts in a Single Cell

Mixing fonts in a single cell can help you emphasize some specific characters within the cell. The following are the steps to apply multiple fonts in a cell using Spire.XLS for .NET.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.Worksheets[index] property.
  • Create two ExcelFont objects using Workbook.CreateFont() method.
  • Get a specific cell using Worksheet.Range[int Row, int Column] property, and set the rich text content of the cell using CellRange.RichText.Text property.
  • Apply the two ExcelFont objects to the rich text using RichText.SetFont() method.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace ApplyMultipleFontsInACell
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Create a font
            ExcelFont font1 = workbook.CreateFont();
            font1.FontName = "Arial Black";
            font1.KnownColor = ExcelColors.LightBlue;
            font1.IsBold = true;
            font1.Size = 13;

            //Create another font
            ExcelFont font2 = workbook.CreateFont();
            font2.KnownColor = ExcelColors.Red;
            font2.IsBold = true;
            font2.IsItalic = true;
            font2.FontName = "Algerian";
            font2.Size = 15;

            //Returns a RichText object from a specified cell
            RichText richText = sheet.Range["A1"].RichText;

            //Set the text of RichText object
            richText.Text = "Buy One, Get One Free";

            //Apply the first font to specified range of characters
            richText.SetFont(0, 16, font1);

            //Apply the second font to specified range of characters
            richText.SetFont(17, 21, font2);

            //Set column width
            sheet.Columns[0].ColumnWidth = 33;

            //Save the workbook to an Excel file
            workbook.SaveToFile("ApplyMultipleFonts.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Apply Fonts to Excel Cells

Apply a Font to a Cell Range

Spire.XLS provides the CellStyle class to manage the cell formatting such as fill color, text alignment and font style. You can create a cell style and apply it to a cell range or the whole worksheet using CellRange.ApplyStyle() method and Worksheet.ApplyStyle() method, respectively. The following are the steps to apply a font to a cell range using Spire.XLS for .NET.

  • Create a Workbook object.
  • Load a sample Excel file using Workbook.LoadFromFile() method.
  • Get the first worksheet using Workbook.Worksheets[index] property.
  • Create a CellStyle object using Workbook.Styles.Add() method, and set the font style through the CellStyle.Font property.
  • Apply the cell style to a cell range using CellRange.ApplyStyle() method.
  • Save the workbook to another Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using System.Drawing;

namespace ApplyFontToCellRange
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Create a CellStyle object
            CellStyle fontStyle = workbook.Styles.Add("headerFontStyle");

            //Set the font color, size and style
            fontStyle.Font.Color = Color.White;
            fontStyle.Font.IsBold = true;
            fontStyle.Font.Size = 12;
            fontStyle.HorizontalAlignment = HorizontalAlignType.Center;

            //Create a CellStyleFlag object, setting the FontColor, FontBold, FontSize and HorizontalAlignment properties to true
            CellStyleFlag flag = new CellStyleFlag();
            flag.FontColor = true;
            flag.FontBold = true;
            flag.FontSize = true;
            flag.HorizontalAlignment = true;

            //Apply the cell style to header row 
            sheet.Range[1, 1, 1, 8].ApplyStyle(fontStyle, flag);

            //Apply the cell style to the whole worksheet
            //sheet.ApplyStyle(fontStyle);

            //Save the workbook to another Excel file
            workbook.SaveToFile("ApplyFontToCellRange.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Apply Fonts to Excel Cells

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Additional Info

  • tutorial_title:
Last modified on Thursday, 24 March 2022 09:39