CODEWITHSUNDEEP
c#epplus
matthewr
matthewr

Reputation: 4739

Adding images into Excel using EPPlus

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.

Upvotes: 32

Views: 62104

Answers (6)

Jhonny Nina
Jhonny Nina

Reputation: 589

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

Upvotes: 19

Bharat Pandey
Bharat Pandey

Reputation: 11

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

Upvotes: 1

Jack7
Jack7

Reputation: 1341

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.

Upvotes: 45

user1125572
user1125572

Reputation: 56

Add the following right before you save the document:

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

Upvotes: 1

S3ddi9
S3ddi9

Reputation: 2151

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

Upvotes: 1

Tushar Chhabhaiya
Tushar Chhabhaiya

Reputation: 700

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.

Upvotes: 0

Related Questions