Epplus SetPosition picture issue

c#-4.0, epplus, excel

Solution

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

enter image description here

Problem

I am using Epplus library to generate Excel 2010 and up compatible files in Asp.Net C#. I am using version 3.1.2 which is the latest at this moment. I am setting the row height first, before adding any pictures like this: ``` ExcelPackage pck = new ExcelPackage(); var ws = pck.Workbook.Worksheets.Add("sheet 1"); while (i < dt.Rows.Count + offset) { ws.Row(i).Height = 84; i++; } ``` `dt` is my DataTable with DataRows. After setting the height, I am looping again through the rows to add the pictures ``` while (i < dt.Rows.Count + offset) { var prodImg = ws.Drawings.AddPicture(dr["code"].ToString(), new FileInfo(path)); prodImg.SetPosition(i - 1, 0, 14, 0); prodImg.SetSize(75); } ``` This works, but this does not: ``` var prodImg = ws.Drawings.AddPicture(dr["code"].ToString(), new FileInfo(path)); int w = prodImg.Image.Width; int h = prodImg.Image.Height; if (h > 140) // because height of 84 is 140 pixels in excel { double scale = h / 140.0; w = (int)Math.Floor(w / scale); h = 140; } int xOff = (150 - w) / 2; int yOff = (140 - h) / 2; prodImg.SetPosition(i - 1, xOff, 11, yOff); prodImg.SetSize(w, h); ``` This results in off center pictures and unresized images. And this code then which is in the same loop: ``` var prodImgDm = ws.Drawings.AddPicture("bcdm" + dr["code"].ToString(), new FileInfo(pathDm)); prodImgDm.SetPosition(i - 1, 25, 15, 40); prodImgDm.SetSize(100); ``` This does work sometimes. the pictures `prodImgDm` are datamatrix images with a static width and height and do not need to be resized because they are always small/tiny. So also without the `SetSize` in some rows, it works and in some other rows, it does not work. Really strange because the code is the same. It might be something in the library and/or Excel. Perhaps I am using it wrong? Any epplus picture expert? Thanks in advance!! edit sometimes a picture is worth a thousand words, so here is the screenshot. As you can see the product images are not horizontal and vertical aligned in the cell. And the datamatrix on the far right is sometimes scaled about 120% even when I set `SetSize(100)` so it is really strange to me. So the last datamatrix has the correct size... I already found this SO thread but that does not help me out, I think. edit 2013/04/09 Essenpillai gave me a hint to set ``` pck.DoAdjustDrawings = false; ``` but that gave me even stranger images: the datamatrix is still changing on row basis. on row is ok, the other is not. and the ean13 code is too wide.

Original source

Related problems