EPPlus:在单元格中定位图像 [英] EPPlus: Position image in a cell

问题描述

我正在尝试使用 Epplus 将图像插入"Excel 中的单元格.

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

使用以下代码

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

推荐答案

所以我放弃了

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);

诀窍是了解 Excel 如何实际计算宽度和高度.

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

单元格的高度:它以磅为单位，但我们需要像素.一英寸有 72 个点.可以使用以下公式将点数转换为像素点 * (1/72.0) * DPI.DPI 是每英寸的点数，可以使用以下方法找到:

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);
    }
}

单元格的宽度:这有点棘手.基本上，excel中单元格的宽度等于单元格可以水平包含的字符数(使用默认字体格式化).

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.

例如

该列的长度为 12，可以包含 12 个 Calibri(11) 字体的数字.

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

这也是我的 excel 默认值，因为我的身体默认值是 calibri(11)

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.

首先我们需要发现默认字体中字符的长度是多少.可以在 System.Windows.Forms 命名空间中使用 TextRenderer.MeasureText.但是我正在使用 .Net Core 并且需要另一种方式.另一种方法是使用现在处于预览状态的 System.Drawing.Common 核心库.

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);
}

请注意，在显示设置下将缩放系数设置为 100% 以上时，仍然会发生重叠

这篇关于EPPlus:在单元格中定位图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！