EPPlus: Position image in a cell

Question

I am trying to insert images "into" a cell in excel using Epplus.

using the following code

private static void SetImage(ExcelWorksheet sheet, ExcelRange cell)
{
    using (WebClient client = new WebClient())
    using (Stream stream = client.OpenRead("https://..."))
    using (Bitmap bitmap = new Bitmap(stream))
    {
        var picture = sheet.Drawings.AddPicture(Guid.NewGuid().ToString(), bitmap);
        picture.From.Column = cell.Start.Column - 1;
        picture.From.Row = cell.Start.Row - 1;

        picture.To.Column = cell.Start.Column;
        picture.To.Row = cell.Start.Row;
    }
}

-

var cell = sheet.Cells[2, 2];
SetImage(sheet, cell);

cell = sheet.Cells[3, 2];
SetImage(sheet, cell);

However it always seems to have an overlap to the right.

If I adjust the cell widths and heights the overlap changes but never disappears

Answer 1

So I abandoned the

picture.To.Column = cell.Start.Column;
picture.To.Row = cell.Start.Row;

since I just could not get it to work and decided to calculated my own dimensions using:

picture.SetSize(width, height);

The trick is to understand how Excel actually calculates widths and heights.

Height of a cell: Its measured in points, but we want pixels. There are 72 points in an inch. One can convert points to pixel using the following formula points* (1/72.0) * DPI. DPI is dots per inch and can be found using the following method:

using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
    float dpiY = graphics.DpiY;
}

So to calculate the height of a cell in pixels I used

private static int GetHeightInPixels(ExcelRange cell)
{
    using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
    {
        float dpiY = graphics.DpiY;
        return (int)(cell.Worksheet.Row(cell.Start.Row).Height * (1 / 72.0) * dpiY);
    }
}

Width of a cell: This is a bit trickier. Basically the width of a cell in excel is equal to the number of characters (formatted using the default font) that a cell can contain horizontally.

For example

This colum is of length 12 and can contain 12 numbers in the Calibri(11) font.

That is also my excel default since my body default is calibri(11)

Here is an article explaining it in more depth.

The next question is how on earth does one translate that to pixels.

Firstly we need to discover what the length of a character is in the default font. One could use TextRenderer.MeasureText in the System.Windows.Forms namespace. However I am using .Net Core and needed another way. Another way is to use the the System.Drawing.Common core lib which is now in preview.

public static float MeasureString(string s, Font font)
{
    using (var g = Graphics.FromHwnd(IntPtr.Zero))
    {
        g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;

        return g.MeasureString(s, font, int.MaxValue, StringFormat.GenericTypographic).Width;
    }
}

I then used that method to calculate the width in pixels as follows

private static int GetWidthInPixels(ExcelRange cell)
{
    double columnWidth = cell.Worksheet.Column(cell.Start.Column).Width;
    Font font = new Font(cell.Style.Font.Name, cell.Style.Font.Size, FontStyle.Regular);

    double pxBaseline = Math.Round(MeasureString("1234567890", font) / 10);

    return (int)(columnWidth * pxBaseline);
}

Edit: Please note that overlap still happens when the zoom factor is set to more than 100% under display settings