Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert picture into excel cell?

My task is to put picture from URL into certain cell in excel worksheet. I'm using NetOffice with C# to do this.

My main problem though is that I cannot find a way to insert a picture exactly into cell. When I use Sheet.Shapes.AddPicture(), I have to calculate coordinates where to put my picture. Of course, I have no problem with that (I created some kind of workaround), however, I'd like to ask whether my approach to solve this problem is right or if there is some other method where I could insert image into cell.

Here is my workaround:

var floatLeft = FloatLeftPixelsCalculation(rowNumber);
var floatTop = FloatTopPixelsCalculation(rowNumber);
Worksheet.Shapes.AddPicture(urlCellValue, MsoTriState.msoFalse, MsoTriState.msoTrue, floatLeft, floatTop, PictureWidth, PictureHeight);

public float FloatTopPixelsCalculation(int rowNumber)
        {
            float floatTop = 0;
            for (var rNumber = 1; rNumber < rowNumber; rNumber++)
            {
                var cellHeight = Convert.ToSingle(Worksheet.Cells[rNumber, ColumnIndex].RowHeight);
                floatTop = floatTop + cellHeight;
            }

            return floatTop;
        }

        public float FloatLeftPixelsCalculation(int rowNumber)
        {
            float floatLeft = 0;
            for (var columnNumber = 1; columnNumber < ColumnIndex; columnNumber++)
            {
                var cellWidth = Convert.ToSingle(Worksheet.Cells[rowNumber, columnNumber].ColumnWidth);
                floatLeft = floatLeft + cellWidth;
            }

            return floatLeft;
        }
like image 954
zmockus Avatar asked Nov 14 '22 03:11

zmockus


1 Answers

Although Doug Glancy is correct in stating that Excel does not consider a picture to be "inside" of a cell, a picture can be associated with a particular cell and will act intuitively if correctly placed.

For example, say you have a list of houses. Columns A-Y have various information about the house, and column Z has a picture of the house. If the pictures are correctly associated with the proper cells, then when you sort, cut, copy, paste ranges, the pictures behave the way you would expect.

In order for Excel to manipulate the houses properly, they have to meet two criteria:

  1. The picture must be entirely inside of the cell. Using your code above, you would first place the picture as you have, and then adjust the row height and column width of the cell to be at least equal to the height and width of the picture.

  2. The shape.Placement property must be set to either xlMove or xlMoveAndSize. From MS documentation, the placement property can be one of the following: xlFreeFloating (3) Object is free floating. xlMove (2) Object is moved with the cells. xlMoveAndSize (1) Object is moved and sized with the cells.

If the picture is entirely inside of the cell and .Placement is set to xlMove or xlMoveAndSize, then whenever the container cell moves, the picture will move with it. If .Placement is set to xlMoveandSize, then the picture will expand or contract whenever the cell width or height is changed. This can be helpful because it makes sure the picture stays inside the boundaries of the cell. If you use xlMoveandSize, you might also want to consider setting ShapeRange.LockAspectRatio = msoTrue to prevent the picture getting distorted when column widths or row heights are adjusted.

So yes, Virginia, you can place a picture in a cell. Just make sure you place it carefully.

like image 160
tpkaplan Avatar answered Dec 04 '22 10:12

tpkaplan