How to: Load and Use Custom Fonts in the Spreadsheet Control

• Oct 27, 2021
• 3 minutes to read

The WinForms Spreadsheet control ships with the FontRepository class that allows you to use fonts that are not installed on the current operating system. When you load a document that uses such fonts, the Spreadsheet substitutes missing fonts with the fonts available on the current machine. The FontRepository class allows you to load and use custom fonts in your application to prevent font substitution when documents are displayed, printed, or exported to PDF.

Use the FontRepository.Instance static property to access a FontRepository instance.

Add Fonts to the Font Repository

Use the FontRepository.AddFont method overloads to add fonts to the font repository. You can load a font from a file, stream, or byte array. Loaded fonts are available to all SpreadsheetControl instances within your project.

Supported font formats include:

• TrueType fonts (.TTF)

• OpenType fonts that use CFF (Compact Font Format) glyph outlines (.OTF)

• OpenType Font Collections (.TTC, .OTC) that contain multiple fonts in a single file

To avoid excessive document layout recalculations, add the required fonts to FontRepository before you load a document that uses these fonts.

The code sample below adds the following Google fonts fonts to FontRepository and uses these fonts to format cells in a workbook:

• Advent Pro
• Roboto

C#

using DevExpress.Office;
using DevExpress.Spreadsheet;

namespace WinFormsSpreadsheet
{
    public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm
    {
        public Form1()
        {
            InitializeComponent();

            FontRepository.Instance.AddFont(@"Fonts\advent-pro.regular.ttf");
            FontRepository.Instance.AddFont(@"Fonts\Roboto-Light.ttf");

            IWorkbook workbook = spreadsheetControl1.Document;
            workbook.LoadDocument(@"Documents\SalesReport.xlsx", DocumentFormat.Xlsx);

            Worksheet worksheet = workbook.Worksheets["Report"];

            // Return the cell that contains the document title.
            Cell cell = worksheet.Cells["B2"];

            // Specify font settings (font name and size).
            cell.Font.Name = "Advent Pro";
            cell.Font.Size = 26;

            // Return the cell range that contains report data.
            CellRange range = worksheet.Range["B4:F20"];

            // Start range format update. 
            var rangeFormatting = range.BeginUpdateFormatting();
            // Specify font settings (font name and size).
            rangeFormatting.Font.Name = "Roboto Light";
            rangeFormatting.Font.Size = 14;
            // End range format update. 
            range.EndUpdateFormatting(rangeFormatting);
        }
    }
}

VB.NET

Imports DevExpress.Office
Imports DevExpress.Spreadsheet

Namespace WinFormsSpreadsheet
    Public Class Form1
        Inherits DevExpress.XtraBars.Ribbon.RibbonForm

        Public Sub New()
            InitializeComponent()

            FontRepository.Instance.AddFont("Fonts\advent-pro.regular.ttf")
            FontRepository.Instance.AddFont("Fonts\Roboto-Light.ttf")

            Dim workbook As IWorkbook = spreadsheetControl1.Document
            workbook.LoadDocument("Documents\SalesReport.xlsx", DocumentFormat.Xlsx)

            Dim worksheet As Worksheet = workbook.Worksheets("Report")

            ' Return the cell that contains the document title.
            Dim cell As Cell = worksheet.Cells("B2")

            ' Specify font settings (font name and size).
            cell.Font.Name = "Advent Pro"
            cell.Font.Size = 26

            ' Return the cell range that contains report data.
            Dim range As CellRange = worksheet.Range("B4:F20")

            ' Start range format update. 
            Dim rangeFormatting As Formatting = range.BeginUpdateFormatting()
            ' Specify font settings (font name and size).
            rangeFormatting.Font.Name = "Roboto Light"
            rangeFormatting.Font.Size = 14
            ' End range format update. 
            range.EndUpdateFormatting(rangeFormatting)
        End Sub
    End Class
End Namespace

The following image demonstrates the result:

Obtain Information About Fonts in the Repository

Use the FontRepository.GetFonts method to return a list of all fonts in the font repository. Each item in this list is a FontData object that contains font information.

Clear the Font Repository

Call the FontRepository.Clear method to remove all fonts from the font repository. Use the FontRepository.IsEmpty property to check whether the repository contains fonts.

C#

using DevExpress.Office;
// ...

// Check whether the font repository is empty.
if (!FontRepository.Instance.IsEmpty)
    // If false, clear the font repository.
    FontRepository.Instance.Clear();

VB.NET

Imports DevExpress.Office
' ...

' Check whether the font repository is empty.
If Not FontRepository.Instance.IsEmpty Then
    ' If false, clear the font repository.
    FontRepository.Instance.Clear()
End If

Limitations

1. Loaded fonts are not saved to a document. These fonts are used to display the document in the control’s UI, print the document, or export the document to PDF.

2. Loaded fonts are not displayed in the drop-down list of the Spreadsheet control’s font editor.