EPPLUS图片位置能定位到单元格，图片的大小与单元格列宽行高的0.5倍，图片在单元格内居中。

Question

要用EPPLUS输出一个Excel文档，图片要求居中单元格内，大小是单元格
列宽行高的0.5倍。图片是浮动的，不能固定到某个单元格，但是代码能控制图片的位置，Excel列宽和行高单位是不一致的，思考过转换成像素，
代码水平不够弄不出来，有没有大佬能给点代码。。。居中思考的是
(单元格列宽-图片宽)/2
(单元格行高-图片高)/2

主要是转换像素和转换完之后的问题，代码这块水平比较缺，大佬们方便的话
能贴上代码最好了。

pic1.SetSize((int)imageWidth, (int)imageHeight);
pic1.SetPosition( 0,x,1,y);

大小和定位这里搞不来。。

图片效果

Answer 1

搞定了！！！我们自己封装了一个方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;

namespace Egem.Report
{
public class EPPlusPicture
{
public static void CreatePicture(ExcelWorksheet worksheet, string name, Image image, int firstColumn, int lastColumn, int firstRow, int lastRow, int defaultOffsetPixels)
{
int columnWidth = GetWidthInPixels(worksheet.Cells[firstRow, firstColumn]);
int rowHeight = GetHeightInPixels(worksheet.Cells[firstRow, firstColumn]);

        int totalColumnWidth = columnWidth * (lastColumn - firstColumn + 1);
        int totalRowHeight = rowHeight * (lastRow - firstRow + 1);
        double cellAspectRatio = Convert.ToDouble(totalColumnWidth) / Convert.ToDouble(totalRowHeight);

        int imageWidth = image.Width;
        int imageHeight = image.Height;
        double imageAspectRatio = Convert.ToDouble(imageWidth) / Convert.ToDouble(imageHeight);

        int pixelWidth;
        int pixelHeight;
        if (imageAspectRatio > cellAspectRatio)
        {
            pixelWidth = totalColumnWidth - defaultOffsetPixels * 2;
            pixelHeight = pixelWidth * imageHeight / imageWidth;
        }
        else
        {
            pixelHeight = totalRowHeight - defaultOffsetPixels * 2;
            pixelWidth = pixelHeight * imageWidth / imageHeight;
        }

        int rowOffsetPixels = (totalRowHeight - pixelHeight) / 2;
        int columnOffsetPixels = (totalColumnWidth - pixelWidth) / 2;

        int rowOffsetCount = 0;
        int columnOffsetCount = 0;

        if (rowOffsetPixels > rowHeight)
        {
            rowOffsetCount = (int)Math.Floor(Convert.ToDouble(rowOffsetPixels) / Convert.ToDouble(rowHeight));
            rowOffsetPixels -= rowHeight * rowOffsetCount;
        }

        if (columnOffsetPixels > columnWidth)
        {
            columnOffsetCount = (int)Math.Floor(Convert.ToDouble(columnOffsetPixels) / Convert.ToDouble(columnWidth));
            columnOffsetPixels -= columnWidth * columnOffsetCount;
        }

        int row = firstRow + rowOffsetCount - 1;
        int column = firstColumn + columnOffsetCount - 1;

        ExcelPicture pic = worksheet.Drawings.AddPicture(name, image);
        pic.SetPosition(row, rowOffsetPixels, column, columnOffsetPixels);
        pic.SetSize(pixelWidth, pixelHeight);
    }

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

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

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

}