weixin_44202887
2019-02-16 17:58
采纳率: 100%
浏览 1.8k

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

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

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

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

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

图片效果

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • weixin_44202887 2019-02-19 17:28
    已采纳

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

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

    }

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • Dadamia 2019-02-18 14:41

    参考:

    using Spire.Xls;
    using System.Drawing;
    
    namespace InsertImage_XLS
    {
        class Program
        {
            static void Main(string[] args)
            {
                //创建Workbook对象
                Workbook workbook = new Workbook();  
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                //指定列宽、行高
                sheet.Columns[0].ColumnWidth = 50;
                sheet.Rows[0].RowHeight = 160;
    
                //加载图片,添加到指定单元格
                ExcelPicture picture = sheet.Pictures.Add(1,1, "tp.png");
                //指定图片宽度和高度
                picture.Width = 300;
                picture.Height = 180;
    
                //通过LeftColumnOffset和TopRowOffset属性值设置图片在单元格中的横向、纵向对齐(位置)
                picture.LeftColumnOffset = 75;
                picture.TopRowOffset = 20;
    
                //保存文档
                workbook.SaveToFile("result.xlsx", FileFormat.Version2013);
                System.Diagnostics.Process.Start("result.xlsx");
            }
        }
    }
    
    

    效果图:
    图片说明

    (注:这里是用的Excel控件
    Spire.XLS)

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题