This page should help resolve the exception about a missing font:
Unable to find font font name or fallback font fallback font name. Install missing fonts or specify a different fallback font through ‘LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine(“Fallback font name”)’.
The exception was most likely thrown during a call of
on Linux or some Cloud service that doesn’t contain Windows fonts (e.g. AWS
lambda functions, Azure functions).
For more details about how the engine works and available API, see graphic engine page.
Explanation of the problem
Graphic engine is responsible for extracting information about images and measuring size of a text in a workbook. The exception indicates that ClosedXML (more precisely SixLabors.Fonts that is used by the default engine) wasn’t enable to find a font that could be used to measure size of a text.
Calculating a correct size of a text rendered by a font is a nontrivial task and there is a dearth of maintained open source .NET font libraries. That is why ClosedXML depends on prerelease version of SixLabors.Fonts.
DefaultGraphicEngine tries to use a requested font (e.g. font of a cell)
and if the requested font isn’t present on the machine, it will use fallback
font (Microsoft Sans Serif). If not even fallback font is found, it throws
the aforementioned exception.
It is responsibility of developer to ensure that required font/s are present. ClosedXML needs font metrics and substitution tables to calculate text bounds in the font. It can’t pull them out of thin air.
SixLabors.Fonts library looks for fonts in the following folders (source):
The default font for a xlsx is a Calibri font. That are two problems with Calibri:
Calibri is a copyrighted font - it can’t be bundled, converted and has a plethora of restrictions.
Calibri isn’t present on non-windows machines.
The consequence is that files that used created on non-windows likely won’t have accurately calculated text width due to incorrect font metrics of the fallback font.
You can either
Install font on the environment
Bundle a font with the application (only for version 0.100+)
Use a wrapper.
Use already installed font
If you don’t care much about accuracy, pick already installed font and instruct ClosedXML to use that one as a fallback font. That is basically the advice from the exception message.
First, get a list of fonts that are available on the target environment
foreach (var fontFamily in SixLabors.Fonts.SystemFonts.Collection.Families)
Pick already installed font that looks similar to Calibri (probably slightly wider to avoid clipping or #### signs). In the setup part of your application, choose a font that will be used as a fallback font.
// "Fallback font name" will likely be something like "DejaVu Sans" or "Tahoma"
// It is not a path to font file, but a font name.
LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine("Fallback font name");
Use a font similar to Calibri (likely Carlito)
Find a font that looks similar to Calibri. Similarity means character widths and heights, not the shape of letters.
The most likely candidate is a Carlito font:
Carlito (Google’s Carlito font, google-crosextrafonts-carlito) is a modern, friendly sans-serif font, metric-compatible with Microsoft Fonts Calibri font. Carlito comes in regular, bold, italic, and bold italic. The family covers Latin-Greek-Cyrillic (not a complete set, though) with about 2,000 glyphs. It has the same character coverage as Microsoft Fonts Calibri. This font is sans-serif typeface family based on Lato. Carlito is a default Calibri font replace in the LibreOffice Suite.
The key part is metric-compatible with Microsoft Fonts Calibri. Note that the claim is only valid for larger sizes, Calibri has a bitpoint maps that are used at smaller sizes (up to 19 pts). It is not exactly the same, but close enough and the important thing is that Carlito metrics is slighly wider (letter M, numbers) than Calibri bitmap sizes. Thanks to that, the text in cells won’t be clipped or displayed as ####).
Install the selected font on the target environment or bundle the font with the application.
using (var fallbackFontStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("Carlito.ttf"))
// Carlito font will be used for everything, since it is the fallback font and no other font was loaded
LoadOptions.DefaultGraphicEngine = DefaultGraphicEngine.CreateOnlyWithFonts(fallbackFontStream);
Install Windows fonts
Install Calibri/Microsofot Sans Serif font to the target environment.
ClosedXML will use correct font metrics
Requires a license.
In some cases, it is not possible to install fonts (e.g. Azure Functions)
Font must be present on all target environments
Bundle font (version 0.100+)
Bundle a font with the application (either as an embedded resource or in an installation
directory) and create
DefaultGraphicEngine with a stream of bundled font.
using (var fallbackFontStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("Namespace.SomeFont.ttf"))
LoadOptions.DefaultGraphicEngine = DefaultGraphicEngine.CreateWithFontsAndSystemFonts(fallbackFontStream);
DefaultGraphicEngine constructor can accept multiple font streams, so
it is possible to load fonts that are actually used in the workbooks, not just
fallback font. That is rather useful for client-side Blazor.
Width of columns and height of row can be set explicitely. Row has its height expressed in points, width of column is expressed in multiples of Maximum Digit Width (MDW). MDW is a maximum width of a any digit (0-9) in pixels. If column has width 12.5, it means 12.5 * width_of_widest_0-9_digit.
For more information about how does Excel calculate width of a cell during
AdjustToContent, see Cell Dimension Dev documentation.
Not recommended, but you can create a new implementation of
DefaultGraphicEngine.Instance.Value.GetPictureInfo and implement font
measurement methods using only the EM square size (IXLFontBase.FontSize is
always passed as a parameter) or some hard coded values.