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:

enter image description here

But it looks like this in excel:

enter image description here

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.

@TusharChhabhaiya What do you mean by width? Did you mean the height of each row I am changing? — matthewr, Aug 04 '12 at 12:26
yes,In which unit you are changing height of row? mean px,cm,point — Tushar Chhabhaiya, Aug 06 '12 at 07:54
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. — matthewr, Aug 06 '12 at 08:00
How to insert a picture in Excel using format Image Base64? (EPPlus) — Магжан Куан, Jun 08 '16 at 05:55

score 44 · Accepted Answer · edited Apr 08 '13 at 10:20

44

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.

enter image description here

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

edited Apr 08 '13 at 10:20

Rob

4,927
12
49
54

answered Aug 09 '12 at 09:35

Jack7

1,310
12
16

Oh, I misread your post. Let me see if i can get the image to get into the rows itself. – Jack7 Aug 09 '12 at 09:36
Do you necessarily need the images to fit into a row? – Jack7 Aug 09 '12 at 09:38
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. – matthewr Aug 09 '12 at 09:41
But does this example stretch the image? I doesn't for me. – Jack7 Aug 10 '12 at 10:54
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. – matthewr Aug 10 '12 at 12:44
Ok. Try it out and let me know how it goes. – Jack7 Aug 10 '12 at 13:01
I still haven't tried it yet (its on another computer), I was really busy but I am able to try it tomorrow. Thanks. – matthewr Aug 11 '12 at 11:55
How to insert a picture in Excel using format Image Base64? (EPPlus) – Магжан Куан Jun 08 '16 at 05:56

score 18 · Answer 2 · edited Jul 29 '16 at 22:04

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

score 1 · Answer 3 · answered Aug 07 '12 at 23:20

1

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

answered Aug 07 '12 at 23:20

S3ddi9

2,121
2
20
34

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. – matthewr Aug 08 '12 at 05:27
@MatthewRz In place of logo.width and logo.height use width = logo.width * 72 / 96, and height = logo.height * 72 / 96. – Tushar Chhabhaiya Aug 10 '12 at 09:06
@TusharChhabhaiya It still stretches the images. – matthewr Aug 10 '12 at 09:41

user1125572 · Answer 4 · 2012-11-23T18:28:34.630

1

Add the following right before you save the document:

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

edited Nov 23 '12 at 18:28

answered Nov 21 '12 at 21:04

user1125572

56
3

That was the only thing that fixed my problem, I even tried changing the source code as suggested in http://stackoverflow.com/questions/15634709/epplus-setposition-picture-issue – Frank van Eykelen Jun 05 '14 at 14:59

score 1 · Answer 5 · edited Jul 29 '16 at 22:03

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

score 0 · Answer 6 · answered Aug 06 '12 at 10:07

0

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.

answered Aug 06 '12 at 10:07

Tushar Chhabhaiya

690
6
28

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. – matthewr Aug 06 '12 at 21:18
try to set row's height to 29.25 and check. – Tushar Chhabhaiya Aug 07 '12 at 05:19

Adding images into Excel using EPPlus

6 Answers6

Linked