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 strings, 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.

var sheet = package.Workbook.Worksheets["MySheet"];
// entire worksheet
sheet.Cells.AutofitColumns();
// a column
sheet.Columns[1].AutoFit();
// a specific range
sheet.Cells["A1:B3"].AutofitColumns();
// a table
tableRange = sheet.Cells[1, 1].LoadFromCollection(tableData2, true, TableStyles.Light1);
tableRange.AutoFitColumns();

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 with GDI support System.Drawing will still be used. For other platforms EPPlus will use its internal text measurer.

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. For windows platforms with support for GDI this is per default using System.Drawing. For other platforms (Linux, Blazor, Windows Nanoserver, etc) the FallbackTextMeasurer will be used.
FallbackTextMeasurer	ITextMeasurer		Will be used by EPPlus if the primary text measurer fails. This is per default EPPlus internal text measurer
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.

Use a custom ITextMeasurer

In some cases it might be useful to create a custom text measurer for EPPlus. One scenario could be that you are on a non-GDI platform and have a set of fonts that EPPlus doesn't provide accurate measurents for with its internal measurer. Here is an example using the SkiaSharp library.

1. Create the text measurer implementation

1. Add a reference to the EPPlus.Interfaces Nuget package.
2. Add a reference to the SkiaSharp Nuget package.
3. Create a class that implements the OfficeOpenXml.Interfaces.Drawing.Text.ITextMeasurer interface.

using OfficeOpenXml.Interfaces.Drawing.Text;
using SkiaSharp;

namespace AutofitSkiaTest
{
    internal class SkiaSharpTextMeasurer : ITextMeasurer
    {
        private SKFontStyle ToSkFontStyle(MeasurementFontStyles style)
        {
            switch (style)
            {
                case MeasurementFontStyles.Regular:
                    return SKFontStyle.Normal;
                case MeasurementFontStyles.Bold:
                    return SKFontStyle.Bold;
                case MeasurementFontStyles.Italic:
                    return SKFontStyle.Italic;
                case MeasurementFontStyles.Bold | MeasurementFontStyles.Italic:
                    return SKFontStyle.BoldItalic;
                default:
                    return SKFontStyle.Normal;
            }
        }

        public TextMeasurement MeasureText(string text, MeasurementFont font)
        {
            var skFontStyle = ToSkFontStyle(font.Style);
            var tf = SKTypeface.FromFamilyName(font.FontFamily, skFontStyle);
            using (var paint = new SKPaint())
            {
                paint.TextSize = font.Size;
                paint.Typeface = tf;
                var rect = SKRect.Empty;
                paint.MeasureText(text.AsSpan(), ref rect);
                // The scaling factors below are numbers that we added to get this example right.
                // We don't know how these works with other fonts and can not guarantee that this is the best way to translate
                // SkiaSharps measurements to Excels column widths.
                return new TextMeasurement(rect.Width / 0.7282505F + (0.444444444F * font.Size), rect.Height * (96F / 72F));
            }

        }

        public bool ValidForEnvironment()
        {
            // you can use this method to check if your text measurer
            // works in the current environment.
            return true;
        }
    }
}

2. Use the custom text measurer with EPPlus

See the console app below.

using AutofitSkiaTest;
using OfficeOpenXml;

Console.WriteLine("Autofit columns!");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
    var skiaMeasurer = new SkiaSharpTextMeasurer();

    var sheet = package.Workbook.Worksheets.Add("Test");
    sheet.Cells["A1"].Value = "A long text that needs autofit...";
    // Autofit the A column using EPPlus default measurer.
    sheet.Columns[1].AutoFit();
    // Replace the primary text measurer with our custom SkiaSharp measurer
    package.Settings.TextSettings.PrimaryTextMeasurer = skiaMeasurer;
    sheet.Cells["B1"].Value = "A long text that needs autofit...";
    // Autofit the B column - now using our custom measurer
    sheet.Columns[2].AutoFit();

    var width1 = sheet.Columns[1].Width; // 28.03
    var width2 = sheet.Columns[2].Width; // 27.88
}