Excel cell inline text formatting

With GemBox.Spreadsheet you can apply font formatting for the whole cell's text (see Styles and Formatting) or for the individual characters and words.

You can specify the size, color, type, underline, strikeout and more. For a full list check the ExcelFont properties.

The following example shows how you can format individual characters and words within an Excel cell.

Setting Excel cell inline text formatting
Screenshot of rich formatted text in Excel cells
View on GitHub 
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("InlineTextFormatting");

        worksheet.Columns[0].Width = 50 * 256;

        var cell = worksheet.Cells["A1"];
        cell.Value = "This is big and red text!";

        // Apply the size to "big and red" part of the text.
        cell.GetCharacters(8, 11).Font.Size = 400;

        // Apply the color to "red" part of the text.
        cell.GetCharacters(16, 3).Font.Color = SpreadsheetColor.FromName(ColorName.Red);

        cell = worksheet.Cells["A3"];
        cell.Value = "Formatting selected characters with GemBox.Spreadsheet component.";

        // Apply formatting on the whole cell content.
        cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);
        cell.Style.Font.Italic = true;
        cell.Style.WrapText = true;

        // Get characters from index 36 to the end of string,
        // e.g. the "GemBox.Spreadsheet component." part of the text.
        var characters = cell.GetCharacters(36);

        // Apply the color and underline to selected characters.
        characters.Font.Color = SpreadsheetColor.FromName(ColorName.Orange);
        characters.Font.UnderlineStyle = UnderlineStyle.Single;

        // Write selected characters.
        worksheet.Cells["A5"].Value = "Selected characters: " + characters.Text;

        workbook.Save("Inline Text Formatting.%OutputFileType%");
    }
}

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("InlineTextFormatting")

        worksheet.Columns(0).Width = 50 * 256

        Dim cell = worksheet.Cells("A1")
        cell.Value = "This is big and red text!"

        ' Apply the size to "big and red" part of the text.
        cell.GetCharacters(8, 11).Font.Size = 400

        ' Apply the color to "red" part of the text.
        cell.GetCharacters(16, 3).Font.Color = SpreadsheetColor.FromName(ColorName.Red)

        cell = worksheet.Cells("A3")
        cell.Value = "Formatting selected characters with GemBox.Spreadsheet component."

        ' Apply formatting on the whole cell content.
        cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue)
        cell.Style.Font.Italic = True
        cell.Style.WrapText = True

        ' Get characters from index 36 to the end of string,
        ' e.g. the "GemBox.Spreadsheet component." part of the text.
        Dim characters = cell.GetCharacters(36)

        ' Apply the color and underline to selected characters.
        characters.Font.Color = SpreadsheetColor.FromName(ColorName.Orange)
        characters.Font.UnderlineStyle = UnderlineStyle.Single

        ' Write selected characters.
        worksheet.Cells("A5").Value = "Selected characters: " & characters.Text

        workbook.Save("Inline Text Formatting.%OutputFileType%")

    End Sub
End Module

HTML text formatting

Instead of using GetCharacters methods, another way to create a rich formatted text is to use an HTML text.

The following example shows how you can create an inline text formatting by setting the cell value with the ExcelCell.SetValue(String, HtmlLoadOptions) method.

Setting Excel cell inline text formatting with HTML formatted text
Screenshot of HTML formatted text in Excel cells
View on GitHub 
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("HtmlTextFormatting");

        worksheet.Columns[0].Width = 50 * 256;

        var htmlOptions = new HtmlLoadOptions();
        var html = "<h1 style='background:#DDEBF7'>HTML formatted text!</h1>";

        worksheet.Cells["A1"].SetValue(html, htmlOptions);

        html = @"<div style='font:11pt Calibri'>
<p>This is <span style='font-size:20pt'>big and <span style='color:red'>red</span></span> text!</p>
<p>This is <sub>subscript</sub>, <sup>superscript</sup>, <strike>strike</strike>, and <u>underline</u> text.</p>
</div>";

        worksheet.Cells["A3"].SetValue(html, htmlOptions);

        workbook.Save("Html Text Formatting.%OutputFileType%");
    }
}

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("HtmlTextFormatting")

        worksheet.Columns(0).Width = 50 * 256

        Dim htmlOptions As New HtmlLoadOptions()
        Dim html = "<h1 style='background:#DDEBF7'>HTML formatted text!</h1>"

        worksheet.Cells("A1").SetValue(html, htmlOptions)

        html = "<div style='font:11pt Calibri'>
<p>This is <span style='font-size:20pt'>big and <span style='color:red'>red</span></span> text!</p>
<p>This is <sub>subscript</sub>, <sup>superscript</sup>, <strike>strike</strike>, and <u>underline</u> text.</p>
</div>"

        worksheet.Cells("A3").SetValue(html, htmlOptions)

        workbook.Save("Html Text Formatting.%OutputFileType%")
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: September 8, 2021 | Author: Mario Zorica