Use the Excel Export API to Apply Rich Formatting to the Cell Text

• Sep 19, 2023
• 3 minutes to read

The example below demonstrates how to specify the rich formatted text for a cell. Such rich text is defined by the XlRichTextString object and includes one or more text runs (XlRichTextRun). Each run defines a region of the cell text with its own set of font characteristics. All text runs are stored in the collection of runs accessible using the XlRichTextString.Runs property. Use the collection’s methods to add new runs to the cell text, to modify the existing one, or to delete the specified runs from the collection.

To apply different fonts to specific regions of the cell text, do the following:

1. Initialize a new instance of the XlRichTextString class.

2. Specify the required number of text runs that compose the cell text as a whole and add them to collection of runs (XlRichTextString.Runs). To create a XlRichTextRun object for each individual text region and set its font settings, use the XlRichTextRun constructor with the following parameters:

   • A String value that specifies the region’s content.
   • An XlFont object that specifies font settings to be applied to this text region.
3. Call the IXlCell.SetRichText method and pass the specified XlRichTextString object as a parameter.

If you wish to change a region of the cell text after all runs are specified, access the required run by its index in the collection and then use the XlRichTextRun.Text and XlRichTextRun.Font properties to modify the text and font settings of this run, respectively.

To obtain the entire text contained in a cell, use the XlRichTextString.Text property. Note that setting this property to a new value replaces all the existing text runs in the collection with a single run that holds the full text of the cell formatted with the default font.

View Example

CellFormattingActions.cs

// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
    // Create the first column and set its width.
    using (IXlColumn column = sheet.CreateColumn())
    {
        column.WidthInPixels = 180;
    }
    // Create the first row.
    using (IXlRow row = sheet.CreateRow())
    {
        // Create the cell A1.
        using (IXlCell cell = row.CreateCell())
        {
            // Create an XlRichTextString instance.
            XlRichTextString richText = new XlRichTextString();
            // Add three text runs to the collection. 
            richText.Runs.Add(new XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(0x53, 0xbb, 0xf4))));
            richText.Runs.Add(new XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(0xf1, 0x77, 0x00))));
            richText.Runs.Add(new XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(0xe3, 0x2c, 0x2e))));
            // Add the rich formatted text to the cell. 
            cell.SetRichText(richText);
        }
    }
}

CellFormattingActions.vb

' Create a new worksheet.
Using sheet As IXlSheet = document.CreateSheet()
    ' Create the first column and set its width.
    Using column As IXlColumn = sheet.CreateColumn()
        column.WidthInPixels = 180
    End Using
    ' Create the first row.
    Using row As IXlRow = sheet.CreateRow()
        ' Create the cell A1.
        Using cell As IXlCell = row.CreateCell()
            ' Create an XlRichTextString instance.
            Dim richText As New XlRichTextString()
            ' Add three text runs to the collection. 
            richText.Runs.Add(New XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(&H53, &Hbb, &Hf4))))
            richText.Runs.Add(New XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(&Hf1, &H77, &H0))))
            richText.Runs.Add(New XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(&He3, &H2c, &H2e))))
            ' Add the rich formatted text to the cell. 
            cell.SetRichText(richText)
        End Using
    End Using
End Using