Autofit columns

The ability to adjust column widths to fit the widest text of the column has been around in EPPlus for a long time, but it has been rewritten in EPPlus 6. In previous versions this functionality, which requires measurements of text string, has been relying on the System.Drawing library which is the graphics library that is included in .NET). From .NET 6 Microsoft has deprecated this library on non-Windows platforms which is the main reason for the new implementation.

Simple example

Here is a simple visualization of the autofit column functionality.

1. Before autofit columns

2. After autofit columns

Accuracy and Excel compatibility

In spreadsheet applications like Excel you would autofit the column width by double-clicking the gridline between two column labels. Please note this is a client-side technology that might render different results on different computers. For this reason it is impossible to achieve a perfect column width that will appear the same on all types of clients, regardless if the System.Drawing classes are used or not.

EPPlus 6 implementation

The new implementation for text measurements in EPPlus does not rely on the underlying O/S. Instead it is built approximations of the width of each character in a number of fonts in combinations with scaling factors. Based on our own testing this gives similar results as the previous functionality based on System.Drawing. On Windows platforms System.Drawing will be used for fonts not covered by the default functionality. If you prefer you can also configure EPPlus to always use System.Drawing/gdi/libgdiplus.

TextSettings

There is a new property in EPPlus 6 that you can use to configure the text measurements functionality. Here is how you access this property:

using var package = new ExcelPackage();
var textSettings = package.Settings.TextSettings;

This property is of the class ExcelTextSettings and it has a few properties where some of them are of the type OfficeOpenXml.Interfaces.Drawing.Text.ITextMeasurer. The ITextMeasurer interface is from a new Nuget package that contains interfaces and abstract classes used in EPPlus, see https://www.nuget.org/packages/EPPlus.Interfaces/

Here is a list of properties on ExcelTextSettings:

Property	Data type	Default value	Description
AutofitScaleFactor	float	1f	Can be used to adjust the results of AutofitColumns(). A value of 1.1f will make all measurements 10% wider, a value of 0.9f will make them 10% narrower.
PrimaryTextMeasurer	ITextMeasurer		The primary text measurer, i.e. the ITextMeasurer that EPPlus will use first. This is per default the new functionality in EPPlus 6 which will measure the text without using System.Drawing.
FallbackTextMeasurer	ITextMeasurer		Will be used by EPPlus if the primary text measurer fails. This is per default a text measurer that uses System.Drawing.
DefaultTextMeasurer	ITextMeasurer		Will be used by EPPlus if both the PrimaryTextMeasurer and FallbackTextMeasurer fails. This is per default a text measurer that uses the new internal functionality in EPPlus 6 and Excels default font to measure the text.