AutoFitColumns is device DPI dependent

[CaringDev]

Update, TL;DR;

Graphics.MeasureString used here is device DPI dependent while AutoFitColumns should assume 96 ppi. The fix would be to scale the measurement (see e.g. this SO post).

History

AutoFitColumns in the following code is dependent on whether we create a WPF window first (because that initializes device DPI awareness). I expected EPPlus to auto fit columns 'properly' regardless of "global state". Is there a known workaround?

Env: EPPlus 5.6.3, dotnet core 5 (SDK: 5.0.202), Windows 10 20H2

using System;
using System.IO;
using System.Windows;
using OfficeOpenXml;

namespace EPP.Test
{
  public class Program
  {
    [STAThread]
    public static void Main(string[] args)
    {
      ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

      // This is the culprit
      // new Window();

      using var package = new ExcelPackage(new MemoryStream());
      var sheet = package.Workbook.Worksheets.Add("Foo");
      sheet.Cells[1, 1].Value = "Some longer random string";
      sheet.Cells.AutoFitColumns();

      // normal: 25.29 (fits perfectly)
      // with new Window: 37.57 (too large)
      var resizedWidth = sheet.Column(1).Width;
    }
  }
}

[JanKallman]

Running this code in a wpf app using .NET 5 seems to work fine, so I am unsure how you are setup. Can you zip your test project and attach it here?

[CaringDev]

Wow, this is crazy... I created a repro that does not repro... but does on my machine:

I will have to investigate further (how 😬)...

Thanks for the great library and your quick response ❤

[CaringDev]

Ah, found something: I'm on a high resolution monitor and have set DPI-Scaling to 150%. If changed back like so: everything is back to 👌

[CaringDev]

For now I'm resorting to [assembly: DisableDpiAwareness]... however, changing AutoFit to be DPI independent would probably be great (as otherwise apps using EPPlus' AutoFitColumn look rather ugly on high DPI screens). The culprit is, that Graphics.MeasureString is device dependent when for the column width we should assume 96 ppi. Would you consider merging a corresponding PR (see this SO post)?

[JanKallman]

Thanks for the update. Sounds correct that 96 dpi should be assumed. I will look into it.

[JanKallman]

I could reproduce the issue and I have added a fix. You can try it using our develop Nuget feed...https://github.com/EPPlusSoftware/EPPlus/wiki/Using-our-Develop-Nuget-Feed

[CaringDev]

Thanks, 5.6.3.603-20210506-develop works for me too 👌