How to: Apply Rich Formatting to Cell Text

Apr 22, 2022
3 minutes to read

Use the RichTextString object’s members to format text values within spreadsheet cells. Rich text consists of one or more text regions (or text runs ), each with its own set of font characteristics. The RichTextString.Runs property provides access to a text run collection. A RichTextRun object defines an individual run within a collection.

Create Rich Text

Use the following members to apply rich formatting to the cell’s text.

Member	Description
RichTextString.AddTextRun	Adds a new text run with the specified font settings to the rich text string.
RichTextString.Characters	Allows you to format specific characters within the cell’s text.
RichTextRange.SetFont/
RichTextRange.Font	Allows you to specify font attributes for specific characters within the cell’s text.
RichTextString.Text	Gets or sets the full text displayed in a cell.
CellRange.SetRichText	Assigns rich formatted text to a cell.

Example I

The following code snippet uses the RichTextString.AddTextRun method to compose the cell’s text from individual text runs.

C#
VB.NET

csharp

// Create a RichTextString instance.
RichTextString richText = new RichTextString();

// Add three text runs. Each run has its own font settings.
richText.AddTextRun("Rich ", new RichTextRunFont("Arial", 14, System.Drawing.Color.FromArgb(0xc5, 0x9f, 0xc9)));
richText.AddTextRun("text ", new RichTextRunFont("Tahoma", 14, System.Drawing.Color.FromArgb(0x2c, 0x60, 0x8e)));
richText.AddTextRun("formatting", new RichTextRunFont("Castellar", 14, System.Drawing.Color.FromArgb(0x2f, 0x24, 0x4f)));

// Assign the rich formatted text to the cell B2.
worksheet["B2"].SetRichText(richText);

' Create a RichTextString instance.
Dim richText As New RichTextString()

' Add three text runs. Each run has its own font settings.
richText.AddTextRun("Rich ", New RichTextRunFont("Arial", 14, System.Drawing.Color.FromArgb(&Hc5, &H9f, &Hc9)))
richText.AddTextRun("text ", New RichTextRunFont("Tahoma", 14, System.Drawing.Color.FromArgb(&H2c, &H60, &H8e)))
richText.AddTextRun("formatting", New RichTextRunFont("Castellar", 14, System.Drawing.Color.FromArgb(&H2f, &H24, &H4f)))

' Assign the rich formatted text to the cell B2.
worksheet("B2").SetRichText(richText)

Example II

The code snippet below uses the RichTextString.Characters method to format a portion of the cell’s text.

C#
VB.NET

csharp

// Create a RichTextString instance.
RichTextString richText = new RichTextString();

// Specify the cell text.
richText.Text = "Rich text formatting";
// Change font characteristics of the first word.
richText.Characters(0, 4).SetFont(new RichTextRunFont("Calibri", 12, System.Drawing.Color.Red));

// Assign the rich formatted text to the cell B2.
worksheet["B2"].SetRichText(richText);

' Create a RichTextString instance.
Dim richText As New RichTextString()

' Specify the cell text.
richText.Text = "Rich text formatting"
' Change font characteristics of the first word.
richText.Characters(0, 4).SetFont(New RichTextRunFont("Calibri", 12, System.Drawing.Color.Red))

' Assign the rich formatted text to the cell B2.
worksheet("B2").SetRichText(richText)

Access Rich Text

Member	Description
CellRange.HasRichText	Checks whether a cell contains rich formatted text.
CellRange.GetRichText	Retrieves rich text from a cell.
RichTextString.Runs	Allows you to iterate through a collection of the existing text runs.
RichTextRun.Text	Gets the current run’s text.
RichTextRun.Font	Gets font attributes applied to the current run.

Format Characters within an Existing Text

The following example shows how to apply rich formatting to a cell that already contains a text value.

C#
VB.NET

csharp

// Set a cell value.
worksheet["B2"].Value = "Rich text formatting";

// Obtain the RichTextString object that contains the cell text.
RichTextString richText = worksheet["B2"].GetRichText();
// Format the first word as bold.
richText.Characters(0, 4).Font.Bold = true;

// Assign the rich formatted text to the cell B2.
worksheet["B2"].SetRichText(richText);

' Set a cell value.
worksheet("B2").Value = "Rich text formatting"

' Obtain the RichTextString object that contains the cell text.
Dim richText As RichTextString = worksheet("B2").GetRichText()
' Format the first word as bold.
richText.Characters(0, 4).Font.Bold = True

' Assign the rich formatted text to the cell B2.
worksheet("B2").SetRichText(richText)

Obtain the Entire Rich Text String and Its Length

Member	Description
RichTextString.Text	Gets or sets the full text contained in a cell.
You can also use the cell’s CellRange.Value property to obtain the resulting text string as plain text.
RichTextString.Length	Returns the rich text length.