CODEWITHSUNDEEP
c#excelepplus
Tim
Tim

Reputation: 20360

(C#) Can I programmatically set an XLSX cell to a picture/image?

I am hoping to make spreadsheets that contain some pictures (embed pictures from files) and I started looking at EPPlus (looks like a great library)

However it seems that the images are not tied to a cell - rather to an x,y, coordinate.

Is there a way with EPPlus or other way to set a cell to a picture (and then manipulate the size of the cell?)

SetPosition

Upvotes: 5

Views: 7234

Answers (4)

Maarten van Dijk
Maarten van Dijk

Reputation: 1

Found in the EPPLUS documentation it should be possible with the EditAs setting on value TwoCell.

picture.EditAs = eEditAs.TwoCell;

"Specifies that the current drawing shall move and resize to maintain its row and column anchors (i.e. the object is anchored to the actual from and to row and column). "

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

You can insert the picture, then adjust its .Top and .Left so it aligns with the .Top and .Left of the appropriate cell. You can set the .RowHeight of the cell's row using the same units as the .height of the picture (though there's a maximum height). The .ColumnWidth of the column is in units of text characters wide, so what I do is something like:

myColumn.ColumnWidth = myColumn.ColumnWidth / myColumn.Width * myPicture.Width

and I run it twice because sometimes the first time you set .ColumnWidth, it isn't set precisely.

Upvotes: 3

Tim
Tim

Reputation: 20360

My misunderstanding...

Here is a comment I found when looking around:

No version of Excel allows you to insert a picture into a cell. Pictures are inserted into the worksheet and will always float. One of the properties of a picture can be set to "move and size with cells" but that only moves or stretches the picture when the underlying rows and columns are inserted, deleted or sized. It does not confine a picture to a cell.

So perhaps I just need to set the properties appropriately.

If I can do this programmatically I will be all set

EDIT

The following code does pretty much what I want/need.

Note that before inserting the pics I set the width and height of the cell I was overlaying to appropriate sizes.

private static void AddImage(ExcelWorksheet ws, int rowIndex, String imageFile)
{
    ExcelPicture picture = null;
    Bitmap image = new Bitmap(imageFile);

    if (image != null)
    {
        picture = ws.Drawings.AddPicture("pic" + rowIndex.ToString(), image);                
        picture.From.Column = 0;
        picture.From.Row = rowIndex-1;
        picture.SetSize(320, 240);
    }
}

Upvotes: 5

CodingGorilla
CodingGorilla

Reputation: 19842

I don't think you can do that in Excel itself; when you add a picture to an Excel worksheet, it's a floating object, it's not fixed to a specific cell.

Upvotes: 1

Related Questions