Adding images into Excel using EPPlus

Question

I am trying to add the same image multiple times into an excel file using EPPlus. I am using the following code to do so:

Image logo = Image.FromFile(path);
ExcelPackage package = new ExcelPackage(info);
var ws = package.Workbook.Worksheets.Add("Test Page");
for(int a = 0; a < 5; a++)
{
    ws.Row(a*5).Height = 39.00D;
    var picture = ws.Drawings.AddPicture(a.ToString(), logo);
    picture.SetPosition(a*5, 0, 2, 0);
}

Everything works perfectly and all the images are correctly added but they are stretched downwards. Here is what one of the pictures should look like:

But it looks like this in excel:

I have to resize each row of the start of each picture but I dont think that would be affecting it. Would there be a way to add the pictures/do what I am trying to do or would I have to copy-paste the images in manually? (I am using the picture as an example)

Thanks.

Zeniazenith

you are proving width in which unit? – Pacific

@TusharChhabhaiya What do you mean by width? Did you mean the height of each row I am changing? – Zeniazenith

yes,In which unit you are changing height of row? mean px,cm,point – Pacific

I am not sure which one it is, but if you go into excel and re-size a row it displays: Height: ##.## (## Pixels). I am changing the first measurement: Height: 39.00. I hope this makes sense. – Zeniazenith

How to insert a picture in Excel using format Image Base64? (EPPlus) – Kanara

Answer 1

I'm not sure if this is the best solution but definetly a workaround for your problem.

Here's what I did:

ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Test Page");

for (int a = 0; a < 5; a++)
{
    ws.Row(a * 5).Height = 39.00D;
}

for (int a = 0; a < 5; a++)
{
    var picture = ws.Drawings.AddPicture(a.ToString(), logo);
    picture.SetPosition(a * 5, 0, 2, 0);
}

Here is how it looks.

For some reason when we have the row height set, its interfering with the picture height.

Thomasinathomasine

Oh, I misread your post. Let me see if i can get the image to get into the rows itself. – Thomasinathomasine

Do you necessarily need the images to fit into a row? – Thomasinathomasine

The only reason I need to change the row height is because on my actual excel sheet there is text on the same row the image is placed. I am just using this image as a placeholder. – Zeniazenith

But does this example stretch the image? I doesn't for me. – Thomasinathomasine

It stretched for me but I think that was because I was testing another answer at the same time. I will try it out properly tomorrow but award you the bounty now since it works for you, and if I don't award it then it will disappear. – Zeniazenith

Ok. Try it out and let me know how it goes. – Thomasinathomasine

I still haven't tried it yet (its on another computer), I was really busy but I am able to try it tomorrow. Thanks. – Zeniazenith

How to insert a picture in Excel using format Image Base64? (EPPlus) – Kanara

Answer 2

This is one solution that you can apply in C#.

private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath)
{
    Bitmap image = new Bitmap(imagePath);
    ExcelPicture excelImage = null;
    if (image != null)
    {
        excelImage = oSheet.Drawings.AddPicture("Debopam Pal", image);
        excelImage.From.Column = colIndex;
        excelImage.From.Row = rowIndex;
        excelImage.SetSize(100, 100);
        // 2x2 px space for better alignment
        excelImage.From.ColumnOff = Pixel2MTU(2);
        excelImage.From.RowOff = Pixel2MTU(2);
    }
}

public int Pixel2MTU(int pixels)
{
    int mtus = pixels * 9525;
    return mtus;
}

Shawnshawna

Answer 3

try this

Image logo = Image.FromFile(path);
ExcelPackage package = new ExcelPackage(info);
var ws = package.Workbook.Worksheets.Add("Test Page");
for(int a = 0; a < 5; a++)
{
    ws.Row(a*5).Height = 39.00D;
    var picture = ws.Drawings.AddPicture(a.ToString(), logo);
    // xlMove disables the auto resizing
    picture.Placement = xlMove; //XLPlacement : xlMoveAndSize,xlMove,xlFreeFloating
    picture.SetPosition(a*5, 0, 2, 0);
}

or

Image logo = Image.FromFile(path);
ExcelPackage package = new ExcelPackage(info);
var ws = package.Workbook.Worksheets.Add("Test Page");
for(int a = 0; a < 5; a++)
{
    ws.Row(a*5).Height = 39.00D;
    var picture = ws.Drawings.AddPicture(a.ToString(), logo);
    picture.From.Column = 0;
    picture.From.Row = a;
    picture.SetSize(120, 150);
}

Gallantry

For the first one picture.Placement does not exist. The second one stretches the image a different way; I tried: picture.SetSize(logo.Width, logo.Height) and the pictures still stretch downwards. – Zeniazenith

@MatthewRz In place of logo.width and logo.height use width = logo.width * 72 / 96, and height = logo.height * 72 / 96. – Pacific

@TusharChhabhaiya It still stretches the images. – Zeniazenith

Answer 4

Add the following right before you save the document:

foreach (ExcelPicture drawing in ws.Drawings)
   drawing.SetSize(100);

Vellum

That was the only thing that fixed my problem, I even tried changing the source code as suggested in #15635209 – Beatification

Answer 5

use the below code to adjust the image in an excel cell:

        Image logo = Image.FromFile(path);
        ExcelPackage package = new ExcelPackage(info);
        var ws = package.Workbook.Worksheets.Add("Test Page");
        for(int a = 0; a < 5; a++)
        {
           ws.Row(a*5).Height = 39.00D;
           var picture = ws.Drawings.AddPicture(a.ToString(), logo);
           picture.From.Column = 0;
           picture.From.Row = a;
           picture.To.Column=0;//end cell value
           picture.To.Row=a;//end cell value
           picture.SetSize(120, 150);
        }

Anglicism

Answer 6

when you are passing say example 39 as pixel it will take it as point insted of pixel internally, so you think that you are going to set row's height to 39 pixel but actually it is setting row's height to 39 point. so according to following formula your row height will became 52 pixel.

If you want to set row's height to 39px, mean you have to pass 29.25 Point(according to formula) insted of 39.

points = pixels * 72 / 96

Try this one.

Pacific

I am re-creating an excel template and the row's height is 39 point. I have to re-create it as close as I can and my issue is the image stretching down. – Zeniazenith

try to set row's height to 29.25 and check. – Pacific